CopyFromRecordSet not returning column headings from sql

[pcchick]

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

 

[AnAnalyst]

Hi there,

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

'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

'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.

 

[Richard Schollar]

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.

 

[pcchick]

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.

 

[xenou]

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))

 

[pcchick]

Ok, it worked great! Thank you all for your help.