ADO.Net-DataAdapter.DeleteCommand Can't Delete Row
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.
My problem is when I try to perform a Delete of a record look my code :
PrivateSub UpdateData()
Dim pAs ParameterControlUpdate
Dim TableToUpdateAsString = m_grdTableStyle.MappingName
Dim CmdAs SqlCommand
Try
Cmd =New SqlCommand
Cmd.CommandText = "UPDATE " & TableToUpdate & " SET "
ForEach pIn m_UpdateValues
IfNot p.KeyThen
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & " = " & p.Name & ", "
EndIf
Next
Cmd.CommandText = Cmd.CommandText.Remove(Cmd.CommandText.Length - 2, 2)
Cmd.CommandText &= " WHERE "
ForEach pIn m_UpdateValues
If p.KeyThen
SelectCase p.DataType
CaseIs = enmDataType.typText, enmDataType.typDate
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & "='" &CType(p.GetValue,String) & "' And "
CaseIs = enmDataType.typNumeric, enmDataType.typBoolean
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & "=" &CType(p.GetValue,String) & " And "
EndSelect
EndIf
Next
Cmd.CommandText = Cmd.CommandText.Remove(Cmd.CommandText.Length - 5, 5)
ForEach pIn 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 exAs Exception
ThrowNew Exception(ex.Message)
EndTry
EndSub
'This sub don't work as I need an don't raise an error.
PrivateSub 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 pAs ParameterControlUpdate
Dim TableForDeleteAsString = m_grdTableStyle.MappingName
Dim CmdAs SqlCommand
Try
Cmd =New SqlCommand
Cmd.CommandText = "Delete " & TableForDelete & " "
Cmd.CommandText &= "WHERE "
ForEach pIn m_UpdateValues
If p.KeyThen
Cmd.CommandText &= p.Name.Trim(Char.Parse("@")) & " = " & p.Name & " And "
EndIf
Next
Cmd.CommandText = Cmd.CommandText.Remove(Cmd.CommandText.Length - 5, 5)
ForEach pIn m_UpdateValues
If p.KeyThen
Cmd.Parameters.Add(p.Name, p.GetValue)
EndIf
Next
Cmd.Connection = m_DBCnn
daData.DeleteCommand = Cmd
daData.Update(dsData.Tables(TableForDelete))
Cmd.Dispose()
Catch exAs Exception
ThrowNew Exception(ex.Message)
EndTry
EndSub
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
