Failed to convert parameter value from a String to a DateTime

[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.

 

[Cor Ligthert[MVP]]

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

 

[Fred]

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 :

 

[John Kotuby]

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.

 

[Cor Ligthert[MVP]]

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

 

[Miha Markic]

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.

 

[Fred]

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))

Yes you did.

Maybe I misunderstand....

No, I read too fast, sorry.

 

[John Kotuby]

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.

 

[John Kotuby]

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.

 

[randheer singh]

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.