insert null into the database

[chambersdon]

I have an application that needs to insert nulls into the database and
I don't seem to be able to do this.
I am currently trying to do this with a Typed DataSet but I can't seem
to Insert Nulls (or udpate columns with a null value).
I have a column in a test table called 'anInteger'.
The Typed DataSet for this element has minOccurs="0" and the type =
xs:int.
To set this field to null I call the SetanIntegerNull method of my
typed DataSet.
I then set an InsertCommand on my adapater, set the parameter, and call
the adapter update command passing it the Typed DataSet.

I get an this error:
"Cast from type 'DBNull' to type 'Integer' is not valid".

What is the best way to insert nulls into the databnase? Most of the
posts I found have to do with reading null FROM the database and
converting it. I need to get them in the database.

I would think this is a common problem. What is the recommended
approach?

Some background:
I started with a custom data class with String and Integer properties
but these cannot be set to null so I had no way of getting nulls to the
SQL Strings I built.

I then tried replacing the data types of my properties with SQLTypes.
These caused more problem and word code with explict casts everytime I
reference a property. I've read a lot about using SQLTypes throughout
and it seem to cause more problem.

How can I get the null values into my database?

Thanks,
Don

 

[xhead]

With a typed dataset, you have a bunch of methods to test for DbNull
and set DbNull on a column.

dim ds as dsCustomer

If ds.IsCountryNull Then
' do something
End If

ds.SetCountryNull

Every Nullable column in the typed dataset will have an
..Is<columnName>Null that returns a boolean, and a .Set<columnName>Null
method.

Mike

 

[W.G. Ryan - MVP]

Are you using DbNull.Value?
You may find these helpful too
http://msdn.microsoft.com/msdnmag/issues/04/12/DataPoints/default.aspx
http://msdn.microsoft.com/library/d...con/html/vbtskHandlingNullValuesInDataset.asp

You can bascially use the IsColumnNameNull method of each column and then
SetColumnNameNull , that should do it for you.

 

[Don]

The second article showed how to set the value to be returned if the
DataColumn's value was null. It says to set the NullValue property in
the properties window. I do not have a property called NullValue. I
have one called nillable but it does not have the same options. The
column is in an <xs:element> tag. Is this correct? I have no problem
reading or writing to the columns. My only problem is when I want to
set it to null.

 

[Don]

I understand this. I used the d.SetCountryNull method and it does not
throws an an error. My problem comes when I actually try to insert
this new record.

The field is in the test table and is called anInteger. This is how I
do the insert.

Dim theTable As New Dataset1.testDataTable
Dim aRow as Dataset1.testRow

aRow = theTable.NewRow 'get a new row from the table
aRow.SetAnIntegerNull() 'AnInteger is my column

adapter.InsertCommand = New SqlClient.SqlCommand("Insert INTO test
(anInteger) " & _
" VALUES(@anInteger", conn)
adapter.InsertCommand.Parameters.Add("@anInteger", aRow.anInteger)
adapter.Update(theTable)

The 'Parameters.Add' call is where the exception occurs. Is there a
better way to insert a new row using a typed dataset?