netchicken netchicken - 6 months ago 17
SQL Question

VB.net SQL error Must Declare the Scalar Variable

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

Answer

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