dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] Conversion Failed Error

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    313

    Resolved [RESOLVED] Conversion Failed Error

    When trying to save my data I am getting this error. "Conversion Failed when converting date and/or time from character string.
    I collect my date time from a Date Picker Box. this variable is to be save in MSSQL 2017 as Datetime2.

    SQL Query
    Code:
    SELECT        WorkOrderID, MachineID, MachineNumber, WorkorderDate
    FROM                    dbo.WorkOrders
    WHERE                 (WorkorderDate IS NOT NULL) AND (WorkorderDate <= { fn NOW() } - 7)

    Code:
    Public Class OpenWO
        Private SQL As New SQLControl
        Dim HoldScreenError As Boolean
        Public MancID As String 'Hold Machine ID for saving
        Public WOID As Integer 'HOLD FOR UPDATING
        Public test As Integer



    Code:
     Private Sub SaveWO()
    
            'ADD SQL PARAMS & RUN THE COMMAND
            SQL.AddParam("@MancID", MancID)
            SQL.AddParam("@Machine", cbxMachineNo.Text)
            SQL.AddParam("@Priority", cbxPriority.Text)
            SQL.AddParam("@dateTime", dtpDateTime.Text)
            SQL.AddParam("@WOReqd", cbxWORequestedBy.Text)
            SQL.AddParam("@Descri", txtDesc.Text)
            SQL.AddParam("@problem", txtProblem.Text)
            SQL.AddParam("Notes", txtNotes.Text)
            SQL.AddParam("@WorkOrderClosed", False)
            SQL.ExecQuery("INSERT INTO WorkOrders (MachineId,MachineNumber,Priority,WorkorderDate,EmployeeName,Description,Problem,Notes,WOclosed)" &
                          "VALUES (@MancID,@Machine,@Priority,@dateTime,@WOReqd,@Descri,@problem,@Notes,@WorkOrderClosed);")
    
    
            'REPORT & ABORT ON ERRORS
            If SQL.hasexception(True) Then Exit Sub
            MsgBox(" EWork Order created Successfully")
    
        End Sub
    Code:
    Imports System.Data.SqlClient
    
    
    Public Class SQLControl
        'INFORMATION FOR THE CONNECTION TO THE SQL DATABASE
        'FIRST IS THE SERVERS NAME; DATABASE NAME;USER; PASSWORD;
        'DO NOT FORGET THE SEMICOLINS AS SEPARTOR
    
        Private DBCon As New SqlConnection("Server=localhost\SQLEXPRESS;Database=HiTech;User=Maint1;Pwd=Password1;")
    
        'THIS WILL BE RECREATED EVERY TIME WE RUN A QUERY
    
        Private DBcmd As SqlCommand
    
        'localhost\SQLEXPRESS
        ' DB Data
        Public DBDA As SqlDataAdapter
        Public DBDT As DataTable ' THIS A STORAE CONTAINER FOR THE DATA
    
    
        ' Qury Parameters
        ' THESE WILL BE USED WHEN WE RUN QUERY'S
        Public Params As New List(Of SqlParameter)
    
        'Query Statistics
        Public RecordCount As Integer
        Public Exception As String   'USED TO STOR ANY ERROR'S ALONG THE WAY
    
    
        Public Sub New()
    
        End Sub
        'Allow Connection String Override
        Public Sub New(ConnectionString As String)
            DBCon = New SqlConnection(ConnectionString)
        End Sub
        'Execute Query Sub
        Public Sub ExecQuery(Query As String)
            'Reset Query Stats
            RecordCount = 0
            Exception = ""
            Try
                DBCon.Open()
    
                'Creat DB Command
                DBcmd = New SqlCommand(Query, DBCon)
    
                'Load Parans into DB Command
                Params.ForEach(Sub(p) DBcmd.Parameters.Add(p))
    
                'Clear Param List
                Params.Clear()
    
                'Execute Command & Fill Dataset
                DBDT = New DataTable
                DBDA = New SqlDataAdapter(DBcmd)
                RecordCount = DBDA.Fill(DBDT)
    
            Catch ex As Exception
                'Capture Error
                Exception = "ExceQuery Error: " & vbNewLine & ex.Message
    
    
            Finally
                'Close Connection
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
    
            End Try
        End Sub
        'Add Params
        Public Sub AddParam(name As String, value As Object)
            Dim NewParam As New SqlParameter(name, value)
            Params.Add(NewParam)
        End Sub
    
        'Error Checking
        Public Function hasexception(Optional Report As Boolean = False) As Boolean
            If String.IsNullOrEmpty(Exception) Then Return False
            If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
            Return True
    
        End Function
    
    
    End Class

    Thanks for Looking

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,602

    Re: Conversion Failed Error

    You should not be using the Text property of a DateTimePicker. The Value property is type DateTime, so no conversion is required.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    313

    Re: Conversion Failed Error

    Quote Originally Posted by jmcilhinney View Post
    You should not be using the Text property of a DateTimePicker. The Value property is type DateTime, so no conversion is required.
    Thanks
    I never gave that a thought.
    Thanks
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width