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("@EmployeeId", 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
Andrew ParkerIT Field Systems Delivery OfficerAsked:
Who is Participating?
 
ibostCommented:
You're using both:

            objCommand.CommandText = "DELETE FROM EmployeeSkills " & _
            "Where EmployeeSkill.EmployeeId = '" & txtEmployeeId.Text & "' AND  EmployeeSkill.SkillId = '" & txtSkillId.Text & "' "

So if the table name is EmployeeSkills (with an s) then your where clause needs to use it too
EmployeeSkills.EmployeeId (not EmployeeSkill.EmployeeId)... etc etc
0
 
ibostCommented:
Are you getting an error or something?  If so, it would help to know what that error is.

I don't think you need to add the parameters... you're not using them in the sql statement.
0
 
Andrew ParkerIT Field Systems Delivery OfficerAuthor Commented:
The only error its giving is a system error

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

occurs at line objCommand.ExecuteNonQuery()
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cubixSoftwareCommented:
Should DELETE FROM EmployeeSkills   be DELETE FROM EmployeeSkill

0
 
Andrew ParkerIT Field Systems Delivery OfficerAuthor Commented:
No, its employeeSkills
0
 
cubixSoftwareCommented:
... also   are EmployeeID and SkillID character fields as you have them enclosed in quotes
0
 
Andrew ParkerIT Field Systems Delivery OfficerAuthor Commented:
EmployeeID & SkillId are set in 2 textboxes as Numbers
0
 
cubixSoftwareCommented:
If EmployeeID & SkillID are defined on the database table as numeric, you cannot enclose in sigle quotes
0
 
Andrew ParkerIT Field Systems Delivery OfficerAuthor Commented:
Well spotted, like I said getting late here eyes are closing :)  pub soon I think

Thanks for your help that worked a treat :)
0
 
ibostCommented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.