Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: sp not giving up the data

    Hi all - trying to do something simple and it's not working. It's more complicated but works like a charm in .NET, but I have to do this in Access.

    Want to pull data from a SQL sp into an ADO rs and use it locally.

    In SQL server, the sp executes nicely, returning 5 columns and thousands of rows.

    In Access, the rs gets the data structure (5 columns, field names, all that) but no data (recordcount = -1).

    What probably simple thing am I missing?

    Access:
    Code:
    Sub FetchSQL(sproc As String, rs As ADODB.Recordset)
    
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim par As ADODB.Parameter
        
        On Error GoTo FetchError
        
        ' Establish connection
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = ConnectionString
        cnn.Open
    
        ' Setup command
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandText = sproc
        cmd.CommandType = adCmdStoredProc
        cmd.Parameters.Refresh
        
       ' Open recordset
        Set rs = New ADODB.Recordset
        Set rs = cmd.Execute()
        
    FetchDone:
        Set cmd = Nothing
        Set cnn = Nothing
        Exit Sub
        
    FetchError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
        Resume FetchDone
    
    End Sub
    SQL Server side:
    Code:
    USE [Sandbox]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[sp_Ups_CombinedCRMList]
    	
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    
        SELECT dbo.Ups_CombinedAddressList.CustKey, 
               dbo.Ups_CombinedAddressList.AddressID, 
               dbo.Ups_CombinedAddressList.Name, 
               dbo.Ups_CombinedAddressList.Cust, 
               dbo.Ups_CombinedAddressList.ID
        FROM dbo.Ups_CombinedAddressList
        ORDER BY dbo.Ups_CombinedAddressList.Name;
    END
    Immediate window debugging:
    Code:
    ? rs.Fields.Count
     5
    ? rs.Fields(1).Name
    AddressID
    ? rs.RecordCount
    -1 
    rs.MoveLast  Generates an error
    Error is: "Rowset does not support fetching backwards"

    So, it's obviously a good connection and the sp is giving the rs the data structure, but no data.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Sorry for ultimately mis posting - the problem is with the ADO, not the SQL Server stored procedure.

    I don't know if this solution is right or not, but the problem has to do with how the ADO recordset is populated. No matter how I set the cursor and lock type, I can only go forward, one record at a time.

    So, to make it work (faster than the pass through's and now bundled into an app wide function) I employed a second rs to hand the data off to, so it truly becomes a "disconnected recordset".

    Now, pull from the DB once through a stored procedure and do the rest on the local machine. Later, move to .NET is more seamless - the Fetch section get's remodeled to use datasource objects to which the controls are tied and the rest of the code is nearly the same.

    Code:
    Option Compare Database
    Option Explicit
    
    Public rsDisconnected_BillToList As ADODB.Recordset
    Const ConnectionString As String = "Provider=SQLOLEDB;Data Source=APP-SRV2;Trusted Connection=Yes;Initial Catalog=Sandbox;Integrated Security=SSPI;"
    
    Function FetchSQL(sproc As String, rs As ADODB.Recordset) As Boolean
    
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim par As ADODB.Parameter
        Dim rsT As ADODB.Recordset
        Dim fld As ADODB.Field
        
        On Error GoTo FetchError
        
        ' Establish connection
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = ConnectionString
        cnn.Open
    
        ' Setup command
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandText = sproc
        cmd.CommandType = adCmdStoredProc
        cmd.Parameters.Refresh
        'cmd.Parameters(1).Value = 10
        
       ' Open recordset
        Set rs = New ADODB.Recordset
        rs.CursorLocation = adUseServer
        rs.LockType = adLockOptimistic
        rs.CursorType = adOpenForwardOnly
        Set rs = cmd.Execute()
        
        ' Move to local rs
        Set rsT = New ADODB.Recordset
        rsT.CursorLocation = adUseClient
        rsT.LockType = adLockOptimistic
        rsT.CursorType = adOpenDynamic
        For Each fld In rs.Fields
            rsT.Fields.Append fld.Name, fld.Type, fld.DefinedSize
        Next
        rsT.Open
        Do While Not (rs.EOF Or rs.BOF)
            rsT.AddNew
            For Each fld In rs.Fields
                rsT.Fields(fld.Name).Value = fld.Value
            Next
            rsT.Update
            rs.MoveNext
        Loop
        
        ' Swap the recordsets
        Set rs = Nothing
        Set rs = rsT
        
        FetchSQL = True
    
    FetchDone:
        Set cmd = Nothing
        Set cnn = Nothing
        Set rsT = Nothing
        Exit Function
        
    FetchError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
        FetchSQL = False
        Resume FetchDone
    
    End Function
    
    Private Sub Command16_Click()
    
        rsDisconnected_BillToList.Filter = "[Cust] = 'Prospect'"
        Set Me.Recordset = rsDisconnected_BillToList
        Me.Refresh
    End Sub
    
    Private Sub Command3_Click()
    
        Dim fld As ADODB.Field
        
    '    Use this tp populate a combo or list box
    '    If FetchSQL("sp_Ups_CombinedCRMList", rsDisconnected_BillToList) Then
    '        Set Me.cboCust.Recordset = rsDisconnected_BillToList
    '    End If
    
    '   Use this to populate a form - just make sure the form has controls
    '   with the same name as the fields in the recordset
        If FetchSQL("sp_Ups_CombinedCRMList", rsDisconnected_BillToList) Then
            Set Me.Recordset = rsDisconnected_BillToList
            Me.Refresh
            For Each fld In rsDisconnected_BillToList.Fields
                On Error Resume Next
                Me.Controls(fld.Name).ControlSource = fld.Name
                If Err Then Err.Clear
            Next
        End If
    End Sub
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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