inserting NULL

[bill]

How can I insert a record with a null value in one of the fields?

I need to use the update method from a data adapter. The data is in a XML
file.

 

[Jon Skeet [C# MVP]]

How can I insert a record with a null value in one of the fields?

I need to use the update method from a data adapter. The data is in a XML
file.

Simply put a null (DBNull.Value) into the appropriate row/column of the
datatable. If this doesn't work, could you post a short but complete
program which demonstrates the problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.

 

[bill]

Thanks Jon
I'm using streamwriter to create a XML file.
Then I use ReadXML to read the XML file into the dataset.

Then I pass the dataset to the data adapter Update method to insert the data
into the database.

I just don't know what to put between the XML file field tags to indicate a
null value should be inserted.

It works fine otherwise.

-Bill

 

[Cor Ligthert]

Bill,

I assume that you create a XML dataset file using the streamwriter (how you
do that is not important).

Normally is setting nothing (not the word however really nothing) between
the tags the same as DBnull.value(null).

I hope this helps?

Cor

Thanks Jon
I'm using streamwriter to create a XML file.
Then I use ReadXML to read the XML file into the dataset.

Then I pass the dataset to the data adapter Update method to insert the
data
into the database.

I just don't know what to put between the XML file field tags to indicate
a
null value should be inserted.

It works fine otherwise.

-Bill

 

[bill]

Below is a sample of XML which I use for testing. The file name is
"c:\importdemo.xml".

My VB.NET webforms application has a data adapter named SQLDataAdapter1 on
the form which is configured with a SQL statement connection to a table
named tImportData.

This code works fine as long as I don't try to insert a null value in any
fields.

If I put nothing between the XML tags, it inserts an empty string in varchar
fields. In integer fields, there is an error:

System.InvalidCastException: - Cast from string "Input string was not in
a correc" to type Integer is not valid.



My VB.NET code reads:

Dim ds as New Dataset
ds.ReadXML("c:\importdemo.xml")
Me.SQLDataAdapter1.Update(ds)

The XML source data:

<?xml version="1.0" encoding="utf-8"?>
<root>

<tImportDemo>

<Name>smith</Name>

<EntryDate>9/17/04</EntryDate>

<Zip></Zip> This inserts an
empty string

<CompanyID></CompanyID> This generates error. The table
def allows nulls

</tImportDemo>
</root>

 

[Jon Skeet [C# MVP]]

Normally is setting nothing (not the word however really nothing) between
the tags the same as DBnull.value(null).

Hmm... I'd be surprised if that were the case for a string column,
although I can't say I've used XML dataset representation enough to
know for sure. I'd expect that to represent an empty string, rather
than a null value, if you see what I mean. (For non-string columns it
would be okay, of course.)

From a quick test (and it *is* only a quick test) it looks to me like
(for string columns at least) when you use WriteXml, null columns
aren't written at all, whereas empty columns are written with tags with
no content. In other words, the XML:

<NewDataSet>
<Foo>
<Baz>x</Baz>
</Foo>
<Foo>
<Bar />
<Baz>y</Baz>
</Foo>
</NewDataSet>

has one row with Baz="x", Bar=DBNull.Value, and one row with Baz="y"
and Bar="".

 

[Jon Skeet [C# MVP]]

Below is a sample of XML which I use for testing. The file name is
"c:\importdemo.xml".

My VB.NET webforms application has a data adapter named SQLDataAdapter1 on
the form which is configured with a SQL statement connection to a table
named tImportData.

This code works fine as long as I don't try to insert a null value in any
fields.

If I put nothing between the XML tags, it inserts an empty string in varchar
fields. In integer fields, there is an error:

System.InvalidCastException: - Cast from string "Input string was not in
a correc" to type Integer is not valid.

Try removing the CompanyID tag entirely. Given my little test earlier,
that may well do what you want.

 

[Cor Ligthert]

Jon,

You brought me in doubt,

I have it in a program where I delete the empty element tags while writing.

However I have kept open in the reading part that the value is changed and
set empty by hand as well.

I will not say my answer was wrong, however can be confusing and is not
complete, therefore as I have it in that program, with two inline comments
in the confusing part.

It is confusing in VBNet because of the "Is" and "=" nothing and probably
even more confusing in CSharp (just assuming I don't know that) It can be
interesting in my opinion when you show this in CSharp. (velden is an array
of strings with names)

\\\
If Not drXML.Item(velden(i)) Is Nothing Then
' The Element does not exist
If drXML.Item(velden(i)).tostring <> Nothing Then
'the Element is not empty
drSQL(velden(i)) = drXML.Item(velden(i))
Else
drSQL(velden(i)) = System.DBNull.Value
End If
Else
drSQL(velden(i)) = System.DBNull.Value
End If
///

I hope this explains it better?

Cor

 

[Jon Skeet [C# MVP]]

I have it in a program where I delete the empty element tags while writing.

However I have kept open in the reading part that the value is changed and
set empty by hand as well.

I will not say my answer was wrong, however can be confusing and is not
complete, therefore as I have it in that program, with two inline comments
in the confusing part.

Well, I think your answer *was* wrong, because loading in a dataset
with an empty element inserts an empty string, *not* a DBNull. You can
easily test it for yourself using ReadXml first including an empty tag,
and then not, and using DataRow.IsNull to test for nullity.

It is confusing in VBNet because of the "Is" and "=" nothing and probably
even more confusing in CSharp (just assuming I don't know that) It can be
interesting in my opinion when you show this in CSharp. (velden is an array
of strings with names)

I think it's actually *less* confusing in C# because there's no exact
equivalent of "Nothing" - there's just null, and an empty string is
entirely different to that.

 

[bill]

I tried that and got:
5 prepared statement '(@col1 int,@col2 varchar(25),@col3 nvarchar(10),@'
expects parameter @columnname, which was not supplied

Thanks
Bill

 

[Jon Skeet [C# MVP]]

I tried that and got:
5 prepared statement '(@col1 int,@col2 varchar(25),@col3 nvarchar(10),@'
expects parameter @columnname, which was not supplied

Well, how are you setting up the parameters?

Also, when your dataset has been loaded, does it correctly know that
the column is there at all?

 

[bill]

When I add the SQL Data Adapter to the web forms page, the wizard configures
the update, insert, and delete statements automatically. The insert works
fine if data is included for all fields in the source XML file.

This error occurs if any of the fields are ommitted from the XML source
file.

The database allows nulls in the fields.

 

[Jon Skeet [C# MVP]]

When I add the SQL Data Adapter to the web forms page, the wizard configures
the update, insert, and delete statements automatically. The insert works
fine if data is included for all fields in the source XML file.

This error occurs if any of the fields are ommitted from the XML source
file.

The database allows nulls in the fields.

I'm afraid it's hard to tell without seeing some code at this stage.

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.

 

[bill]

I know it is difficult to identify the problem without seeing the
application.

However, I believe the problem is in the configuration of the SQL Data
Adapter, which is created graphically using the wizard, so I can't provide
that part of the app.

I will re-write the application without using the wizard, instead
instantiating the Data Adapter in code, including the insert statement.
Perhaps this will work! If not, I will post the code on the newsgroup.

Currently, the only written code is
Dim ds as New Dataset
ds.ReadXML("c:\importdemo.xml")
Me.SQLDataAdapter1.Update(ds)

 

[Jon Skeet [C# MVP]]

I know it is difficult to identify the problem without seeing the
application.

However, I believe the problem is in the configuration of the SQL Data
Adapter, which is created graphically using the wizard, so I can't provide
that part of the app.

Yes you can - it's still in the code!

I will re-write the application without using the wizard, instead
instantiating the Data Adapter in code, including the insert statement.
Perhaps this will work! If not, I will post the code on the newsgroup.

Currently, the only written code is
Dim ds as New Dataset
ds.ReadXML("c:\importdemo.xml")

And does that XML contain schema information? Does it contain the
appropriate column for *any* of the rows? If not, that's probably
what's wrong. The table will need to know which columns it should have
- your XML file should either contain the schema, or you should have
some code to add any columns which might have been missed out.

 

[Cor Ligthert]

Jon,

Well, I think your answer *was* wrong, because loading in a dataset
with an empty element inserts an empty string, *not* a DBNull. You can
easily test it for yourself using ReadXml first including an empty tag,
and then not, and using DataRow.IsNull to test for nullity.

My answer was wrong and than I readed it afterwards wrong myself.
To clear it more,
an item with a dbnull.value will not create an element (node)
a nothing item wil create an empty element (node)

Thanks for showing that.

Cor

 

[Jon Skeet [C# MVP]]

My answer was wrong and than I readed it afterwards wrong myself.
To clear it more,
an item with a dbnull.value will not create an element (node)
a nothing item wil create an empty element (node)

Yup, spot on.

Thanks for showing that.

No problem