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

Answer 1

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?

 

Answer 2

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 

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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)

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

Can you please post your complete code as it's difficult to see what is happening?

Cheers
Jeff

Answer 12

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

Answer 13

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

Answer 14

Thanks Jeff, weekend at the moment so will try on Monday.

Answer 15

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)

Answer 16

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

Answer 17

Hi,


  I am new to data base queries, my query is

  reqrst2 = dbs.openRecordset( "select sum(npos) as [nopsSum] from [crvmreq] where [crvmreq].Staff = '"+ staffArr+"' 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.





 

Answer 18

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.