Problem using VB to retrieve ASP/XML using ADO recordset

Problem using VB to retrieve ASP/XML using ADO recordset

Post by Kris Truyen » Wed, 22 Jan 2003 22:36:20


I've read about ADO url recordset connections, but somehow can't get
this simple example working:

Dim strConnection               As String
Dim myRecordset                 As ADODB.Recordset

Set myRecordset = New ADODB.Recordset
myRecordset.Open "", , _
                  adOpenStatic, adLockBatchOptimistic

Set myRecordset = Nothing

The ASP referenced above is a simple XML file as follows:

<?xml version="1.0" encoding="ISO-8859-1"?>

When running the VB app, following error occurs on the myRecordset.Open
command line:

Run Time Error 3709 - The connection cannot be used to perform this
operation. It is either closed, invalid or incorrect.

I have tried opening an ADODB.connection first and inserting this one in
the line, but no success.

Please help!!!



1. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

I created a sample Stored Proc in SQLServer 2000 which Selects a field
from a "User" tabe and inserts it into a temporary table (#tmpUser):

Select UserID as UserID into #tmpUser  from [User]
Select * from #tmpUser

I test the procedure using SQL Query Analyzer and 15 records are show.

I then tried to excute the Proc from  VB which resulted in an error:
(Run Time error: 3704 'Operation is not allowed when the object is

If I remove the temp table in the stored procedure and do a simple
Slect statement (i.e. Select UserID from [User]), the VB code runs

Can anyone shed some light as to what the problem may be and how to
resolve this problem?

Thanks in advance.

'  ****** VB Code *******'

Private Sub Form_Load()

    Dim Cnn As Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command

    Const CnString =

    Set Cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Cnn.Open CnString

    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
    End With

    With cmd
        .ActiveConnection = Cnn
        .CommandType = adCmdStoredProc
        .CommandText = "TestProc"
    End With

    rs.Open cmd

    While Not rs.EOF
        Print rs(0)

    Set rs = Nothing
    Set cmd = Nothing
    Set Cnn = Nothing

End Sub

2. Not enough memory/Virtual memory error from large query

3. Newbie for External Procedures

4. Database Replication Available for FileMaker (SyncDeK 4.5)

5. SQL XML Stored Procedure using XML Explicit Returing XML to ASP Page

6. Round-robin select via Stored Procedure

7. Using FOR XML to retrieve data into VB

8. Retrieving XML from SQL Server 2000 using an ado connection object

9. Retrieving XML from ADO using XSL

10. retrieve datamodel in Access DB in ASP using ADO

11. Using an ADO stream to retrieve xml data from remote website