database INSERT choking on dbNULL values

[JohnR]

Hi, I'm trying to update a DBF file which I'm using VB.NET ODBC adapters,
commands and connections. I fill the dataset and databind the columns to
textboxes on my form. I can successfully view, delete and update the
records. The problem is when I try to add a new record.

Because I actually have a few controls bound to the same table I use
FORM.BINDINGCONTEXT to keep it all synced. So, using the
form.bindingcontext.addnew method. my textboxes clear and I enter 2 or 3 of
the 7 fields. When I hit my save button which does a
BindingContext.EndCurrentEdit and creates a new dataset (dsChanged) with
only the "changed" records, then I do a dataAdapter.Update(dsChanged) and it
throws an exception stating something about my first unentered field is
DBNull. and the database does not allow that. If I actually fill in the
field that I got the error on and try again, the error "moves" to the next
unentered field.

I've tried a couple of ways programatically "blank out" the fields in the
dsChanged dataset, but they remain (the debugger quickview confirms this) a
DBNull value.

How can I eliminate the DBNull values from my dataset programatically? Any
suggestions gratefully accepted.

[Cor Ligthert]

John,

As I have seen is ODBC rarely used by visitors to this newsgroups. The best
change on an answerer you therefore will have in a more dedicated (active)
newsgroup like.

Adonet

Adonet
news://msnews.microsoft.com/microsof...amework.adonet

Web interface:
http://communities2.microsoft.com/co...amework.adonet

I hope this helps,

Cor

[Nick Malik [Microsoft]]

The problem is not your code... it is a disconnect between your code and the
expectations of your database.

If you expect to be able to enter two of the seven fields and save the
record, then the other five fields must allow null values in the database.
If the other five fields do not allow nulls, you have to ask yourself: did
the database developer (was it you?) set up the column nullability correctly
on those columns.

In other words, the right answer may be to fix the database, and not your
code.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"JohnR" <Jo******@hotmail.com> wrote in message
news:0a1%d.9992$Ue6.7916@trndny04...

Hi, I'm trying to update a DBF file which I'm using VB.NET ODBC adapters,
commands and connections. I fill the dataset and databind the columns to
textboxes on my form. I can successfully view, delete and update the
records. The problem is when I try to add a new record.

Because I actually have a few controls bound to the same table I use
FORM.BINDINGCONTEXT to keep it all synced. So, using the
form.bindingcontext.addnew method. my textboxes clear and I enter 2 or 3
of the 7 fields. When I hit my save button which does a
BindingContext.EndCurrentEdit and creates a new dataset (dsChanged) with
only the "changed" records, then I do a dataAdapter.Update(dsChanged) and
it throws an exception stating something about my first unentered field is
DBNull. and the database does not allow that. If I actually fill in the
field that I got the error on and try again, the error "moves" to the next
unentered field.

I've tried a couple of ways programatically "blank out" the fields in
the dsChanged dataset, but they remain (the debugger quickview confirms
this) a DBNull value.

How can I eliminate the DBNull values from my dataset programatically?
Any suggestions gratefully accepted.

[JohnR]

Hi Nick,

You have a very good point, and I will check it out. However the DB I'm
using now is just a test for when I convert an old VB6 application to VB.NET
and I wanted to make sure I understood how to do all the things I need to do
for the conversion.

The actual production DB may, in fact, not allow null values, so my
question is this: After I load the dsChanged dataset with the newly
inserted row, I try to replace any nulls with a blank string: here's a code
snippet:

dsChanged = dsTele.GetChanges() 'get all the changed rows and stick them in
a dataset

If Not dsChanged Is Nothing Then

For Each xRow As DataRow In dsChanged.Tables("rolodex").Rows

If Not xRow.RowState = DataRowState.Deleted Then 'can't change field on del
rec

'scan remaining columns to ck for null entries

For i As Int32 = 0 To xRow.ItemArray.GetLength(0) - 1

xRow.ItemArray(i) = utility.NullSafeString(xRow.ItemArray(i), " ")

Next

End If

Next

End If

The nullsavestring will stick the 2nd parameter (the " ") into the 1st
parameter (xRow.ItemArray(i) ) if it is null.

Do you see anything wrong with this code? Cause it doesn't seem to work,
even though when I trace it thru debug it seems like it should work.

Thanks, John

"Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message
news:yr********************@comcast.com...

The problem is not your code... it is a disconnect between your code and
the expectations of your database.

If you expect to be able to enter two of the seven fields and save the
record, then the other five fields must allow null values in the database.
If the other five fields do not allow nulls, you have to ask yourself: did
the database developer (was it you?) set up the column nullability
correctly on those columns.

In other words, the right answer may be to fix the database, and not your
code.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"JohnR" <Jo******@hotmail.com> wrote in message
news:0a1%d.9992$Ue6.7916@trndny04...

Hi, I'm trying to update a DBF file which I'm using VB.NET ODBC adapters,
commands and connections. I fill the dataset and databind the columns to
textboxes on my form. I can successfully view, delete and update the
records. The problem is when I try to add a new record.

Because I actually have a few controls bound to the same table I use
FORM.BINDINGCONTEXT to keep it all synced. So, using the
form.bindingcontext.addnew method. my textboxes clear and I enter 2 or 3
of the 7 fields. When I hit my save button which does a
BindingContext.EndCurrentEdit and creates a new dataset (dsChanged) with
only the "changed" records, then I do a dataAdapter.Update(dsChanged) and
it throws an exception stating something about my first unentered field
is DBNull. and the database does not allow that. If I actually fill in
the field that I got the error on and try again, the error "moves" to the
next unentered field.

I've tried a couple of ways programatically "blank out" the fields in
the dsChanged dataset, but they remain (the debugger quickview confirms
this) a DBNull value.

How can I eliminate the DBNull values from my dataset programatically?
Any suggestions gratefully accepted.

[JohnR]

Hi All,

Well, I found the answer to my own problem.... The line:

xRow.ItemArray(i) = utility.NullSafeString(xRow.ItemArray(i), " ")

was not updating the columns in the dataset as expected. when I changed the
ItemArray to Item like this:

xRow.Item(i) = utility.NullSafeString(xRow.Item(i), " ")

it worked fine. Now I have to go back and study the difference between
using Item and ItemArray.

John

"JohnR" <Jo******@hotmail.com> wrote in message
news:8Tl%d.12054$I16.11095@trndny03...

Hi Nick,

You have a very good point, and I will check it out. However the DB
I'm using now is just a test for when I convert an old VB6 application to
VB.NET and I wanted to make sure I understood how to do all the things I
need to do for the conversion.

The actual production DB may, in fact, not allow null values, so my
question is this: After I load the dsChanged dataset with the newly
inserted row, I try to replace any nulls with a blank string: here's a
code snippet:

dsChanged = dsTele.GetChanges() 'get all the changed rows and stick them
in a dataset

If Not dsChanged Is Nothing Then

For Each xRow As DataRow In dsChanged.Tables("rolodex").Rows

If Not xRow.RowState = DataRowState.Deleted Then 'can't change field on
del rec

'scan remaining columns to ck for null entries

For i As Int32 = 0 To xRow.ItemArray.GetLength(0) - 1

xRow.ItemArray(i) = utility.NullSafeString(xRow.ItemArray(i), " ")

Next

End If

Next

End If

The nullsavestring will stick the 2nd parameter (the " ") into the 1st
parameter (xRow.ItemArray(i) ) if it is null.

Do you see anything wrong with this code? Cause it doesn't seem to work,
even though when I trace it thru debug it seems like it should work.

Thanks, John

"Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message
news:yr********************@comcast.com...

The problem is not your code... it is a disconnect between your code and
the expectations of your database.

If you expect to be able to enter two of the seven fields and save the
record, then the other five fields must allow null values in the
database.
If the other five fields do not allow nulls, you have to ask yourself:
did the database developer (was it you?) set up the column nullability
correctly on those columns.

In other words, the right answer may be to fix the database, and not your
code.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"JohnR" <Jo******@hotmail.com> wrote in message
news:0a1%d.9992$Ue6.7916@trndny04...

Hi, I'm trying to update a DBF file which I'm using VB.NET ODBC
adapters, commands and connections. I fill the dataset and databind the
columns to textboxes on my form. I can successfully view, delete and
update the records. The problem is when I try to add a new record.

Because I actually have a few controls bound to the same table I use
FORM.BINDINGCONTEXT to keep it all synced. So, using the
form.bindingcontext.addnew method. my textboxes clear and I enter 2 or 3
of the 7 fields. When I hit my save button which does a
BindingContext.EndCurrentEdit and creates a new dataset (dsChanged) with
only the "changed" records, then I do a dataAdapter.Update(dsChanged)
and it throws an exception stating something about my first unentered
field is DBNull. and the database does not allow that. If I actually
fill in the field that I got the error on and try again, the error
"moves" to the next unentered field.

I've tried a couple of ways programatically "blank out" the fields in
the dsChanged dataset, but they remain (the debugger quickview confirms
this) a DBNull value.

How can I eliminate the DBNull values from my dataset programatically?
Any suggestions gratefully accepted.