insert null into the database

7 Nov 2005 4:25 PM

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

7 Nov 2005 5:31 PM

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

7 Nov 2005 6:31 PM

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?

7 Nov 2005 5:39 PM

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/default.asp?url=/library/en-us/vbcon/html/vbtskHandlingNullValuesInDataset.asp

You can bascially use the IsColumnNameNull method of each column and then
SetColumnNameNull , that should do it for you.
<chambers***@hotmail.com> wrote in message
Show quote

news:1131380737.099723.91120@g44g2000cwa.googlegroups.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 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
>

7 Nov 2005 6:23 PM

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.