Retrieve data from SQL stored procedure in MS Excel
-
I have been searching this forum and the web to find the answer for this question, but no luck. I am not new the VBA but I am new to using ADO in VBA. Hopefully, someone in this forum has experience with this.
Basically, I want the result set from my stored procedure to be posted in Excel spreadsheet. The result set has more than 8 columns. This stored procedure has two parameter and both are datetime type (proc_Charge @Start, @End). These parameter will get when the input from the Excel spreadsheet after user selection.
Below is my sub procedure in the form:
Sub GetData()
Dim RangeA As Range
Dim RangeB As Range
Set RangeA = Worksheets("CS").Range("B3")
Set RangeB = Worksheets("CS").Range("E3")
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stParam As String
Dim svrConn As String
Dim i As Interger, j As Integer
Const SERVER As String = "MyPC"
svrConn = "Provider=SQLOLEDB;Server=" & SERVER & ";Database=Test;Integrated Security=SSPI"
Set conn = New ADODB.Connection
conn.Open svrConn
With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "proc_Charge"
End With
stParam = "Start"
Set param = cmd.CreateParameter(stParam, adDBDate, adParamInput)
cmd.Parameters.Append param
cmd.Parameters(stParam).Value = RangeA.Value
stParam = "End"
Set param = cmd.CreateParameter(stParam, adDBDate, adParamInput)
cmd.Parameters.Append param
cmd.Parameters(stParam).Value = RangeB.Value
Set rst = cmd.Execute
rst.Open cmd
If rst.EOF <> True Then
rst.MoveFirst
j=5
Do Until rst.EOF
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
Loop
End If
rst.Close
conn.Close
Set cmd = Nothing
Set conn = Nothing
Set rst = Nothing
End Sub
This procedure always stop with run-time error at "If rst.EOF <> True Then" statement saying the operation is not allowed when the object is closed. I don't see how the object is close.
Any help will be greatly appreciated. If there is anything wrong with the sub procedure, feel free to correct me too. Thank you so much.
-
KC
The first thing to check for is to make sure that in your stored procedure you place at the top (after your parameters) SET NOCOUNT ON. That may be the reason why the proc is not returning a recordset, even though it may work perfectly well in Query Analyser. Give that a shot and let's see what happens.
cje
-
Thank you for the reply, cje.
On my stored procedure, I do have the SET NOCOUNT at the top. Like you said, it ran perfectly find in QA. Below is how my stored proc looks like:
CREATE PROCEDURE dbo.proc_GetData
(
@Start datetime,
@End datetime
)
AS
BEGIN
SET NOCOUNT ON
...
...
END
SET NOCOUNT OFF
END
GO
Thank you again.
-
>>I don't see how the object is close.
The problem may be that you did this, paradoxical as it may seem:
Set rst = cmd.Execute rst.Open cmd ' <-- this line here may be the problem
After the Execute, it's like you're telling the rst to get ready to do something else.
Try code that looks like this, without the .Open:
Set rst = cmd.Execute If Not rst.BOF() Then ...
If that doesn't work, take your parameters-arranging and command-handling out of the question, like this (I'm not sure I have your params right, I'm just looking at your code quickly, but it will be something similar):
Set rst = .Execute("proc_Charge @Start='" & _ Trim(CSTR(RangeB.Value)) & "'")If Not rst.BOF() Then ...
Once you've verified what should be happening, using the simpler syntax above but still using the sproc, you can go back to using the command object if you like.
Regards,
>L<
-
I had to take out the following line to get it to work
rst.Open cmd
-
I have tried the above suggestion suggestions, none works for me.
Below is my new sub procedure code:
Sub GetData()
Dim RangeA As Range
Dim RangeB As Range
Set RangeA = Worksheets("CS").Range("B3")
Set RangeB = Worksheets("CS").Range("E3")
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim param1 As ADODB.Parameter, param2 As ADODB.Parameter
Dim stParam As String
Dim svrConn As String
Dim i As Interger, j As Integer
Const SERVER As String = "MyPC"
conn.Provider = "SQLOLEDB"
svrConn = "Server=" & SERVER & ";Database=Test;Integrated Security=SSPI"
conn.Open svrConn
cmd.ActiveConnection = conn
With cmd
.CommandType = adCmdStoredProc
.CommandText = "proc_Charge"
End With
Set param1 = cmd.CreateParameter("Start", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = Trim(CStr(RangeA.Value))
Set param2 = cmd.CreateParameter(stParam, adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = Trim(CStr(RangeB.Value))
Set rst = cmd.Execute
j=5
While Not rst.EOF
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
Wend
rst.Close
conn.Close
Set cmd = Nothing
Set conn = Nothing
Set rst = Nothing
End Sub
The procedure still crashed @ the "While Not rst.EOF" with error message "Operation is not allowed when the object is closed."
Any idea? I used the immediate watch window to troubleshoot the variable, the parameters returns the correct values. Thank you once again.
-
Did you try what I suggested, about removing the command object from the picture, while still executing your stored procedure?
Note: this is different from confirming that the parameters return the correct values.
>L<
-
on your parameters, you're using Cstr() - try Cdate() function instead, since your proc is looking for DateTime parameters
-
Wow!! just noticed that you append the parameters to the collection before you set the values for them - switch the 2 lines where you append and set the values.
-
Yes, I did try both of your suggestion, Lisa.
1) Try this way and it made no different, gave me the same error code.
Set rst = cmd.Execute
If Not rst.BOF() Then ...
2) Try this way and recevied a different error message. It complained the stored procedure expecting a @Start parameter.
Set rst = .Execute("proc_Charge @Start='" & _
Trim(CSTR(RangeB.Value)) & "'")
If Not rst.BOF() Then ...
As for your suggestion, cje. I did change the function to use CDate() and made no different. Still said the object was closed. Then I moved the statement for setting the value for the parameter before the append statement, it still gave me the same error. Now, it is like this:
Set param1 = cmd.CreateParameter("Start", adDBDate, adParamInput)
param1.Value = Trim(CDate(RangeA.Value))
cmd.Parameters.Append.param1
Any more suggestion?
-
no, no
set param1 = cmd.createparameter("@start", adDBDate, adParamInput, ,CDate(RangeA.value))
cmd.Parameters.Append param1 --(no period after append)
Do this for both parameters
-
>>It complained the stored procedure expecting a @Start parameter.
That's funny, I do it this way and it works fine. Are you sure you used the connection.Execute() method?
Remember, we're not talking to the command object here, and we're definitely not talking to a stored procedure-configured instance of a command object. We're just executing a line of SQL code against a connection. That line of code just happens to be executing a stored procedure, including its arguments, just as you might do it in the query analyzer.
If this does not turn out to be the problem... then what version of MDAC components is on the computer you're using? Here is some typical mdac-version checking code. (If this sub crashes for you, of course, you *know* you need to update MDAC <g>
Function CheckMDACVer() Dim MDACVer As MDACVer.Version Set MDACVer = CreateObject("MDACVer.Version") CheckMDACVer = (MDACVer.Major = 2 And _ MDACVer.Minor >= 8) Or _ MDACVer.Major > 3 If (Not CheckMDACVer) Then MsgBox "This application requires an updated version " _ & vbCr & _ "of Microsoft MDAC components to function." & vbCr & vbCr & _ "Please contact the developer (Lisa).", _ vbCritical, "Update your computer!" End If Set MDACVer = Nothing End Function>L<
-
sorry, cje. I made the change and it still crashes. Same error, "Operation is not allowed when the object is closed".
set param1 = cmd.createparameter("@Start", adDBDate, adParamInput, ,CDate(RangeA.value))
cmd.Parameters.Append param1
set param2 = cmd.createparameter("@Start", adDBDate, adParamInput, ,CDate(RangeA.value))
cmd.Parameters.Append param2
-
Lisa, I used the MS Component Checker to check my MDAC version. It reports that I have MDAC 2.8 SP1 on my PC.
-
OK, KC, what about the other question I asked you in that message? Are you sure you executed the command against the right object? I can't understand how you could get a complaint about a stored procedure when, as far as the MDAC component knows, it's not executing a stored procedure.
Maybe you should post the entire revision that (attempt to) follow my the suggestion to remove the command object from your code?
>L<
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply