Easy VB.NET Update question

[ourman]

Hi:

I'm a newbie on the .NET technology but have been using the old Visual Basic and ASP for some years now.

I made a VB.NET winform application.
This form have a listbox and a textbox. I created a DataAdapter to fill a DataSet with records from an Access2000 database.

Using the texbox and some buttons (cmdAdd, cmdDelete, cmdSave) I'm able to delete and add records from the listbox and subsequently from the Database.

The problem arise when tryng to update records:
I can see that the DataAdapter is tryng to update the ID field of the Access table and it raise an exception since the ID is an autonumber type... But I can't figured out how to fix this?

Here is the code & the exception being raised:

************************************
Code:
' Getting ID of the selected record to de updated
myDataRowView = Me.lstItems.SelectedItem
lngID = CLng(myDataRowView("ID"))

' Having a Datarow var pointing to the record to be updated
myDataRow = Me.DataSet1.Tables("tblCheckListItems").Rows.Find(lngID)

' Put the DataRow var into edit mode
myDataRow.BeginEdit()
myDataRow("CheckListItem") = Me.txtItem.Text
myDataRow.EndEdit()

' Actually update the record to the database by updating the
' DataAdapter Object
Try
OleDbDataAdapter1.Update(Me.DataSet1, "tblCheckListItems")
Catch x As Exception
' Error during Update, add code to locate error
MsgBox(x.ToString)
End Try

****************************************
Exception:

System.InvalidOperationException ---> System.Data.OleDb.OleDbException: Cannot update 'ID'; field not updateable.
--- End of inner exception stack trace ---
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at Maintenance.frmMain.cmdSave_Click(Object sender, EventArgs e) in E:\My Documents\Visual Studio Projects\Maintenance\Form1.vb:line 295
****************************************

Can you please help me to get this working properly?
Thanks,
Erich[COLOR=blue]

[wolfofthenorth]

I think if you ignore the autonumber field in your code the database will handle it for you.

[ourman]

well, I think I'm ignoring it...

look on the code:

• myDataRow.BeginEdit()
• myDataRow("CheckListItem") = Me.txtItem.Text
• myDataRow.EndEdit()

this part ONLY change the ChekListItem field of the table, not the ID field...

??

[wolfofthenorth]

Did you set the Update command on the dataAdapter object? Make sure it doesn't include the ID field.

[ourman]

YES !!

since the update command was automatically set up by VB, I didn't even nother to look at it before

it was:

Me.OleDbUpdateCommand1.CommandText = "UPDATE tblCheckListItems SET CheckListItem = ?, ID = ? WHERE (ID = ?) AND (CheckListItem = ? OR ? IS NULL AND CheckListItem IS NULL)"

so: it was tryng to update the ID field all the time.

I change this and got a concurrency error that was gone after commenting out another VB auto-generated line.

Thank you VERY much for your help...

[wolfofthenorth]

Glad I could help.