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);"
cmd = New MySqlCommand(strSQL, cnM)
rst1 = cmd.ExecuteReader()
dtTmp.Load(rst1) 'loads the temp datatable
rst1 = Nothing
cmd = Nothing
'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
'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"
Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)
Dim dcbCommand As New MySqlCommandBuilder(daAdapter)
dcbCommand = Nothing