Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Deleting from database

Posted on 2006-03-22
10
Medium Priority
?
205 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:Andrew Parker
  • 4
  • 3
  • 3
10 Comments
 
LVL 10

Expert Comment

by:ibost
ID: 16258951
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
 

Author Comment

by:Andrew Parker
ID: 16258994
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
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16259003
Should DELETE FROM EmployeeSkills   be DELETE FROM EmployeeSkill

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Andrew Parker
ID: 16259017
No, its employeeSkills
0
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16259025
... also   are EmployeeID and SkillID character fields as you have them enclosed in quotes
0
 

Author Comment

by:Andrew Parker
ID: 16259043
EmployeeID & SkillId are set in 2 textboxes as Numbers
0
 
LVL 10

Accepted Solution

by:
ibost earned 800 total points
ID: 16259044
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
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16259068
If EmployeeID & SkillID are defined on the database table as numeric, you cannot enclose in sigle quotes
0
 

Author Comment

by:Andrew Parker
ID: 16259087
Well spotted, like I said getting late here eyes are closing :)  pub soon I think

Thanks for your help that worked a treat :)
0
 
LVL 10

Expert Comment

by:ibost
ID: 16259092
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month20 days, 17 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question