Too few parameters - ADODB in VB6

[patljones]

Running the code below comes up with
"Run-time error '-2147217904 (80040e10)': [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1."
on the cmd.execute line.

Private Sub Command1_Click()
Const ConnectString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=L:\HCUK Synergy\Generic\CWtoGM.mdb"
Dim param As ADODB.Parameter
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
conn.ConnectionTimeout = 10
conn.Open (ConnectString)
cmd.ActiveConnection = conn
cmd.CommandText = "Parameters [pCName] Integer; SELECT CWDATA.NAME FROM CWDATA WHERE CWDATA.ID = [pCName]"
cmd.CommandType = adCmdText
Set param = cmd.CreateParameter("[pCName]", adInteger, adParamInput)
cmd.Parameters.Append param
param.Value = 11
cmd.Execute
End Sub

I'm using VB6.0 and connecting to an Access 97 db. Replacing the [pCName] in the SQL statement with a literal '11' works so there's some problem with using parameters.

 

[MarkSweetland]

Looks like you're appending the parameter before you're assigning the value. Try swapping the order of the assignment and append:

from:
cmd.Parameters.Append param
param.Value = 11

to:
param.Value = 11
cmd.Parameters.Append param

If you use a command object to return a recordset (since you are using a SELECT in the command text) it will be a read-only, forward only recordset. But you can't use it if you don't assign the recordset to from your execute method. You have to use the SET method: set rs = cmd.Execute

And if you are returning a recordset, why not create the SQL statement and use it to open a recordset and bypass the command object all together?

strSQL ="SELECT CWDATA.NAME FROM CWDATA WHERE CWDATA.ID = " & intSomeIDValue
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic
if not (rs.EOF and rs.BOF)
' do something
.
.









Mark

 

[zemp]

Parameters are used for stored procedures or existing quesries to fill in the blanks. It looks like you are using an SQL statement and not an existing Access query. If that is true then you should not be using the parameter. Instead, construct your SQL statement with the variable.

If you use parameters the cmd.execute must have a place to use it. Since [pCName] is not defined anywhere you are getting an error.

Your last statement shows that you resolved the issue, you just may not have known why you got the error in the first place.

Your code should look something like this

cmd.CommandText = "SELECT CWDATA.NAME FROM CWDATA WHERE CWDATA.ID = 11;"
cmd.Execute

Or, if using the variable, intVariable

cmd.CommandText = "SELECT CWDATA.NAME FROM CWDATA WHERE CWDATA.ID = " & intVariable & ";"
cmd.Execute


Thanks and Good Luck!

zemp

 

[patljones]

Thanks Mark and Zemp, ditched the parameters idea and reform the query every time I change the "parameters" - not very elegant but it works.