Retrieve data from SQL stored procedure in MS Excel
-
Well KC,there is one other thing to check - maybe you're not connecting to the DB like you think. In your code, immediately after the "conn.open svrConn" line, add these 2 lines:
dim iStatus as int
iStatus = Conn.Status
set a break point on the line after this code, and check the value of iStatus. If it is anything other than 1 then you are not connecting to the DB.
let us know the results.
cje
-
ok, cje. The istatus return a value of 1.
-
I also set a breakpoint right after the statement "Set rst = cmd.Execute". The rst.fields.count = 0 when I checked the count value for the recordset on the immediate window.
-
well, if you're not totally bummed out yet, look at this article i found
http://support.microsoft.com/kb/245374
it seems that there are issues with "adDBDate". Try replacing it with adVarchar????
keep the CDate function to convert the string to a proper date.
Give that a shot.
-
Yep, there are probably 100 issues with parameter types. That's one of the reasons why using conn.Execute rather than cmd.Execute is a good idea when you find you're having problems and can't figure them out... the implicit conversion of the string date in the executed string command will take over...
>L<
-
almost to that point...i made a change to my stored procedure and see if that any diff. Just to test until I figure out why it bombed. I removed the parameters in my stored proc so I just need to call the proc. I tested in QA and it works...but it doesn't work in Excel.
I will post my revision in a sec.
-
exec proc_Charge2 will return results in QA.
Below is my revision for the sub proc in Excel:
Sub Test()
Dim RangeA As Range
Dim RangeB As Range
Set RangeA = Worksheets("CS").Range("B3")
Set RangeB = Worksheets("CS").Range("G3")
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 stConn As String
Dim i As Integer, j As Integer
Const SERVER As String = "MyPC"
' Connect using OLE DB provider
conn.Provider = "sqloledb"
' Specify connection string on Open method
stConn = "Server=" & SERVER & ";Database=Septage;Trusted_Connection=yes"
conn.Open stConn
Dim iStatus As Integer
iStatus = conn.State
' Execute command and loop through recordset
Set rst = conn.Execute("proc_Charge2")
If Not rst.BOF() Then
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
End If
' Close recordset and connection
rst.Close
conn.Close
Set cmd = Nothing
Set conn = Nothing
Set rst = Nothing
End Sub
Lisa, is it what you want me to try? If so, it still bombed with "Operation is not alloed when the object is closed" @ the "If Not rst.BOF() Then".
-
Another version of code. This one is using command.
Sub Test()
Dim RangeA As Range
Dim RangeB As Range
Set RangeA = Worksheets("CS").Range("B3")
Set RangeB = Worksheets("CS").Range("G3")
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 stConn As String
Dim i As Integer, j As Integer
Const SERVER As String = "MyPc"
j = 5
' Connect using OLE DB provider
conn.Provider = "sqloledb"
' Specify connection string on Open method
stConn = "Server=" & SERVER & ";Database=Septage;Trusted_Connection=yes"
conn.Open stConn
Dim iStatus As Integer
iStatus = conn.State
' Set up a command object for the stored procedure
Set cmd.ActiveConnection = conn
cmd.CommandText = "proc_Charge2"
cmd.CommandType = adCmdStoredProc
' Execute command and loop through recordset
Set rst = cmd.Execute
If Not rst.BOF() Then
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
End If
' Close recordset and connection
rst.Close
conn.Close
Set cmd = Nothing
Set conn = Nothing
Set rst = Nothing
End Sub
Again, this sub bombed at the yellow statement with the same error message as the previous one.
-
I noticed that you changed the connection string - hope it still works.
Try the following code as is - i'm assuming that proc_Charge2 does not take any parameters:
Sub Test()
Dim conn As New ADODB.Connection
Dim cmd as new ADODB.Command
Dim rst As New ADODB.Recordset
Dim stConn As String
Dim i As Integer, j As Integer
Const SERVER As String = "MyPC"
' Connect using OLE DB provider
conn.Provider = "sqloledb"
' Specify connection string on Open method
stConn = "Server=" & SERVER & ";Database=Septage;Trusted_Connection=yes"
conn.Open stConn
Dim iStatus As Integer
iStatus = conn.State
if iStatus = 1 then
cmd.Activeconnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_Charge2"
' Execute command and loop through recordset
Set rst = cmd.Execute
If Not rst.BOF() Then
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
End If
' Close recordset and connection
rst.Close
end if
conn.Close
Set conn = Nothing
set cmd = nothing
Set rst = Nothing
End Sub
-
>>Lisa, is it what you want me to try? If so, it still bombed with "Operation is not alloed when the object is closed" @ the "If Not rst.BOF() Then".
No, KC, it is not. <s> You're using conn instead of command but you didn't do what I suggested, which is execute a command using the proc name in a string. Here is how I suggested it originally:
>>
Set rst = .Execute("proc_Charge @Start='" & _ Trim(CSTR(RangeB.Value)) & "'")' where the . was supposed to be in a with/end with, but
' was conn.Execute rather than cmd.Execute
If Not rst.BOF() Then ...
... where I am assuming that RangeB.Value is your date value. I am suggesting that you use your procedure in an execute statement directly passing your date argument just as you would do it in the query analyzer. (Notice the two single quote marks in the statement delimiting the value.)
IOW, the server now sees proc_Charge @Start = '1/5/2006'
as the executable statement. If you have more than one argument, add more information into the string:
Dim x as String
x = "proc_Charge @Start='" & _ Trim(CSTR(RangeB.Value)) & "', @End='" & _ Trim(CSTR(RangeB.Value)) & "' "Set rst = .Execute(x)
If this does not work, I suggest you change your .Execute to have a literal in it, or more than one if you have more than one argument to be passed):
SET rst = .Execute("proc_Charge @Start='1/5/2006'")
... as a test, and see if that works. I understand that you won't really use the literals, this test is important to diagnose.
>L<
-
ok, cje. I tried your suggestion but it didn't work. Crashed at the same spot, which is "If Not rst.BOF() then..." with the same error messge.
Lisa, I am going tried your suggestion next hopefully I got it right this time. I will post the full revision. Thank again.
-
ok, Lisa. The execute only allows these parameter:
Execute([RecordsAffected], [Paramaters], [Options as Long = -1)
Here is the revision for the 1st suggestion:
if iStatus = 1 then
With cmd
.Activeconnection = conn
.CommandType = adCmdCtoredProc
' Execute command and loop through recordset
Set rst = .Execute("proc_Charge2")
If Not rst.BOF() Then
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
End If
rst.close
End If
conn.Close
Set conn = Nothing
set cmd = nothing
Set rst = Nothing
Here is the revision for the 2nd suggestion:
Dim proc As String
proc = "proc_Charge2"
if iStatus = 1 then
With cmd
.Activeconnection = conn
.CommandType = adCmdCtoredProc
' Execute command and loop through recordset
Set rst = .Execute(proc)
If Not rst.BOF() Then
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
End If
rst.close
End If
conn.Close
Set conn = Nothing
set cmd = nothing
Set rst = Nothing
Both revision received the same error message, "syntax error or access violation" @ the yellow statement.
I cannot remove the ".CommandType = adCmdCtoredProc". If I do, I'll get the "command text was not set for the command object" message.
Did I do it right this time? Sorry, if I don't get it.
-
you left out the "end with" statment - that's your syntax error - it goes before the yellow line and the set rst line should read
set rst = .Execute "proc_Charge2", cmd
-
thank you, cje and lisa. I figured what the problem is.
It is the stored procedure.The way I figured it out is to create a simple stored proc that has a simple select statement which will return all data in a table.
I used the same sub proc in Excel to call that stored procedure and it returned all the results as in QA.
The original stored procedure is a little complicated. In it, I have to create temp table and stored the return result in it from a select statement with some manipulations. Then select the result from the temp table joining another table.
My question is does it have to do with the temp table used in the stored procedure? As far as I know, the temp table will disappear after exec the stored procedure. Could this explain the error I kept receiving which is "the operation is not allowed when the object is closed"? Thank you again.
-
>>I cannot remove the ".CommandType = adCmdCtoredProc
The reason you get the error if you remove that line is that you are not doing what I am asking you to do <g>.
I'm saying DON'T use the command object AT ALL. You are using the execute method of the WRONG OBJECT to follow the suggestion that I am giving you. There should be NO WITH cmd statement!
I am not sure what you mean by "first suggestion" and "second suggestion". What I am suggesting is this:
if iStatus = 1 then
Dim myvar as String String myvar = CStr(MyRangeB.value) ' you should check the date-worthiness of the ' value here I suppose, before continuing
Set rst = conn.Execute("proc_Charge @Start='" & myvar & "' ")' or if using a literal for testing, it would be: ' Set rst = conn.Execute("proc_Charge @Start='1\1\2006'") ' -- and please make sure that the string is in appropriate ' locale form for a date from SQL Server's POV, ' if there is any possibility of a problem thereIf Not rst.BOF() Then For i = 0 To rst.Fields.Count - 1 ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value Next j = j + 1 rst.MoveNext End If
rst.close End If
conn.Close Set conn = Nothing Set rst = Nothing
Do you see? You're still executing a stored proc, KC, but you're not using the command object, with attendant difficulties in handling date parameter types, to do it.
>L<
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply