pdi656
asked on
MySQL VB.NET datetime conversion issues
In the following code, I am using VB.NET to query data from one MySQL database and inserting the records into another MySQL database on another server. The issue I am encountering is that MySQL holds the datetime (Test_Date) value in this format YYYY-MM-DD hh:mm:ss. When I bring it into a datatable in VB.Net, the format changes to MM-DD-YYYY hh:mm:ss. Subesquently, when I attempt to insert to the new datatable, the date is not updated, as MySQL doesn't know how to convert the VB.NET date to it's format. Does anyone know how to either convert the datatable column to the MySQL format or somehow render the SELECT results in the proper MySQL format (and I can perhaps store it as a string)? I tried predefining the columns (setting Test_Date to a string), but that didn't work.
'SELECT the master data to be archived into a temp datatable
dtTmp.Clear() 'clears the temp datatable of data
strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Format(Test_Date, 'yyyy-MM-dd hh:mm:ss') AS Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
" FROM tbllkt_trans_log" & _
" WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
cnM.Open()
cmd = New MySqlCommand(strSQL, cnM)
rst1 = cmd.ExecuteReader()
dtTmp.Load(rst1) 'loads the temp datatable
rst1 = Nothing
cmd = Nothing
cnM.Close()
'Loop the rows of the datatable and set the row status to ADDED
'NOTE This is required for the subsequent INSERT to work.
For Each dr As DataRow In dtTmp.Rows
dr.SetAdded()
Next
'INSERT the master temp datatable to the archive database
strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Format(Test_Date, 'yyyy-MM-dd hh:mm:ss') AS Test_Date, Test_Cell, Comp_ID" & _
" FROM tbllkt_trans_archive"
cnA.Open()
Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)
Dim dcbCommand As New MySqlCommandBuilder(daAdapter)
daAdapter.Update(dtTmp)
daAdapter.Dispose()
dcbCommand = Nothing
cnA.Close()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did try it without the FORMAT statement in the SQL that loads the temp datatable, but I never removed it from the SQL that inserts it into the second db. When I removed the FORMAT from both, it seemed to work - I put in a few test transaction and they all came over with dates. Hopefully I'll have the same results tonight when the routine tries to ship over 4k records.
I can't believe I missed that. I must have played with this for 4 hours yesterday. Thank you so much for your help!