ADO and Stored Proc Help

[dunagh]

Hello. I have found some discussions on this site about how to execute a stored procedure. I have followed the examples and even had a DBA run a trace while I executed the SP. The follwoing code runs fine until after I issue the .Execute method and assign it to an ADODB.Recordset object; the recordset seems to be closed. My DBA verified that I am getting data returned and if I run the SP in a Passthrough Query, I get data. Can anyone help point me in the right direction or point out what I am doing wrong? This is in an Access 2010 database.

    Dim adoConnection As New ADODB.Connection
 
    Dim adoCommand As ADODB.Command
 
    Dim WorkstationsRST As ADODB.Recordset
 
    adoConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVERNAME;Database=DBNAME;Trusted_Connection=Yes;Integrated Secuirty=SSPI"
    adoConnection.ConnectionTimeout = 10
    adoConnection.Open 
 
    Set adoCommand = New ADODB.Command
 
    adoCommand.ActiveConnection = adoConnection
    adoCommand.CommandType = adCmdStoredProc
    adoCommand.CommandText = "usp_get_wrkstn_info"
    adoCommand.Parameters.Refresh
 
 
    'adoCommand.Parameters("@RACF_ID").value = "ALL" 
    adoCommand("@RACF_ID") = "ALL"
 
    Set WorkstationsRST = adoCommand.Execute()
 
    Me.lstAssignedWorkstations.Recordset = WorkstationsRST
    Me.lstAssignedWorkstations.Requery

Thanks,

 

[JHB]

What is "lstAssignedWorkstations"?
An ADODB.Recordset only return one record at a time, you can loop trought it, but you can't assign it to a list's or a form's recordset.
A list's or form's recordset is comming from a recordsource!

 

[dunagh]

Thanks for the reply JHB.

"lstAssignedWorkstations" is an Access ListBox. I am attempting to assign the record set to the list box as one would assign a DAO.Recordset to a ListBox.

I am new using ADO and am only using it because that is the only way I can get results from a Stored Procedure on our SQL Server. I have successfully used a standard SQL statement to get data back from the SQL server but when I try to call the stored procedure, I can't get any data. (I have played with the ADO.Recordset in the immediate window with a standard "SELECT * FROM <tableName>" and get records back but calling the stored procedure and playing with it yields no data as it reports the recordset is closed.(Hopefully that last bit made sense.)). I have attached a screenshot of the watch window for the workstationsRST object showing its after the execute command is called.

So at this point I would just be happy with getting the stored procedure to give me back data, or, if I am indeed getting back data, learning why I can't access it.

Continued help is much appreciated.

Thanks!

 

[JHB]

Have you seen the example from the link below?
http://msdn.microsoft.com/en-us/library/windows/desktop/ms676516(v=vs.85).aspx
I see they have another way for parameters.
Try follow the example change it to fit yours.
When you get data back, then you can link it to the list box, but again - not setting the recordset - A list's recordset is coming from a recordsource!

 

[dunagh]

So I copied that example into my Access frontend, changing the appropriate attributes and I still get the "Operation is not allowed when the object is closed." message. Perhaps there is somehting with the Stored Procedure on the server side? Feeling really frustrated with Access (or myself).

Private Function GetNewConnection() As ADODB.Connection
    Dim oCn As New ADODB.Connection
    Dim sCnStr As String
 
    sCnStr = "Provider='SQLOLEDB';Data Source='SERVERNAME';" & _
             "Integrated Security='SSPI';Initial Catalog='DB';"
    oCn.Open sCnStr
 
    If oCn.State = adStateOpen Then
        Set GetNewConnection = oCn
    End If
 
End Function
 
 
Private Sub populateAssignedWSList(WorkstationStatus As String, Optional TADUser As String)
    Stop
    'Example From Web
    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objParm1 As New ADODB.Parameter
    Dim objRs As New ADODB.Recordset
 
    ' Set CommandText equal to the stored procedure name.
    objCmd.CommandText = "usp_get_wrkstn_info"
    objCmd.CommandType = adCmdStoredProc
 
    ' Connect to the data source.
    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn
 
    ' Automatically fill in parameter info from stored procedure.
    objCmd.Parameters.Refresh
 
    ' Set the param value.
    objCmd(1) = "ALL"
 
    ' Execute once and display...
    Set objRs = objCmd.Execute
 
    Debug.Print objParm1.value
    Do While Not objRs.EOF
        Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
                    objRs(2) & vbTab & objRs(3)
        objRs.MoveNext
    Loop
End Sub

 

[Galaxiom]

What is "lstAssignedWorkstations"?
An ADODB.Recordset only return one record at a time, you can loop trought it, but you can't assign it to a list's or a form's recordset.
A list's or form's recordset is comming from a recordsource!

No so. An ADO recordset can be assigned as the Recordset property of a Listbox or Combo.

They are actually very good at this job because unlike using a RowSource query the recordset can be Filtered and Sorted without requerying.

 

[JHB]

... and I still get the "Operation is not allowed when the object is closed." message. ...

Do you check if the connection gets open?

    If oCn.State = adStateOpen Then 
        Set GetNewConnection = oCn
        Msgbox("Open con")
    End If

 

[JHB]

No so. An ADO recordset can be assigned as the Recordset property of a Listbox or Combo.

They are actually very good at this job because unlike using a RowSource query the recordset can be Filtered and Sorted without requerying.

Ok thanks - I'll keep that in mind.

 

[dunagh]

Do you check if the connection gets open?

    If oCn.State = adStateOpen Then 
        Set GetNewConnection = oCn
        Msgbox("Open con")
    End If

Yes, this is in the example code that was on the Microsoft site that I added to my form.

Using the same connection parameters, I can issue a "SELECT * FROM <table>" query without issue.

 

[JHB]

Ok - can't you see in the log file on the server what error is raised, when you run the code?
If you not get it - I'll try a similar code tomorrow, on an old computer, (which has MS-SQL server installed).

 

[JHB]

I have made a test now with the code from the link I posted including the stored procedure, and everything runs ok.
I've some doubts to your connection string, then I see you've Data Source='SERVERNAME' and this was the same you have in the post #1, without the quotes.
I've attached a picture how you can find what to put into the Data Source if you have [FONT=&quot]Microsoft SQL Server Management Studio[/FONT].

My connectionstring is shown below, my servername is 'JOERNSDELL\SQLEXPRESS' and my database name is 'CheckConnection'.

sCnStr = "Provider='SQLOLEDB';Data Source='JOERNSDELL\SQLEXPRESS';" & _
"Integrated Security='SSPI';Initial Catalog='CheckConnection';"

 

[JHB]

Did you get it to work?

 

[dunagh]

Hi JHB.

No. I haven't gotten it to work yet and have been upable to put my full attention to it as I have needed to put out other fires and provide other functionality for my users. I will talk with my DBA who has full access to the SQL server to reconfirm that there are no errors on his end. I would do this myself but they don't give me that much power. :-( I will also try to execute the SP on our production server to see if maybe there is something with the server that my be causing it to not work.

As far as that connection string, I have used that string successfully to perform these commands:

Dim adoConnection As New ADODB.Connection
 
    Dim workstationsRST As ADODB.Recordset
 
 
    adoConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=<SERVERNAME>;Database=<DB>;Trusted_Connection=Yes;Integrated Secuirty=SSPI"
    adoConnection.ConnectionTimeout = 10
    adoConnection.Open
 
    WorkstationsRST.ActiveConnection = adoConnection
    WorkstationsRST.Open "SELECT * FROM BLD_REQ", , adOpenStatic, adLockReadOnly, adCmdText
    'Navagate records from the table...
 
    WorkstationsRST.Open ("SELECT @@VERSION")
    Dim version As String
    version = WorkstationsRST(0)
    MsgBox "Server Version : " & version

 

[JHB]

As far as that connection string, I have used that string successfully to perform these commands:

Dim adoConnection As New ADODB.Connection
 
    Dim workstationsRST As ADODB.Recordset
 
 
    adoConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=<SERVERNAME>;Database=<DB>;Trusted_Connection=Yes;Integrated Secuirty=SSPI"
    adoConnection.ConnectionTimeout = 10
    adoConnection.Open
 
    WorkstationsRST.ActiveConnection = adoConnection
    WorkstationsRST.Open "SELECT * FROM BLD_REQ", , adOpenStatic, adLockReadOnly, adCmdText
    'Navagate records from the table...
 
    WorkstationsRST.Open ("SELECT @@VERSION")
    Dim version As String
    version = WorkstationsRST(0)
    MsgBox "Server Version : " & version

If the above code is ok, have you tried to insert the other code in that module?
Ofcause with necessary change, (I tip 99,9% it is the connection string that make trouble in the other code, then I see here it is different again now it is with <> around the server name and the database name.). When you talk to your I DBA ask him for the servername and the database name.

 

[dunagh]

I put <ServerName> in as a place holder for the server name, as that is standard for designating that that symbol should be replaced with the actual value and I wanted it to be clear that it's just a place holder when putting code online. I don't put in the actual server name in the examples here as I work for the state and don't want to put the server names out there for the whole world to see, and I am not sure what the policy is about disclosing that type of information. In my actual code, I do have the correct server name. The same is true for the database name.

I have tried the code for calling the stored procedure but I still have the issue of the object being closed.

 

[Galaxiom]

This line should use the Set keyword as it is assigning an object.

Me.lstAssignedWorkstations.Recordset = WorkstationsRST

This line will reload the recordset based on the RowSource. It shouldn't be there. I expect that is the actual problem.

Me.lstAssignedWorkstations.Requery

The other things I would suggest is using a Client side cursor and disconnecting the recordset by setting its ActiveConnection to Nothing. There is no need to have the listbox recordset connected since it isn't updatable.