[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Recordset from SQL Stored Procedure not opening

I have been trying for days now to open a recordet in excel 2007 from a stored procedure in SQL Server.  The error I receive is "operation not allow on closed object."  So I put some messagin in my code to determine if the connection or the recordset was the problem.  The connection state is open, however the recordset state is closed.  When I execute the code, it actually pauses on the open recordset as if it is retreiving the data, but the next line of code tests its state and it is always closed.  I validated permissions on the SP, and that should not be a problem.  Please look at this code and help me figure out what could possibly be wrong or what I need to test.  Thanks!
Sub GetBom_Click()
 
Dim strPart As String
Dim cn As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim strConn  As String
Dim qt As QueryTable
 
Set cn = New ADODB.Connection
 
Worksheets("M2MBom").Activate
strPart = Worksheets("M2MBOM").Range("B1")
 
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=MyServer; INITIAL CATALOG=Database01; USER ID=test; Password=test"
 
'This procedure requires one parameter (i also tested with another proc requiring no variables, does not work
 
strSQL = "Proc_Get_Item strPart"
 
cn.Open strConn
 
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
 
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
 
'Here the connection state is 1
MsgBox cn.State
 
'Here the rs state is 0
MsgBox rs.State
 
 
If rs.State = adStateOpen Then
 
If rs.BOF And rs.EOF Then
    MsgBox "This Part Number does not have a Bill of Materials."
    Exit Sub
End If
 
Range("A2:AZ500").Select
Selection.ClearContents
 
ActiveSheet.Range("A2").CopyFromRecordset rs
 
rs.Close
Set rs = Nothing
Else
MsgBox "Unable to open recordset.  Please notify adminsitrator."
End If
 
 
cn.Close
Set cn = Nothing
 
End Sub

Open in new window

0
mizveggie
Asked:
mizveggie
  • 2
1 Solution
 
spattewarCommented:
Hello mizveggie,

Hi try using the connection execute method instead of the recordset open method. Or also set the CommandType property of the recordset before using your method.

http://visualbasic.freetutes.com/learn-vb6-advanced/lesson9/p11.html

Let me know.


Regards,

spattewar
0
 
mizveggieAuthor Commented:
I changed by code to include the stored procedure type  - this works where I have no parameter.  It still looks like its executing, but the recordset does not remain open.

When I try to execute with a parameter, I get a syntax error.  The reference you referred me to is good, but does not have the sample of code I am looking for (includes parameter syntax).

cn.Open strConn
 
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
 
Set rs = cn.Execute("MP_Get_BOM1", , adCmdStoredProc)
 
 
MsgBox cn.State
MsgBox rs.State

Open in new window

0
 
mizveggieAuthor Commented:
Could this be related to the versions of software I am running?  I am running Excel 2007 and SQL Server 2000.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now