logicmaker
asked on
C#, VFP, ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.
I am trying to pass a DateTime value to my FoxPro database in C# and getting the following exception
ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.
I have tried passing value in many different ways such via query string, or inline with query but failed.
I am only getting this exception while passing the DateTime, I can pass any other data type in the query.
Your help is much appreciated.
ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.
I have tried passing value in many different ways such via query string, or inline with query but failed.
I am only getting this exception while passing the DateTime, I can pass any other data type in the query.
Your help is much appreciated.
apart from what bob said , sometimes date might be a character field too just check in case
ASKER
In Foxpro the field is specified as Date(8) "YYYYMMDD" however from C# i am trying to pass the DateTime object as a parameter. I have also tried passing date as a string (theDate.ToString("yyyyMMd d") ) with the same date format but does not work.
If the FoxPro field is a date, then you need to pass in the arguments with the delimiters that I showed, and not a string.
Bob
Bob
try passing only the date portion rather than including the time portion also, I mean only date not datetime
ASKER
I have tried passing with delimiter but no gain, this is how the date in my foxpro db looks like this : 20070718
I have tried passing date in a string similar but did not work.
This is how I am initilizating my date time object
System.DateTime dt1 = new System.DateTime(2007, 7, 18);
If possible can any of you post a code snippet here, coz I may be making a crucial mistake somewhere in the code.
I have tried passing date in a string similar but did not work.
This is how I am initilizating my date time object
System.DateTime dt1 = new System.DateTime(2007, 7, 18);
If possible can any of you post a code snippet here, coz I may be making a crucial mistake somewhere in the code.
Do you have a string field instead of a date field in FoxPro? What string value were you passing? If you need a string in the format yyyyMMdd, then you can pass like this:
string s = dt1.ToString("yyyyMMdd");
Bob
string s = dt1.ToString("yyyyMMdd");
Bob
ASKER
In foxpro the field is defined as date not as string. Still I have tried following code
[thisCommand.CommandText = "select * from CMS where odr_date > " + dt1.ToString("yyyyMMdd") +" ;";]
with following exception
[ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch]
I have also tried following code
thisCommand.CommandText = "select * from CMS where odr_date > @sdate ;";
thisCommand.Parameters.Add ("@sdate", System.Data.Odbc.OdbcType. DateTime). Value = dt1;
with this exception
[ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Missing operand]
[thisCommand.CommandText = "select * from CMS where odr_date > " + dt1.ToString("yyyyMMdd") +" ;";]
with following exception
[ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch]
I have also tried following code
thisCommand.CommandText = "select * from CMS where odr_date > @sdate ;";
thisCommand.Parameters.Add
with this exception
[ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Missing operand]
where did you see the field type for date ?
In foxpro ?
Or after retriving it in c# ?
Also there might be just 1 more way
Just do the following
Select * from CMS where Recno() = 1
Check the type of odr_date what id returned
then
Select * from CMS where odr_date = date
DAte must be the one returned in the select statment with recno() = 1 exactly the same
In foxpro ?
Or after retriving it in c# ?
Also there might be just 1 more way
Just do the following
Select * from CMS where Recno() = 1
Check the type of odr_date what id returned
then
Select * from CMS where odr_date = date
DAte must be the one returned in the select statment with recno() = 1 exactly the same
Or on second thought
thisCommand.CommandText = "select * from CMS where odr_date > " + dt1.ToString("yyyyMMdd") ;
Might just work
There are no ; at the end of the select statement in VFP
thisCommand.CommandText = "select * from CMS where odr_date > " + dt1.ToString("yyyyMMdd") ;
Might just work
There are no ; at the end of the select statement in VFP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OR even this
thisCommand.CommandText = "select * from CMS where odr_date > " + dt1.ToString("{^yyyy-MM-dd }") ;
thisCommand.CommandText = "select * from CMS where odr_date > " + dt1.ToString("{^yyyy-MM-dd
using System;
using System.Collections.Generic ;
using System.Text;
using System.Data.OleDb;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
OleDbCommand cmd=new OleDbCommand();
OleDbConnection conn=new OleDbConnection();
System.DateTime dt = new DateTime(2007,8,30);
conn.ConnectionString="Pro vider=VFPO LEDB.1;Dat a Source=c:\\stocks\\;Collat ing Sequence=MACHINE";
cmd.CommandText = "select symbol from bhav_data where date = " + dt.ToString("{^yyyy-MM-dd} ");
conn.Open();
cmd.Connection=conn;
int ret = cmd.ExecuteNonQuery();
Console.WriteLine("returne d Records {0}",ret);
Console.WriteLine("Press enter to exit");
Console.ReadLine();
}
}
}
Returns 1134 records from by table (It is a free table and has date as field Date)
using System.Collections.Generic
using System.Text;
using System.Data.OleDb;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
OleDbCommand cmd=new OleDbCommand();
OleDbConnection conn=new OleDbConnection();
System.DateTime dt = new DateTime(2007,8,30);
conn.ConnectionString="Pro
cmd.CommandText = "select symbol from bhav_data where date = " + dt.ToString("{^yyyy-MM-dd}
conn.Open();
cmd.Connection=conn;
int ret = cmd.ExecuteNonQuery();
Console.WriteLine("returne
Console.WriteLine("Press enter to exit");
Console.ReadLine();
}
}
}
Returns 1134 records from by table (It is a free table and has date as field Date)
ASKER
Many Thanks guys, specially Bob. It worked
Can you please take time to explain how did you format the string? slightly confused.
Regards
Can you please take time to explain how did you format the string? slightly confused.
Regards
To add through the parameter way
thisCommand.CommandText = "select * from CMS where odr_date > ? ";
thisCommand.Parameters.Add ("?",Syste m.Data.Odb c.OdbcType .Date)
thisCommand.Parameters[0]. Value = dt1.date;
if more parameter
select * from CMS where odr_date > ? and abcd = ? and cdf = ? ";
The oledbdriver supports only ? without name or @
and the index of the parameter array would be passed in sequence i.e. 0 - first ,1 - second ,...
thisCommand.CommandText = "select * from CMS where odr_date > ? ";
thisCommand.Parameters.Add
thisCommand.Parameters[0].
if more parameter
select * from CMS where odr_date > ? and abcd = ? and cdf = ? ";
The oledbdriver supports only ? without name or @
and the index of the parameter array would be passed in sequence i.e. 0 - first ,1 - second ,...
{^yyyy-mm-dd}
Bob