0

I have a function in vb.net module that is meant to get the number of records in an access database.

Function CalculateRecords(ByVal column As String, ByVal table As String)
    Dim count As Integer = 0
    Using connect As New OleDbConnection(connectionString)
        connect.Open()
        Using com As New OleDbCommand("SELECT COUNT(@column) FROM @table", connect)
            com.Parameters.Add(New OleDbParameter("@column", column))
            com.Parameters.Add(New OleDbParameter("@table", table))
            count = com.ExecuteScalar
        End Using
    End Using
    Return count
End Function

Every time it's run an Incomplete sql clause exception is thrown. Through trial and error I found that the problem lies with the @table parameter. If rewritten this way:

Function CalculateRecords(ByVal column As String, ByVal table As String)
    Dim count As Integer = 0
    Using connect As New OleDbConnection(connectionString)
        connect.Open()
        Using com As New OleDbCommand("SELECT COUNT(@column) FROM " & table, connect)
            com.Parameters.Add(New OleDbParameter("@column", column))
            count = com.ExecuteScalar
        End Using
    End Using
    Return count
End Function

The function works just fine. Where am I going wrong with my parameters?

belaythat
  • 45
  • 8
  • 1
    Parameters won't work on table names. You need to use the real names there. Parameters are used to supply the value for a field in the query. – LarsTech Aug 05 '15 at 17:23
  • Easy enough a fix, thanks for the speedy response! – belaythat Aug 05 '15 at 17:24
  • If using `SqlServer` you could use parameter's as table names and do a replace on them with dynamic sql... – Trevor Aug 05 '15 at 17:43

0 Answers0