VB.net SQL error Must Declare the Scalar Variable

Question

Hi I have been stuck for a long time on this simple but obtuse message "Must Declare the Scalar Variable"

I have a vb.net and SQL code that inserts data into a simple table. It works OK with sample data, but when I try to use a parameter ( insertcommand.Parameters.Add("@ID", SqlDbType.Int, 3).Value = 50) it gives me the error. If I replace the @ID with just a number it works.

Eventually .Value = txtid.text if I can get the parameter to work.

Thanks for your help.

        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

Solution 1

The code is a bit mixed up - you're setting up a SqlCommand object and then not using it. Try this:

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

Solution 2

Try to use

insertcommand.Parameters.AddWithValue("@ID", 50);

See some docs here: http://msdn.microsoft.com/it-it/library/system.data.sqlclient.sqlcommand.parameters.aspx