时间戳

首页 > 解决方案 > 出现错误 System.Data.SqlClient.SqlException：'必须声明标量变量“@DoctorID”。'

vb.net - 出现错误 System.Data.SqlClient.SqlException：'必须声明标量变量“@DoctorID”。'

问题描述

我一直在寻找解决方案，但我找不到任何解决方案。

而且我还参考了这里获取错误 System.Data.SqlClient.SqlException：'必须声明标量变量“@EmployeeId”。'

但它似乎也没有解决方案。

我一直有 System.Data.SqlClient.SqlException 的错误：'必须声明标量变量“@DoctorID”。'

我的 SQL 表

    [DoctorID] NVARCHAR (5)  NOT NULL,
    [Name]     NVARCHAR (50) NULL,
    [Gender]   NVARCHAR (10) NULL,
    [Email]    NVARCHAR (30) NULL,
    [Username] NVARCHAR (10) NULL,
    [Password] NVARCHAR (10) NULL,
    PRIMARY KEY CLUSTERED ([DoctorID] ASC)
);

插入数据

    Public Function insertdata(ByVal qr As String) As Integer

        cmd = New SqlCommand(qr, con)
        con.Open()
        i = cmd.ExecuteNonQuery()
        con.Close()
        Return i

    End Function

我的代码

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click

        Dim qr As String

        cmd.Parameters.AddWithValue("@DoctorID", txtDocID.Text)
        cmd.Parameters.AddWithValue("@Name", txtName.Text)
        cmd.Parameters.AddWithValue("@Gender", cboGender.Text)
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
        cmd.Parameters.AddWithValue("@Username", txtUsername.Text)
        cmd.Parameters.AddWithValue("@Password", txtPassword.Text)

        qr = "INSERT INTO Doctor (DoctorID, Name, Gender, Email, Username, Password) Values (@DoctorID, @Name, @Gender, @Email, @Username, @Password)"

        Dim logincorrect As Boolean = Convert.ToBoolean(insertdata(qr))
        If (logincorrect) Then
            MsgBox("Record Inserted Successfully", MsgBoxStyle.Information)
        Else
            MsgBox("Something Wrong, Record Not Saved.", MsgBoxStyle.Critical)
        End If

        disp_data()

    End Sub

如何解决这个问题？

标签： vb.net

解决方案

如果您将数据库对象保留在使用它们的方法的本地，您将为自己省去很多麻烦。

您将查询字符串传递给您的insertdata函数，然后创建一个New命令。这个新命令对您在添加按钮中添加参数的命令一无所知。

参数的.Add方法优于.AddWithValue. 请参阅http://www.dbdelta.com/addwithvalue-is-evil/ 和 https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ 还有一个： https ://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications 这是另一个 https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-使用.html

公开方法的数据库对象.Dispose不仅需要关闭，而且需要处置，因为它们可能包含非托管代码。Using...End Using即使出现错误，blocks 也会为您解决这个问题。

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
    Dim rowsInserted As Integer
    Using cn As New SqlConnection("Your connection string."),
            cmd As New SqlCommand("INSERT INTO Doctor (DoctorID, Name, Gender, Email, Username, Password) Values (@DoctorID, @Name, @Gender, @Email, @Username, @Password);", cn)
        With cmd.Parameters
            .Add("@DoctorID", SqlDbType.NVarChar, 5).Value = txtDocID.Text
            .Add("@Name", SqlDbType.NVarChar, 50).Value = txtName.Text
            .Add("@Gender", SqlDbType.NVarChar, 10).Value = cboGender.Text
            .Add("@Email", SqlDbType.NVarChar, 30).Value = txtEmail.Text
            .Add("@Username", SqlDbType.NVarChar, 10).Value = txtUsername.Text
            .Add("@Password", SqlDbType.NVarChar, 10).Value = txtPassword.Text
        End With
        cn.Open()
        rowsInserted = cmd.ExecuteNonQuery
    End Using
    If rowsInserted = 1 Then
        MsgBox("Record Inserted Successfully", MsgBoxStyle.Information)
    Else
        MsgBox("Something Wrong, Record Not Saved.", MsgBoxStyle.Critical)
    End If
    disp_data()
End Sub

推荐阅读