|
| 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.
|
|
|
| |
|
| |
| |
| 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
|
|
|
| |
|
| |
|
| |
| 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
|
|
|
| |
|
|
| |
|
| |
| 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.
|
|
|
| |
|
| |
|
| |
| 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]
|
|
|
| |
|
| |
|
| |
| 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
|
|
|
| |
|
|
| |
|
| |
| 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
|
|
|
| |
|
| |
|
| |
| 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]
|
|
|
| |
|
| |
|
| |
| 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
|
|
|
| |
|
|
| |
|
| |
| 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]
|
|
|
| |
|
| |
|
| |
| 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
|
|
|
| |
|
|
| |
|
| |
| 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]
|
|
|
| |
|
|
| |
|
| |
| 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
|
|
|
| |
|
|
| |
| |
| 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
|
|
|
| |
|
| |
|
|
|
|
|
|
|
|
|
BootFX
Reliable and powerful .NET application framework. |
|
|
|
|
|
|
