How to fix error "Must Declare the Scalar Variable"

Question

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.

        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

Ian Horwill · Accepted Answer · 2012-03-28 10:46:03Z

2

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

answered Mar 28, 2012 at 10:46

Ian Horwill

3,0172 gold badges26 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marco · Accepted Answer · 2012-03-28 09:23:41Z

1

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

answered Mar 28, 2012 at 9:23

Marco

57.7k15 gold badges135 silver badges159 bronze badges

1 Comment

netchicken Over a year ago

Thanks I used that initially but it still didn't work So I went back to the old add.

Rahul · Accepted Answer · 2012-03-28 09:30:31Z

0

You need to declare the parameter before using like this

SqlParameter @ID = new SqlParameter();

answered Mar 28, 2012 at 9:30

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

tripleee · Accepted Answer · 2016-05-27 13:27:04Z

0

Why not just write the string into a variable and use the variable to insert into your table?

dim ID as string = txtid.text

If you want to write data into a table, you have to wrap it like this 'data', just like you did with 'The Duck', for example. This means your command string has to look like this:

"Values('" & ID & "', 'Mr', 'Howard', 'The Duck', 'm', '12/12/23', 'asd', 'sdf', 'City', '0', '56') "

edited May 27, 2016 at 13:27

tripleee

192k37 gold badges318 silver badges369 bronze badges

answered Mar 28, 2012 at 9:29

brizz

1293 bronze badges

Collectives™ on Stack Overflow

How to fix error "Must Declare the Scalar Variable"

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related