Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-08-30
15
Medium Priority
?
4,787 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
Comment
Question by:logicmaker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
15 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19799868
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
ID: 19800571
apart from what bob said , sometimes date might be a character field too just check in case

0
 

Author Comment

by:logicmaker
ID: 19801754
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 19801918
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
ID: 19805806
try passing only the date portion rather than including the time portion also, I mean only date not datetime
0
 

Author Comment

by:logicmaker
ID: 19806985
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
ID: 19807258
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
ID: 19807319
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
ID: 19807451
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
ID: 19807473
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:
Bob Learned earned 1000 total points
ID: 19807537
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
ID: 19808362
OR even this

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

Expert Comment

by:suhashegde
ID: 19809832
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
ID: 19820427
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
ID: 19821479
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

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

618 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