none
Conversion failed when converting datetime from character string.

    Question

  • ms visual studio 2005
    sql erver 2005

    I originally posted this question at experts-exchange without resolution

    http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22069012.html

    http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22068922.html

    I get the following error when trying to insert into db from web app

    have tried....
    txtrequireddate.Text.ToString("dd-MMM-yyyy ")
    reqrddate = Convert.ToDateTime(txtrequireddate.Text)
    reqrddate= CDate(txtrequireddate.Text)


    Server Error in '/' Application.
    --------------------------------------------------------------------------------

    Conversion failed when converting datetime from character string.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting datetime from character string.

    Source Error:


    Line 94:                 "values ('" & username & "','" & reqrddate & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate & "' )"
    Line 95:                 Dim myCommand As New SqlCommand(sql, myConnection)
    Line 96:                 myCommand.ExecuteNonQuery()<--- highlighted
    Line 97:             ElseIf (j = 0) Then
    Line 98:                 lblnoitem.Visible = True
     

    Source File: C:\Inetpub\loans\MemberPages\Request.aspx.vb    Line: 96

    Stack Trace:


    [SqlException (0x80131904): Conversion failed when converting datetime from character string.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
       System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
       Request.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\loans\MemberPages\Request.aspx.vb:96
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
       System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
    Wednesday, November 22, 2006 8:58 PM

Answers

  • Sorry for the delay, been busy.

    Create your parameters like this:

    • Dim pusername As New SqlParameter("@username", username)
    • Dim pnhi As New SqlParameter("@nhi", nhi)
    • Dim peventnum As New SqlParameter("@eventnum", CStr(eventnum))
    • Dim paccnum As New SqlParameter("@accnum", CStr(accnum))
    • Dim preqstdate As New SqlParameter("@reqstdate", reqstdate)

    Sorry for the confusion

    Cheers
    Jeff

    Friday, November 24, 2006 9:29 AM

All replies

  • 1) What's the input string?

    2) What is the field datatype and length for the field you are trying to insert this record into?

     

    Wednesday, November 22, 2006 10:03 PM
  • SQL Server expects dates in the format mm/dd/yyyy so you will need to convert your date into this format before submitting.

    The problem with this error is that if the day value of a date is <=12, it will automagically be converted to mm/dd/yyyy giving you the false impression that your code is working.  it's not until the day hits 13> that the problem raises it's ugly head.

    Please note that this error occurs irrespective of the localisation of the product or language configuration of the client/server.  This format is hard-coded and we can thank American for that 

    Wednesday, November 22, 2006 11:52 PM
  • sql string is as follows, this is output from auto window when debugging.
    breakpoint set at   myCommand...

    Dim sql As String = "insert into requests ( [User_Name] , [NHI], [Event_Number], [ACC_Number], [request_date] ) " & _
                    "values ('" & username & "','" & nhi & "'," & eventnum & ",'" & accnum & "' ,'" & reqstdate & "')"
                    Dim myCommand As New SqlCommand(sql, myConnection)
                    myCommand.ExecuteNonQuery()

        sql    "insert into requests ( [User_Name] , [NHI], [Event_Number], [ACC_Number], [request_date] ) values ('robemorg','aaa2345',22,'22' ,'23/11/2006 14:42:03')"    String


    Dattype in db is datetime
    length is 8
    Thursday, November 23, 2006 1:47 AM
  • Tried this just to see if it would take hardcoded as opposed to variable.
    reqstdate = CDate(txtrequestdate.Text)
    reqstdate = Format(reqstdate, "12232006")

    resulting in .....

    System.Data.SqlClient.SqlException was unhandled by user code

      Class=16

      ErrorCode=-2146232060

      LineNumber=1

      Message="The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    The statement has been terminated."

      Number=242

      Procedure=""

      Server="SQLDEV2"

      Source=".Net SqlClient Data Provider"

      State=3

    Thursday, November 23, 2006 1:59 AM
  • I would begin by using a parameterized query. It's more safer than doing the standard way you are (but even safer and faster using a stored procedure) and you will know what values you are giving and it will interpret those values correctly as possible.

    Dim sql As String = "insert into requests ( [User_Name] , [NHI], [Event_Number], [ACC_Number], [request_date] ) " & _
                    "values (@username, @nhi, @eventnum, @accnum, @reqstdate)"
                    Dim myCommand As New SqlCommand(sql, myConnection)

    Dim username as new SqlParameter("@username", "username")

    Dim nhi as new SqlParameter("@nhi", "nhivalue")

    Dim eventnum as new SqlParameter("@eventnum", "eventnum")

    Dim accountNumber as new SqlParameter("@accnum", "accountNumber")

    Dim datevalue as new SqlParameter("@reqstdate", "datevalue")

    myCommand.Parameters.Add(username)

    myCommand.Parameters.Add(nhi)

    myCommand.Parameters.Add(eventnum)

    myCommand.Parameters.Add(accountNumber)

    myCommand.Parameters.Add(datevalue)

    'execute query

     

    now what happens? of course replace the values correctly in the bold text.

    Thursday, November 23, 2006 2:15 AM
  • As per my previous post, your date is in the wrong format.  It needs to be in the format mm/dd/yyyy so '23/11/2006' needs to be reformatted to 11/23/2006.  reqstdate = Format(reqstdate, "12232006") would return a date of 12232660 which is not in the format required.  You should use reqstdate = Format(reqstdate, "mm/dd/yyyy")

    Likewise

    Dim datevalue as new SqlParameter("@reqstdate", "datevalue")

    needs to be

    Dim datevalue as new SqlParameter("@reqstdate", Format("datevalue","mm/dd/yyyy"))

    SQL Server expects dates in the format mm/dd/yyyy irrespective of whether you use a parameter or not.

    Thursday, November 23, 2006 2:25 AM
  • Thanks for the reply, how do I assign values to these parameters?
    Do I remove the declaration I already have for these variables ...

             Dim username As String
            'Dim reqrddate As DateTime
            Dim nhi As String
            Dim eventnum As Integer
            Dim accnum As Integer
            Dim reqstdate As String

    where do the parameters get there values from?

    Dim sql As String = "insert into requests ( [User_Name] , [NHI], [Event_Number], [ACC_Number], [request_date] ) " & _
                            "values (@username, @nhi, @eventnum, @accnum, @reqstdate)"
                    Dim myCommand As New SqlCommand(sql, myConnection)
                    Dim username As New SqlParameter("@username", "username")
                    Dim nhi As New SqlParameter("@nhi", "nhi")
                    Dim eventnum As New SqlParameter("@eventnum", "eventnum")
                    Dim accnumber As New SqlParameter("@accnum", "accnumber")
                    Dim datevalue As New SqlParameter("@reqstdate", Format("datevalue", "mm/dd/yyyy"))
                    myCommand.Parameters.Add(username)
                    myCommand.Parameters.Add(nhi)
                    myCommand.Parameters.Add(eventnum)
                    myCommand.Parameters.Add(accnumber)
                    myCommand.Parameters.Add(datevalue)
    Thursday, November 23, 2006 3:00 AM
  • well you just give it the variable names/control values in the second parameter of each SqlParameter. Example:

                    Dim username As New SqlParameter("@username", Me.txtUsername.Text)

    will get the value from the textbox "username" and put it into the sqlparameter

    Thursday, November 23, 2006 3:04 AM
  • Based on your previous posts, you have the following (found in the string declaration Dim sql As String) whcih contain the values:

    • username
    • nhi
    • eventnum
    • accnum
    • reqstdate

    Therefore your parameters would look like this:

    Dim pusername As New SqlParameter("@username", SqlDbType.VarChar, , username)
    Dim pnhi As New SqlParameter("@nhi", SqlDbType.VarChar, , nhi)
    Dim peventnum As New SqlParameter("@eventnum", SqlDbType.Int, , eventnum)
    Dim paccnum As New SqlParameter("@accnum", SqlDbType.Int, , accnum)
    Dim preqstdateAs New SqlParameter("@reqstdate", SqlDbType.DateTime, , reqstdate)

    myCommand.Parameters.Add(pusername )
    myCommand.Parameters.Add(pnhi )
    myCommand.Parameters.Add(peventnum )
    myCommand.Parameters.Add(paccnum )
    myCommand.Parameters.Add(preqstdate)

    Cheers
    Jeff

    Thursday, November 23, 2006 3:15 AM
  • Hi Jeff thanks for that, however

    System.Data.SqlClient.SqlException was unhandled by user code
      Class=16
      ErrorCode=-2146232060
      LineNumber=0
      Message="Parameterized Query '(@username varchar(10),@nhi varchar(10),@eventnum int,@accnum in' expects parameter @username, which was not supplied."
      Number=8178
      Procedure=""

    Mousing over the variables in the parameter declaration shows they all have the right values




    Thursday, November 23, 2006 4:27 AM
  • Can you please post your complete code as it's difficult to see what is happening?

    Cheers
    Jeff

    Thursday, November 23, 2006 7:00 AM
  • Heres code as asked for.
    I appreciate you looking at this.

    Option Strict On
    Imports System.Configuration
    Imports System.Data.SqlClient

    Partial Class Request
        Inherits System.Web.UI.Page

        Protected Sub Txtusername_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtusername.PreRender
            txtusername.Text = CStr(Session("username"))
        End Sub

        Protected Sub TextBox2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtrequestdate.Load
            txtrequestdate.Text = CStr(Date.Now())
        End Sub
        Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

            'declare array
            Dim requests(8) As String

            'assign values to array indexes
            requests(0) = txtreq0.Text
            requests(1) = txtreq1.Text
            requests(2) = txtreq2.Text
            requests(3) = txtreq3.Text
            requests(4) = txtreq4.Text
            requests(5) = txtreq5.Text
            requests(6) = txtreq6.Text
            requests(7) = txtreq7.Text

            'declare vars
            Dim username As String
            'Dim reqrddate As DateTime
            Dim nhi As String
            Dim eventnum As Integer
            Dim accnum As Integer
            Dim reqstdate As String
            Dim i As Integer
            Dim Item As String = Nothing
            Dim j As Integer
          

          
            'assign values to vars
            reqstdate = txtrequestdate.Text
            username = txtusername.Text
            nhi = Trim(txtNHI.Text)
            If (txteventnumber.Text = "") Then
                eventnum = 0
            Else
                eventnum = CInt(txteventnumber.Text)
            End If
            If (txtaccnumber.Text = "") Then
                accnum = 0
            Else
                accnum = CInt(txtaccnumber.Text)
            End If

            'Connection String  value
            Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString

            'Create a SqlConnection instance
            Using myConnection As New SqlConnection(conn)
                myConnection.Open()

                ' test to see that there is a value in requests array if not don't execute query else Execute(query)
                i = 0
                j = 0
                For i = 0 To 7
                    If requests(i) <> "" Then
                        j = +1
                    End If
                Next
                If (j > 0) Then           ' Specify the SQL query
                    Dim sql As String = "insert into requests ( [User_Name] , [NHI], [Event_Number], [ACC_Number], [request_date] ) " & _
                            "values (@username, @nhi, @eventnum, @accnum, @reqstdate)"
                    Dim myCommand As New SqlCommand(sql, myConnection)
                    Dim pusername As New SqlParameter("@username", SqlDbType.VarChar, 10, username)
                    Dim pnhi As New SqlParameter("@nhi", SqlDbType.VarChar, 10, nhi)
                    Dim peventnum As New SqlParameter("@eventnum", SqlDbType.Int, 4, CStr(eventnum))
                    Dim paccnum As New SqlParameter("@accnum", SqlDbType.Int, 4, CStr(accnum))
                    Dim preqstdate As New SqlParameter("@reqstdate", SqlDbType.DateTime, 8, reqstdate)

                    myCommand.Parameters.Add(pusername)
                    myCommand.Parameters.Add(pnhi)
                    myCommand.Parameters.Add(peventnum)
                    myCommand.Parameters.Add(paccnum)
                    myCommand.Parameters.Add(preqstdate)
                    myCommand.ExecuteNonQuery()

                    'Dim sql As String = "insert into requests ( [User_Name] , [NHI], [Event_Number], [ACC_Number], [request_date] ) " & _
                    '"values ('" & username & "','" & nhi & "'," & eventnum & ",'" & accnum & "' , '" & s & "')"
                    'Dim myCommand As New SqlCommand(sql, myConnection)

                ElseIf (j = 0) Then
                    lblnoitem.Visible = True
                    Exit Sub
                End If

                'loop to test for zero length string if not assign array value to variable and insert into table
                i = 0
                For i = 0 To 7
                    If requests(i) <> "" Then
                        Item = requests(i)
                        Item.Trim()
                        Dim sql1 As String = "insert into request_items ([req_id],[request]) Select  max ([req_id]), '" & Item & "'  from requests "
                        Dim myCommand1 As New SqlCommand(sql1, myConnection)
                        myCommand1.ExecuteNonQuery()
                    Else
                        Continue For
                    End If
                Next



                'reset controls
                txtNHI.Text = " "
                txtrequireddate.Text = ""
                txtaccnumber.Text = ""
                txteventnumber.Text = ""
                txtreq0.Text = ""
                txtreq1.Text = ""
                txtreq2.Text = ""
                txtreq3.Text = ""
                txtreq4.Text = ""
                txtreq5.Text = ""
                txtreq6.Text = ""
                txtreq7.Text = ""

                'Close the connection
                myConnection.Close()

            End Using
        End Sub
        Protected Sub lblconfirm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles lblconfirm.Load
            lblconfirm.Visible = False
        End Sub
        Protected Sub lblnoitem_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles lblnoitem.Load
            lblnoitem.Visible = False
        End Sub
       
     
    End Class

    Thursday, November 23, 2006 5:54 PM
  • Sorry for the delay, been busy.

    Create your parameters like this:

    • Dim pusername As New SqlParameter("@username", username)
    • Dim pnhi As New SqlParameter("@nhi", nhi)
    • Dim peventnum As New SqlParameter("@eventnum", CStr(eventnum))
    • Dim paccnum As New SqlParameter("@accnum", CStr(accnum))
    • Dim preqstdate As New SqlParameter("@reqstdate", reqstdate)

    Sorry for the confusion

    Cheers
    Jeff

    Friday, November 24, 2006 9:29 AM
  • Thanks Jeff, weekend at the moment so will try on Monday.

    Friday, November 24, 2006 6:49 PM
  • Hi Jeff Thanks for that it worked great. I had already found a workning solution but not as secure.

    Dim reqrddate As String = Format(CDate(txtrequireddate.Text), "dd MMM yyyy").ToString
    Dim reqstdate As String = Format(Date.Now, "dd MMM yyyy").ToString

     Dim sql As String = "insert into requests ( [User_Name] , [required_date], [NHI], [Event_Number], [ACC_Number], [request_date] ) " & _
                    "values ('" & username & "', '" & reqrddate & "','" & nhi & "','" & eventnum & "','" & accnum & "' , '" & reqstdate & "' )"
                    Dim myCommand As New SqlCommand(sql, myConnection)
    Sunday, November 26, 2006 7:14 PM
  • Why pass as a string parameter, when you can validate it, then pass as a datetime parameter?

    Code Snippet

    Imports System.Globalization
    Imports System.Data.SqlClient
    Imports System.Data


            Dim username As String = "Something"
            Dim nhi As String = "Something Else"
            Dim eventnum As Integer = 0
            Dim accnum As Integer = 0
            Dim reqstdate As Date

            '**************** Set up the date ******************
            ' This would be whatever culture you determine or could be current users culture
            Dim culture As CultureInfo = CultureInfo.CreateSpecificCulture("en-US")
            Dim sDate As String = TextBox1.Text ' some string from somewhere
            Try
                reqstdate = Date.Parse(sDate, culture)
            Catch
                reqstdate = Date.MinValue
                'or
                Exit Sub
            End Try
            '**************** Set up the date ******************

            Dim conn As String = "some connection string"
            Dim myconnection As New SqlConnection(conn)
            Dim sql As String = "insert into requests ([user_name], [nhi], [event_number], [acc_number], [request_date] ) " & _
                                    "values (@username, @nhi, @eventnum, @accnum, @reqstdate)"
            Dim mycommand As New SqlCommand(sql, myconnection)
            mycommand.Parameters.AddWithValue("@username", username)
            myCommand.Parameters.AddWithValue("@nhi", nhi)
            myCommand.Parameters.AddWithValue("@eventnum", eventnum)
            myCommand.Parameters.AddWithValue("@accnum", accnum)
            myCommand.Parameters.AddWithValue("@reqstdate", reqstdate)
            '... the rest of your code
            TextBox2.Text = reqstdate.ToString(culture) ' See what we get



    Thursday, February 07, 2008 6:59 PM
  • Hi,


      I am new to data base queries, my query is

      reqrst2 = dbs.openRecordset( "select sum(npos) as [nopsSum] from [crvmreq] where [crvmreq].Staff = '"+ staffArrIdea+"' and (Date >= ' "+ 7/11/2008 +"'  and Date <=  '"+ 7/15/2008 +"') ",AdoConst.adOpenDynamic, 0); 


    just i hard coded Date for testing..and the data type of Date is "DateTime"

    it's giving me the error like this


    Conversion failed when converting datetime from character string.





     


    Tuesday, July 15, 2008 7:52 PM
  • You should never build an sql string with values. It's good practice to pass parameters. This reduces the threat of sql injection attacks. It's also cleaner to read. Also, passing parameters prevents formatting issues, like getting the date in the wrong format, because you are passing a datetime object as a parameter instead of a string.

    See the example in my previous post, to use parameters.

    Tuesday, July 15, 2008 8:08 PM