469,892 Members | 2,110 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,892 developers. It's quick & easy.

inserting NULL

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.


Jul 21 '05 #1
16 1684
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #2
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
"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP**********************@msnews.microsoft.com ...
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Jul 21 '05 #3
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

"bill" <be****@datamti.com>
...
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
"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP**********************@msnews.microsoft.com ...
bill <be****@datamti.com> wrote:
> 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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too


Jul 21 '05 #4
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]" <sk***@pobox.com> wrote in message
news:MP**********************@msnews.microsoft.com ...
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Jul 21 '05 #5
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #6
Cor Ligthert <no************@planet.nl> wrote:
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 - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #7
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]" <sk***@pobox.com>
Cor Ligthert <no************@planet.nl> wrote:
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 - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Jul 21 '05 #8
Cor Ligthert <no************@planet.nl> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #9
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]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Jul 21 '05 #10
bill <be****@datamti.com> wrote:
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?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #11
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]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
bill <be****@datamti.com> wrote:
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?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Jul 21 '05 #12
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #13
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]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Jul 21 '05 #14
bill <be****@datamti.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #15
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


Jul 21 '05 #16
Cor Ligthert <no************@planet.nl> wrote:
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)


Yup, spot on.
Thanks for showing that.


No problem :)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Jul 21 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by GreenFROG Design | last post: by
reply views Thread by Marko Poutiainen | last post: by
5 posts views Thread by dos360 | last post: by
reply views Thread by Edwin.Madari | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.