VB.NET Delete in Sorted DataGrid

[toekneel]

I'm working in VB.NET 2003. I'm working with datagrids, and have been able to successfully add, edit, cancel, and sort records. But the delete command is somehow deleting the wrong row. I'm thinking it might have something to do with the table being sorted and the item being deleted is equal to the item number in the unsorted table, but not the sorted data table. I could easily be wrong there. Can anyone point me to some code that will properly delete? I've searched all kinds of websites for code snippets to make this work but have failed on every attempt.

Thanks!

 

[toekneel]

Hard to believe that I haven't seen any posts on this one. I figured this out on my own, and I'll post my code here in case others are wrestling with this. This code looks for values in the datagrid columns that match the primary key for records in the table. If your key is a single field, you would only need 1 "item01"; if you have multiple fields comprising the key, you would need to capture each of those fields. The item.cells(x) is looking at specific columns in the datagrid. I am working with a Oracle datasource; if your source is different you'll need to adjust those points in this code.

Public Sub Data_DeleteCommand(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
'delete the row item

' e.Item is the table row where the command is raised. For bound
' columns, the value is stored in the Text property of a TableCell.
Dim itemCell01 As TableCell = e.Item.Cells(2)
Dim item01 As String = itemCell01.Text
Dim itemCell02 As TableCell = e.Item.Cells(3)
Dim item02 As String = itemCell02.Text
Dim strSQL As String = "SELECT * FROM TableName"
Dim strSQL2 As String
Dim sConn As String
sConn = ("user id = " + Session("UserName") + ";password = " + Session("Password") + ";data source = " & strDataSource)
Dim objConn As New System.Data.OracleClient.OracleConnection(sConn)
strSQL2 = "DELETE FROM TableName WHERE Field01 = '" & item01 & "'" & " and Field02= '" & item02 & "'"
Dim objCmd As New System.Data.OracleClient.OracleCommand(strSQL2, objConn)

Try
objConn.Open()
Catch myException As System.Exception
ErrorMessage.Text = myException.ToString
End Try

If objConn.State = ConnectionState.Open Then
Try
Dim objDeleteCommand As New OracleClient.OracleCommand
Dim objAdapter As New OracleClient.OracleDataAdapter(strSQL, objConn)
Dim objParam As New OracleClient.OracleParameter

With objDeleteCommand
.Connection = objConn
.CommandText = strSQL2
End With

objAdapter.DeleteCommand = objDeleteCommand
objAdapter.DeleteCommand.ExecuteNonQuery()

objAdapter = Nothing
Catch myException As System.Exception
ErrorMessage.Text = MyException.ToString
Finally
'Garbage collection
objConn.Close()
strSQL = Nothing
sConn = Nothing
objConn = Nothing
objCmd = Nothing
End Try
End If

End Sub

 

[ca8msm]

I should probably point out that you may have received better feedback if this question had been posted in the ASP.NET forum (forum855).

Thanks for posting your solution though.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.