"too few parameters expected" when filtering OpenRecordset

  • Thread starter JJ of Eugene OR
  • Start date
J

JJ of Eugene OR

My SQL Server database has a stored procedure that is rather resource
intensive. Rather than run that stored procedure 3 times, I want to run the
stored procedure once, return all the data that I need to the front end
(which is an MS Access 2003 .mdb file), and then use code to break up that
small amount of data into the three listboxes. Put another way: The stored
proc is returning a single that has rows which need to be split into three
listbox controls on the form.

I'm using code that looks to me to be exactly like an example in the on-line
help, but it returns an error. The basic idea is to get the data into one
recordset. Then use Filter and OpenRecordset to get just the data I need
into a second recordset and then assign the second recordset to the listbox.

Here's the guts of the code problem. This code DOES work if I comment OUT
the .Filter line. But if I leave the rs.Filter line in, then at the
OpenRecordset line (the Filter line itself does not produce an error), I get
the following error: "Too few parameters expected. Expected 2"

'Define the SQL to run the stored proc
stSQL = "Exec proc_Appts '" & gBranch & _
"', '" & Format(Me.txtSrchApptDt, "m/d/yyyy") & "'"
fRunSQL stSQL, rs 'fRunSQL=custom function to run the stored proc and
put results in the recorset variable, 'rs'
rs.Filter = "Right([Time],1) = 9" 'the real filter I want to use
'rs.Filter = "Right([Client],1) = 'e'" 'test filter. This fails with
same error.
'rs.Filter = "[Time] = '8:00'" 'test filter. This fails with same error.
'rs.Filter = "[Note] = 'dbl'" 'test filter. This fails with same error.
'the following line is the line that fails with the 'too few parameters'
error if the Filter line above is included
Set thisApptsG = rs.OpenRecordset() 'open new recordset object off of
rs, but with the filter
Set Me.lstAppts_g.Recordset = thisApptsG 'assign the filtered recordset
to the listbox

Note one weird thing: As mentioned, if I comment out the rs.Filter line,
the code runs without error. The weird thing is that the listbox shows the
header row with a period before the text of each column. For example, it
shows the header text for the client column as ".Client". The period is not
there in the stored proc. (Proof: If I instead run the stored proc through a
pass-through query and assign the listbox's RowSource to the pass-through
query, the header rows appear correctly.) If I change the rs.Filter row to
include a period in the field's name (ex: rs.Filter = "[.Note] = 'dbl'"), I
get an error.

Here is all of the code so you can see the context in which the snippet
above lives.

'At the top of the form as module-level scope, I declare a recordset variable:
Dim thisApptsG As DAO.Recordset

'The next bit of code is in the body of the Sub:

'This is another recordset variable to hold all the data from the stored
proc
Dim rs As DAO.Recordset 'return results from stored proc for which we
want to manipulate the results here

'Set SQL for the first appointments list box.
stSQL = "Exec proc_Appts '" & gBranch & _
"', '" & Format(Me.txtSrchApptDt, "m/d/yyyy") & "'"
fRunSQL stSQL, rs
rs.Filter = "Right([Time],1) = 9"
Set thisApptsG = rs.OpenRecordset() 'open new rs object off of rs, but
with the filter
Set Me.lstAppts_g.Recordset = thisApptsG 'assign filtered recordset to
the listbox
...

'The above code uses a custom function to run the stored proc: fRunSQL.
This is
'the code for fRunSQL

Function fRunSQL(argSQL As String, Optional argRecs, Optional argConn) As Long
' Comments: Does a pass-through query using argSQL and the connection
' string specified below or in the arguments. If argRecs exists,
' then the pass-through query will be set to return records.
' Otherwise, it is assumed that argSQL is an action query, like
' a Delete query.
' Arguments: argRecs is a variable to hold the returned recordset
' argSQL is the SQL statement to be sent to the database
' argConn is the connection string. If not sent, a default of
' gCTSodbc is assumed below. Use a zero length string ("") to
' connect to local tables in this .mdb.
' Returns: True if all went well and false otherwise.
' Examples: fRunSQL(StringVariableOFASQLstatement)
' fRunSQL("Select * From Person", RecorsetVariable) - you can then
use
' the resulting records 'RecordsetVariable' in your code
' fRunSQL argSQL:="Update Table Set Field = 'bla'",
argConn:=gORACCodbc.ConnString
' Created: Wednesday, July 28, 1999 10:35:03 AM clpl146a
' Modified:
'
' --------------------------------------------------------
On Error GoTo fRunSQL_ERR

'Declair and initialize variables
Dim dbs As Database
Dim myQuery As QueryDef
Dim errStored As Error 'see error handler below

'must be good practice because all the examples do it
Set dbs = CurrentDb

'create the query def
Set myQuery = dbs.CreateQueryDef("") 'by not setting a name, this
becomes a temporary query

'use With statement to improve performance
With myQuery
'Set the Querydef properties.
' If argConn is missing, then use a default global variable for the
connection.
If IsMissing(argConn) Then 'use the default
.Connect = gCTSodbc.ConnString
Else 'use the argument sent to the function
.Connect = argConn
End If '{If IsMissing(argConn) Then }
.SQL = argSQL

'Do different things depending on whether the query is supposed to
'return records or not
If IsMissing(argRecs) Then 'the query does not return records
'let the object know that records will NOT be returned
.ReturnsRecords = False
'execute the query
.Execute dbSQLPassThrough
Else 'the query DOES return records
'let the object know that records will be returned
.ReturnsRecords = True
'run the query/open the recordset
Set argRecs = .OpenRecordset()
End If

End With

'If you made it to here, everything must have gone hunky dory
fRunSQL = True

Exit_fRunSQL:
Exit Function

fRunSQL_ERR:
'The first error that comes back from a server error is a generic 3146
ODBC
'error. It is the second and subsequent numbers that tell you anything.
'In order to access the meaningful numbers, you have to loop through the
Errors
'object of DBEngine.
'(See Knowledge Base article Q185384 for more info.)
'Below are a list of some of the known error numbers. If you are not
receiving
'an error in this list, you add that error to the Select Case statement.
For Each errStored In DBEngine.Errors
Select Case errStored.Number
Case 3146
' No action -- standard ODBC--Call failed error.
Case 1222
' Timeout lock exceeded error - about a block or lock issue.
MsgBox "Beware: CTS was trying to do more than one thing at
once, and " & _
"there was a conflict. One of those things did not run.
This may " & _
"or may not be a big deal. If you are worried about it,
please " & _
"take a detailed note of EXACTLY what you were doing when
you " & _
"got this message and then call the Help Desk: x2322." & _
vbNewLine & vbNewLine & "Error Number " & errStored.Number &
": " & _
errStored.Description, vbOKOnly, "Bummer"
Case 2627
' Error caused by duplicate value in primary key.
MsgBox "You tried to enter a duplicate value " & _
"in the Primary Key.", vbOKOnly, "OOPS"
Case 3621
' No action -- standard ODBC command aborted error.
Case 547
' Foreign key constraint error.
MsgBox "You violated a foreign key constraint.", vbOKOnly, "OOPS"
Case Else
' An error not accounted for in the Select Case
' statement.
MsgBox "Error in fRunSQL. Error Number " & errStored.Number &
": " & _
errStored.Description & " " & _
vbNewLine & errStored.Source, vbOKOnly, "ERROR"
End Select
Next errStored

fRunSQL = False 'there is a dark shadow in PC land
Resume Exit_fRunSQL

End Function


THANK YOU FOR ANY HELP YOU CAN PROVIDE!!!!

- JJ, Eugene OR
 
S

Steve Sanford

Hi JJ,

There's a lot I don't understand about SQL pass thru queries, but I'm having
(trying) to learn.

In looking at your code, the filter line doesn't look right.

If you added this line

msgbox "Right([Time],1) = 9"

before the rs.Filter line, do you get a valid filter statement??

As I understand it, the filter statement should be like a WHERE clause
without the 'WHERE'. Do you have a field named "Right([Time],1)"?

The only solution I know of is to add a column in the query to get the
rightmost char of the field and use that column to compare to your criteria.

So in the query (in Access) would be:

Expr1: Right([Time],1)

Then the filter statement would be

rs.Filter = "[Expr1] = 9 "


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


JJ of Eugene OR said:
My SQL Server database has a stored procedure that is rather resource
intensive. Rather than run that stored procedure 3 times, I want to run the
stored procedure once, return all the data that I need to the front end
(which is an MS Access 2003 .mdb file), and then use code to break up that
small amount of data into the three listboxes. Put another way: The stored
proc is returning a single that has rows which need to be split into three
listbox controls on the form.

I'm using code that looks to me to be exactly like an example in the on-line
help, but it returns an error. The basic idea is to get the data into one
recordset. Then use Filter and OpenRecordset to get just the data I need
into a second recordset and then assign the second recordset to the listbox.

Here's the guts of the code problem. This code DOES work if I comment OUT
the .Filter line. But if I leave the rs.Filter line in, then at the
OpenRecordset line (the Filter line itself does not produce an error), I get
the following error: "Too few parameters expected. Expected 2"

'Define the SQL to run the stored proc
stSQL = "Exec proc_Appts '" & gBranch & _
"', '" & Format(Me.txtSrchApptDt, "m/d/yyyy") & "'"
fRunSQL stSQL, rs 'fRunSQL=custom function to run the stored proc and
put results in the recorset variable, 'rs'
rs.Filter = "Right([Time],1) = 9" 'the real filter I want to use
'rs.Filter = "Right([Client],1) = 'e'" 'test filter. This fails with
same error.
'rs.Filter = "[Time] = '8:00'" 'test filter. This fails with same error.
'rs.Filter = "[Note] = 'dbl'" 'test filter. This fails with same error.
'the following line is the line that fails with the 'too few parameters'
error if the Filter line above is included
Set thisApptsG = rs.OpenRecordset() 'open new recordset object off of
rs, but with the filter
Set Me.lstAppts_g.Recordset = thisApptsG 'assign the filtered recordset
to the listbox

Note one weird thing: As mentioned, if I comment out the rs.Filter line,
the code runs without error. The weird thing is that the listbox shows the
header row with a period before the text of each column. For example, it
shows the header text for the client column as ".Client". The period is not
there in the stored proc. (Proof: If I instead run the stored proc through a
pass-through query and assign the listbox's RowSource to the pass-through
query, the header rows appear correctly.) If I change the rs.Filter row to
include a period in the field's name (ex: rs.Filter = "[.Note] = 'dbl'"), I
get an error.

Here is all of the code so you can see the context in which the snippet
above lives.

'At the top of the form as module-level scope, I declare a recordset variable:
Dim thisApptsG As DAO.Recordset

'The next bit of code is in the body of the Sub:

'This is another recordset variable to hold all the data from the stored
proc
Dim rs As DAO.Recordset 'return results from stored proc for which we
want to manipulate the results here

'Set SQL for the first appointments list box.
stSQL = "Exec proc_Appts '" & gBranch & _
"', '" & Format(Me.txtSrchApptDt, "m/d/yyyy") & "'"
fRunSQL stSQL, rs
rs.Filter = "Right([Time],1) = 9"
Set thisApptsG = rs.OpenRecordset() 'open new rs object off of rs, but
with the filter
Set Me.lstAppts_g.Recordset = thisApptsG 'assign filtered recordset to
the listbox
...

'The above code uses a custom function to run the stored proc: fRunSQL.
This is
'the code for fRunSQL

Function fRunSQL(argSQL As String, Optional argRecs, Optional argConn) As Long
' Comments: Does a pass-through query using argSQL and the connection
' string specified below or in the arguments. If argRecs exists,
' then the pass-through query will be set to return records.
' Otherwise, it is assumed that argSQL is an action query, like
' a Delete query.
' Arguments: argRecs is a variable to hold the returned recordset
' argSQL is the SQL statement to be sent to the database
' argConn is the connection string. If not sent, a default of
' gCTSodbc is assumed below. Use a zero length string ("") to
' connect to local tables in this .mdb.
' Returns: True if all went well and false otherwise.
' Examples: fRunSQL(StringVariableOFASQLstatement)
' fRunSQL("Select * From Person", RecorsetVariable) - you can then
use
' the resulting records 'RecordsetVariable' in your code
' fRunSQL argSQL:="Update Table Set Field = 'bla'",
argConn:=gORACCodbc.ConnString
' Created: Wednesday, July 28, 1999 10:35:03 AM clpl146a
' Modified:
'
' --------------------------------------------------------
On Error GoTo fRunSQL_ERR

'Declair and initialize variables
Dim dbs As Database
Dim myQuery As QueryDef
Dim errStored As Error 'see error handler below

'must be good practice because all the examples do it
Set dbs = CurrentDb

'create the query def
Set myQuery = dbs.CreateQueryDef("") 'by not setting a name, this
becomes a temporary query

'use With statement to improve performance
With myQuery
'Set the Querydef properties.
' If argConn is missing, then use a default global variable for the
connection.
If IsMissing(argConn) Then 'use the default
.Connect = gCTSodbc.ConnString
Else 'use the argument sent to the function
.Connect = argConn
End If '{If IsMissing(argConn) Then }
.SQL = argSQL

'Do different things depending on whether the query is supposed to
'return records or not
If IsMissing(argRecs) Then 'the query does not return records
'let the object know that records will NOT be returned
.ReturnsRecords = False
'execute the query
.Execute dbSQLPassThrough
Else 'the query DOES return records
'let the object know that records will be returned
.ReturnsRecords = True
'run the query/open the recordset
Set argRecs = .OpenRecordset()
End If

End With

'If you made it to here, everything must have gone hunky dory
fRunSQL = True

Exit_fRunSQL:
Exit Function

fRunSQL_ERR:
'The first error that comes back from a server error is a generic 3146
ODBC
'error. It is the second and subsequent numbers that tell you anything.
'In order to access the meaningful numbers, you have to loop through the
Errors
'object of DBEngine.
'(See Knowledge Base article Q185384 for more info.)
'Below are a list of some of the known error numbers. If you are not
receiving
'an error in this list, you add that error to the Select Case statement.
For Each errStored In DBEngine.Errors
Select Case errStored.Number
Case 3146
' No action -- standard ODBC--Call failed error.
Case 1222
' Timeout lock exceeded error - about a block or lock issue.
MsgBox "Beware: CTS was trying to do more than one thing at
once, and " & _
"there was a conflict. One of those things did not run.
This may " & _
"or may not be a big deal. If you are worried about it,
please " & _
"take a detailed note of EXACTLY what you were doing when
you " & _
"got this message and then call the Help Desk: x2322." & _
vbNewLine & vbNewLine & "Error Number " & errStored.Number &
": " & _
errStored.Description, vbOKOnly, "Bummer"
Case 2627
' Error caused by duplicate value in primary key.
MsgBox "You tried to enter a duplicate value " & _
"in the Primary Key.", vbOKOnly, "OOPS"
Case 3621
' No action -- standard ODBC command aborted error.
Case 547
' Foreign key constraint error.
MsgBox "You violated a foreign key constraint.", vbOKOnly, "OOPS"
Case Else
' An error not accounted for in the Select Case
' statement.
MsgBox "Error in fRunSQL. Error Number " & errStored.Number &
": " & _
errStored.Description & " " & _
vbNewLine & errStored.Source, vbOKOnly, "ERROR"
End Select
Next errStored

fRunSQL = False 'there is a dark shadow in PC land
Resume Exit_fRunSQL

End Function


THANK YOU FOR ANY HELP YOU CAN PROVIDE!!!!

- JJ, Eugene OR
Click to expand...
 
J

JJ of Eugene OR

Steve:

Thank you for your reply. The text:
.. Right([Time],1) = 9
may not be very efficient, but it is a valid expression in a WHERE clause.
Since it is a tiny recordset, I wasn't too worried about the efficiency.

However, I thought that the function and/or the name of the column might be
giving VBA a difficult time. So, I also tried the following filter just to
see if I could get anything to work:
.. [Note] = 'dbl'
This also failed.

By the way, this second test (it was about my 5th test really) is almost
identical to the example from the on-line help. Here is the example from the
on-line help:

' Use the Filter property to retrieve only certain
' records with the next OpenRecordset call.
Debug.Print "Opening recordset from existing " & _
"Recordset object to filter records..."
rstTemp.Filter = "LastName >= 'M'"
Set rstTemp2 = rstTemp.OpenRecordset()

I wish you luck in learning pass-through queries. They are a great tool to
have in your toolbox.

- JJ, Eugene OR
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenRecordset fails Error 3061 too few params expected 12 6
CurrentDb.OpenRecordset & "Too Few Parameters expected 1". 6
OpenRecordset ... Too few parameters 1
DAO Clone and filter question 1
Error 3061: Too Fee Parameters 2
Too few parameters. Expected 1. 2
too few parameters. Expected 1 2
Run-time error 3061. Too Few Parameters. Expected 2. 2

Top