[RESOLVED] ADO parameters.. in query

[Static]

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?

[RobDog888]

Create a Command Object and pass the parameters that way.

[Static]

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

lemme try again

[Static]

doesnt like it?? did I do this wrong?

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

[RobDog888]

This is an Access db or a SQL server db?

[Static]

sorry

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

[Static]

Access

[RobDog888]

What is the error?

[RobDog888]

Probably needing to specify the storedproc property.

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

[brucevde]

To execute the Parameter Query use

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

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

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.

[RobDog888]

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

[Static]

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