date parameters and null

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

You probably can use DBNull.Value if you want to specify "Null".

 

[tshad]

You probably can use DBNull.Value if you want to specify "Null".

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.

 

[Guest]

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

 

[tshad]

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

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