Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL VB.NET datetime conversion issues

Posted on 2011-03-16
2
Medium Priority
?
796 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 2000 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

618 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