CopyFromRecordSet not returning column headings from sql

pcchick

New Member
Joined
Dec 3, 2010
Messages
3
Hi,

I have written vba code to run a stored procedure and copy the result set by using the CopyResultSet from Excel VBA. I added a for loop to reference the column headers explicitly but when I check the value in the "watch" window it says there is no value in any of the iterations. Not sure how this is possible. The only thing that I can think of is that the stored proc creates a temporary table which is what excel references as the result set. The reason for the temporary table creation is because the stored proc creates a dynamic result so the table changes every time it is called. Any help would be greatly appreciated!! :) Thanks in advance.

Here is the actual code:

Private Sub cmdRunExport_Click()

'create a connection object
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

'create parameter variable
Dim prm As ADODB.Parameter

'create a recordset
Dim rsResponses As ADODB.Recordset
Set rsResponses = New ADODB.Recordset

'create a command to execute stored procedure
Dim cmdSQL As ADODB.Command
Set cmdSQL = New ADODB.Command

'provide the connection string
Dim strConn As String

'Use the SQL Server OLEDB Provider
strConn = "Provider=SQLOLEDB;"

'Connect to the Database on the xxx server
strConn = strConn & "DATA SOURCE=server;INITIAL CATALOG=dbname;"

'Use an integrated login.
strConn = strConn & " User ID=xxxxx;PASSWORD=xxxxx;"

'Open connection
conn.Open strConn
With cmdSQL
.ActiveConnection = conn
.CommandText = "zzKS_USP_StoredProc"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("caseid", adInteger, adParamInput)
.Parameters.Append prm
.Parameters("caseid").Value = FrmResponseExport.txtCaseid.Value
End With

Dim fields As String
Dim i As Integer

'loop to get column headings.

For i = 1 To rsResponses.fields.Count
Worksheets("Sheet1").Cells(i, 1).Value = rsResponses.fields(i).Name
Next i

'copy the result of the stored proc to the second row of the excel sheet
Set rsResponses = cmdSQL.Execute(CInt(FrmResponseExport.txtCaseid.Value))
Worksheets("Sheet1").Range("A2").CopyFromRecordset rsResponses
rsResponses.Close

conn.Close
Set rsResponses = Nothing
Set conn = Nothing
MsgBox "All done", vbOKOnly, "Finished"

End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi there,

Looks to me like you're trying to retrieve the field headers before populating the recordset. Try changing this

Code:
'loop to get column headings.
 
For i = 1 To rsResponses.fields.Count
Worksheets("Sheet1").Cells(i, 1).Value = rsResponses.fields(i).Name
Next i
 
'copy the result of the stored proc to the second row of the excel sheet
Set rsResponses = cmdSQL.Execute(CInt(FrmResponseExport.txtCaseid.Value))
Worksheets("Sheet1").Range("A2").CopyFromRecordset rsResponses
rsResponses.Close
to this

Code:
'copy the result of the stored proc to the second row of the excel sheet
Set rsResponses = cmdSQL.Execute(CInt(FrmResponseExport.txtCaseid.Value))
Worksheets("Sheet1").Range("A2").CopyFromRecordset rsResponses
 
'loop to get column headings.
 
For i = 1 To rsResponses.fields.Count
Worksheets("Sheet1").Cells(i, 1).Value = rsResponses.fields(i).Name
Next i
 
rsResponses.Close

Hope this helps and all the best.
 
Upvote 0
Also note that Fields is a zero-based collection so you should start your loop from 0 to recordset.Fields.Count -1. I am surprised you didn't get a Subscript out of range error with your existing code because of this.
 
Upvote 0
Thank you so much! I didn't think about that. I was thinking column headings first. I will try your solution and post back if it works.
 
Upvote 0
I am surprised you didn't get a Subscript out of range error with your existing code because of this.

If the recordset is created but not opened we get a Recordset.Fields.Count of 0 and For 1 to 0 on that particular For block - so it's quietly ignored (missing out on the chance to inflict another subscript out of range error on unsuspecting users).

pcchick - As noted, you can write the headers and copy the data or copy the data and write the headers, but both only after opening the recordset, which happens here:
Set rsResponses = cmdSQL.Execute(CInt(FrmResponseExport.txtCaseid.Value))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top