Why Can't I Delete a record this way?

[Stanav]

Hello all,

When a user click on the "Update" button on my program, I want to update the
database using the sub below. I was able to insert and modify records, but
can't delete records. The error reads: "Deleted row information cannot be
accessed through the row". I understand that when I refer to dr.Item(0) but
that datarow had been deleted thus I cannot get the data.... Is there any
workaround to this?
Thanks for reading.
VhD50

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click

Dim dsChanged As New Data.DataSet

dsChanged.Clear()

dsChanged = ds.GetChanges

Dim dr As Data.DataRow

Dim cmdStr As String

Dim Cmd As New OleDb.OleDbCommand

conn.Open()

Try

For Each dr In dsChanged.Tables(0).Rows

Select Case dr.RowState.ToString

Case "Modified"

cmdStr = "UPDATE Users SET " & _

"[Password] = '" & dr.Item(1) & "', " & _

"[Domain] = '" & dr.Item(2) & "' " & _

"WHERE [User] = '" & dr.Item(0) & "';"

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.UpdateCommand = Cmd

da.UpdateCommand.Connection = conn

da.UpdateCommand.ExecuteNonQuery()

MsgBox("Modified OK")

Case "Added"

cmdStr = "INSERT INTO Users VALUES ( " & _

"'" & dr.Item(0) & "', " & _

"'" & dr.Item(1) & "', " & _

"'" & dr.Item(2) & "')"

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.InsertCommand = Cmd

da.InsertCommand.Connection = conn

da.InsertCommand.ExecuteNonQuery()

Case "Deleted"

cmdStr = "DELETE FROM Users WHERE User = '" & dr.Item(0) & "'" <<<<== I
gets the error here at this line of code

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.DeleteCommand = Cmd

da.DeleteCommand.Connection = conn

da.DeleteCommand.ExecuteNonQuery()

End Select

Next

MsgBox("Update OK")

ds.Reset()

Button2.PerformClick()

DataGrid1.Refresh()

Catch ex As Exception

MsgBox(ex.Message)

Finally

conn.Close()

End Try

End Sub

 

[dfoster]

Hello all,

When a user click on the "Update" button on my program, I want to update the
database using the sub below. I was able to insert and modify records, but
can't delete records. The error reads: "Deleted row information cannot be
accessed through the row". I understand that when I refer to dr.Item(0) but
that datarow had been deleted thus I cannot get the data.... Is there any
workaround to this?

Case "Deleted"

cmdStr = "DELETE FROM Users WHERE User = '" & dr.Item(0) & "'" <<<<== I
gets the error here at this line of code

dr.Item(0,DataRowVersion.Original)

 

[Scott M.]

Why would you want to is my question? You are manually creating the same
functionality that is already available via a DataAdapter. Just create a
DataAdapter and configure the CommandText of the various sub command objects
to your liking and your done.

 

[Stanav]

Thanks a lot, dfoster, for the tip and for the lightning fast response.
It works now.
VhD50.