Andrew Parker
asked on
Deleting from database
Hi all
Getting late in the day, easy question possible, what am I doing wrong?
All I am trying to do is delete a row from Table EmployeeSkills, when SkillId = txtSkillId and employeeId = txtemployeeId.
Help Please : ) Thanks
'code
Private Sub btnDeleteSkill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteSkill.Click
Dim Result As Integer = MsgBox("Confirm Delete of this Skill???", _
MsgBoxStyle.OKCancel, "Deleting Skill")
If Result = MsgBoxResult.OK Then
Dim objCommand As SqlCommand = New SqlCommand()
objCommand.Connection = objConnection
objCommand.CommandText = "DELETE FROM EmployeeSkills " & _
"Where EmployeeSkill.EmployeeId = '" & txtEmployeeId.Text & "' AND EmployeeSkill.SkillId = '" & txtSkillId.Text & "' "
objCommand.Parameters.Add( "@Employee Id", txtEmployeeId.Text)
objCommand.Parameters.Add( "@SkillId" , txtSkillId.Text)
objConnection.Open()
objCommand.ExecuteNonQuery ()
objConnection.Close()
StatusBar1.Text = "Record Deleted"
objConnection.Close()
ListSkillsView()
HideSkillOps()
ElseIf Result = MsgBoxResult.Cancel Then
MsgBox("Please ammend and press Delete when correct, or cancel to end Deleting")
End If
Getting late in the day, easy question possible, what am I doing wrong?
All I am trying to do is delete a row from Table EmployeeSkills, when SkillId = txtSkillId and employeeId = txtemployeeId.
Help Please : ) Thanks
'code
Private Sub btnDeleteSkill_Click(ByVal
Dim Result As Integer = MsgBox("Confirm Delete of this Skill???", _
MsgBoxStyle.OKCancel, "Deleting Skill")
If Result = MsgBoxResult.OK Then
Dim objCommand As SqlCommand = New SqlCommand()
objCommand.Connection = objConnection
objCommand.CommandText = "DELETE FROM EmployeeSkills " & _
"Where EmployeeSkill.EmployeeId = '" & txtEmployeeId.Text & "' AND EmployeeSkill.SkillId = '" & txtSkillId.Text & "' "
objCommand.Parameters.Add(
objCommand.Parameters.Add(
objConnection.Open()
objCommand.ExecuteNonQuery
objConnection.Close()
StatusBar1.Text = "Record Deleted"
objConnection.Close()
ListSkillsView()
HideSkillOps()
ElseIf Result = MsgBoxResult.Cancel Then
MsgBox("Please ammend and press Delete when correct, or cancel to end Deleting")
End If
ASKER
The only error its giving is a system error
An unhandled exception of type 'System.Data.SqlClient.Sql Exception' occurred in system.data.dll
Additional information: System error.
occurs at line objCommand.ExecuteNonQuery ()
An unhandled exception of type 'System.Data.SqlClient.Sql
Additional information: System error.
occurs at line objCommand.ExecuteNonQuery
Should DELETE FROM EmployeeSkills be DELETE FROM EmployeeSkill
ASKER
No, its employeeSkills
... also are EmployeeID and SkillID character fields as you have them enclosed in quotes
ASKER
EmployeeID & SkillId are set in 2 textboxes as Numbers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If EmployeeID & SkillID are defined on the database table as numeric, you cannot enclose in sigle quotes
ASKER
Well spotted, like I said getting late here eyes are closing :) pub soon I think
Thanks for your help that worked a treat :)
Thanks for your help that worked a treat :)
and as cubix is implying:
if EmployeeID and SkillID are defined as INT datatypes in the db, you need to pass INTs in the update statement. I believe in VB.net you'd use CInt to cast them to integers.
"Where EmployeeSkills.EmployeeId = " & Cint(txtEmployeeId.Text) & " AND EmployeeSkills.SkillId = " & CInt(txtSkillId.Text)
if EmployeeID and SkillID are defined as INT datatypes in the db, you need to pass INTs in the update statement. I believe in VB.net you'd use CInt to cast them to integers.
"Where EmployeeSkills.EmployeeId = " & Cint(txtEmployeeId.Text) & " AND EmployeeSkills.SkillId = " & CInt(txtSkillId.Text)
I don't think you need to add the parameters... you're not using them in the sql statement.