Solved

C#, VFP,  ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.

Posted on 2007-08-30
4,172 Views
Last Modified: 2008-01-09
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.
0
Question by:logicmaker
    15 Comments
     
    LVL 96

    Expert Comment

    by:Bob Learned
    How are you delimiting the date?  It is important to use the date delimiters that FoxPro expects in the query string.

       {^yyyy-mm-dd}

    Bob
    0
     
    LVL 8

    Expert Comment

    by:suhashegde
    apart from what bob said , sometimes date might be a character field too just check in case

    0
     

    Author Comment

    by:logicmaker
    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("yyyyMMdd") ) with the same date format but does not work.
    0
     
    LVL 96

    Expert Comment

    by:Bob Learned
    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
    0
     
    LVL 8

    Expert Comment

    by:suhashegde
    try passing only the date portion rather than including the time portion also, I mean only date not datetime
    0
     

    Author Comment

    by:logicmaker
    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.
    0
     
    LVL 96

    Expert Comment

    by:Bob Learned
    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
    0
     

    Author Comment

    by:logicmaker
    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]
    0
     
    LVL 8

    Expert Comment

    by:suhashegde
    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





    0
     
    LVL 8

    Expert Comment

    by:suhashegde
    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
    0
     
    LVL 96

    Accepted Solution

    by:
    What happened when you did this?

    thisCommand.CommandText = string.Format("Select * From CMS Where odr_date > {{^{0}}}", dt1.ToString("yyyy-MM-dd"));

    Bob
    0
     
    LVL 8

    Expert Comment

    by:suhashegde
    OR even this

    thisCommand.CommandText = "select * from CMS where odr_date > " + dt1.ToString("{^yyyy-MM-dd}") ;
    0
     
    LVL 8

    Expert Comment

    by:suhashegde
    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="Provider=VFPOLEDB.1;Data Source=c:\\stocks\\;Collating 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("returned 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)
    0
     

    Author Comment

    by:logicmaker
    Many Thanks guys, specially Bob. It worked
    Can you please take time to explain how did you format the string? slightly confused.

    Regards
    0
     
    LVL 8

    Expert Comment

    by:suhashegde
    To add through the parameter way
    thisCommand.CommandText = "select * from CMS where odr_date > ? ";

     thisCommand.Parameters.Add("?",System.Data.Odbc.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 ,...
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    693 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now