vb.net 如何从VB中永久删除数据库记录

How to permanently delete a record of database from VB

提问by Etto Sama

Im a beginner in VB and Im trying to delete a record from a database but it doesnt let me... they gave me this error message which i do not fully understand what it meant... or is there any other way to delete the record permanently?

我是 VB 的初学者，我试图从数据库中删除一条记录，但它不允许我......他们给了我这个错误消息，我不完全理解它的含义......永久记录？

Column named userID cannot be found. Parameter name: columnName

找不到名为 userID 的列。参数名称：列名

Heres my codes

这是我的代码

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class frmUserManagement
    Dim countID As Integer = 0
    Dim conn As New SqlConnection
    Dim drEmployee As SqlClient.SqlDataReader
    Dim cmdEmployee As New SqlClient.SqlCommand

    Dim sAdapter As New SqlDataAdapter
    Dim sTable As New DataTable

    Private Sub btnAddEmp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddEmp.Click
        Dim tranEmployee As SqlClient.SqlTransaction
        sAdapter = New SqlDataAdapter(cmdEmployee)
        Dim strID As String
        Dim strName As String

        Dim strPosition As String
        Dim strContactNo As String
        Dim strAddress As String
        Dim strDOB As String
        Dim strGender As String
        Dim strSQL As String

        conn.Open()


        strID = lblEmpID.Text
        strName = txtEmpName.Text
        strPosition = cboEmpPosition.Text
        strContactNo = mskEmpDOB.Text
        strDOB = mskEmpDOB.Text
        strAddress = txtEmpAddress.Text
        If radEmpMale.Checked Then
            strGender = "Male"
        Else
            strGender = "Female"
        End If


        strSQL = "INSERT INTO Users(userID,userName,userPosition,userGender,userDOB,userAddress)" & _
            "VALUES(@ID,@NAME,@POSITION,@GENDER,@DOB,@ADDRESS)"

        tranEmployee = conn.BeginTransaction() 

        With cmdEmployee
            .Transaction = tranEmployee
            .CommandText = strSQL
            .Parameters.AddWithValue("@ID", strID)
            .Parameters.AddWithValue("@NAME", strName)
            .Parameters.AddWithValue("@POSITION", strPosition)
            .Parameters.AddWithValue("@GENDER", strGender)
            .Parameters.AddWithValue("@DOB", strDOB)
            .Parameters.AddWithValue("@ADDRESS", strAddress)
            .Connection = conn

        End With

        Try
            cmdEmployee.ExecuteNonQuery()
            tranEmployee.Commit()

        Catch ex As Exception
            tranEmployee.Rollback()
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try

    End Sub

    Private Sub frmUserManagement_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If mintIndex <= 9000 Then
            lblEmpID.Text = "EMP" & (countID + 1).ToString("000#")
        End If

        Try
            With conn
                .ConnectionString = strConnection
                .Open()
            End With

            With cmdEmployee
                .CommandText = "SELECT * FROM Users ORDER BY userID"
                .Connection = conn
            End With
            drEmployee = cmdEmployee.ExecuteReader()

            If drEmployee.HasRows Then
                While drEmployee.Read()

                    DataGridView1.Rows.Add(drEmployee(0), drEmployee(3), drEmployee(1), drEmployee(4), drEmployee(2), drEmployee(5))


                End While
                drEmployee.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
    End Sub

    Private Sub btnDeleteEmp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteEmp.Click
        With cmdEmployee
            .CommandText = "DELETE FROM Users WHERE userID = " & DataGridView1.CurrentRow.Cells(0).Value & ""
            .Connection = conn
            .Parameters.AddWithValue("@ID", 0)
        End With
        Dim rows = DataGridView1.SelectedRows
        For Each row In rows
            cmdEmployee.Parameters("@ID").Value = row.Cells("userID").Value
            cmdEmployee.ExecuteNonQuery()
        Next
           drEmployee = cmdEmployee.ExecuteReader()

    End Sub

    Private Sub btnEditEmp_Click(sender As System.Object, e As System.EventArgs) Handles btnEditEmp.Click
        lblEmpID.Enabled = False
        txtEmpName.Enabled = False
        grpGender.Enabled = False
    End Sub
End Class

回答by prospector

It means you do not have a column named userID in your table, are you sure it's not just ID?

这意味着您的表中没有名为 userID 的列，您确定这不仅仅是 ID 吗？

回答by Alex Filipovici

You should use:

你应该使用：

With cmdEmployee
    .CommandText = "DELETE FROM Users WHERE userID = @ID"
    .Connection = conn
    .Parameters.AddWithValue("@ID", 0)
End With