dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: Problem returning a ADO recordset

Hybrid View

  1. #1
    Join Date
    Jul 2002
    Posts
    7

    Problem returning a ADO recordset

    I am having a problem returning a recordset when calling a SQL Server 2000 store procedure from VB6 using ADO. The following code example works fine if I comment out the insert statement, before the Select statement. But if the insert statement is in the store procedure I get a run-time error "3704, Operation is not allowed when the object is closed" when I check for end of file on the recordset "rst.EOF". I have no problem running the stored procedure in SQL Query Analyzer. Can anyone tell me what I am doing wrong? Thanks


    'SQL Server 2000 Stored Procedure
    --------------------------------------------------------------------------------------------------
    CREATE PROCEDURE spTest AS

    --Table Definition
    --CREATE TABLE testTable (FileNames Varchar(110))

    insert into testTable values('c:\test\test.txt')


    select * from testTable
    go


    VB Code
    ---------------------------------------------------------------------------------
    Private Sub Command1_Click()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim value As String

    Dim strConnectionString As String

    strConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;" + _
    "Initial Catalog=iiA;Data Source=" + "iiapro2"

    Set cnn = New ADODB.Connection

    cnn.ConnectionString = strConnectionString

    cnn.Open , "sa", "temp"

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn

    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sptest"

    Set rst = New ADODB.Recordset

    Set rst = cmd.Execute

    If rst.EOF = False Then
    value = rst.Fields(0)
    End If
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set cnn = Nothing
    End Sub

  2. #2
    Join Date
    Apr 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    1,792
    Check that the stored proc has permission to insert into the table first - if it does, try breaking your proc up into two - an insert and a select, then call each of them seperately.

  3. #3
    Join Date
    Apr 2000
    Location
    Southampton, UK
    Posts
    329
    There are two ways around this problem, (Three if you break it up into two seperate procedures).

    The reason you get the problem is because there are two statements in the stored procedure, each one returns a recordset object, the Insert statement returns a closed recordset and as this is the first statement to execute the default recordset object returned to your recordset in your application is this closed one. (Basically it equates to the message (n) rows updated that you would see when running the sql in query analyzer.) The following methods solve this:

    1) use SET NOCOUNT ON before your SQL statements in the Stored Procedure and SET NOCOUNT OFF after the statements, this suppresses the messages and therefore doesn't return the closed recordset:

    Code:
    CREATE PROCEDURE spTest AS
    
    --Table Definition
    --CREATE TABLE testTable (FileNames Varchar(110))
    
    SET NOCOUNT ON
    
    insert into testTable values('c:\test\test.txt')
    
    
    select * from testTable
    
    SET NOCOUNT OFF
    
    go
    2) Iterate the recordsets collection of the returned recordset until you find an open one:

    [code]
    Private Sub Command1_Click()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim value As String

    Dim strConnectionString As String

    strConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;" + _
    "Initial Catalog=iiA;Data Source=" + "iiapro2"

    Set cnn = New ADODB.Connection

    cnn.ConnectionString = strConnectionString

    cnn.Open , "sa", "temp"

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn

    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sptest"

    Set rst = New ADODB.Recordset

    Set rst = cmd.Execute

    'Iterate the recordsets collection to find an open one
    Do While rst.State = adStateClosed
    ' Get the next recordset in the collection
    Set rst = rst.NextRecordset
    Loop


    If rst.EOF = False Then
    value = rst.Fields(0)
    End If
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set cnn = Nothing
    End Sub
    TimCottee
    I know a little about a lot of things and a lot about very little.

    Brainbench MVP For Visual Basic
    http://www.brainbench.com

    MCP, MCSD, MCDBA, CPIM

  4. #4
    Join Date
    Jul 2002
    Posts
    7
    Thanks Tim, using the SET NOCOUNT worked great

  5. #5
    Join Date
    Apr 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    1,792
    I never knew about closed recordsets - Thanks Tim - that will solve some of my problems too!!!!

    stars for you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)