insert null into the database

  • Thread starter Thread starter chambersdon
  • Start date Start date
C

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
 
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
 
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.
 
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?
 
Back
Top