Deleting rows from Access database

[ses41]

I am having trouble deleting rows from an Access database.

 

Given that I know my datarowstate is unchanged, I have the following code.

 

myDS.myTable.Rows(me.bindingcontext(myDS, "myTable")_

.position).Delete()

 

adapter.update(myDS)

 

This works and deletes the row form the database

 

But when I use the following:

 

myDS.myTable.Rows(me.bindingcontext(myDS, "myTable")_

.position).Delete()

 

adapter.update(myDS.getchanges(datarowstate.deleted))

 

I throw an exception because there are no rows found with a datarowstate set to deleted

 

Does anybody have a clue to the reason.

 

I would really like to only update the deleted rows instead of all rows.

 

Thanks in advance.

[Nerseus]

It throws an exception because there are no rows? Can you paste the error message for us - I've never seen that one. I've gotten errors when trying what you try if the DataSet has a relation and you're deleting the child row, for instance. It's because GetChanges returns a new DataSet with only the rows you specify but that may break the relationship.

 

-ner

[ses41]

Nerseus

 

Thanks for your response. Here is the error message I get with the "getchanges(datarowstate.deleted)" added to the line.

 

An unhandled exception of type 'System.ArgumentNullException' occurred in system.data.dll

 

Additional information: Value cannot be null.

 

Another problem has surfaced, that might give you a clue to what is happening. I have been using the Update command without the "getchanges(datarowstate.deleted)" added to the line.

 

I was testing the operation of the program and everything looked good, until I deleted the last record. I then created a new record and accepted changes. Then I decided to try and delete the last record again and got the following message.

 

An unhandled exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll

 

Additional information: Concurrency violation: the DeleteCommand affected 0 records.

 

I am using auto "ID" and it appears that both records "had" the same ID number.

 

I am using .NET 2002 and have ordered the 2003 version. I was hoping that some of the changes would fix the problem. Also you need to know that the database being connected to was created in Access 97.

 

Thanks

[ses41]

Nerseus,

 

I made the same thing happen again, but this time I put some feedback into the program. The first time through the program gave me the current("ID") as 106 and the database showed it as ID=106. Then I deleted it. Accepted changes. I looked at the database and the last ID was 105.

 

I then added a new record, accepted changes and the program again gave me a current "ID" as 106. Then I tried to delete it and failed. When I look at the database it shows up as ID=107, with ID=105 but no ID=106.

 

It appears that maybe VB.NET is using the ID=105, adding 1 to get 106, and using that. But Access has already used 106 and increements to 107, thus setting up the conflict.

 

I tried it again, and after adding the second record, I made some changes, and I tried to save it with "getChanges(datarowstate.modified) and it threw a concurrency exception.

 

I tried it again, but this time I exited out of the program without doing anything to second record after the initial save. The database again showed an ID incremented one more than current("ID"). What is also wierd is that all the values I assigned to the record were done with current("field") while the current("ID") was one less than where it showed up in the database.

 

With all this said and done, I guess I can always check to see if deleted is Null and if so then add one and try again.

[ses41]

Nerseus,

 

Sorry, for the number of post in a row, but I keep on finding out more information. I was getting in and out of my program with each add, which means i was going through a new dataset fill each time. This last time I stayed in the program. I started at current"ID"=112 and database ID=112. As long as I stayed in the program, both current("ID") and database ID incremented. I went up to 115, deleting each time, and accepting changes. I then got out of the program and back in. Current("ID") was 112 and database ID was 116.

 

Thanks