Dataset and MS Access

  • Thread starter Thread starter Scott D. Barrish
  • Start date Start date
S

Scott D. Barrish

There are several issues that I need help on. According to the examples
I've seen in books, MSDN, and news groups, this should be a very simple
exercise. I have found often that simple exercises fail to point out a DUH!
factor which makes me say DOH!

1) When I run the Data Adapter wizard, it creates the INSERT and SELECT
command objects. However, it gives me an error on the generation of the
UPDATE and DELETE command objects. So, I manually entered the necessary
code for the Update Command Object for the Data Adapter. In the properties
window, it displays the 3 command objects.

2) I have created a dataset and bound my controls on my form. I am able to
successfully navigate forward and backward through the table on my form.

3) When I update a field and click on the Update command button, nothing
happens because when I close the application and view the database table
directly through Access, the field was never modified. However, if I modify
the field, then navigate 1 before or after the modified record, modify a
field on the new record, click on the submit button, the first record that
was "updated" shows as modified when viewed in Access but the 2nd record is
unchanged.

4) The code for the cmdUpdate button is:
myDataAdapter(dsComputerList1).Update() [VB.NET]

Would appreciate some guidence on this matter.

Sincerely,
Scott D. Barrish
 
Scott:

It's hard to tell without seeing the statments, but the first thing that
comes to mind is verifying that you have a primary key...that will cause
some problems with the commandbuilder or the DA configuration wizard.

The next thing to do is verify that there are changes in the dataset b/c
even if the update/delete logic is correct, if you don't have any changes in
your dataset, it won't fire...to do this, add this line of code right before
the update and if you don't get a big ugly assertion box, then you have
changes and the problem is probably with the update/delete statement..

Debug.Assert(myDataSet.HasChanges)

After that, I'd make sure I have file access to write to the db, but if you
didn't that wouldn't explain the error in the configuration wizard so it's
doubtful thisis the problem.
 
William,

1) I do get a big ugly dirty message box when modify one field and click on
the Update button
2) When I modify 1 field, navigate to another record, and click on Update
button, I don't get a dialog box. When I inspect the database table, I see
that the record has infact been updated.

3) When it comes to the Access file, the table [Computer List] does have a
primary key designated: IP_Address. Also, there is an index PrimaryKey_IP.
This index is set to Primary, Unique, and does not accept null. I don't
know why then the DA Wizard is having a difficult time identifying the
unique column to generate the Update and Delete command objects.

Sincerely,
Scott D. Barrish

William Ryan eMVP said:
Scott:

It's hard to tell without seeing the statments, but the first thing that
comes to mind is verifying that you have a primary key...that will cause
some problems with the commandbuilder or the DA configuration wizard.

The next thing to do is verify that there are changes in the dataset b/c
even if the update/delete logic is correct, if you don't have any changes in
your dataset, it won't fire...to do this, add this line of code right before
the update and if you don't get a big ugly assertion box, then you have
changes and the problem is probably with the update/delete statement..

Debug.Assert(myDataSet.HasChanges)

After that, I'd make sure I have file access to write to the db, but if you
didn't that wouldn't explain the error in the configuration wizard so it's
doubtful thisis the problem.

Scott D. Barrish said:
There are several issues that I need help on. According to the examples
I've seen in books, MSDN, and news groups, this should be a very simple
exercise. I have found often that simple exercises fail to point out a DUH!
factor which makes me say DOH!

1) When I run the Data Adapter wizard, it creates the INSERT and SELECT
command objects. However, it gives me an error on the generation of the
UPDATE and DELETE command objects. So, I manually entered the necessary
code for the Update Command Object for the Data Adapter. In the properties
window, it displays the 3 command objects.

2) I have created a dataset and bound my controls on my form. I am able to
successfully navigate forward and backward through the table on my form.

3) When I update a field and click on the Update command button, nothing
happens because when I close the application and view the database table
directly through Access, the field was never modified. However, if I modify
the field, then navigate 1 before or after the modified record, modify a
field on the new record, click on the submit button, the first record that
was "updated" shows as modified when viewed in Access but the 2nd record is
unchanged.

4) The code for the cmdUpdate button is:
myDataAdapter(dsComputerList1).Update() [VB.NET]

Would appreciate some guidence on this matter.

Sincerely,
Scott D. Barrish
Click to expand...
Click to expand...
 
If you are getting the big ugly box the assertion is failing, that means
that the value (it was an int variable, I forget the name), has a value of 0
unless it's getting set elsewhere b/c that line of code exists right before
the assertion fails.

That's interesting about the Primary Key...just to be thorough, are any of
your column/field names reserved words like username or password?
Scott D. Barrish said:
William,

1) I do get a big ugly dirty message box when modify one field and click on
the Update button
2) When I modify 1 field, navigate to another record, and click on Update
button, I don't get a dialog box. When I inspect the database table, I see
that the record has infact been updated.

3) When it comes to the Access file, the table [Computer List] does have a
primary key designated: IP_Address. Also, there is an index PrimaryKey_IP.
This index is set to Primary, Unique, and does not accept null. I don't
know why then the DA Wizard is having a difficult time identifying the
unique column to generate the Update and Delete command objects.

Sincerely,
Scott D. Barrish

William Ryan eMVP said:
Scott:

It's hard to tell without seeing the statments, but the first thing that
comes to mind is verifying that you have a primary key...that will cause
some problems with the commandbuilder or the DA configuration wizard.

The next thing to do is verify that there are changes in the dataset b/c
even if the update/delete logic is correct, if you don't have any
Click to expand...
changes
in
your dataset, it won't fire...to do this, add this line of code right before
the update and if you don't get a big ugly assertion box, then you have
changes and the problem is probably with the update/delete statement..

Debug.Assert(myDataSet.HasChanges)

After that, I'd make sure I have file access to write to the db, but if you
didn't that wouldn't explain the error in the configuration wizard so it's
doubtful thisis the problem.

Scott D. Barrish said:
There are several issues that I need help on. According to the examples
I've seen in books, MSDN, and news groups, this should be a very simple
exercise. I have found often that simple exercises fail to point out
Click to expand...
a
DUH!
factor which makes me say DOH!

1) When I run the Data Adapter wizard, it creates the INSERT and SELECT
command objects. However, it gives me an error on the generation of the
UPDATE and DELETE command objects. So, I manually entered the necessary
code for the Update Command Object for the Data Adapter. In the properties
window, it displays the 3 command objects.

2) I have created a dataset and bound my controls on my form. I am
Click to expand...
able
to
successfully navigate forward and backward through the table on my form.

3) When I update a field and click on the Update command button, nothing
happens because when I close the application and view the database table
directly through Access, the field was never modified. However, if I modify
the field, then navigate 1 before or after the modified record, modify a
field on the new record, click on the submit button, the first record that
was "updated" shows as modified when viewed in Access but the 2nd
Click to expand...
record
is
unchanged.

4) The code for the cmdUpdate button is:
myDataAdapter(dsComputerList1).Update() [VB.NET]

Would appreciate some guidence on this matter.

Sincerely,
Scott D. Barrish
Click to expand...
Click to expand...
Click to expand...
 
William,

Other than binding the text boxes to the appropriate field in the table and calling the Update method, is there other code that needs to be written in order to make the update work?

I have the Microsoft Press book ADO.NET Core Reference, and Chapter 10 p.434 really does not show any code examples what-so-ever on how to accomplish an Update using the DA wizard.

Sincerely,
Scott D. Barrish

----- William Ryan eMVP wrote: -----

If you are getting the big ugly box the assertion is failing, that means
that the value (it was an int variable, I forget the name), has a value of 0
unless it's getting set elsewhere b/c that line of code exists right before
the assertion fails.

That's interesting about the Primary Key...just to be thorough, are any of
your column/field names reserved words like username or password?
Scott D. Barrish said:
William,
1) I do get a big ugly dirty message box when modify one field and click
Click to expand...
on
the Update button
2) When I modify 1 field, navigate to another record, and click on Update
button, I don't get a dialog box. When I inspect the database table, I see
that the record has infact been updated.
3) When it comes to the Access file, the table [Computer List] does have a
Click to expand...
primary key designated: IP_Address. Also, there is an index PrimaryKey_IP.
This index is set to Primary, Unique, and does not accept null. I don't
know why then the DA Wizard is having a difficult time identifying the
unique column to generate the Update and Delete command objects.
Sincerely, Scott D. Barrish

comes to mind is verifying that you have a primary key...that will cause
some problems with the commandbuilder or the DA configuration wizard.
even if the update/delete logic is correct, if you don't have any
Click to expand...
changes
in
your dataset, it won't fire...to do this, add this line of code right before
the update and if you don't get a big ugly assertion box, then you have
changes and the problem is probably with the update/delete statement..
Debug.Assert(myDataSet.HasChanges)
After that, I'd make sure I have file access to write to the db, but if
Click to expand...
you
didn't that wouldn't explain the error in the configuration wizard so it's
doubtful thisis the problem.
There are several issues that I need help on. According to the examples
I've seen in books, MSDN, and news groups, this should be a very simple
exercise. I have found often that simple exercises fail to point out
Click to expand...
a
DUH!
factor which makes me say DOH!
1) When I run the Data Adapter wizard, it creates the INSERT and SELECT
command objects. However, it gives me an error on the generation of the
UPDATE and DELETE command objects. So, I manually entered the necessary
code for the Update Command Object for the Data Adapter. In the properties
window, it displays the 3 command objects.
2) I have created a dataset and bound my controls on my form. I am
Click to expand...
able
to
successfully navigate forward and backward through the table on my form.
3) When I update a field and click on the Update command button, nothing
happens because when I close the application and view the database table
directly through Access, the field was never modified. However, if I modify
the field, then navigate 1 before or after the modified record, modify a
field on the new record, click on the submit button, the first record that
was "updated" shows as modified when viewed in Access but the 2nd
Click to expand...
record
is
unchanged.
4) The code for the cmdUpdate button is:
myDataAdapter(dsComputerList1).Update() [VB.NET]
Would appreciate some guidence on this matter.
Sincerely,
Scott D. Barrish
Click to expand...
Click to expand...
Click to expand...
 
Below is the code for the cmdUpdate button:

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Clic

Dim result As Intege
Dim row As DataRo

row = DsComputerList1.Tables("Computer_List").Rows(0
row(0) = txtUserName.Tex
row(2) = txtCompID.Tex

row.AcceptChanges(
Debug.Assert(DsComputerList1.HasChanges
myDataAdapter.Update(DsComputerList1

End Su

I have setup a breakpoint for this sub. I've noticed that the ItemArray elements do change to what is typed int he textboxes. Also, I noticed that the RowState remains as unchanged. I then get the ugly dialog box with the assertion excepetion error message. I click on ignore. When I click the Update button a second time, the RowState property has changed to Modified. But, I still get an assertion error. Also, the field in the database does not get modified. I am at a loss here and need some guidence

Sincerely
Scott D. Barrish
 
Scott,
The AcceptChanges call on your row marks the row state as unchanged so
there won't be anything seen as changed. Take this out and try it again.
Normally AcceptChanges is only used when you need to set something in a
row and don't want to have that change pushed back to the database.

Ron Allen

Scott D. Barrish said:
Below is the code for the cmdUpdate button:

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As
Click to expand...
System.EventArgs) Handles cmdUpdate.Click
Dim result As Integer
Dim row As DataRow

row = DsComputerList1.Tables("Computer_List").Rows(0)
row(0) = txtUserName.Text
row(2) = txtCompID.Text

row.AcceptChanges()
Debug.Assert(DsComputerList1.HasChanges)
myDataAdapter.Update(DsComputerList1)

End Sub

I have setup a breakpoint for this sub. I've noticed that the ItemArray
Click to expand...
elements do change to what is typed int he textboxes. Also, I noticed that
the RowState remains as unchanged. I then get the ugly dialog box with the
assertion excepetion error message. I click on ignore. When I click the
Update button a second time, the RowState property has changed to Modified.
But, I still get an assertion error. Also, the field in the database does
not get modified. I am at a loss here and need some guidence.
 
Hi Scott,

Just an addition to Ron,

Acceptchanges can you use by instance when you have two datasets, in which
the update from one implicate the update from the other, than you can forget
(cannot even do) the real update and just do acceptchanges.

(Not needed for a remove because that does it automaticly)

Cor
 
Back
Top