STORED PROCEDURE IN DATAENVIRONMENT!

[ChrisM]

Does anyone know how to run a stored procedure a data environment connection so it's results can be displayed in a combo box.
I thought this problem would be quite easy as I can create and run stored procedures using ado code. Can anyone help me?

Thanks in advance.

[jbooth]

I think this may be what you are needing, This example uses a stored procedure that has multiple select statements.
MicroDataEnv is the Data Environment and dbo_sp_clifcombo is the Stored procedure. Let me know if this helps.

Dim WithEvents adoAllComboRS as ADODB.Recordset

Public Sub ClifPopAllCombo()
' Populate the State Drop Down
' Call Stored Procedure
MicroDataEnv.dbo_sp_clifcombo

Set adoAllComboRS = New ADODB.Recordset
Set adoAllComboRS =
MicroDataEnv.rsdbo_sp_clifcombo

Do While adoAllComboRS.EOF = False
cboState.AddItem RTrim(adoAllComboRS.Fields("StateProvID"))
adoAllComboRS.MoveNext
Loop

' Populate the Ship From Drop Down
Set adoAllComboRS = adoAllComboRS.NextRecordset()

Do While adoAllComboRS.EOF = False
cboShipFrom.AddItem Trim(adoAllComboRS.Fields("shpfrmnme"))
adoAllComboRS.MoveNext
Loop

[snow]

The problem that you may encounter is that i f you have to passe parameter to your stored procedure, you will not find a lot of documentation (i didn't !). The best way for me was to create a ADO command object and call my procedure. Here's an example:

dim cmd as New ADODB.Command
dim rs as New ADODB.RecordSet

DEOSDE.conn.Open 'Open the Dataenvironment connection
Set cmd.ActiveConnection = DEOSDE.conn
' Now my command is connect to my Dataenvironment who have a connection to my database

'Now the procedure call

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_GetClientInfo"
cmd.Parameters.Refresh ' go to SQL and retrieve what parameter as to be pass
cmd.Parameters(1) = 6000 ' Act as a Collection. I pass the first parameter 6000 to my procedure

Set rs = cmd.Execute ' execute...


Hope this will help

Patrice Merineau
Groupe Conseil OSI, Montreal

[ChrisM]

Thanks for the replies but I have managed to achieve the task.
This is the method I have found:

DataEnvironment.NameofStoredProcedure Param1, param2

The method above will action the stored procedure in the data environment and populate it's corresponing recordset rsNameofStoredProc) with the results.