Solved

MySQL VB.NET datetime conversion issues

Posted on 2011-03-16
2
792 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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