7 Replies - 860 Views - Last Post: 22 May 2014 - 04:28 AM Rate Topic: -----

#1 skeepon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-May 14

Need help with the error"String was not recognized as valid DateTi

Posted 21 May 2014 - 11:40 PM

I'm using VB2010 with MySQL as the database. I need help with my code. My program is an employee database, it has the add, update, and delete functions. So far, the add and delete functions are working accordingly but I can't seem to make the 'update' function work. Whenever I click the button, the error message 'String was not recognized as a valid DateTime'. I think there's something wrong with the DateHired part, but I don't know how to fix it. Can anyone tell me how? Thanks!

Here is my source code for the update part:

Imports MySql.Data.MySqlClient

Public Class ViewRecord
    Public sConnection As New MySqlConnection
    Public id As Integer
    Private Sub ViewRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        If sConnection.State = ConnectionState.Closed Then
            sConnection.ConnectionString = "SERVER = localhost; USERID = root; PASSWORD=; DATABASE = cph; Convert Zero Datetime=True"
            sConnection.Open()


        End If

        LoadPeople()
    End Sub

    Public Sub LoadPeople()
        Dim sqlQuery As String = "SELECT * FROM employee_table"
        Dim sqlAdapter As New MySqlDataAdapter
        Dim sqlCommand As New MySqlCommand
        Dim TABLE As New DataTable
        Dim i As Integer

        With sqlCommand
            .CommandText = sqlQuery
            .Connection = sConnection
        End With

        With sqlAdapter
            .SelectCommand = sqlCommand
            .Fill(TABLE)

        End With

        'Listview fix
        ListView1.Items.Clear()


        For i = 0 To TABLE.Rows.Count - 1
            With ListView1
                .Items.Add(TABLE.Rows(i)("id"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(TABLE.Rows(i)("emp_no"))
                    .Add(TABLE.Rows(i)("emp_firstnm"))
                    .Add(TABLE.Rows(i)("emp_midnm"))
                    .Add(TABLE.Rows(i)("emp_lastnm"))
                    .Add(TABLE.Rows(i)("dept1"))
                    .Add(TABLE.Rows(i)("dept2"))
                    .Add(TABLE.Rows(i)("position"))
                    .Add(TABLE.Rows(i)("schedtype"))
                    .Add(TABLE.Rows(i)("date_hired"))
                    .Add(TABLE.Rows(i)("emp_stat"))
                    .Add(TABLE.Rows(i)("emp_type"))
                End With
            End With
        Next

    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        AddRecord.ShowDialog()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        If id = Nothing Then
            MsgBox("Please choose a record to edit", MsgBoxStyle.Exclamation)
        Else
            Dim sqlQuery As String = "SELECT emp_no, emp_firstnm, emp_midnm, emp_lastnm, dept1, dept2, position, schedtype, date_hired, emp_stat, emp_type FROM employee_table WHERE id='" & ListView1.SelectedItems(0).Text & "'"
            Dim sqlAdapter As New MySqlDataAdapter
            Dim sqlCommand As New MySqlCommand
            Dim sqlTable As New DataTable

            With sqlCommand
                .CommandText = sqlQuery
                .Connection = sConnection

            End With

            With sqlAdapter
                .SelectCommand = sqlCommand
                .Fill(sqlTable)
            End With

            UpdateRecord.id = ListView1.SelectedItems(0).Text
            UpdateRecord.emp_no = sqlTable.Rows(0)("emp_no")
            UpdateRecord.emp_firstnm = sqlTable.Rows(0)("emp_firstnm")
            UpdateRecord.emp_midnm = sqlTable.Rows(0)("emp_midnm")
            UpdateRecord.emp_lastnm = sqlTable.Rows(0)("emp_lastnm")
            UpdateRecord.dept1 = sqlTable.Rows(0)("dept1")
            UpdateRecord.dept2 = sqlTable.Rows(0)("dept2")
            UpdateRecord.position = sqlTable.Rows(0)("position")
            UpdateRecord.schedtype = sqlTable.Rows(0)("schedtype")
            UpdateRecord.date_hired = sqlTable.Rows(0)("date_hired")
            UpdateRecord.emp_stat = sqlTable.Rows(0)("emp_stat")
            UpdateRecord.emp_type = sqlTable.Rows(0)("emp_type")
            UpdateRecord.ShowDialog()

        End If
    End Sub

    Private Sub ListView1_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
        id = ListView1.SelectedItems(0).Text
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        If id = Nothing Then
            MsgBox("Please choose an item to delete", MsgBoxStyle.Exclamation)
        Else
            Dim sqlQuery As String = "DELETE FROM employee_table WHERE id='" & id & "'"
            Dim sqlCommand As New MySqlCommand
            Dim sqlTable As New DataTable

            With sqlCommand
                .CommandText = sqlQuery
                .Connection = sConnection
                .ExecuteNonQuery()
            End With
            MsgBox("Successfully deleted and item", MsgBoxStyle.Information)
            LoadPeople()
        End If

    End Sub
End Class




Is This A Good Question/Topic? 0
  • +

Replies To: Need help with the error"String was not recognized as valid DateTi

#2 astonecipher  Icon User is online

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,611
  • Joined: 03-December 12

Re: Need help with the error"String was not recognized as valid DateTi

Posted 22 May 2014 - 12:48 AM

Che k the format of the string in the variable. Is the column type a date/ datetime ?
Was This Post Helpful? 0
  • +
  • -

#3 skeepon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-May 14

Re: Need help with the error"String was not recognized as valid DateTi

Posted 22 May 2014 - 01:03 AM

It is in 'date' format
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is online

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,611
  • Joined: 03-December 12

Re: Need help with the error"String was not recognized as valid DateTi

Posted 22 May 2014 - 01:50 AM

Which date format? American, European, Unix, mysql?
Was This Post Helpful? 0
  • +
  • -

#5 skeepon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-May 14

Re: Need help with the error"String was not recognized as valid DateTi

Posted 22 May 2014 - 02:17 AM

It's mysql format sir
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6500
  • View blog
  • Posts: 26,288
  • Joined: 12-December 12

Re: Need help with the error"String was not recognized as valid DateTi

Posted 22 May 2014 - 02:37 AM

What line does the error refer to?
Where is UpdateRecord defined?
(Your code doesn't include an UPDATE statement.)
Was This Post Helpful? 0
  • +
  • -

#7 skeepon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-May 14

Re: Need help with the error"String was not recognized as valid DateTi

Posted 22 May 2014 - 02:42 AM

The error refers to the line ' UpdateRecord.id = ListView1.SelectedItems(0).Text'

Oh sorry I forgot to include the update one, here it is:

Imports MySql.Data.MySqlClient
Imports System.Globalization

Public Class UpdateRecord
    Friend id As Integer
    Friend emp_no As String
    Friend emp_firstnm As String
    Friend emp_midnm As String
    Friend emp_lastnm As String
    Friend dept1 As String
    Friend dept2 As String
    Friend position As String
    Friend schedtype As String
    Friend date_hired As DateTime = DateTime.ParseExact(date_hired, "dd-yyyy-MM", CultureInfo.InvariantCulture)
    Friend emp_stat As String
    Friend emp_type As String


    Public sConnection As New MySqlConnection
    Private Sub UpdateRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        txtEmpno.Text = emp_no
        txtFname.Text = emp_firstnm
        txtMname.Text = emp_midnm
        txtLname.Text = emp_lastnm
        txtDept1.Text = dept1
        txtDept2.Text = dept2
        txtPosition.Text = position
        txtSchedtype.Text = schedtype
        txtDatehired.Text = date_hired
        txtStatus.Text = emp_stat
        txtType.Text = emp_type


    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs)
        If sConnection.State = ConnectionState.Closed Then
            sConnection.ConnectionString = "SERVER = localhost; USERID = root; PASSWORD=; DATABASE = cph;Convert Zero Datetime=True"
            sConnection.Open()
        End If

        Dim sqlQuery As String = "UPDATE employee_table SET emp_no='" & txtEmpno.Text & _
            "', emp_firstnm='" & txtFname.Text & "', emp_midnm='" & txtMname.Text & _
            "', emp_lastnm='" & txtLname.Text & "', dept1='" & txtDept1.Text & _
            "', dept2='" & txtDept2.Text & "', position='" & txtPosition.Text & _
            "', schedtype='" & txtType.Text & "', date_hired='" & txtDatehired.Text & _
            "', emp_stat'" & txtStatus.Text & "', emp_type='" & txtType.Text &
            "' WHERE id='" & id & "'"
        Dim sqlCommand As New MySqlCommand

        With sqlCommand
            .CommandText = sqlQuery
            .Connection = sConnection
            .ExecuteNonQuery()
        End With

        MsgBox("Record updated successfully")
        Dispose()
        Close()

        'Update
        ViewRecord.LoadPeople()
    End Sub
End Class


Was This Post Helpful? 0
  • +
  • -

#8 astonecipher  Icon User is online

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,611
  • Joined: 03-December 12

Re: Need help with the error"String was not recognized as valid DateTi

Posted 22 May 2014 - 04:28 AM

If I could offer a suggestion more than a specific correction. Your update class is to tightly bound. Pass what you need to send to it instead of binding specific controls to it. It would make it far more modular; as would making an update method instead of an update class.

Your error probably refers to trying to pass a datetime to a string. Convert it from the datetime to a string, not the other way around.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1