Inserting null dates and integers into a SQL Server 2000 table - how?!?!

Printable View

Feb 3rd, 2003, 12:03 AM
nzmike

Inserting null dates and integers into a SQL Server 2000 table - how?!?!

I have an app which uses stored procedures to select, insert, update and delete rows into various tables. The SP's run fine but I can't seem to insert values from my form with no value (eg: an empty text box) even though the relevant columns in the tables are set to accept nulls.

I'm doing this at the moment:

If Trim(txtCM_GraduationDate.Text) = "" Then
sqlCM_InsertContact.Parameters("@cnt_DateGraduate").Value = ????
Else
sqlCM_InsertContact.Parameters("@cnt_DateGraduate").Value = Trim(txtCM_GraduationDate.Text)
End If

Where I have the '???' above is where I want to insert a null smalldatetime - or do I have to use "01/01/1900" or something similiar? I really don't want any value in there - I actually want a null value. It's the same for integer/numeric fields - I want to write a null value to the appropriate column.

Cany anyone help me out this one - I have a huge deadline looming and can't seem to solve this (very simple) question - any help would be greatly appreciated!!

TIA...

Mike.
Feb 3rd, 2003, 12:24 AM
Pirate

Re: Inserting null dates and integers into a SQL Server 2000 table - how?!?!
Quote:

Originally posted by nzmike
I have an app which uses stored procedures to select, insert, update and delete rows into various tables. The SP's run fine but I can't seem to insert values from my form with no value (eg: an empty text box) even though the relevant columns in the tables are set to accept nulls.

I'm doing this at the moment:

If Trim(txtCM_GraduationDate.Text) = "" Then
sqlCM_InsertContact.Parameters("@cnt_DateGraduate").Value = ????
Else
sqlCM_InsertContact.Parameters("@cnt_DateGraduate").Value = Trim(txtCM_GraduationDate.Text)
End If

Where I have the '???' above is where I want to insert a null smalldatetime - or do I have to use "01/01/1900" or something similiar? I really don't want any value in there - I actually want a null value. It's the same for integer/numeric fields - I want to write a null value to the appropriate column.

Cany anyone help me out this one - I have a huge deadline looming and can't seem to solve this (very simple) question - any help would be greatly appreciated!!
TIA...
Mike.

try this
VB Code:
```
Dim nl As System.DBNull
```
Feb 3rd, 2003, 05:24 PM
nzmike

Thanx Pirate - I had tried using DBNull with no luck - adding the .Value made all he difference!

Mike.
Feb 3rd, 2003, 05:52 PM
techgnome

As far as I know, you can't actually insert a NULL value into a date field. I've found using '12/31/1899' to work the best. It seems that is the "NULL" equivelent when it comes to dates.
Feb 3rd, 2003, 06:47 PM
EagleEye

Quote:

Originally posted by nzmike
Thanx Pirate - I had tried using DBNull with no luck - adding the .Value made all he difference!

Mike.

Huh? Does this mean you go it to work? If so could you please explain what you did with the code you used above?

I gave myself a migraine today trying to get this SIMPLE task to work to no avail.

TIA
Feb 4th, 2003, 09:46 AM
EagleEye

I called Microsoft and they explained a few things for me.

If you are using an untyped dataset you can use the system.dbnull.value because you are setting the value to an object

If you are using a typed dataset you have to use code for an untyped dataset for that field in order to use System.DBNull.Value

Example:
Untyped Dataset Method -> this works just fine
<vbcode>
dataset.Tables("TABLENAME").Rows(ROWNUMBER).Item("FIELDNAME") = System.DBNull.Value
</vbcode>

Typed Dataset Method -> this does not work at all
<vbcode>
dataset.TABLENAME(ROWNUMBER).FIELDNAME = System.DBNull.Value
</vbcode>

The reason behind this is when you use the typed dataset method you are assigning a value to a PROPERTY of the typed dataset. When you use the UNtyped method you are setting a value to an OBJECT.
Feb 4th, 2003, 12:42 PM
Pirate

1 Attachment(s)

nzmike
this's what I'm talking about .It's working .I know it's not a solution but maybe a step for solution .
Feb 4th, 2003, 02:48 PM
Mr.No
nzmike

What if you handled it at the stored procedure level?
VB Code:
```
If Trim(txtCM_GraduationDate.Text) = "" Then 
sqlCM_InsertContact.Parameters("@cnt_DateGraduate").Value =   1899/12/30 
Else 
sqlCM_InsertContact.Parameters("@cnt_DateGraduate").Value = Trim(txtCM_GraduationDate.Text) 
End If
```
Then use and if condition inside the stored procedure

Code:

CASE WHEN @cnt_DateGraduate = '1899-12-30' INSERT INTO TABLE1 (col1, datecol) SELECT 1, null ELSE INSERT INTO TABLE1(col1, datecol) SELECT 1, @cnt_DateGraduate END

I haven't tried the code but I guess you have an idea.
Feb 4th, 2003, 05:48 PM
nzmike

Thanks everyone!!

Thanks for all your suggestions - especially you Pirate, 'preciate the effort in posting a demo app.

As mentioned in an earlier post, I solved this initially by using DBNull.Value which worked fine. I also set the default values in the stored procs to NULL.

The problem I then had was storing the null date when I read rows with null dates in (I store all the original values of each record I read as I'm doing optimistic locking) and it got to be so much hassle in the end I defaulted all the date values to 31/12/1899 and added a small validation routine to not let the users enter a date before 01/01/1900 - it all works fine, even if it's a bit of a hack!!

Cheers...

Mike.