date parameters and null

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

I have the following code:
***************************************************************************
Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position
(client,dateposted) VALUES ( @client,@dateposted)"

Dim objCmd as New SqlCommand(CommandText,objConn)

with objCmd.Parameters
.Add("@client",SqlDbType.Char,50).value = "the companies next test"
.Add("@dateposted",SqlDbType.datetime).value = dateposted.text
end with

objCmd.ExecuteNonQuery
****************************************************************************

My problem is that if the dateposted.text is blank, then I get an error
saying date is invalid. If I take out the dateposted line all together (as
well as in the insert statement), it uses todays date - which is what I want
as I have getdate() as the default.

How do I use a parameter and allow it to be null so it takes the default?

Thanks,

Tom.
 
Girish Bharadwaj said:
You probably can use DBNull.Value if you want to specify "Null".
Click to expand...

But how would I handle that with the Parameters statement?

I want to allow the user to put a date in, but if he doesn't I want to use
getdate(), which is the default on the field.

Tom.
 
with objCmd.Parameters
.Add("@client",SqlDbType.Char,50).value = "the companies next test"
if dateposted.text <> "" then
.Add("@dateposted",SqlDbType.datetime).value = dateposted.text
else
.Add("@dateposted",SqlDbType.datetime).value = getdate()
End if
end with
 
vinay said:
with objCmd.Parameters
.Add("@client",SqlDbType.Char,50).value = "the companies next test"
if dateposted.text <> "" then
.Add("@dateposted",SqlDbType.datetime).value = dateposted.text
else
.Add("@dateposted",SqlDbType.datetime).value = getdate()
End if
end with
Click to expand...

That will work fine. But I was curious why I get an error that says "String
was not recognized as a valid DateTime." if the value is blank. I would
have thought the program would have seen the type as SqlDbType.datetime and
the field as blank and set it to Null, which would have allowed Sql Server
to use the default value (getdate()).

Thanks,

Tom
 
An empty string is not the same as a NULL value in SQL and SQLServer won't
automatically convert from an empty string to NULL, nor will it be able to
parse an empty string into a DateTime value. NULL is a special value that can
be stored in a DateTime column as long as the database schema specifies that
the column is NULLable. It sounds like your DateTime column may not be
NULLable, but you have a default value specified to set the value to
GetDate() when a NULL value is specified.

By the way, you could check for an empty string in the stored procedure
instead of in your VB code, then any code that uses the SProc could avoid
having the conditional statement. Also, if you're going to allow an empty
date field, I'd recommend going the extra distance and stripping out any
whitespace before checking for an empty string. However, I think the best
way, if the default value in the database is today's date, would be to just
fill in the text box with today's date and put a RequiredFieldValidator and a
RegularExpressionValidator on the text box. Then you don't need the check in
your code any more.
 
Back
Top