Re: Failed to convert parameter value from a String to a DateTime

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:e0vtp$hdJHA.1916@xxxxxxxxxxxxxxxxxxxx, Cor Ligthert[MVP] wrote :

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

"John Kotuby" <johnk@xxxxxxxxxxxxx> wrote in message
news:eZDEDQedJHA.4900@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
Fred
foleide@xxxxxxx

.

Relevant Pages

  • Re: Problems with DAO and SQL Server
    ... String, Long or Date/Time. ... Will look at what is possible in the SQL string ... SQL or using TRIMin your query (if available, not familiar with SQL Server) ... Dim cnn As DAO.Connection ...
    (microsoft.public.excel.programming)
  • Re: Dates
    ... The SQL query is a string, so the date in it is a part of the string, not a separate DateTime value. ... Frederik Vanderhaeghe wrote: ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Datumsformat im SQL String
    ... daß der String im richtigen Format kommt. ... Die von mir immer gepredigte Variante. ... > - das am SQL Server eingestellte Datumsformat ist egal ...
    (microsoft.public.de.sqlserver)
  • Re: Variable as Alias Name
    ... @friday_as_of_date must be a string in order to concatenate the value with the remainder of the SQL string. ... I suggest you use sp_executesql sp that you can pass @org_level_1_name as a parameter instead of building the value into the SQL statement itself. ... Conversion failed when converting datetime from character string. ... declare @sqlstring varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: How to look at parameter string
    ... I am tracing, but the problem I can't trace (at least, I haven't figured out ... string it is putting together to send to Sql. ... >> The problem is not coming from Sql Server but from my page. ...
    (microsoft.public.dotnet.framework.adonet)
Loading