Delete records from SQL table in VB.Net

Question

0.00/5 (No votes)

See more:

, +

Hello Friends,

I am trying to delete records from my table but unable to do so. I have window form with Insert, Update, Delete and Save button. I can insert data but I can't delete specific or any records from that table. Below is my code.

Whenever I click 'delete' button I get ("Error while deleting record on table..." & ex.Message, "Delete Records") msgbox only.

Am I missing something? Please advise. Any help would be great. Thank you!

Private Sub Deletebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Deletebtn.Click

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Try
            con.ConnectionString = "Server=USRAG-L-0067215\SQLEXPRESS;Database=Alamo Products_Design Data;Trusted_Connection=True"
            con.Open()
            cmd.Connection = con
           cmd.CommandText = "Delete From Design_ParameterswhereDesignID=@DesignID "
            cmd.Parameters.Add(New SqlParameter("@DesignID", " Am I missing any value here?"))
            cmd.ExecuteNonQuery()
            If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then

                MsgBox("Operation Cancelled")

                Exit Sub

            End If
        Catch ex As Exception
            MessageBox.Show("Error while deleting record on table..." &amp; ex.Message, "Delete Records")

        Finally

            con.Close()
        End Try
    End Sub
End Class

Posted 10-Dec-12 10:19am

Joy1979

Updated 10-Dec-12 10:58am

v8

Add a Solution

Comments

Richard C Bishop 10-Dec-12 16:26pm

I would suggest putting ' ' around your question mark.

"Delete From Design_Parameters Where DesignID = '?' "

Joy1979 10-Dec-12 16:29pm

Yes, I agree but it will only return value with '? ' correct? where I need to find records by 'ID'. Thanks

Richard C Bishop 10-Dec-12 16:30pm

So is the "?" not the value you are looking for to delete?

Joy1979 10-Dec-12 16:51pm

No, I need to delete records by 'DesignID'. user put their order number to form and that order number inserted to 'DesignID' column. If I pick any designID number then I should be able to delete it. Thanks

Richard C Bishop 10-Dec-12 16:59pm

Ok, so just use a variable with the value of the DesignID instead of the "?". Is your problem that you don't know how to get the ID you want as the variable?

Joy1979 10-Dec-12 17:05pm

Yes Sir, Correct. Thanks!

Richard C Bishop 10-Dec-12 17:15pm

Ok, looks like DinoRondelly summed it up good with the post below. The "@DesignID" is what you are looking for.

Joy1979 11-Dec-12 9:53am

Thx for your help.

cmd.CommandText = "Delete From Design_Parameters where DesignID = @DesignID "
cmd.Parameters.Add(New SqlParameter("@DesignID", TextBox4.Text))

...I can delete records now. :)

Richard C Bishop 11-Dec-12 10:24am

Good, glad I could help.

DinoRondelly 10-Dec-12 16:48pm

When you run
cmd.ExecuteNonQuery()

you are executing the query so to have you message box come up after this makes no sense because you are asking them if they are sure they want to delete.

What is ? supposed to be? I would imagine you want to have you id value there.

Joy1979 10-Dec-12 17:04pm

Hi, Thanks for your reply. Apologies If my question is not cleared. there is not value in table associated with "?". I have updated my question.

DinoRondelly 10-Dec-12 17:07pm

read the link below
http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

Also
cmd.Parameters.Add(New SqlParameter("@DesignID", " Am I missing any value here?"))

Datatype????

Also Spacing
"Delete From Design_Parameters where DesignID = @DesignID "

DinoRondelly 10-Dec-12 16:51pm

Also your quotes are messed up in your statement which is another reason this would fail

Joy1979 11-Dec-12 9:53am

Thx for your help.

cmd.CommandText = "Delete From Design_Parameters where DesignID = @DesignID "
cmd.Parameters.Add(New SqlParameter("@DesignID", TextBox4.Text))

...I can delete records now. :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Answer 1 · 2012-12-10T10:32:00

Solution 1

What the '?' stands for in your CommandText. If it is an unnamed parameter, please see "How to set unnamed SQL parameters in SqlCommand"[^]. Moreover where such parameter is set in your code?

Posted 10-Dec-12 10:32am

CPallini