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 SetanIntegerNul l 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.IsCountryNul l Then
' do something
End If

ds.SetCountryNu ll

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/is...s/default.aspx
http://msdn.microsoft.com/library/de...sInDataset.asp

You can bascially use the IsColumnNameNul l method of each column and then
SetColumnNameNu ll , that should do it for you.
<ch*********@ho tmail.com> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.com...

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 SetanIntegerNul l 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

[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.SetCountryNul l 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.testDa taTable
Dim aRow as Dataset1.testRo w

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

adapter.InsertC ommand = New SqlClient.SqlCo mmand("Insert INTO test
(anInteger) " & _
" VALUES(@anInteg er", conn)
adapter.InsertC ommand.Paramete rs.Add("@anInte ger", 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?