Cant Insert date in mysql date attribute

Question

I have a asp.net project where i have to get input from the user and insert it into date attribute in my database. but instead I have only 0000/00/00. I can't understand why.

the input comes from 3 text boxes. Than i concatenate them and pass it to the query. But something goes wrong. here is the code:

Bday = Month2.Text & "/" & Dates2.Text & "/" & years.Text
Dim StrQwery As String = "INSERT INTO account VALUES(accoint_id, '" & Bday &"')"
Dim smd As MySqlCommand = New MySqlCommand(stquery, myconn)
smd.ExecuteReader()

The same thing happens when I add time to the same string and want to pass it to the dateTime attribute. Can someone tell me what is wrong?

regTime = Month2.Text & "/" & Dates2.Text & "/" & years.Text & CStr(TimeValue(CStr(Now)))
Dim StrQwery As String = "INSERT INTO account VALUES(accoint_id, '" & regTime &"')"
Dim smd As MySqlCommand = New MySqlCommand(stquery, myconn)
smd.ExecuteReader()

Try to use the [**prepared statements**](http://dev.mysql.com/doc/refman/5.0/en/connector-net-programming-prepared.html), [Prepared Statements in VB.NET](http://stackoverflow.com/questions/7351135/prepared-statements-in-vb-net) — Mahmoud Gamal, Apr 27 '13 at 21:46

score 0 · Answer 1 · answered Apr 27 '13 at 21:44

0

MySQL's date-as-string format is yyyy-mm-hh and yyyy-mm-dd hh:mm:ss for datetime. If you insert anything else, like your xxxx/xx/xx, MySQl will not even TRY to guess what format it's in, and simply insert a 0000-00-00 to signal an invalid date.

This is not something you can change. Convert your dates to MySQL's standard format, or deal with the consequences.

answered Apr 27 '13 at 21:44

Marc B

356,200
43
426
500

I've triede several versions including yyyy-mm-dd. Still inserts zeroes – meks Apr 27 '13 at 21:53
dump out the query string you're generating, and see what you're ACTUALLY using. e.g `sql = "INSERT INTO...."` then print out that var. – Marc B Apr 27 '13 at 21:57
But what difference it would make? – meks Apr 27 '13 at 22:48
How can I print the variable if I didn't concatenate 3 strings first? – meks Apr 27 '13 at 22:51
it's called basic debugging. learn how to do it, or you'll never get anywhere with your coding. – Marc B Apr 28 '13 at 00:41

Cant Insert date in mysql date attribute

1 Answers1