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

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

[Pirate]

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

Dim nl As System.DBNull

[nzmike]

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

Mike.

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

[EagleEye]

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

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

[Pirate]

nzmike
this's what I'm talking about .It's working .I know it's not a solution but maybe a step for solution .

[Mr.No]

nzmike

What if you handled it at the stored procedure level?

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

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.

[nzmike]

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.