Must declare the scalar variable "@??????"

2
votes

I am using the following SQL statement :

      Dim strSql5 As String = "SELECT  * FROM dbo.ontledings " & _
                                " where plaasblok =  @plaasblokparsversoeke " & _
                                " and analisedatum  =  @laastedatum"


    cnn.Open()
    Dim aksie2 As New SqlClient.SqlCommand(strSql5, cnn)
    aksie2.Parameters.Add("@plaasblokparsversoeke", SqlDbType.VarChar).Value = plaasblokparsversoeke
    aksie2.Parameters.Add("@laastedatum", SqlDbType.Date).Value = laastedatum
    aksie2.ExecuteNonQuery()
    cnn.Close()

I want to fill a datatable like so :

    Dim dtb5 As New DataTable
    dtb5.Clear()

    Using cnn As New SqlConnection("Data Source=GIDEON-E-LAPTOP\SQLEXPRESS2014;Initial Catalog=SkeduleringDatabasis;Integrated Security=True")
        cnn.Open()

        Using dad5 As New SqlDataAdapter(strSql5, cnn)

            dad5.Fill(dtb5)
        End Using
        cnn.Close()
    End Using

I get the following error message : Must declare the scalar variable "@plaasblokparsversoeke"

I cannot figure out where and how to declare the scalar variables.

The error occurs at the line : dad5.Fill(dtb5)

Regards

3
sql-servervb.net

3 Answers

5
votes

You need to add the parameters like

cmd.Parameters.Clear() 
cmd.Parameters.AddWithValue("@plaasblokparsversoeke", plaasblokparsversoeke.Text)
cmd.Parameters.AddWithValue("@laastedatum", laastedatum.Text)

As correctly commented below, you need to use the Add method to add the paramters:

cmd.Parameters.Add("@plaasblokparsversoeke", SqlDbType.Varchar).Value = plaasblokparsversoekeVariable;
cmd.Parameters.Add("@laastedatum", SqlDbType.Varchar).Value = laastedatumVariable;
1
votes

Firstly, avoid hard-coding a parameterised query in this way, for avoidance of SQL injection.

To answer your specific question: you are trying to use parameters in the query without having defined them in the SQLCommand.

Have a look at MSDN for the full documentation.

1
votes

With this query

Dim strSql5 As String = "SELECT  * FROM dbo.ontledings " & _
                            " where plaasblok =  @plaasblokparsversoeke " & _
                            " and analisedatum  =  @laastedatum"

You are definining 2 SQL variables called @plaasblokparsversoeke and @laastedatum that you have to pass as parameters to your command, like this

command.Parameters.Add("@plaasblokparsversoeke", SqlDbType.Int);
command.Parameters["@plaasblokparsversoeke "].Value = yourValue;

MSDN article on SQL command parameters here