如何使用SQL在VB.NET中创建删除功能 [英] How do I make a delete function in VB.NET using SQL

问题描述

长话短说，我想通过序号位置或gridview.selectedrow创建一个SQL删除函数。我已经尝试过几次失败了。我正在帮助解决这个问题。

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

我赞成帮助。



我尝试过：



我试图在使用ID列作为文本源的序数之间切换，我也试图做一个选定的gridrow删除。当我选择了gridrow删除时，消息出现并表示记录被删除，但没有记录从数据库中删除。

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.

推荐答案

你的一个主要问题代码是您没有使用参数。通过使用它们，可以简化很多并保证您免受SQL注入，转换问题等的影响。



另一件事是缺少使用块应该用于正确处理对象。



关于这些，请看一下正确执行数据库操作 [ ^ ]但是请记住，你不能使用参数表名，只能使用行值。



什么是删除本身，如前所述，您始终显示消息框。但另一个问题是，您的标准中是否正确使用了ID？目前你似乎使用列的序数：

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:

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

这总是给出了列的位置，而不是数据的价值。因此，请尝试删除序数并使用实际值。您只需指向您想要该值的行。请参阅 DataTable.Rows属性（System.Data） [ ^ ]

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)[^]

这篇关于如何使用SQL在VB.NET中创建删除功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！