none
I get this error when updating sql database with vb,net Conversion failed when converting date and/or time from character string. RRS feed

  • Question

  • I am using SQL SERVER EXPRESS 2008. And VB.NET 2010 pro. I am using Class Objects as datasource.

    the code makes it all the way un till it hits the command open statement and then i get the error FYI

    I have a different table and it has np problems with the dates i set both tables in sql as date because

    thats all we need is to store dates and not time. My data is stored in Bindingsources. How can I convert

    the dates before I do the update.

    updateCommand.Parameters.AddWithValue("@NewR_DATE", newRider.R_DATE)

    the value in this object class "newRider.R_DATE " = "#01/22/1956#" my birthday. :) This value comes from a textBox on a form and from user input.

    I have been fighting this error for weeks.

    I get this error when updating sql database with vb,net Conversion failed when converting date and/or time from character string.

    is there away to control how the data is stored in the textBoxes on a form, so i would not have to do conversions. Like I

    said above I have no problems from the other table.

    Thanks


    Poppygb

    Thursday, January 10, 2013 5:44 AM

Answers

  • Hi Poppy,

    Please use this instead to check if it works or not:

    Dim updateCommand As New SqlCommand(updateStatement, connection)
            updateCommand.Parameters.AddWithValue("@NewLAST_NAME", newRider.LAST_NAME)
            updateCommand.Parameters.AddWithValue("@NewFIRST_NAME", newRider.FIRST_NAME)
            updateCommand.Parameters.AddWithValue("@NewR_DATE", Convert.ToDateTime(newRider.R_DATE))
    ...

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by PoppyGB Wednesday, January 16, 2013 12:44 AM
    Monday, January 14, 2013 9:27 AM

All replies

  • Have you tried using Convert.ToDateTime to convert the string value to a Date data type variable that you can use in the AddWithValue statement?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 10, 2013 2:41 PM
  • Paul

    Please for give me for posting all this code. Your solution is what I need, but I

    don't know where to place the code. I have a validation sub. I have posted it also.

    this is thing is making me sick. I am about to loose this project. My family is

    depending on me.

    Could you give an example on how to do this? And where to place the code.

    I am using class object 3 layered program. Below in the class I use also.

    THANKS THANKS SO MUCH.

    Imports DCTS_CLASS Public Class frmaddModifyRiders Public addrider As Boolean Private oldrider As Riders Private newrider As Riders Private Sub frmAddModifyTrips_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load If addrider Then Me.Text = "Add Riders" newrider = New Riders Else Me.Text = "Modify Trip" oldrider = frmRiders.riders newrider = New Riders Me.PutNewRider() End If RidersBindingSource.Add(newrider) End Sub Private Sub PutNewRider() newrider.RIDER_NO = oldrider.RIDER_NO newrider.LAST_NAME = oldrider.LAST_NAME newrider.FIRST_NAME = oldrider.FIRST_NAME newrider.R_DATE = oldrider.R_DATE newrider.UP_DATE = oldrider.UP_DATE newrider.MONTH = oldrider.MONTH newrider.STREET = oldrider.STREET newrider.CITY = oldrider.CITY newrider.ST = oldrider.ST newrider.ZIP = oldrider.ZIP newrider.COUNTY = oldrider.COUNTY newrider.AC = oldrider.AC newrider.PHONE = oldrider.PHONE newrider.ALT_PHONE = oldrider.ALT_PHONE newrider.DOB = oldrider.DOB newrider.AGE = oldrider.AGE newrider.HDCAP = oldrider.HDCAP newrider.DCN = oldrider.DCN newrider.LIFT = oldrider.LIFT newrider.CHILD = oldrider.CHILD newrider.SSAN = oldrider.SSAN newrider.RURAL = oldrider.RURAL newrider.URBAN = oldrider.URBAN newrider.CD = oldrider.CD newrider.AREA = oldrider.AREA newrider.HSE = oldrider.HSE newrider.SEX = oldrider.SEX newrider.RACE = oldrider.RACE newrider.EMER_CONT = oldrider.EMER_CONT newrider.EMER_A_C = oldrider.EMER_A_C newrider.EMER_PHONE = oldrider.EMER_PHONE newrider.COMMENTS = oldrider.COMMENTS End Sub Private Sub btnAccept_Click(sender As System.Object, e As System.EventArgs) Handles btnAccept.Click If IsValidData() Then If addrider Then Try newrider.RIDER_NO = RidersDB.AddRiders(newrider) frmRiders.riders = newrider Me.DialogResult = DialogResult.OK Catch ex As Exception MessageBox.Show(ex.Message, ex.GetType.ToString) End Try Else Try If RidersDB.UpdateRiders(oldrider, newrider) Then frmRiders.riders = newrider Me.DialogResult = DialogResult.OK Else MessageBox.Show("Another user has updated or deleted " & "that vendor.", "Database Error") Me.DialogResult = DialogResult.Retry End If Catch ex As Exception MessageBox.Show(ex.Message, ex.GetType.ToString) End Try End If End If End Sub Private Function IsValidData() As Boolean If Validator.IsPresent(LAST_NAMETextBox) AndAlso Validator.IsPresent(FIRST_NAMETextBox) Then Return True Else Return False End If End Function End Class

    Public Class Validator
        Private Shared m_Title As String = "Entry Error"
    
        Public Shared Property Title() As String
            Get
                Return m_Title
            End Get
            Set(ByVal value As String)
                m_Title = value
            End Set
        End Property
    
        Public Shared Function IsPresent(ByVal control As Control) As Boolean
            If control.GetType.ToString = "System.Windows.Forms.TextBox" Then
                Dim textBox As TextBox = CType(control, TextBox)
                If textBox.Text = "" Then
                    MessageBox.Show(textBox.Tag.ToString & " is a required field.", Title)
                    textBox.Select()
                    Return False
                Else
                    Return True
                End If
            ElseIf control.GetType.ToString = "System.Windows.Forms.ComboBox" Then
                Dim comboBox As ComboBox = CType(control, ComboBox)
                If comboBox.SelectedIndex = -1 Then
                    MessageBox.Show(comboBox.Tag.ToString & " is a required field.", Title)
                    comboBox.Select()
                    Return False
                Else
                    Return True
                End If
            End If
            Return True
        End Function


    Public Shared Function UpdateRiders(ByVal oldRider As Riders, ByVal newRider As Riders) As Boolean Dim connection As SqlConnection = dctsConnect.GetConnection Dim updateStatement As String = "UPDATE RIDERS SET " & "LAST_NAME = @NewLAST_NAME, " & "FIRST_NAME = @NewFIRST_NAME, " & "R_DATE = @NewR_DATE, " & "UP_DATE = @NewUP_DATE, " & "MONTH = @NewMONTH, " & "STREET = @NewSTREET, " & "CITY = @NewCITY, " & "ST = @NewST, " & "ZIP = @NewZIP, " & "PHONE = @NewPHONE, " & "COUNTY = @NewCOUNTY, " & "AC = @NewAC, " & "ALT_PHONE = @NewALT_PHONE, " & "DOB = @NewAC, " & "AGE = @NewAGE, " & "HDCAP = @NewHDCAP, " & "CD = @NewCD, " & "DCN = @NewDCN, " & "LIFT = @NewLIFT, " & "CHILD = @NewCHILD, " & "SSAN = @NewSSAN, " & "RURAL = @NewRURAL, " & "URBAN = @NewURBAN, " & "AREA = @NewAREA, " & "HSE = @NewHSE, " & "SEX = @NewSEX, " & "RACE = @NewRACE, " & "EMER_CONT = @NewEMER_CONT, " & "EMER_A_C = @NewEMER_A_C, " & "EMER_PHONE = @NewEMER_PHONE, " & "COMMENTS = @NewCOMMENTS " & "WHERE RIDER_NO = @OldRIDER_NO " & "AND LAST_NAME = @OldLAST_NAME " & "AND FIRST_NAME = @OldFIRST_NAME " & "AND R_DATE = @OldR_DATE " & "AND UP_DATE = @OldUP_DATE " & "AND MONTH = @OldMONTH " & "AND STREET = @OldSTREET " & "AND CITY = @OldCITY " & "AND ST = @OldST " & "AND ZIP = @OldZIP " & "AND COUNTY = @OldCOUNTY " & "AND AC = @OldAC " & "AND PHONE = @OldPHONE " & "AND ALT_PHONE = @OldALT_PHONE " & "AND DOB = @OldDOB " & "AND AGE = @OldAGE " & "AND HDCAP = @OldHDCAP " & "AND CD = @OldCD " & "AND DCN = @OldDCN " & "AND LIFT = @OldLIFT " & "AND CHILD = @OldCHILD " & "AND RURAL = @OldRURAL " & "AND URBAN = @OldURBAN " & "AND AREA = @OldAREA " & "AND HSE = @OldHSE " & "AND SEX = @OldSEX " & "AND RACE = @OldRACE " & "AND EMER_CONT = @OldEMER_CONT " & "AND EMER_A_C = @OldEMER_A_C " & "AND EMER_PHONE = @OldEMER_PHONE " & "AND COMMENTS = @OldCOMMENTS" Dim updateCommand As New SqlCommand(updateStatement, connection) updateCommand.Parameters.AddWithValue("@NewLAST_NAME", newRider.LAST_NAME) updateCommand.Parameters.AddWithValue("@NewFIRST_NAME", newRider.FIRST_NAME) updateCommand.Parameters.AddWithValue("@NewR_DATE", newRider.R_DATE) updateCommand.Parameters.AddWithValue("@NewUP_DATE", newRider.UP_DATE) updateCommand.Parameters.AddWithValue("@NewMONTH", newRider.MONTH) updateCommand.Parameters.AddWithValue("@NewSTREET", newRider.STREET) updateCommand.Parameters.AddWithValue("@NewCITY", newRider.CITY) updateCommand.Parameters.AddWithValue("@NewST", newRider.ST) updateCommand.Parameters.AddWithValue("@NewZIP", newRider.ZIP) updateCommand.Parameters.AddWithValue("@NewCOUNTY", newRider.COUNTY) updateCommand.Parameters.AddWithValue("@NewAC", newRider.AC) updateCommand.Parameters.AddWithValue("@NewPHONE", newRider.PHONE) updateCommand.Parameters.AddWithValue("@NewALT_PHONE", newRider.ALT_PHONE) updateCommand.Parameters.AddWithValue("@NewDOB", newRider.DOB) updateCommand.Parameters.AddWithValue("@NewAGE", newRider.AGE) updateCommand.Parameters.AddWithValue("@NewHDCAP", newRider.HDCAP) updateCommand.Parameters.AddWithValue("@NewLIFT", newRider.LIFT) updateCommand.Parameters.AddWithValue("@NewDCN", newRider.DCN) updateCommand.Parameters.AddWithValue("@NewCD", newRider.CD) updateCommand.Parameters.AddWithValue("@NewCHILD", newRider.CHILD) updateCommand.Parameters.AddWithValue("@NewSSAN", newRider.SSAN) updateCommand.Parameters.AddWithValue("@NewRURAL", newRider.RURAL) updateCommand.Parameters.AddWithValue("@NewURBAN", newRider.URBAN) updateCommand.Parameters.AddWithValue("@NewAREA", newRider.AREA) updateCommand.Parameters.AddWithValue("@NewHSE", newRider.HSE) updateCommand.Parameters.AddWithValue("@NewSEX", newRider.SEX) updateCommand.Parameters.AddWithValue("@NewRACE", newRider.RACE) updateCommand.Parameters.AddWithValue("@NewEMER_CONT", newRider.EMER_CONT) updateCommand.Parameters.AddWithValue("@NewEMER_A_C", newRider.EMER_A_C) updateCommand.Parameters.AddWithValue("@NewEMER_PHONE", newRider.EMER_PHONE) updateCommand.Parameters.AddWithValue("@NewCOMMENTS", newRider.COMMENTS) updateCommand.Parameters.AddWithValue("@OldRIDER_NO", oldRider.RIDER_NO) updateCommand.Parameters.AddWithValue("@OldLAST_NAME", oldRider.LAST_NAME) updateCommand.Parameters.AddWithValue("@OldFIRST_NAME", oldRider.FIRST_NAME) updateCommand.Parameters.AddWithValue("@OldR_DATE", oldRider.R_DATE) updateCommand.Parameters.AddWithValue("@OldUP_DATE", oldRider.UP_DATE) updateCommand.Parameters.AddWithValue("@OldMONTH", oldRider.MONTH) updateCommand.Parameters.AddWithValue("@OldSTREET", oldRider.STREET) updateCommand.Parameters.AddWithValue("@OldCITY", oldRider.CITY) updateCommand.Parameters.AddWithValue("@OldST", oldRider.ST) updateCommand.Parameters.AddWithValue("@OldZIP", oldRider.ZIP) updateCommand.Parameters.AddWithValue("@OldCOUNTY", oldRider.COUNTY) updateCommand.Parameters.AddWithValue("@OldAC", oldRider.AC) updateCommand.Parameters.AddWithValue("@OldPHONE", oldRider.PHONE) updateCommand.Parameters.AddWithValue("@OldALT_PHONE", oldRider.ALT_PHONE) updateCommand.Parameters.AddWithValue("@OldDOB", oldRider.DOB) updateCommand.Parameters.AddWithValue("@OldAGE", oldRider.AGE) updateCommand.Parameters.AddWithValue("@OldHDCAP", oldRider.HDCAP) updateCommand.Parameters.AddWithValue("@OldCD", oldRider.CD) updateCommand.Parameters.AddWithValue("@OldLIFT", oldRider.LIFT) updateCommand.Parameters.AddWithValue("@OldDCN", oldRider.DCN) updateCommand.Parameters.AddWithValue("@OldCHILD", oldRider.CHILD) updateCommand.Parameters.AddWithValue("@OldSSAN", oldRider.SSAN) updateCommand.Parameters.AddWithValue("@OldRURAL", oldRider.RURAL) updateCommand.Parameters.AddWithValue("@OldURBAN", oldRider.URBAN) updateCommand.Parameters.AddWithValue("@OldAREA", oldRider.AREA) updateCommand.Parameters.AddWithValue("@OldHSE", oldRider.HSE) updateCommand.Parameters.AddWithValue("@OldSEX", oldRider.SEX) updateCommand.Parameters.AddWithValue("@OldRACE", oldRider.RACE) updateCommand.Parameters.AddWithValue("@OldEMER_CONT", oldRider.EMER_CONT) updateCommand.Parameters.AddWithValue("@OldEMER_A_C", newRider.EMER_A_C) updateCommand.Parameters.AddWithValue("@OldEMER_PHONE", newRider.EMER_PHONE) updateCommand.Parameters.AddWithValue("@OldCOMMENTS", oldRider.COMMENTS) Try connection.Open() Dim count As Integer = updateCommand.ExecuteNonQuery If count > 0 Then Return True Else Return False End If Catch ex As SqlException Throw ex Finally connection.Close() End Try End Function



    Poppygb

    Thursday, January 10, 2013 6:19 PM
  • Hi Poppy,

    Please use this instead to check if it works or not:

    Dim updateCommand As New SqlCommand(updateStatement, connection)
            updateCommand.Parameters.AddWithValue("@NewLAST_NAME", newRider.LAST_NAME)
            updateCommand.Parameters.AddWithValue("@NewFIRST_NAME", newRider.FIRST_NAME)
            updateCommand.Parameters.AddWithValue("@NewR_DATE", Convert.ToDateTime(newRider.R_DATE))
    ...

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by PoppyGB Wednesday, January 16, 2013 12:44 AM
    Monday, January 14, 2013 9:27 AM