[RESOLVED] ADO parameters.. in query

How do I pass in Params to a regular Recordset query??

the query that the data is pulling from is like this:

PARAMETERS c_CILLI Text ( 255 );
SELECT Fields.... etc

there are about 10 more queries after this that finally feed a union query that I am pulling off of....

So.. my query is

"SELECT * INTO tbl_temp_data FROM quniTABLE"

now.. it wants the param...? how do I pass it in?

Create a Command Object and pass the parameters that way.

ok.. maybe I did it worng.. but It didnt work!??

lemme try again

doesnt like it?? did I do this wrong?

VB Code:

Dim rst As New Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim tmp() As String
Dim SQL As String
 
tmp = Split(cboCILLI, " - ")
SQL = "SELECT COLLO, CILLI, COLLO_NAME, ADDRESS, SWITCH, ILEC_ZONE, " & _
        "TRO_DS1_LOOP , TRO_TIER1, NET_ONOFF, T1, TRUNK_MUX, MILES " & _
        "Into tbl_temp_COLLO_DATA " & _
        "FROM quniCOLLO_DATA"
 
param.Name = "c_CILLI"
param.Value = tmp(0)
param.Type = adVarChar
 
CNN.Execute "DROP TABLE tbl_temp_COLLO_DATA"
cmd.ActiveConnection = CNN
cmd.CommandText = SQL
cmd.CommandType = adCmdText
cmd.Parameters.Append param
 
Set rst = cmd.Execute

This is an Access db or a SQL server db?

sorry

Error:
3708
Parameter object is improperly defined. Inconsistant or Incomplete information was provided

Access

What is the error?

Probably needing to specify the storedproc property.

VB Code:

Set goRs = New ADODB.Recordset
    Set oCM = New ADODB.Command
    With oCM
        .ActiveConnection = goEnv.Cnn
        .CommandType = adCmdStoredProc
        .CommandText = sSP_Name
        .Prepared = True
        .Parameters.Append .CreateParameter("@Where", adChar, adParamInput, 10, Mid$(sWhere, 2))
    End With
    goRs.CursorLocation = adUseClient
    goRs.Open oCM, , adOpenKeyset, adLockOptimistic, adCmdStoredProc
    Set Exec_SPrRS = goRs

To execute the Parameter Query use

VB Code:

With cmd
    .CommandText = "[quniCOLLO_DATA]"
[B]    .CommandType = adCmdTable[/B] 'adCmdUnknown when specifying a Select List
    .Parameters.Append .CreateParameter("@Parm1", adVarChar, adParamInput,255 , Mid$(sWhere, 2))
    Set .ActiveConnection = db
    Set rs = .Execute
End With

To use the Parameter Query as a dataset to another query use

VB Code:

With cmd
    .CommandText = "Select * Into tblTempDate From [quniCOLLO_DATA]"
[B]    .CommandType = adCmdUnknown[/B]
    .Parameters.Append .CreateParameter("@Parm1", adVarChar, adParamInput,255 , Mid$(sWhere, 2))
    Set .ActiveConnection = db
    .Execute , , adExecuteNoRecords
End With

Quote:

---

Parameter object is improperly defined. Inconsistant or Incomplete information was provided

---

You must specify the Size property of the Parameter object when its data type is variable length.

Oops for got to change that as I pulled the code from a project of mine. :blush:

ok.. I couldnt get it to work with a select list.. but when I used the mak table query name it work perfectly! thanks!