date parameters and null

[tshad]

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

Dim objCmd as New SqlCommand(Comm andText,objConn )

with objCmd.Paramete rs
.Add("@client", SqlDbType.Char, 50).value = "the companies next test"
.Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
end with

objCmd.ExecuteN onQuery
*************** *************** *************** *************** *************** *

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

--
Girish Bharadwaj
http://msmvps.com/gbvb
"tshad" <ts**********@f tsolutions.com> wrote in message
news:OB******** ******@TK2MSFTN GP11.phx.gbl...

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

Dim objCmd as New SqlCommand(Comm andText,objConn )

with objCmd.Paramete rs
.Add("@client", SqlDbType.Char, 50).value = "the companies next test"
.Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
end with

objCmd.ExecuteN onQuery
*************** *************** *************** *************** *************** *
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.

[tshad]

"Girish Bharadwaj" <gi*****@mvps.o rg> wrote in message
news:O9******** ******@TK2MSFTN GP14.phx.gbl...

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.

--
Girish Bharadwaj
http://msmvps.com/gbvb
"tshad" <ts**********@f tsolutions.com> wrote in message
news:OB******** ******@TK2MSFTN GP11.phx.gbl...

I have the following code:

*************** *************** *************** *************** ***************

Dim CommandText as String = "INSERT INTO ftsolutions.dbo .position
(client,datepos ted) VALUES ( @client,@datepo sted)"

Dim objCmd as New SqlCommand(Comm andText,objConn )

with objCmd.Paramete rs
.Add("@client", SqlDbType.Char, 50).value = "the companies next test"
.Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
end with

objCmd.ExecuteN onQuery

*************** *************** *************** *************** *************** *

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.

[vinay]

with objCmd.Paramete rs
.Add("@client", SqlDbType.Char, 50).value = "the companies next test"
if dateposted.text <> "" then
.Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
else
.Add("@datepost ed",SqlDbType.d atetime).value = getdate()
End if
end with
"tshad" wrote:

"Girish Bharadwaj" <gi*****@mvps.o rg> wrote in message
news:O9******** ******@TK2MSFTN GP14.phx.gbl...

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.

--
Girish Bharadwaj
http://msmvps.com/gbvb
"tshad" <ts**********@f tsolutions.com> wrote in message
news:OB******** ******@TK2MSFTN GP11.phx.gbl...

I have the following code:

*************** *************** *************** *************** ***************

Dim CommandText as String = "INSERT INTO ftsolutions.dbo .position
(client,datepos ted) VALUES ( @client,@datepo sted)"

Dim objCmd as New SqlCommand(Comm andText,objConn )

with objCmd.Paramete rs
.Add("@client", SqlDbType.Char, 50).value = "the companies next test"
.Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
end with

objCmd.ExecuteN onQuery

*************** *************** *************** *************** *************** *

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.

[tshad]

"vinay" <vi***@discussi ons.microsoft.c om> wrote in message
news:E3******** *************** ***********@mic rosoft.com...

with objCmd.Paramete rs
.Add("@client", SqlDbType.Char, 50).value = "the companies next test"
if dateposted.text <> "" then
.Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
else
.Add("@datepost ed",SqlDbType.d atetime).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.datet ime and
the field as blank and set it to Null, which would have allowed Sql Server
to use the default value (getdate()).

Thanks,

Tom

"tshad" wrote:

"Girish Bharadwaj" <gi*****@mvps.o rg> wrote in message
news:O9******** ******@TK2MSFTN GP14.phx.gbl...

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

>
> --
> Girish Bharadwaj
> http://msmvps.com/gbvb
> "tshad" <ts**********@f tsolutions.com> wrote in message
> news:OB******** ******@TK2MSFTN GP11.phx.gbl...
>> I have the following code:
>>
> *************** *************** *************** *************** ***************
>> Dim CommandText as String = "INSERT INTO ftsolutions.dbo .position
>> (client,datepos ted) VALUES ( @client,@datepo sted)"
>>
>> Dim objCmd as New SqlCommand(Comm andText,objConn )
>>
>> with objCmd.Paramete rs
>> .Add("@client", SqlDbType.Char, 50).value = "the companies next test"
>> .Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
>> end with
>>
>> objCmd.ExecuteN onQuery
>>
> *************** *************** *************** *************** *************** *
>>
>> 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.
>>
>>
>
>

[P. Gruenhagen]

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 RequiredFieldVa lidator and a
RegularExpressi onValidator on the text box. Then you don't need the check in
your code any more.

"tshad" wrote:

"vinay" <vi***@discussi ons.microsoft.c om> wrote in message
news:E3******** *************** ***********@mic rosoft.com...

with objCmd.Paramete rs
.Add("@client", SqlDbType.Char, 50).value = "the companies next test"
if dateposted.text <> "" then
.Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
else
.Add("@datepost ed",SqlDbType.d atetime).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.datet ime and
the field as blank and set it to Null, which would have allowed Sql Server
to use the default value (getdate()).

Thanks,

Tom

"tshad" wrote:

"Girish Bharadwaj" <gi*****@mvps.o rg> wrote in message
news:O9******** ******@TK2MSFTN GP14.phx.gbl...
> 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.

>
> --
> Girish Bharadwaj
> http://msmvps.com/gbvb
> "tshad" <ts**********@f tsolutions.com> wrote in message
> news:OB******** ******@TK2MSFTN GP11.phx.gbl...
>> I have the following code:
>>
> *************** *************** *************** *************** ***************
>> Dim CommandText as String = "INSERT INTO ftsolutions.dbo .position
>> (client,datepos ted) VALUES ( @client,@datepo sted)"
>>
>> Dim objCmd as New SqlCommand(Comm andText,objConn )
>>
>> with objCmd.Paramete rs
>> .Add("@client", SqlDbType.Char, 50).value = "the companies next test"
>> .Add("@datepost ed",SqlDbType.d atetime).value = dateposted.text
>> end with
>>
>> objCmd.ExecuteN onQuery
>>
> *************** *************** *************** *************** *************** *
>>
>> 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.
>>
>>
>
>