I recently develop a tool to add, edit, delete, filter, search, locate record on a Database Table. Also includes Relations, my tool uses sqldataadapter, dataset.etc. < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
My problem is when I try to perform a Delete of a record look my code :
Private Sub UpdateData()
Dim p As ParameterControlUpdate
Dim TableToUpdate As String = m_grdTableStyle.MappingName
Dim Cmd As SqlCommand
Try
Cmd = New SqlCommand
Cmd.CommandText = "UPDATE " & TableToUpdate & " SET "
For Each p In m_UpdateValues
If Not p.Key Then
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & " = " & p.Name & ", "
End If
Next
Cmd.CommandText = Cmd.CommandText.Remove(Cmd.CommandText.Length - 2, 2)
Cmd.CommandText &= " WHERE "
For Each p In m_UpdateValues
If p.Key Then
Select Case p.DataType
Case Is = enmDataType.typText, enmDataType.typDate
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & "='" & CType(p.GetValue, String) & "' And "
Case Is = enmDataType.typNumeric, enmDataType.typBoolean
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & "=" & CType(p.GetValue, String) & " And "
End Select
End If
Next
Cmd.CommandText = Cmd.CommandText.Remove(Cmd.CommandText.Length - 5, 5)
For Each p In m_UpdateValues
Cmd.Parameters.Add(p.Name, p.GetValue)
Next
Cmd.Connection = m_DBCnn
daData.UpdateCommand = Cmd
daData.Update(dsData.Tables(TableToUpdate))
Cmd.Dispose()
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Sub
'This sub don't work as I need an don't raise an error.
Private Sub DeleteData()
'This method can't work correctly, it don't throw any error and can't
'delete the record "I Need help with it" if some one can help me I will appreciate it a lot.
Dim p As ParameterControlUpdate
Dim TableForDelete As String = m_grdTableStyle.MappingName
Dim Cmd As SqlCommand
Try
Cmd = New SqlCommand
Cmd.CommandText = "Delete " & TableForDelete & " "
Cmd.CommandText &= "WHERE "
For Each p In m_UpdateValues
If p.Key Then
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & " = " & p.Name & " And "
End If
Next
Cmd.CommandText = Cmd.CommandText.Remove(Cmd.CommandText.Length - 5, 5)
For Each p In m_UpdateValues
If p.Key Then
Cmd.Parameters.Add(p.Name, p.GetValue)
End If
Next
Cmd.Connection = m_DBCnn
daData.DeleteCommand = Cmd
daData.Update(dsData.Tables(TableForDelete))
Cmd.Dispose()
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Sub
The DeleteData Sub don't raise any error, the commandtext used is correct, I tested directly in SQL Query Analizer, the Parameters of SqlCommand object are ok.
If you can help me, I can send you my entire project if you need it.
(I have a sample project which uses Northwind database)
Regards
Carlos Vara
ADO.Net-DataAdapter.DeleteCommand Can't Delete Row
techiedella
Please check if you call AcceptChanges() method in your datatable. Coz if you call the method before the update process occurs, then no changes would be made in your database...
cheers,
Paul June A. Domag
lucius153
Since you said that you ran the Profiler and saw no delete commands being passed to SQL Server, then it's not anything in the SQL Server database itself that could be causing the problems.
Bob Baird
One thing to try is to take a look at SQL Profiler and see if your Delete command is even being sent to the database.
Shilnet
How are you deleting the rows Are you calling AcceptChanges prior to calling Update
David Sceppa
ADO.NET Program Manager
Microsoft
camilletrapp
Well said, Connie.
Carlos, if you're not seeing the RowUpdating event fire for the pending deletions and/or you're not seeing the DELETE queries in the SQL Profiler trace, this might be an issue with how you're deleting the rows.
How are you deleting the rows Make sure you're not calling Remove which physically removes the DataRow from the Rows collection. You should be calling Delete so the row is marked as Deleted but is still available in the Rows collection so the DataAdapter can submit the pending change to the database.
David Sceppa
ADO.NET Program Manager
Microsoft
JeffWard
DataSet.Tables[0].Rows[Counter].Delete();
Here is how I am handeling the update. I am passing a dataset to a function, then updating. I do not call AcceptChanges, but I also do not see it as an option through intelesense.
SqlConnection SqlConn = new SqlConnection(connString);
SqlConn.Open();
SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT * FROM " + updatedTable, SqlConn);
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myDataAdapter);
myDataAdapter.Update(SavedDataSet, updatedTable);
SqlConn.Close();
** Edit **
I was looking for AcceptChanges off the DataAdapter and not the DataSet, so I added that to my code right before I call the function that does the db connection and update. However, it still does not delete rows.
hazz
(Thanks for clarifying my mention of AcceptChanges, Bonnie.)
You're deleting the rows correctly by calling Delete and your overall approach appears sound. Try following your current approach in a new application using basic sample tables (like Northwind.Customers or pubs.authors).
I'm assuming that will work. Let us know either way. If it doesn't work, post enough code for someone to reproduce the behavior. That will make it easier for someone to help provide a solution.
David Sceppa
ADO.NET Program Manager
Microsoft
Etienne Team System MVP
When David was asking about the AcceptChanges() it's because he wanted to make sure that you were NOT using it (then it wouldn't work at all!!)
Does your code work for Updates or Inserts ok Is it only the Deletes that aren't working
dba123
I don't see any errors in your logic. You could try adding code to handle the DataAdapter's RowUpdating and RowUpdated events to see if the DataAdapter attempts to submit the expected changes. Also, using a simplified query (one key column, one non-key column) may also help.
I hope this information proves helpful.
David Sceppa
ADO.NET Program Manager
Microsoft
fr0zed
Ville Mattila
I use Add New, Edit And Save, Delete; but "Delete" don't phisically Deletes the row on DataBase. I'm really tired of try one thing and another without success.
Can I send you a little sample project to you may be you can figure out this behavior I hope this will not be a Microsoft's Error.
Justin-Josef Angel
ondrejsv
Tera_Dragon