Solved

MySQL VB.NET datetime conversion issues

Posted on 2011-03-16
2
784 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
ID: 35152165
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
ID: 35155775
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Library to convert HTML to PDF. 8 57
Hide Tab Page 3 34
Get String split 5 47
Get size of each directory on each mapped drive 5 21
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

21 Experts available now in Live!

Get 1:1 Help Now