Solved

MySQL VB.NET datetime conversion issues

Posted on 2011-03-16
2
777 Views
Last Modified: 2012-05-11
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()

Open in new window

0
Comment
Question by:pdi656
2 Comments
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
Comment Utility
Hi,

Have you tried to NOT use Format function and only get the Test_Date Field as Is?

So, instead of this
Format(Test_Date, 'yyyy-MM-dd hh:mm:ss') AS Test_Date

Use just this in both Select Statments
Test_Date
0
 
LVL 1

Author Comment

by:pdi656
Comment Utility
Hello again! This code should look familiar to you. I should have known that when one problem is fixed, there always another one right behind it.

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!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now