By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,356 Members | 1,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,356 IT Pros & Developers. It's quick & easy.

insert null into the database

P: n/a
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

Nov 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 19 '05 #2

P: n/a
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 IsColumnNameNull method of each column and then
SetColumnNameNull , that should do it for you.
<ch*********@hotmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.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

Nov 19 '05 #3

P: n/a
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.

Nov 19 '05 #4

P: n/a
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?

Nov 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.