Conversion failed when converting datetime from character string.

ms visual studio 2005
sql erver 2005

I originally posted this question at experts-exchange without resolution

http://www.hide-link.com/

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

.NET Development22

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

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

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

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

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] ) " & _

Dim myCommand As New SqlCommand(sql, myConnection)

username")

nhivalue")

eventnum")

accountNumber")

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.

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

datevalue")

needs to be

datevalue","mm/dd/yyyy"))

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

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] ) " & _

Dim myCommand As New SqlCommand(sql, myConnection)

myCommand.Parameters.Add(username)
myCommand.Parameters.Add(nhi)
myCommand.Parameters.Add(eventnum)
myCommand.Parameters.Add(accnumber)
myCommand.Parameters.Add(datevalue)

well you just give it the variable names/control values in the second parameter of each SqlParameter. Example:

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

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( , SqlDbType.VarChar, , username)
Dim pnhi As New SqlParameter( , SqlDbType.VarChar, , nhi)
Dim peventnum As New SqlParameter( , SqlDbType.Int, , eventnum)
Dim paccnum As New SqlParameter( , SqlDbType.Int, , accnum)
Dim preqstdateAs New SqlParameter( , 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

Hi Jeff thanks for that, however

System.Data.SqlClient.SqlException was unhandled by user code
Class=16
ErrorCode=-2146232060
LineNumber=0

Number=8178
Procedure=""

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

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

Cheers
Jeff

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] ) " & _

Dim myCommand As New SqlCommand(sql, myConnection)

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

Sorry for the delay, been busy.

Create your parameters like this:

Dim pusername As New SqlParameter( , username)

Dim pnhi As New SqlParameter( , nhi)

Dim peventnum As New SqlParameter( , CStr(eventnum))

Dim paccnum As New SqlParameter( , CStr(accnum))

Dim preqstdate As New SqlParameter( , reqstdate)

Sorry for the confusion

Cheers
Jeff

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