Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sql stored procedure results in a recordset questions
When I run a stored procedure out of dataimport...new database query by
pasting in {call procname} in the sql code box, the results returned include the field names from the select * procedure . When I assign the results of the stored procedure to a recordset out of VBA I encounter several issues: The code I use to assign the results to the recordset is as follows: Set runspcmd = New ADODB.Command runspcmd.CommandText = procname runspcmd.CommandType = adCmdStoredProc ' execute the command. Set runspcmd.ActiveConnection = cnnct Set recset = runspcmd.Execute 1) The field/column names from the procedure results are not included in recset. Anyway I can get the names as well? 2) recordcount, and certain of the Move commands don;t work --(presumably because of the implicit cursor type?). Is there any way I can easily count how may records are in the recordset? 3) I know how to place the recordset on a worksheet using copyfromrecordset. Can copyfromrecordset be used to assign the recordset to an array? (I am trying to populate a multi-column list box with the data in recset) Any other suggestions on how to easily do this? Thanks for any help anyone can provide. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sql stored procedure results in a recordset questions
I almost answered my own questions 2 and 3.
Using recset.getrow, I can assign my results to an array and then use ubound to get the number of records. However,getrow seems to transpose the data in the resultset (or maybe the resultset transposed the results of the stored procedure) E.g. the stored procedure results has 3 rows and 2 columns. aa=recset.getrows has 2 rows and 3 columns. Any way I can get aa to mirror the result set without having to scroll though row by row, column by column? "in-over-his-head-bill" wrote: When I run a stored procedure out of dataimport...new database query by pasting in {call procname} in the sql code box, the results returned include the field names from the select * procedure . When I assign the results of the stored procedure to a recordset out of VBA I encounter several issues: The code I use to assign the results to the recordset is as follows: Set runspcmd = New ADODB.Command runspcmd.CommandText = procname runspcmd.CommandType = adCmdStoredProc ' execute the command. Set runspcmd.ActiveConnection = cnnct Set recset = runspcmd.Execute 1) The field/column names from the procedure results are not included in recset. Anyway I can get the names as well? 2) recordcount, and certain of the Move commands don;t work --(presumably because of the implicit cursor type?). Is there any way I can easily count how may records are in the recordset? 3) I know how to place the recordset on a worksheet using copyfromrecordset. Can copyfromrecordset be used to assign the recordset to an array? (I am trying to populate a multi-column list box with the data in recset) Any other suggestions on how to easily do this? Thanks for any help anyone can provide. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sql stored procedure results in a recordset questions
Alright, the coffee finally kicked in and through rs().name I figured out how
to get the headers. And through lst.column() , I got the transpose problem fixed. Down to one question that I will post in a separate thread. How do you populate column headers in a list box from a recordset? "in-over-his-head-bill" wrote: I almost answered my own questions 2 and 3. Using recset.getrow, I can assign my results to an array and then use ubound to get the number of records. However,getrow seems to transpose the data in the resultset (or maybe the resultset transposed the results of the stored procedure) E.g. the stored procedure results has 3 rows and 2 columns. aa=recset.getrows has 2 rows and 3 columns. Any way I can get aa to mirror the result set without having to scroll though row by row, column by column? "in-over-his-head-bill" wrote: When I run a stored procedure out of dataimport...new database query by pasting in {call procname} in the sql code box, the results returned include the field names from the select * procedure . When I assign the results of the stored procedure to a recordset out of VBA I encounter several issues: The code I use to assign the results to the recordset is as follows: Set runspcmd = New ADODB.Command runspcmd.CommandText = procname runspcmd.CommandType = adCmdStoredProc ' execute the command. Set runspcmd.ActiveConnection = cnnct Set recset = runspcmd.Execute 1) The field/column names from the procedure results are not included in recset. Anyway I can get the names as well? 2) recordcount, and certain of the Move commands don;t work --(presumably because of the implicit cursor type?). Is there any way I can easily count how may records are in the recordset? 3) I know how to place the recordset on a worksheet using copyfromrecordset. Can copyfromrecordset be used to assign the recordset to an array? (I am trying to populate a multi-column list box with the data in recset) Any other suggestions on how to easily do this? Thanks for any help anyone can provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DAO recordset Call to SQL Stored Proc | Excel Programming | |||
ADO recordset problem with stored procedure | Excel Programming | |||
Passing an array or recordset to a stored procedure | Excel Programming | |||
Passing an array or recordset to a stored procedure | Excel Programming | |||
Passing an array or recordset to a stored procedure | Excel Programming |