burnerxp
Posts: 1
|
| Posted: 09/13/2006, 6:04 PM |
|
Helping one of my friends who is using Codecharge Studio with a VFP ODBC data freetables the connection Server Is set to ANSI SQL 92, date searching gives the following error:
Form: Report recent
Error: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch. (Microsoft OLE DB Provider for ODBC Drivers)
I have tried changing all the things such as connection server date formats, I have confirmed that the field is in fact a foxpro date type, and i can create a connection manually and display the following results:
Invoice: 152561
Date: 8/6/2006
Is date ?: True
Date: -1 Month 7/6/2006
VarType 7
using this ASP coding:
Dim Connection3
Set Connection3 = New clsDBConnection2
Connection3.Open
response.Write " Invoice: " & CCDLookUp("invoice","recent","booth='109'" ,Connection3)& "<br />"
response.Write " Date: " & CCDLookUp("date","recent","booth='109'",Connection3)& "<br />"
response.write " Is date ?: " & IsDate(CCDLookUp("date","recent","booth='109'",Connection3)) & "<br />"
response.Write " Date: -1 Month " & Dateadd("m",-1,CCDLookUp("date","recent","booth='109'",Connection3))& "<br />"
response.Write " VarType " & VarType(Dateadd("m",-1,CCDLookUp("date","recent","booth='109'",Connection3)))& "<br />"
Connection3.Close
Set Connection3 = Nothing
I have tried using the report generator to create the report using a existing ODBC connection to the dir containing the DBF files and we cannot get a date filter to work, i have tried using a variety of changes to the SQL manually or using the visual SQL designer with no luck, I know CCS see's the data as a date because I can confirm that with the above code date math etc., my only conclusion is that there is a problem with the link between CCS and the ODBC driver, I can put a linked table into an Acxess MDB file and it works fine, but with performance losses, I guess Access handles the date translation internally. I have also added a label control to the search form which which tells me that the Form Control s_date is not a Date but it is set as a date data type using the following code attached to the before show event of the form and the onClick event of the Search Button:
recentSearch.Label1.Value = isDate(recentSearch.s_date) it always says No.
Do i need to convert the data type of the s_date used to query the date parameter? or maybe convert the date into string in the actual query, I read over the internet that some Foxpro date query's need brackets surrounding the date value but I have had no luck with that either. Thanks for any help someone can give, I have been programming for some 20yrs in a variety of languages and i really stumped this time. It is pretty obvious that there is a data mismatch, i just am not sure where the mismatch is, inside the code CCS generates or in the ODBC driver itself.
|
 |
 |
|
