Passing Parameters with ADO 2

[emeryp]

Hello Everyone,
Could someone please look at my code and tell me why the first parameter is receiving the error: Parameter object is improperly design #3708?

Private Sub Stored_Procedure(Value As String)
Dim EMEDcnn As ADODB.Connection
Dim cmdEMED As ADODB.Command
Dim prmEMEDinput As ADODB.Parameter
Dim prmEMEDoutput1 As ADODB.Parameter
Dim prmEMEDoutput2 As ADODB.Parameter
Dim prmEMEDoutput3 As ADODB.Parameter
Dim strCnn As String
Dim x As Integer
Dim prmVal As Integer

' Open connection.
Set EMEDcnn = New ADODB.Connection
strCnn = "Provider = Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=T:\Emedxlate.mdb"
EMEDcnn.Open strCnn
EMEDcnn.CursorLocation = adUseClient

' Open command object with one parameter.
Set cmdEMED = New ADODB.Command
cmdEMED.CommandType = adCmdStoredProc
cmdEMED.CommandText = "SQL_Name"

' Get parameter value and append parameter.
Set prmEMEDinput = cmdEMED.CreateParameter("QryName", adVarChar, adParamInput, , Value)
Set prmEMEDoutput1 = cmdEMED.CreateParameter("RtnName", adVarChar, adParamOutput, 255)
Set prmEMEDoutput2 = cmdEMED.CreateParameter("RtnNum", adInteger, adParamOutput, 25)
Set prmEMEDoutput3 = cmdEMED.CreateParameter("RtnDept", adVarChar, adParamOutput, 255)
cmdEMED.Parameters.Append prmEMEDinput
cmdEMED.Parameters.Append prmEMEDoutput1
cmdEMED.Parameters.Append prmEMEDoutput2
cmdEMED.Parameters.Append prmEMEDoutput3

' Create recordset by executing the command.
Set cmdEMED.ActiveConnection = EMEDcnn
cmdEMED.Execute

'Get Parameter values
With FrmDocTbl
For x = 0 To 2
prmVal = (x + 1)
.LblDoc(x).Caption = cmdEMED.Parameters(prmVal).Value
Next x
End With

EMEDcnn.Close
End Sub

 

[zemp]

Here is an example of ADO code that I use to pass a parameter to an access query and open the query with a recordset.

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With cmd
.ActiveConnection = Conn
.CommandText = "MyQuery"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ParamName", adInteger, adParamInput, 6)
.Parameters("ParamName&quot.Value = intID
End With
rs.Open cmd, , adOpenStatic, adLockOptimistic

Conn is an ADO connection object defined and openec elsewhere.

If more parameters are required just repeat the two lines of code. I don't know if it is required to keep the parameters in order. I do, but I started that by chance more to organize the parameters. But it makes sense.

Thanks and Good Luck!

zemp

 

[SemperFiDownUnda]

Note that as of, I think, AD0 2.1 you don't have to append on the parameters.
As soon as you supply a valid open connection to the command object, tell it the store proc name and set the commandtype to adCmdStoredProc the parameter collection will be populated for you. You appending on the parameters only creates duplicate entries in the collection at best and at worst puts in a parameter with the wrong information.

run the code

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With cmd
.ActiveConnection = Conn
.CommandText = "MyQuery"
.CommandType = adCmdStoredProc

and look at the .Parameters collection at this point...BINGO

 

[zemp]

I guess I just followed my old practices and never noticed that change. I'll have to look into it. Thanks SemperFiDownUnda!

FYI, I have yet to run into any problems with appending my parameters as above.

Thanks and Good Luck!

zemp

 

[CCLINT]

Er, I think you will need the catalog object when using the JET 4 provider to do it that way (calling up the parameters), otherwise, you will not see any parameters automatically.
I just cannot remember if this worked with JET 3.51 as emeryp is using.
emeryp may want to consider changing to using the JET 4 provider when working with a JET 3 mdb. You'll have fewer problems.

Other than that, in this case there is no problem creating and appending the parameter with the JET provider even if the parameters exist in the query.

BTW, if the provider does return the parameters, it creates alot overhead and traffic. For each parameter, a trip from the server is made.

Here is an example for JET:

Dim Comm1 As New ADODB.Command
Dim cat As New ADOX.Catalog
Dim rsADO As ADODB.Recordset

cat.ActiveConnection = conn
Set Comm1 = cat.Procedures("SomeQuery&quot.Command

'Let's look at the available parameters:

For i = 0 to Comm1.Parameters.Count - 1
Debug.Print Comm1.Parameters(i).Name

'You could also do this:
'Comm1(i).Name

Next i

'Continuing...

Comm1.Parameters("[Some Parameter?]&quot.Value = "Some Parameter Value"
Set rsADO = Comm1.Execute

 

[zemp]

CCLINT to the rescue ... again.

Seemd that what SemperFiDownUnda was refering to and I ran a small test on works for SQL Server. With Access (Jet) the same test failed.

I know that appending the parameter first works for both Access Jet and SQL Server.

Thanks and Good Luck!

zemp

 

[CCLINT]

But Zemp...your method is perfectly alright for JET.
I use this too....

The method I posted is only when you want to "see" the paramters, and even set them to an array of parameter objects - which may not be a bad idea (doing it this way) if the parameter names change, or change order, depending on the method used.