sql 2k stored procedure as source for list box

[Keith G Hicks]

Sorry, there just doesn't seem to be a good place to ask this. Figured a
busy NG would be best.
I need to set the source of a list box to the set of records returned from a
MS SQL Server 2k stored procedure.
There's no problem executing the SP, but how do I set the result set so that
it's displayed in the list box?
Here's my code:

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = sConnectionStirng

cmd.CommandText = "spCustomers"
cmd.CommandType = adCmdStoredProc

'Set the parameters
cmd.Parameters.Append cmd.CreateParameter("@StateID", adChar,
adParamInput, 2, Me!cboState)

cmd.Prepared = True

Dim rs As ADODB.Recordset
Set rs = cmd.Execute()

' ????? Me!lstCustomersFromACertainState.RowSource = cmd.Execute()


Thanks,

Keith

 

[Guest]

Why don't you create a procedure and use it as the record sourc
and the parameter depends on the value from another form

Edmun
MCP - Access & SQL Serve

----- Keith G Hicks wrote: ----

Sorry, there just doesn't seem to be a good place to ask this. Figured
busy NG would be best
I need to set the source of a list box to the set of records returned from
MS SQL Server 2k stored procedure
There's no problem executing the SP, but how do I set the result set so tha
it's displayed in the list box
Here's my code

Dim cmd As ADODB.Comman

Set cmd = New ADODB.Comman
cmd.ActiveConnection = sConnectionStirn

cmd.CommandText = "spCustomers
cmd.CommandType = adCmdStoredPro

'Set the parameter
cmd.Parameters.Append cmd.CreateParameter("@StateID", adChar
adParamInput, 2, Me!cboState

cmd.Prepared = Tru

Dim rs As ADODB.Recordse
Set rs = cmd.Execute(

' ????? Me!lstCustomersFromACertainState.RowSource = cmd.Execute(


Thanks

Keit

 

[Keith G Hicks]

What? Excuse me, but how does this help me? The parameter isn't the
problem. That works just fine. When I loop thru the dataset that's returned
in rs, all the records I expect are there. I just don't know how to connect
that data to the list box.

(by the way, I forgot to mention, this is not an ADP)

 

[Keith G Hicks]

I decided to set the source type of the list box to value and then loop thru
the rs in order to populate the list box. The code is easy and it works.
It's also pretty fast. I'm not dealing with a lot of records for this
listbox so I'm not too concerned. Same question as before though if anyone
has other ideas on how to do this.

Thanks

Keith

 

[Guest]

Keith: I am not as knowledgeable as you on these issues, but recently had a similar question. I am posting here Mary Chipman's response to my question about technique for using SQL stored procedures with an Access frontend

Base browse forms, combo boxes, and reports on store
procedures called through pass-through queries. Perform al
aggregations in stored procedures, and any formatting or presentatio
code in Access

In other words, her recommendation was to use pass-through queries in the situation you describe. Open the query grid, don't select any tables, right click, choose SQL specific queries, pass-through query and write your SQL statement.

I got her book and am finding it VERY helpful
HT
SM
HTH

 

[Keith G Hicks]

Thanks for the info. I use pass thru queries all the time. They're great.
Brain fart city! I must have been really tired when I was putting this one
form together. It's a piece of cake to call a stored procedure in a pass
thru and then have the query return the results! Thanks again for rocking
my brain. I feel pretty silly.

Keith

Keith: I am not as knowledgeable as you on these issues, but recently had

a similar question. I am posting here Mary Chipman's response to my question
about technique for using SQL stored procedures with an Access frontend:

Base browse forms, combo boxes, and reports on stored
procedures called through pass-through queries. Perform all
aggregations in stored procedures, and any formatting or presentation
code in Access.

In other words, her recommendation was to use pass-through queries in the

situation you describe. Open the query grid, don't select any tables, right
click, choose SQL specific queries, pass-through query and write your SQL
statement.