Solved

Conversion failed when converting datetime from character string.

Posted on 2006-11-21
23
2,572 Views
Last Modified: 2012-06-27
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
Comment
Question by:deNZity
  • 10
  • 10
  • 2
  • +1
23 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17993548
Pass the date strings to SQL in this format

YYYYMMDD HH:mm:ss
0
 
LVL 4

Expert Comment

by:satish_nagdev
ID: 17993762
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
 
LVL 1

Expert Comment

by:dacz
ID: 17993790
try this:

"values ('" & username & "','" & STUFF(STUFF(CONVERT(VARCHAR(10),'"& reqrddate &"', 6),3,1,'-'),7,1,'-') & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate & "' )"
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17993808
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
 

Author Comment

by:deNZity
ID: 17997942
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17998019
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
 

Author Comment

by:deNZity
ID: 17998165
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17998190
OK, generate the string and post the complete SQL statement here (no variables, just what you are sending to SQL)
0
 

Author Comment

by:deNZity
ID: 17998194
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17998205
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
 

Author Comment

by:deNZity
ID: 17998258
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 29

Expert Comment

by:Nightman
ID: 17998294
OK, now format the date in the actual format (instead of whatr you are seeing there) and give it a shot ;)
0
 

Author Comment

by:deNZity
ID: 17998470
?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
 
LVL 29

Expert Comment

by:Nightman
ID: 17998537
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
 

Author Comment

by:deNZity
ID: 17998616
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17998705
Try this:

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

Author Comment

by:deNZity
ID: 17998801
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17999023
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
 

Author Comment

by:deNZity
ID: 17999420
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
 
LVL 1

Expert Comment

by:dacz
ID: 18000596
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
 

Author Comment

by:deNZity
ID: 18004655
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
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 18004667
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
 

Author Comment

by:deNZity
ID: 18005220
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now