Failed to convert parameter value from a String to a DateTime

  • Thread starter Thread starter John Kotuby
  • Start date Start date
J

John Kotuby

Hi all,
In VS2008 with VB I am trying to re-write an Update query to a SQL Server
2005 table that was working fine when I was simply assembling a string with
in-line variable values like txtCreateDate where the value may be a valid
date such as '01/01/2007' or a null string.

sql = "UPDATE dbo.user_SearchCriteria Set createDate = ' " & txtCreateDate &
" 'where sequence='8' "

The above sql works just fine.

Now that I have gone to the trouble of doing it the "right" way (even though
archaic to most developers) using command parameters so that I can replace
null strings with SqlDateTime.Null (the createDate field is DateTime
nullable) I get the error:

Failed to convert parameter value from a String to a DateTime.

This occurs whether the @txtCreateDate parameter value is set to
SqlDateTime.Null or DateTime.Parse(txtCreateDate).
Remember that when I was simply using the txtCreateDate variable itself the
update worked without a problem.
So below is part of the code. Can anyone tell me what I might be doing
wrong?
===========================================================

Cmd.Parameters.Add(New SqlParameter("@txtCreateDate", SqlDbType.DateTime,
True))

If Trim(txtCreateDate) = "" Then
Cmd.Parameters("@txtCreateDate").Value = SqlDateTime.Null
Else
Cmd.Parameters("@txtCreateDate").Value = DateTime.Parse(txtCreateDate)
End If

sql = "UPDATE dbo.user_SearchCriteria Set createDate=@txtCreateDate where
sequence='8'

Cmd.ExecuteNonQuery()
=============================================================
Thanks for any help.
 
John,

VB has an extra namespace above C# with very handy methods. One of those is
the CDate (a little bit alike the system TryParse),

The CDate is one of the methods I like still above the system methods.

Before you get the wrong idea, CDate is a full compatible member of Net and
will be like that like Parse.

Cor
 
Hello Cor and John,

In this case, perhaps the error occurs because SQL Server defaults to
nvarchar when a parameter is NULL.
I would try to declare the parameter as DateTime before to assign it the
DBNull.Value.


in news:[email protected], Cor Ligthert[MVP] wrote :
 
Hi Fred,
I thought I was declaring the parameter as DateTime with the following
statement:
Cmd.Parameters.Add(New SqlParameter("@txtCreateDate", SqlDbType.DateTime,
True))

Maybe I misunderstand...thanks.
 
John,

My answer was a little bit to quick, I made a sample for you using the
Northwind sample database

\\\
Dim cmd = New SqlClient.SqlCommand
cmd.Parameters.Add(New SqlParameter("@txtCreateDate", SqlDbType.DateTime,
True))
cmd.Parameters("@txtCreateDate").Value = CDate(Now.ToString)
cmd.Connection = New SqlConnection _
("Data Source=My2008Server;Initial Catalog=Northwind;Integrated
Security=True")
cmd.CommandText = "UPDATE orders Set OrderDate=@txtCreateDate where orderID
= '10248'"
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
///

Cor
 
Hi John,

Your current problem lies in conversion from string to datetime.
What does your input string looks like?
DateTime.Parse uses current regional settings to do the conversion. Perhaps
this is a problem?
You can try using DateTime.Parse overload method that accepts different
formatting settings.
I also recommend switching to DateTime.TryParse method as it is more
performant and easier to use.
 
in John Kotuby wrote :
Hi Fred,
I thought I was declaring the parameter as DateTime with the following
statement:
Cmd.Parameters.Add(New SqlParameter("@txtCreateDate",
SqlDbType.DateTime, True))
Click to expand...

Yes you did.
Maybe I misunderstand....
Click to expand...

No, I read too fast, sorry.
 
Cor,

The CDate function does seem to be more reliable. I also noticed that I had
declared Cmd as SqlCommand rather than SqlClient.SqlCommand and changed that
also. The update query now seems to work fine with all 60 parameters, 6 of
which are DateTime.

Thanks for your help.
 
Miha,

I have looked at the documentation for TryParse and Parse. It appears that
DateTime.Parse may not actually be successful at times but will not throw a
trappable error. I find that odd.

Whereas, TryParse returns an output parameter that can be checked for
success or failure.

Thank you for mentioning the alternative!

I am also going to add a control that forces the user to enter valid dates.
 
Hi,
I do also have the following error but in C#. I am using
stored procedure.

Here
SqlParameter arr3 = new SqlParameter("@dob",
SqlDbType.DateTime);
arr3.Value =txtdob.Text;

Now I passed the values to another class Data Utility which
has all of my connections. Every Parameters are pasing
instead of date. I give the code how I passed in the class.

public int ExecuteSQL(SqlParameter sp1)
{
OpenConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_inscustomer";
cmd.Parameters.Add(sp1);
int result = cmd.ExecuteNonQuery();
}

I get the error here "Failed to convert parameter value
from a String to a DateTime". I also tried

arr3.Value =datetime.Parse(txtdob.Text);

but it does not helps. Can you tell me what to do.
 
Back
Top