Must declare the scalar variable "@SSTGroupID"

Question

Private Function GetSvcType(ByVal oCommand As OleDbCommand, ByVal SSTGroupID As Integer) As DataTable
    Dim sSQL As New StringBuilder

    sSQL.AppendLine(" Select SSTServiceTypeID AS ID, SSTServiceTypeName AS Name ")
    sSQL.AppendLine(" from fgen_SSTServiceType (nolock) ")
    sSQL.AppendLine(" Where 1=1 AND Disabled = 0 ")
    sSQL.AppendLine(" AND fgen_SSTServiceType.SSTGroupID = @SSTGroupID ")
    oCommand.Parameters.AddWithValue("@SSTGroupID", SSTGroupID)
    Return GetDataTable(sSQL.ToString)
End Function

Private Function GetDataTable(ByVal SQL As String) As DataTable
    Dim oConn As OleDbConnection = New OleDbConnection(_strConnection)
    Dim oCommand As New OleDbCommand("", oConn)
    oCommand.Connection.Open()
    oCommand.CommandText = SQL
    oCommand.Parameters.Clear()
    Dim oDataTable As New DataTable
    Dim oDataAdapter As New OleDbDataAdapter(oCommand)
    oDataAdapter.Fill(oDataTable)
    If oDataTable.Rows.Count > 0 Then
      GetDataTable = oDataTable
    Else
      GetDataTable = Nothing
    End If
    oCommand.Connection.Close()
    oCommand.Dispose()

  End Function

I've been searching for hours on end and can't seem to find a solution. I need your help please thanks

I've updated my question include the GetDataTable function. Please take a look thanks.

There's nothing in the code you've provided that the parameter is added to the same command that you're actually executing. — jmcilhinney, Nov 25 '19 at 07:33
That's because AppendLine does not support the variables for AddWithValue — Jiunn Wye Leow, Nov 25 '19 at 07:38
That makes no sense at all I'm afraid. The two methods have nothing to do with each other. You're simply building a `String` containing SQL code with that `StringBuilder`. You then have to assign that `String` to the `CommandText` of a command object and you need to add your parameter(s) to the same command. There's no specific evidence in the code you have provided that you're doing that. — jmcilhinney, Nov 25 '19 at 08:34
Ok so then, how do I include my ```addwithvalue``` to the ```getdatatable``` method? — Jiunn Wye Leow, Nov 25 '19 at 09:14
How would we know, given that we've never seen what's in that `GetDataTable` method? — jmcilhinney, Nov 25 '19 at 12:01
Are you re-using this OleDbCommand object? Need to show us how it's being used. — LarsTech, Nov 25 '19 at 16:22

score 0 · Answer 1 · answered Nov 25 '19 at 16:28

Your command never gets the text from the StringBuilder. So I think the missing link is that you should assign the string you've built to the command text

oCommand.CommandText = sSQL.ToString()

then add the parameter after that

Private Function GetSvcType(ByVal oCommand As OleDbCommand, ByVal SSTGroupID As Integer) As DataTable
    Dim sSQL As New StringBuilder()
    sSQL.AppendLine(" Select SSTServiceTypeID AS ID, SSTServiceTypeName AS Name ")
    sSQL.AppendLine(" from fgen_SSTServiceType (nolock) ")
    sSQL.AppendLine(" Where 1=1 AND Disabled = 0 ")
    sSQL.AppendLine(" AND fgen_SSTServiceType.SSTGroupID = @SSTGroupID ")
    oCommand.CommandText = sSQL.ToString()
    oCommand.Parameters.AddWithValue("@SSTGroupID", SSTGroupID)
    Return GetDataTable(oCommand.CommandText)
End Function

Alternatively, you may want to use a Using to create a command and dispose it. I'd write it but I don't see your connection so you should look into this answer for an example.

I've followed your method but I seem to still get the same error. I fathom it has something to do with my ```GetDataTable``` method and not including the ```@SSTGroupID``` into this method. I've included the method into the question for your reference. The error is pointed to ```oDataAdapter.Fill(oDataTable)``` — Jiunn Wye Leow, Nov 26 '19 at 02:07

Mary · Answer 2 · 2019-11-26T05:23:22.813

I wasn't sure what the Where 1 = 1 and the (no lock) were doing so I removed them.

The function FillDataTable contains all your database access code which keeps it separate from the User Interface code. Your database objects should be locale so you can control that they are closed and disposed. The Using...End Using block takes care of this even if there is an error. Get rid of any class level variables for commands and connections. Both the command and connection are included; note the comma at the end if the first line of the Using.

You can pass your connection string directly to the constructor of the connection and pass the command text and connection directly to the constructor of the command. Saves having to set these properties individually.

OleDb pays no attention to the name of the parameter, so, the order that the parameter is added to the Parameters collection must match the order that the parameter appears in the command text. In this case, you have only one but just for future reference. It is better to use the Parameters.Add() which includes the database datatype. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications Here is another https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

Note: I had to guess at the datatype of your parameter. Check your database for the actual type.

Always open your connection at the last possible moment (the line before the .Execute...) and close it as soon as possible (the End Using)

Private Function FillDataTable(GroupID As Long) As DataTable
    Dim strSQL = "Select SSTServiceTypeID AS ID, SSTServiceTypeName As Name
    From fgen_SSTServiceType 
    Where Disabled = 0
    And SSTGroupID = @SSTGroupID "
    Dim dt As New DataTable
    Using cn As New OleDbConnection("Your connection string"),
            cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@SSTGroupID", OleDbType.BigInt).Value = GroupID
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt = FillDataTable(7L) 'the L indicates that this is a long,pass the GroupID to the function
    DataGridView1.DataSource = dt
End Sub

EDIT

Dim dt = FillDataTable(7L) 'In the button code

And in the Data Access code change Oledb to Sql

Imports System.Data.SqlClient
Class DataAccess
    Private Function FillDataTable(GroupID As Long) As DataTable
        Dim strSQL = "Select SSTServiceTypeID AS ID, SSTServiceTypeName As Name
From fgen_SSTServiceType 
Where Disabled = 0
And SSTGroupID = @SSTGroupID "
        Dim dt As New DataTable
        Using cn As New SqlConnection("Your connection string"),
        cmd As New SqlCommand(strSQL, cn)
            cmd.Parameters.Add("@SSTGroupID", SqlDbType.BigInt).Value = GroupID
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
        Return dt
    End Function
End Class

Both the ```SSTGroupID``` and ```ServiceTypeID``` are of type ```Long``` . The ```SSTServiceTypeName``` is of type String — Jiunn Wye Leow, Nov 26 '19 at 02:10
@JiunnWyeLeow See change to my answer. `GroupID As Long` and `OleDbType.BigInt` — Mary, Nov 26 '19 at 02:19
When I use your function i get the same error at ```dt.Load(cmd.ExecuteReader)``` — Jiunn Wye Leow, Nov 26 '19 at 03:03
Version Visual Studio Professional 2015 14.0.25431.01 Update 3. I'm not using Access, I'm using another type of db software — Jiunn Wye Leow, Nov 26 '19 at 03:49
@djv Thank you! I have done all sorts of dances when building a search query based on different fields. Now I know the easy way. — Mary, Nov 26 '19 at 06:21

Must declare the scalar variable "@SSTGroupID"

2 Answers2