Cant search Date from mySQL.

  • Thread starter Thread starter Kwok
  • Start date Start date
K

Kwok

Hi,

I search some record(s) according to specific date which
is took from datetimepicker, but a dataset.table(tbname).count
is zero.

here is my code. I use ODBC.net and Option strict On

dim seaSql as string
seaSql = "Select * from Orders where JobDate = @tmpJdate "

DBCom.CommandText = seaSql
DBCom.CommandType = CommandType.Text
DBCom.Parameters.Add("@tmpJDate", OdbcType.Date)

DBCom.Parameters("@tmpJDate").Value = seaJobDate.Value.Year.ToString & "-"
& _
seaJobDate.Value.Month.ToString & "-" & seaJobDate.Value.Day.ToString)
'because mySQL date Vale = YYYY-MM-DD

DBAdapter.SelectCommand = DBCom
DBAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
DBAdapter.Fill(seaJobSet, "Orders")

IS the code got any mistake??
I try the date value is "'" & "2003-9-15" & "'" or
"2003-9-15", but it also cant work.

Thanks
KWOK
 
You seem to have defined the parameter type as OdbcType.Date but the value
assigned to it is a string. Assuming that the MySql column is a date type,
try setting the value to 'seaJobDate.Value'.

Alternatively, if the column is a string type then add a parameter of type
string and size 10 (bytes).
 
Hi,

I think Neil is on to the problem. MySQL expects all references to dates
and times to be in the form of a string of format "YYYY-MM-DD" and
optionally "YYYY-MM-DD HH:MM:SS". I use the following code from a
MySQLDataBase class I wrote a while back:

public string ConvertDateToString (DateTime date){
string s = date.Year+"-";
if (date.Month<10) s=s+"0"; //Pad extra 0.
s=s+date.Month+"-";
if (date.Day<10) s=s+"0"; //Pad extra 0.
s=s+date.Day;
return s;
}

Now you can use the returned string in your SQL and everything should work
just fine.

Hope this helps.
 
Back
Top