MBR IT/.NET 247 : 1/1/1900 inserted rather than nothing on ASPFriends.com 'ngfx-sqlclient' list

1/1/1900 inserted rather than nothing

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Bryan Andrews

I have tried several combinations of this (below) yet the date 1/1/1900
ends up posting to my dbase in a field I put up for birthdates.

If Not txtEfBirthdate.Text Is Nothing Then
cmdInsert.Parameters.Add("@efBirthDate", txtEfBirthdate.Text)

The goal is to not insert any date if nothing is entered in the field.

The column is a smalltimedate with allow nulls checked.

Any advice on this is appreciated.

Reply to this message...

David L. Penton

You must insert a DBNull instead of an empty string. SQL Server interprets
a ZLS (zero length string) as 1900-01-01 00:00:00

David L. Penton, Microsoft MVP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Bryan Andrews [mailto:Click here to reveal e-mail address]

-- Moved from [aspngarchitecture] to [ngfx-sqlclient] by Marcie Jones
<Click here to reveal e-mail address> --

I have tried several combinations of this (below) yet the date 1/1/1900
ends up posting to my dbase in a field I put up for birthdates.
=20
If Not txtEfBirthdate.Text Is Nothing Then
cmdInsert.Parameters.Add("@efBirthDate", txtEfBirthdate.Text)
=20
The goal is to not insert any date if nothing is entered in the field.=20
=20
The column is a smalltimedate with allow nulls checked.
=20
=20
Any advice on this is appreciated.
=20

Reply to this message...

Bryan Andrews

Sorry but how can I do this?

I theoretically would like to do this:

            If txtEfBirthdate.Text Is Nothing Then
                txtEfBirthdate.Text =3D System.DBNull

            End If

But this is not working (or any variation I have tried).

Thanks for the thoughts.

-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: Sunday, April 07, 2002 12:02 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: 1/1/1900 inserted rather than nothing

You must insert a DBNull instead of an empty string. SQL Server
interprets
a ZLS (zero length string) as 1900-01-01 00:00:00

David L. Penton, Microsoft MVP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Bryan Andrews [mailto:Click here to reveal e-mail address]

-- Moved from [aspngarchitecture] to [ngfx-sqlclient] by Marcie Jones
<Click here to reveal e-mail address> --

I have tried several combinations of this (below) yet the date 1/1/1900
ends up posting to my dbase in a field I put up for birthdates.
=3D20
If Not txtEfBirthdate.Text Is Nothing Then
cmdInsert.Parameters.Add("@efBirthDate", txtEfBirthdate.Text)
=3D20
The goal is to not insert any date if nothing is entered in the
field.=3D20
=3D20
The column is a smalltimedate with allow nulls checked.
=3D20
=3D20
Any advice on this is appreciated.
=3D20

| [ngfx-sqlclient] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp =3D JOIN/QUIT

Reply to this message...

darren

On 07 April 2002 at 06:40:28, ngfx-sqlclient <Click here to reveal e-mail address> wrote:

BA> I theoretically would like to do this:

BA> If txtEfBirthdate.Text Is Nothing Then
BA> txtEfBirthdate.Text = System.DBNull
BA> End If

you have to refer to System.DBNull.Value.

however, i haven't found a way to insert nulls for date or integer
types when using command parameters. it always fails on the execute
command.

if you set the parameter to default to null in the stored proc and
don't include the parameter, things work fine. trying to add a
parameter with a null value throws an error.

anyone any ideas on how to do this??

cheers,

darren.

Reply to this message...

Curtis Swartzentruber

Hi,

It works fine to pass a parameter a null value.
There are a few tricks you have to use.
Here is the wrapper function I wrote to create
and add parameters to a SQLCommand. I use this
in all my data access code and haven't had a problem.

Public Sub AppendSQLParameter(ByRef cmd As SqlCommand, _
ByVal strParamName As String, _
ByVal strDataType As SqlDbType, _
ByVal strDirection As ParameterDirection, _
ByVal lngSize As Integer, _
ByVal varValue As Object)

Dim param As SqlParameter

If lngSize > 0 Then
param = New SqlParameter(strParamName, strDataType, lngSize)
Else
param = New SqlParameter(strParamName, strDataType)
End If
param.Direction = strDirection
If Not strDirection = ParameterDirection.Output Then
If Not ((varValue Is System.DBNull.Value) Or (varValue Is
Nothing)) Then
param.Value = varValue
Else
param.Value = System.DBNull.Value
End If
End If

cmd.Parameters.Add(param)

End Sub

Then I just pass this function the value Nothing when I want
a null value added and it works fine.

AppendSQLParameter(cmd, "@DateToSend", SqlDbType.DateTime,
ParameterDirection.Input, 8, DateToSend)

where I have set DateToSend to Nothing

Curtis
[Original message clipped]

Reply to this message...

Bryan Andrews

I have tried almost every combination of this and cannot seem to keep my
code from adding 1/1/1900 to my db!

If Not txtEfBirthdate.Text Is Nothing Then
cmdInsert.Parameters.Add("@efBirthDate", txtEfBirthdate.Text)
Else
cmdInsert.Parameters.Add("@efBirthDate", System.DBNull.Value)
End If

Any ideas are appreciated!!

-----Original Message-----
From: darren [mailto:Click here to reveal e-mail address]
Sent: Sunday, April 07, 2002 7:27 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: 1/1/1900 inserted rather than nothing

On 07 April 2002 at 06:40:28, ngfx-sqlclient
<Click here to reveal e-mail address> wrote:

BA> I theoretically would like to do this:

BA> If txtEfBirthdate.Text Is Nothing Then
BA> txtEfBirthdate.Text =3D System.DBNull
BA> End If

you have to refer to System.DBNull.Value.

however, i haven't found a way to insert nulls for date or integer
types when using command parameters. it always fails on the execute
command.

if you set the parameter to default to null in the stored proc and
don't include the parameter, things work fine. trying to add a
parameter with a null value throws an error.

anyone any ideas on how to do this??

cheers,

darren.

| [ngfx-sqlclient] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp =3D JOIN/QUIT

Reply to this message...

Iain Smallwood

Is it possible that Curtis' post constructor assignment of the null =
value holds the key? It is not clear whether you noticed his post. I =
happen to have assigned the System.DBNull.Value value to the param AFTER =
the constructor has been done myself and that seems to work OK.=20

If that is no good then have you investigated the possibility of some =
server end override that you left in by accident?

cheers,
Iain

-----Original Message-----
From: Bryan Andrews [mailto:Click here to reveal e-mail address]
Sent: 09 April 2002 11:40
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: 1/1/1900 inserted rather than nothing

I have tried almost every combination of this and cannot seem to keep my
code from adding 1/1/1900 to my db!

If Not txtEfBirthdate.Text Is Nothing Then
cmdInsert.Parameters.Add("@efBirthDate", txtEfBirthdate.Text)
Else
cmdInsert.Parameters.Add("@efBirthDate", System.DBNull.Value)
End If

Any ideas are appreciated!!

-----Original Message-----
From: darren [mailto:Click here to reveal e-mail address]
Sent: Sunday, April 07, 2002 7:27 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: 1/1/1900 inserted rather than nothing

On 07 April 2002 at 06:40:28, ngfx-sqlclient
<Click here to reveal e-mail address> wrote:

BA> I theoretically would like to do this:

BA> If txtEfBirthdate.Text Is Nothing Then
BA> txtEfBirthdate.Text =3D System.DBNull
BA> End If

you have to refer to System.DBNull.Value.

however, i haven't found a way to insert nulls for date or integer
types when using command parameters. it always fails on the execute
command.

if you set the parameter to default to null in the stored proc and
don't include the parameter, things work fine. trying to add a
parameter with a null value throws an error.

anyone any ideas on how to do this??

cheers,

darren.

| [ngfx-sqlclient] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp =3D JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp =3D JOIN/QUIT

Reply to this message...

Richard Method II

This is the code that I use, and it works every time.

If dob = "" Then

SqlCmd.Parameters.Add(new SqlParameter("@dob",
SqlDbType.SmallDateTime, 4))
SqlCmd.Parameters("@dob").Value = DBNull.Value

Else

SqlCmd.Parameters.Add(new SqlParameter("@dob",
SqlDbType.SmallDateTime, 4))
SqlCmd.Parameters("@dob").Value = dob

End if

Hope it helps.
Richard M.

Bryan Andrews wrote:

[Original message clipped]

Reply to this message...

Matt Serdar

If all else fails you can always put in code into the sproc like so

INSERT INTO Users
(
BirthDate
)
VALUES
(
CASE @BDay WHEN '' THEN NULL ELSE @BDay END
)

-----Original Message-----
From: Richard Method II [mailto:Click here to reveal e-mail address]
Sent: Tuesday, April 09, 2002 8:04 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: 1/1/1900 inserted rather than nothing

This is the code that I use, and it works every time.

If dob = "" Then

SqlCmd.Parameters.Add(new SqlParameter("@dob",
SqlDbType.SmallDateTime, 4))
SqlCmd.Parameters("@dob").Value = DBNull.Value

Else

SqlCmd.Parameters.Add(new SqlParameter("@dob",
SqlDbType.SmallDateTime, 4))
SqlCmd.Parameters("@dob").Value = dob

End if

Hope it helps.
Richard M.

Bryan Andrews wrote:

[Original message clipped]

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

Curtis Swartzentruber

Iain

I think there is something to that (the assigment
post constructor idea). Might even be
a bug, I seem to recall reading something about it.
It took me awhile to get things working consistently
(this was back in Beta 2), but now I use that function
everywhere I am dealing with SQL parameters and never
had a problem.

curtis
[Original message clipped]

Reply to this message...

Bob Levittan (VIP)

Since paramter names now actually count, the way to do it is this:

0) make sure the table column allows null

1) make the parameter optional in the stored proc - that is:

@efBirthDate DateTime = Null

2) In your code:

If Not txtEfBirthdate.Text Is Nothing Then
cmdInsert.Parameters.Add("@efBirthDate", txtEfBirthdate.Text)
End if

that is, when txtEfBirthdate.Text is Nothing/Null then DON'T add the
parameter. When you do this, the default value (null in this case) is
inserted into the table.

Bob Levittan
BBK Consulting, Inc.

----Original Message Follows----
From: Curtis Swartzentruber <Click here to reveal e-mail address>
Reply-To: "ngfx-sqlclient" <Click here to reveal e-mail address>
To: "ngfx-sqlclient" <Click here to reveal e-mail address>
Subject: [ngfx-sqlclient] RE: 1/1/1900 inserted rather than nothing
Date: Wed, 10 Apr 2002 12:46:40 -0500

Iain

I think there is something to that (the assigment
post constructor idea). Might even be
a bug, I seem to recall reading something about it.
It took me awhile to get things working consistently
(this was back in Beta 2), but now I use that function
everywhere I am dealing with SQL parameters and never
had a problem.

curtis
[Original message clipped]

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Bob Levittan
BBK Consulting, Inc.

_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

Reply to this message...

Curtis Swartzentruber

well, optional parameters should be used with
caution. i rarely use this approach as it can
lead to difficulty in troubleshooting. it is
generally better to explicitly pass a null if
that is indeed what you want. it is also a good
idea to only use nullable columns if it actually
makes sense for that piece of data. Most data
should have a value of some sort.

regards,

curtis

[Original message clipped]

Reply to this message...

Bob Levittan (VIP)

Perhaps it escaped your attention, but the original question asked how to
insert nulls in a date column using parameters. I'm not advocating anything,
just answering the question.

----Original Message Follows----
From: Curtis Swartzentruber <Click here to reveal e-mail address>
Reply-To: "ngfx-sqlclient" <Click here to reveal e-mail address>
To: "ngfx-sqlclient" <Click here to reveal e-mail address>
Subject: [ngfx-sqlclient] RE: 1/1/1900 inserted rather than nothing
Date: Thu, 11 Apr 2002 11:19:31 -0500

well, optional parameters should be used with
caution. i rarely use this approach as it can
lead to difficulty in troubleshooting. it is
generally better to explicitly pass a null if
that is indeed what you want. it is also a good
idea to only use nullable columns if it actually
makes sense for that piece of data. Most data
should have a value of some sort.

regards,

curtis

[Original message clipped]

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Bob Levittan
BBK Consulting, Inc.

_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

Reply to this message...

Roy Rodsson

Hi all, I'm trying to do something similar but I have a strong typed dataset with a field that is a datetime field.
However, When the dataset is returned to my dataaccess layer and I try to apply the updatedataset for the dataadapter, I get a SQLException saying that I cannot convert DBNull to Date..
However, the original field in the data base is already Null! .
Please help!

--------------------------------
From: Roy Rodsson

Reply to this message...

	System.Data.ParameterDirection
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlParameter
	System.Data.SqlDbType
	System.DateTime
	System.DBNull

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com