ADO parameterized query help

[j420exe]

If I have a query that has 5 parameters but I am only passing in 3 of them how can i delete the parameter or what can I set the parameter to if it isempty??? This is to an Access Database.

Public Function GetRecordset(sSQL As String, Optional arrParams As Variant) As ADODB.Recordset
Dim cmd As ADODB.Command
Dim rsResult As New ADODB.Recordset

' Initialize variables
Set cmd = New ADODB.Command

' Set connection and SQL statement to be run by the command
' * Note: SQL statement can be a parameterized query
Set cmd.ActiveConnection = m_connADO
cmd.CommandText = sSQL

Dim i As Integer
For i = LBound(arrParams) To UBound(arrParams)
cmd.Parameters(i) = arrParams(i)
Next i

With rsResult
.CursorLocation = adUseClient
.Open cmd, , adOpenDynamic, adLockBatchOptimistic
End With

' Execute the query (with parameters) and get the result
'Set rsResult = cmd.Execute

' Release objects
Set cmd = Nothing

' Set return object
Set GetRecordset = rsResult
End Function

Thanks for the help.

 

[tb]

Will it help if you loop through arrParms until you get one the first one that doesn't have a value and the do a
REDIM PRESERVE arrParms(x) ?

 

[chiph]

You will need to set the remaining 2 parameters to values that would make sense for the SQL query you're running -- typically an empty string for character values, zeros for numeric ones. The trick is knowing which is which, and for that you have to know what your query wants.

Chip H.