Row Doesn't Get Deleted. What to do ?

[Kannan]

I am having a grid where I delete a row. But when I do the adapter update, changes doesnt take effect.

Here is the code..

'In the form load event 
Const pPROVIDER As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
Dim pDataSource As String = "Data Source=" & Application.StartupPath & "[url="file://Database//Archive.mdb"]\\Database\\Archive.mdb[/url]"
mConn = New OleDb.OleDbConnection(pPROVIDER & pDataSource)
daMain.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM tblArchive ", mConn)
cbMain = New OleDb.OleDbCommandBuilder(daMain)

private sub loadGrid()
Try
dsMain.Clear()
daMain.Fill(dsMain, "tblArchive")
grdDelete.SetDataBinding(dsMain, "tblArchive")
grdDelete.RetrieveStructure()
Catch Eror As Exception
MsgBox(Eror.Message, MsgBoxStyle.Critical, "Error Opening Database")
mConn.Close() 
End Try
end sub

Now I delete a row in the grid and the dataset gets updated. Then I press a button to save changes to database which calls processDelete()

private sub processDelete() 
Try
Dim delCommand As OleDb.OleDbCommandBuilder
delCommand = New OleDb.OleDbCommandBuilder(daMain)
dsMain.AcceptChanges()
daMain.Update(dsMain, "tblArchive")
Catch eror As Exception
MsgBox(eror.Message.ToString)
End Try
end sub

But the database is not updated and the row still remains. Please tell me what I am doing wrong ?

Thanks,
Kannan.

 

[Arg81]

Kannan,

Why are you declaring delCommand in the processDelete sub but then not using it?

Has daMain got a DeleteCommand? I.E. when you set it up did you allow for INSERT, UPDATE and DELETE, or only SELECT?

I wouldn't call the sub from the button click, instead run all of the .update command from the click event;

Private Sub button1_click (blah blah)

Try

daMain.Update(dsMain1)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

That will update the dataset and call the update / delete / insert command to the database.

Hope that helps,
Luke

 

[Kannan]

Try

daMain.Update(dsMain1)

Catch ex As Exception

MessageBox.Show(ex.Message)

EndTry


Now I have delcared the delete command using commabd builder in the form load event.

I tried what you have given and I get exception message "Concurrency Violation: the DeleteCommand affected 0 records."

 

[jmcilhinney]

I don't know if you still are, but don't call AcceptChanges. If you are working with data from a database you will 999,999,999 times out of 1,000,000,000 not have to call AcceptChanges explicitly.

 

[Kannan]

Yes I have removed the AcceptChanges long back still I get I get exception message "Concurrency Violation: the DeleteCommand affected 0 records."

 

[jmcilhinney]

A concurrency violation occurs when the data you are trying to update does not match the data currently in the database. Some possible reasons are that you have messed up the order of your parameters, which cannot be the problem here because you are using a CommandBuilder, or that you have edited a row and then called AcceptChanges, then deleted it and are now trying to call Update, or some other user has changed the data in the database since you retrieved it.