Can't save Date?

[zimzon]

hi,

im MySQL Server as backend. I have the code given below to add record into a table. When I check new record in table, Date field saved as "0000-00-00" but other one is ok....

What is the problem?...

MySQL server ver is 5.0.18 and i'm using MySQL/Connector for .NET.

Pls help me......

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] InsertRow([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] XRD [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] XRV [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Double[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myInsertQuery [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"INSERT INTO xrates (XRDate, XRValue) VALUES ('"[/COLOR][/SIZE][SIZE=2] & XRD & [/SIZE][SIZE=2][COLOR=#800000]"', '"[/COLOR][/SIZE][SIZE=2] & XRV & [/SIZE][SIZE=2][COLOR=#800000]"')"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myXRInsert [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] MySqlCommand(myInsertQuery, myCon)
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]myXRInsert.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] MySqlException[/SIZE][SIZE=2]
DBError(ex.Number, AppDBUser.ToString)
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]myXRInsert.Connection.Close()
myXRInsert.Dispose()
SelectRow()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

 

[lingsn]

May have to use this sql

[COLOR=#0000ff]Dim[/COLOR][SIZE=2] myInsertQuery [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"INSERT INTO xrates (XRDate, XRValue) VALUES ('"[/COLOR][/SIZE][SIZE=2] & Format(XRD, "yyyyMMdd") & [/SIZE][SIZE=2][COLOR=#800000]"', '"[/COLOR][/SIZE][SIZE=2] & XRV & [/SIZE][SIZE=2][COLOR=#800000]"')"[/COLOR][/SIZE]

or use jmcilhinney's recommendation in the thread
http://www.vbdotnetforums.com/showthread.php?t=15955

 

[jmcilhinney]

NEVER insert literal values into an SQL statement if it can be avoided. ALWAYS use parameters, as per that other thread mentioned. There are a number of reasons for this. Apart from your date issue you're also quoting a numeric value. Issues like that go away when you use parameters too.

 

[cjard]

zimzon,
Youre sending a string to the database and hoping it can interpret it as a date.. You should instead find out how dates are to be send to MySQL. Most reliably this would be in the form of an SQL parameter of whatever type the MySQL driver thinks is a Date..

 

[zimzon]

Thanx its working