How do I make a delete function in VB.NET using SQL

Question

0.00/5 (No votes)

See more:

Long story short, I am wanting to make an SQL delete function by either ordinal location or by gridview.selectedrow. I have tried and failed quite a few times. I am asking help in resolving this issue.

Private Sub Single_delete()
        Try

            Dim sqlcon As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jj\documents\visual studio 2015\Projects\WindowsApplication3\WindowsApplication3\Database1.mdf;Integrated Security=True")
            Dim sqladapt = New SqlDataAdapter("Select * from " + "[" + TreeView1.SelectedNode.Text.ToString, sqlcon)

            sqlcon.Open()
            Dim cmd As SqlClient.SqlCommand
            Dim sql As String = "Delete from " + "[" + TreeView1.SelectedNode.Text.ToString + "]" + " where Id= " + Database1DataSet.Tables(TreeView1.SelectedNode.ToString).Columns("Id").Ordinal.ToString
            cmd = New SqlClient.SqlCommand(sql, sqlcon)

            cmd.Parameters.AddWithValue("@id", Database1DataSet.Tables(TreeView1.SelectedNode.ToString).Columns("Id").Ordinal.ToString)

            cmd.ExecuteNonQuery()
            sqlcon.Close()

            MessageBox.Show("Record deleted")
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub

I apreciate the help.

What I have tried:

I have tried to switch between ordinal using the "ID" column as the source for the text, I have also tried to do a selected gridrow delete. When I did the selected gridrow delete, the message came up and said the record was deleted, but no record was removed from the database.

Posted 3-Jan-17 6:55am

Member 11856456

Updated 3-Jan-17 7:44am

Add a Solution

Comments

[no name] 3-Jan-17 13:07pm

You are getting the "Record deleted" because you are not checking if the record was actually deleted or not so it will display every time.
Why would you convert an object that is already a string to a string? What purpose does that serve?
Why are you creating a parameter and then not using it in your query?
Why only create one parameter and not 2 as you should be?

Member 11856456 3-Jan-17 13:14pm

If I do not change it into a string I get an error saying "converting from string "delete from [table2] where Id = " to type 'Double' is not valid"

my parameter is supposed to be equal to the number in the ID column.

not sure what you mean by create 2 parameters?

[no name] 3-Jan-17 19:41pm

Who said anything about the ID? Text is a string already so why are you calling ToString on an object that is already a string?
"my parameter is supposed", okay so? Why didn't you answer the question?
Why are you bothering to create a parameter and not even use it?
"what you mean by create 2 parameters", how can you not know? You created a parameter for your query instead if 2, one for the ID why didn't you create a parameter for the SelectedNode? It's a simple question. Not that you would use the parameter if you created one as you aren't using the parameter that you created already.

Maciej Los 3-Jan-17 15:07pm

Your code is SQL Injection vulnerable!

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)

Wendelius · Accepted Answer · 2017-01-03T07:44:00

One major problem in your code is that you're not using parameters. By using them, the could would simplify a lot and keep you safe from SQL injections, conversion problems and so on.

Another thing is the lack of using blocks which should be used in order to dispose objects properly.

Concerning those, have a look at Properly executing database operations[^], however keep in mind that you cannot use parameters for table names, only row values.

What comes to the deletion itself, as already pointed out, you always show the message box. But another question is if the Id is used correctly in your criteria? Currently you seem to use the ordinal of a column:

VB

Database1DataSet.Tables(TreeView1.SelectedNode.ToString).Columns("Id").Ordinal.ToString

That always gives the position of the column, not the value of the data. So try removing the ordinal and using the actual value instead. You just have to point the row you want the value from. See DataTable.Rows Property (System.Data)[^]