Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2831
  • Last Modified:

Conversion failed when converting datetime from character string.

ms visual studio 2005
sql erver 2005

I initially thought this might be vb.net problem and asked the question in vb.net forum after
much effort by hillwaa I think it might be an sql server issue.

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

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
0
deNZity
Asked:
deNZity
  • 10
  • 10
  • 2
  • +1
1 Solution
 
NightmanCTOCommented:
Pass the date strings to SQL in this format

YYYYMMDD HH:mm:ss
0
 
satish_nagdevCommented:
hi,
could you do one thing, debug your .net code, copy the value of command text & try to run it in sql you might get to know what is the problem, your code seems to be proper to me.
see what is value getting passed in datetime fields.
regards,
satish.
0
 
daczCommented:
try this:

"values ('" & username & "','" & STUFF(STUFF(CONVERT(VARCHAR(10),'"& reqrddate &"', 6),3,1,'-'),7,1,'-') & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate & "' )"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NightmanCTOCommented:
When passing dates as strings (instead of date parameters, which are strongly types and region independant) always stick to the standard ISO formats. YYYYMMDD HH:mm:ss will work on ALL regional installations of SQL Server, regardless of the regional date formatting of either the server or the client.
0
 
deNZityAuthor Commented:
I tried creating 2 parameters but get the same error message.
 
Dim parameter1 As New SqlParameter
        With parameter1
            .ParameterName = "@reqrddate"
            .IsNullable = True
            .SqlDbType = SqlDbType.DateTime
            .Value = txtrequireddate.Text
        End With
        Dim parameter2 As New SqlParameter
        With parameter2
            .ParameterName = "@reqstdate"
            .IsNullable = True
            .SqlDbType = SqlDbType.DateTime
            .Value = txtrequestdate.Text
        End With


dacz, your suggestion reulted in error msg 'expression expected' I think your missing single or double quote.
0
 
NightmanCTOCommented:
Looks like you are using VB.NET. Try this

"values ('" & username & "','" & Format(reqrddate, "yyyyMMdd HH:mm:ss") & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & Format(reqstdate , "yyyyMMdd HH:mm:ss") & "' )"
0
 
deNZityAuthor Commented:
Hi Nightman, tried your suggestion ....System.Data.SqlClient.SqlException: Conversion failed when converting datetime from character string.

tried...
reqrddate = Split(reqrddate, "/")
        Dim reqyear As Date = reqrddate(0)
        Dim reqmonth As Date = reqrddate(1)
        Dim reqday As Date = reqrddate(2)
no luck


I have  

Dim reqstdate As String
Dim reqrddate As String

reqrddate = txtrequireddate.Text
reqstdate = txtrequestdate.Text

I can't believe the problem I'm having with this, no matter what I declare the variable as string,object,date,datetime it can't convert to date even when I use the type coversion functions  #!$@@$%

0
 
NightmanCTOCommented:
OK, generate the string and post the complete SQL statement here (no variables, just what you are sending to SQL)
0
 
deNZityAuthor Commented:
Surely what I want to do is not a big deal, simply take a user entered value for a date and insert that date into a database. I'm using ms visual studio asp.net controls and a sql server 205 db as backend the trouble I'm having is ridiculous. Should have stuck with php and mysql.
0
 
NightmanCTOCommented:
It isn't a big deal. You will probably find that it will be something straightforward that we are ALL overlooking. Post the statement here and let's have a closer look.
0
 
deNZityAuthor Commented:
sorry about the venting.

I can see why the  insert wont work

sql      "insert into requests ( [User_Name], [required_date],[NHI], [Event_Number], [ACC_Number], [Request_Date]) values ('robemorg','yyyyMMdd HH:mm:ss','aaa1234',0,'0','yyyyMMdd HH:mm:ss' )"      String
0
 
NightmanCTOCommented:
OK, now format the date in the actual format (instead of whatr you are seeing there) and give it a shot ;)
0
 
deNZityAuthor Commented:
?not sure what you meant      

I changed the format

sql      "insert into requests ( [User_Name], [required_date],[NHI], [Event_Number], [ACC_Number], [Request_Date]) values ('robemorg','ddMMyyyy HH:mm:ss','aaa1234',0,'0','ddMMyyyy HH:mm:ss' )"      String

but instead of bringing the value through from the variable it shows the format
0
 
NightmanCTOCommented:
Make sure that the actual date is passed into the SQL string in that format.

Format(dateobject, "yyyyMMdd HH:mm:ss") will return a string date in that format in VB.NET

e.g. Format(Date.Now(),"yyyyMMdd HH:mm:ss") returns a string '20061122 22:01:58'. That is what must be passed in to SQL.
0
 
deNZityAuthor Commented:
getting closer the value is now getting passed in,

Format(txtrequireddate.Text, "ddMMyyyy HH:mm:ss")
txtrequestdate.Text = Format(Date.Now(), "dd/MM/yyyy HH:mm:ss")

reqrddate = txtrequireddate.Text
reqstdate = txtrequestdate.Text

but still getting an error :(

Conversion from string "12/23/2006" to type 'Date' is not valid.
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.InvalidCastException: Conversion from string "12/23/2006" to type 'Date' is not valid.

Source Error:


Line 58:         Format(txtrequireddate.Text, "ddMMyyyy HH:mm:ss")
Line 59:         username = txtusername.Text
Line 60:         reqrddate = txtrequireddate.Text
Line 61:         reqstdate = txtrequestdate.Text
Line 62:
 
0
 
NightmanCTOCommented:
Try this:

Format(CDate(txtrequireddate.Text), "yyyyMMdd HH:mm:ss")
0
 
deNZityAuthor Commented:
Format(CDate(txtrequireddate.Text), "yyyyMMdd HH:mm:ss")

=

Conversion from string "12/23/2006" to type 'Date' is not valid.
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.InvalidCastException: Conversion from string "12/23/2006" to type 'Date' is not valid.

Source Error:


Line 56:
Line 57:         'assign values to vars
Line 58:         Format(CDate(txtrequireddate.Text), "dd/MM/yyyy")
Line 59:         username = txtusername.Text
Line 60:         reqrddate = txtrequireddate.Text
 
0
 
NightmanCTOCommented:
Try this to generate the date string to use:
dim reqrddate as string
dim reqstdate as string
reqrddate = Format(System.DateTime.ParseExact(txtrequireddate.Text, "MM/dd/yyyy", Nothing), "yyyyMMdd HH:mm:ss")
reqstdate = Format(System.DateTime.ParseExact(txtrequestdate.Text, "MM/dd/yyyy", Nothing), "yyyyMMdd HH:mm:ss")

"values ('" & username & "','" & reqrddate  & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate  & "' )"
0
 
deNZityAuthor Commented:
same  error msg as before :(

Exception Details: System.FormatException: String was not recognized as a valid DateTime.

Source Error:


Line 58:
Line 59:         username = txtusername.Text
Line 60:         reqrddate = Format(System.DateTime.ParseExact(txtrequireddate.Text, "MM/dd/yyyy", Nothing), "yyyyMMdd HH:mm:ss")
Line 61:         reqstdate = Format(System.DateTime.ParseExact(txtrequestdate.Text, "MM/dd/yyyy", Nothing), "yyyyMMdd HH:mm:ss")
Line 62:
 
0
 
daczCommented:
try to Trim some spaces in txtrequireddate and txtrequestdate before converting to DateTime.
and add some valdation and error trapping.

example:

'validate the txtrequireddate
If not Isdate(txtrequireddate.Text.Trim) then
        'put some error trapping here
Else
     txtrequireddate.Text= txtrequireddate.Text.Trim
End If

'validate the txtrequestdate
If not Isdate(txtrequestdate.Text.Trim) then
        'put some error trapping here
Else
     txtrequestdate.Text= txtrequestdate.Text.Trim
End If

If the txtrequireddate and txtrequestdate have been validated, then convert it to datetime and then save.
0
 
deNZityAuthor Commented:
This works

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

Dim reqstdate As String = Format(Date.Now, "dd MMM yyyy").ToString
0
 
NightmanCTOCommented:
And when you pass that through to SQL?

This should also work:
Dim reqrddate As String = Format(CDate(txtrequireddate.Text), "yyyyMMdd HH:mm:ss").ToString
Dim reqstdate As String = Format(Date.Now, "yyyyMMdd HH:mm:ss").ToString
0
 
deNZityAuthor Commented:
Dim reqrddate As String = Format(CDate(txtrequireddate.Text), "dd MMM yyyy").ToString
Dim reqstdate As String = Format(Date.Now, "dd MMM yyyy").ToString

It works fine database accepts it. Thanks for your help though I now know more about dates than I did.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 10
  • 10
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now