Link to home
Start Free TrialLog in
Avatar of Andrew Parker
Andrew ParkerFlag for Australia

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("@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
Avatar of ibost
ibost

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.
Avatar of Andrew Parker

ASKER

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()
Should DELETE FROM EmployeeSkills   be DELETE FROM EmployeeSkill

No, its employeeSkills
... also   are EmployeeID and SkillID character fields as you have them enclosed in quotes
EmployeeID & SkillId are set in 2 textboxes as Numbers
ASKER CERTIFIED SOLUTION
Avatar of ibost
ibost

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If EmployeeID & SkillID are defined on the database table as numeric, you cannot enclose in sigle quotes
Well spotted, like I said getting late here eyes are closing :)  pub soon I think

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)