OdbcConnection - Querying MySql tables using date parameters

  • Thread starter Thread starter Ben Fidge
  • Start date Start date
B

Ben Fidge

Hi

I'm trying to query a MySql database using OdbcConnection. My query looks
like so:

select Count(*) from Table1 where BookingDate = @Date

I'm using an OdbcParameter set up as such:

OdbcParamter oParam = oCmd.Parameters.Add("@Date", OdbcType.Date);
oParam.Value = dDate.Date;

Where dDate = new DateTime(2005, 11, 4);

I know there to be records matching this query in the database yet nothing
is returned. I'm new to MySql, coming from a SqlServer background. Am I
missing something obvious?

Thanks

Ben
 
I am nto sure, but if your value is stored as a date/tiem field in the
database you may not get a match because the parameter and the database
columns have different tiem stamps but the same date value.

I always have trouble with this myself, as different databases seem to
handle it differently, but try forcing the date to a specific format on both
ends. Mainly, truncate the date on the mysql side to insure that you are
not returning any time value then do the same with the C# code (I think you
are already), or convert the date to a string value on both ends.
 
Thanks for your response Jim.

While I was waiting, I tried issuing another query, and it appears that it
doesn't like using paramters. For example, the following worked:

select count(*) from Table1 where ShowNo = 1

However, this didn't

select count(*) from Table1 where ShowNo = 1

...

OdbcParamter oParam = oCmd.Parameters.Add("@ShowNo", OdbcType.Int);
oParam.Value = 1;

Any ideas where I'm going wrong here?

Ben
 
Again... I am not sure... but I think you need to use a ? where your
parameters are, and then set them in the order they appear...
 
Excellent, it works. I'm quite concerned about using ? to denote parameters,
however. It takes a lot of discipline to make sure everything is in the
right order. Especially if you're likely to change the query in the future.

I'm really missing Sql Server!!

Ben
 
Glad to hear you have it working.

Please post a sample of your final code, so others can see what it looks
like when it is completed.
 
Back
Top