sql - VB.net SQL 错误必须声明标量变量

Question

嗨，我在这个简单但迟钝的消息“必须声明标量变量”上被困了很长时间

我有一个将数据插入到一个简单表中的 vb.net 和 SQL 代码。它适用于示例数据，但是当我尝试使用参数( insertcommand.Parameters.Add("@ID", SqlDbType.Int, 3).Value = 50)时，它给了我错误。如果我只用一个数字替换@ID，它就可以工作。

最终 .Value = txtid.text 如果我能让参数起作用。

谢谢你的帮助。

        Dim connection As New SqlConnection
    Dim connectionString As String =
            "Data Source=LAPTOP-PC\SQLEXPRESS2008;Initial Catalog=golf;" &
             "Integrated Security=True"
    connection.ConnectionString = connectionString
    Dim insertcommand As New SqlCommand
    insertcommand.Connection = connection

    insertcommand.Parameters.Add("@ID", SqlDbType.Int, 3).Value = 50
    'Must declare the scalar variable'

    Dim insertStatement As String =
     "INSERT INTO Golf (ID, Title, Firstname, Surname, Gender, DOB, Street, Suburb, City, [Available week days], Handicap)" &
     "Values( @ID, 'Mr', 'Howard', 'The Duck', 'm', '12/12/23', 'asd', 'sdf', 'City', '0', '56') "

    Using insertconnection As New SqlConnection(connectionString)
        Dim adapter As New SqlDataAdapter()
        Try
            insertconnection.Open()
            adapter.InsertCommand = insertconnection.CreateCommand
            adapter.InsertCommand.CommandText = insertStatement
            adapter.InsertCommand.ExecuteNonQuery()

            MsgBox("Data Inserted  !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Using

Answer 1

代码有点困惑 - 您正在设置一个 SqlCommand 对象，然后不使用它。尝试这个:

Using connection As New SqlConnection("Data Source=LAPTOP-PC\SQLEXPRESS2008;Initial Catalog=golf;Integrated Security=True")
    Dim insertStatement As String = _
     "INSERT INTO Golf (ID, Title, Firstname, Surname, Gender, DOB, Street, Suburb, City, [Available week days], Handicap)" _
     & "Values( @ID, 'Mr', 'Howard', 'The Duck', 'm', '12/12/23', 'asd', 'sdf', 'City', '0', '56') "
    Using insertcommand As New SqlCommand(insertStatement, connection)
        connection.Open()
        insertcommand.Parameters.AddWithValue("@ID", txtid.Text)
        insertcommand.ExecuteNonQuery()
    End Using
End Using