SQlite: insufficient parameters supplied to the command

Question

I've been looking at the code below for God knows how long and I can't seem to find what the issue is. Most discussions I've seen on in relation to the stated error indicate that the source is from typos of the parameters. But why lie, I can't see any typo. Can someone tell me where am going wrong?

Dim dt As System.Data.DataTable = New System.Data.DataTable
dim sqlText as string
sqlText = "SELECT id AS privilegeID, " & _
"user_id AS userID, " & _
"module_id AS moduleID, " & _
"can_read AS canRead, " & _
"can_create AS canCreate, " & _
"can_update AS canUpdate, " & _
"can_delete AS canDelete, " & _
"is_deleted AS isDeleted, " & _
"is_active AS isActive " & _
"FROM base_user_privilege " & _
"WHERE " & _
"user_id=@userID AND " & _
"is_deleted=@isDeleted AND " & _
"is_active=@isActive"
SetDBCommand(sqlText)
DBCommand.Parameters.AddWithValue("@userID", userID)
DBCommand.Parameters.AddWithValue("@isDeleted", IsDeleted)
DBCommand.Parameters.AddWithValue("@isActive", IsActive)
dt = DBAction(sqlText, DBActionType.DataTable)

The setdbcommand(sqltext) is meant to establish the connection and it works so I don't think the error is there as its working on other select and insert statements.

Please assist.

Thanks.

EDITS

Here is the setdbcommand:

Public Sub SetDBCommand(ByVal sqlString As String)
    If DBConnection.State = ConnectionState.Closed Then
        DBConnection.Open()
    End If
    DBCommand = New SQLiteCommand(sqlString, DBConnection)
End Sub

Works in Reader but not data Table

I've realized that with my dataconnection function, the above parameters work when passed via the DataReader but don't work when passed via the DataTable Is there something am missing? Do parameters work with DataTables?

Public Function DBAction(ByVal sqlText As String, ByVal ActionType As DBActionType) As Object
    DBAction = Nothing
    Select Case ActionType
        Case DBActionType.DataReader
            Return DBCommand.ExecuteReader
        Case DBActionType.Dataset
            Dim MyAdpater As New SQLiteDataAdapter(sqlText, DBConnection)
            Dim myDataSet As New System.Data.DataSet
            MyAdpater.Fill(myDataSet)
            Return myDataSet
        Case DBActionType.DataTable
            Dim MyAdpater As New SQLiteDataAdapter(sqlText, DBConnection)
            Dim MyDataTable As New System.Data.DataTable
            MyAdpater.Fill(MyDataTable)
            Return MyDataTable
        Case DBActionType.executeNonQuery
            DBCommand.ExecuteNonQuery()
        Case DBActionType.ExecuteScalar
            Dim ScalarItem As Object = DBCommand.ExecuteScalar
            Return ScalarItem
        Case Else
            Throw New Exception("Error in DBACTION")
    End Select
End Function

please include SetDBcommand for context. Can't see what's going on without it! — Michael
What happens if you replace @parameter with :parameter? Or try changing the parameter names all together (you have the parameter names the same as aliased columns...that shouldn't be an issue, but "shouldn't" and "isn't" can often be different in debugging...) — sybkar
I've replaced the @parameter with :parameter but its still gives me the same error. I've also changed the parameter names and still I get the same error. — lukik
why are you initializing MyAdapter with sqlText, and not using the command at all? How does the adapter use the parameters this way? — sybkar

sybkar sybkar · Accepted Answer · 2012-09-14T17:03:14

The DBCommand must be supplied as a parameter when creating the SQLiteDataAdapter. As a bonus, you won't need to provide the connection parameter, since you've already setup the command appropriately:

Public Function DBAction(ByVal sqlText As String, ByVal ActionType As DBActionType) As Object
    DBAction = Nothing
    Select Case ActionType
        Case DBActionType.DataReader
            Return DBCommand.ExecuteReader
        Case DBActionType.Dataset
            Dim MyAdpater As New SQLiteDataAdapter(DBCommand)
            Dim myDataSet As New System.Data.DataSet
            MyAdpater.Fill(myDataSet)
            Return myDataSet
        Case DBActionType.DataTable
            Dim MyAdpater As New SQLiteDataAdapter(DBCommand)
            Dim MyDataTable As New System.Data.DataTable
            MyAdpater.Fill(MyDataTable)
            Return MyDataTable
        Case DBActionType.executeNonQuery
            DBCommand.ExecuteNonQuery()
        Case DBActionType.ExecuteScalar
            Dim ScalarItem As Object = DBCommand.ExecuteScalar
            Return ScalarItem
        Case Else
            Throw New Exception("Error in DBACTION")
    End Select
End Function

As an aside, since I didn't see them anywhere in the code you posted, make sure you're properly disposing of the SQLiteCommand and SQLiteDataAdapter objects. The connection should be closed, and then disposed, when finished.

SQlite: insufficient parameters supplied to the command

2 Answers