How to print an item from a recordset using ADO abd SQL Server

Access 2002. SQL 2000 Server

The procedure below does run and gives no output message, but the statement below did not execute.
    MsgBox rst!ord_date
   
///////////////////////////////////////////

Private Sub ADOAsyncQuery_2()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim err As ADODB.Error
    Dim strConnect As String
   
    On Error GoTo Err_ADOAsyncQuery
   
    Set cnn = New ADODB.Connection
    strConnect = "Provider=sqloledb; Data Source=Win-2000-Server;" & _
                 "Initial Catalog=pubs; Integrated Security=SSPI"
    cnn.ConnectionString = strConnect
    cnn.Open
 
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = "SELECT * FROM Sales WHERE qty = 5"
    cmd.Execute , , adAsyncExecute

'''''''' Recordset ''''''''''''''''''
    Set cnn = CurrentProject.Connection
    rst.Open "SELECT * FROM Sales WHERE qty = 5", _
             cnn, adOpenDynamic, adLockOptimistic, adCmdTable
    rst.MoveFirst
    MsgBox rst!ord_date
    'Perform other tasks

    If cmd.State = adStateExecuting Then
       cmd.Cancel
    End If

Exit_ADOAsyncQuery:
    On Error Resume Next
    cnn.Close
    Exit Sub

Err_ADOAsyncQuery:
    For Each err In cnn.Errors
        Debug.Print err.Number, err.Description
    Next err
    Resume Exit_ADOAsyncQuery
End Sub
lin100Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lin100Author Commented:
Sorry, but I meant to say ERROR message, not
output message.
"The procedure below does run and gives no ERROR message, but the statement below did not execute."

   
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Private Sub ADOAsyncQuery_2()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim err As ADODB.Error
    Dim strConnect As String
   
    On Error GoTo Err_ADOAsyncQuery
   
    Set cnn = New ADODB.Connection
    strConnect = "Provider=sqloledb; Data Source=Win-2000-Server;" & _
                 "Initial Catalog=pubs; Integrated Security=SSPI"
    cnn.ConnectionString = strConnect
    cnn.Open
 
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = "SELECT * FROM Sales WHERE qty = 5"
    cmd.CommentType = adAsyncExecute
   
    set rst = new adodb.recordset
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open cmd

    'Perform other tasks
    ...


    while.State = adStateExecuting Then
       doevents
    wend

    if rst.eof and rst.bof then
       'no records
    else
       MsgBox rst!ord_date
   end if

Exit_ADOAsyncQuery:
    On Error Resume Next
    cnn.Close
    Exit Sub

Err_ADOAsyncQuery:
    For Each err In cnn.Errors
        Debug.Print err.Number, err.Description
    Next err
    Resume Exit_ADOAsyncQuery
End Sub
pootle_flumpCommented:
Gotta admit - I wouldn't trap the error quite like that so this is a hunch. Is it conceivable that there is an error but it is not contained in the connection error collection?

Err_ADOAsyncQuery:

Debug.Print err.Number, err.Description
   
    Resume Exit_ADOAsyncQuery
End Sub

??
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pootle_flumpCommented:
Ok - missed the adAsyncExecute. That might be the problem. However - if (for example) you got the field name wrong then the code would go to the error trap but the error would not be in the connection errors collection. e.g.:

/////////////////////////////////////////
Public Sub SQL_Errors()
On Error GoTo MyError

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim TheErr As ADODB.Error
   
    cnn.Open "Driver={SQL Server};Server=(Local);Database=MyDB;Trusted_Connection=yes;"
   
    strSQL = "SELECT * FROM MyTable"
   
    rst.Open strSQL, cnn
   
    Debug.Print rst!MyField
   
Exit_Sub:
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
       
    Exit Sub
   
MyError:
   
    Debug.Print cnn.Errors.Count
    Debug.Print err.Source
       
    For Each TheErr In cnn.Errors
        Debug.Print err.Number, err.Description
    Next TheErr
   
    Resume Exit_Sub

End Sub
/////////////////////////////////////////


Prints
 0
ADODB.Fields

if there is no field called MyField.
HTH
lin100Author Commented:
Hi angelIII . When I ran the procedure
ADOAsyncQuery_2()  I got an error message.
The yellow highlight is on CommentType

Error Message:
Method or data member not found.
cmd.CommentType = adAsyncExecute

//////////////////////////////////////

Hi pootle_flump. When I ran the procedure
SQL_Errors(), it went into an infinite loop and I had hit control-break.

The yellow higlight is at the code
    Next TheErr

The immediate window has this text
"ADODB.Recordset
 3704         Operation is not allowed when the object is closed.
 1 "

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sorry, this is a typo:
>cmd.CommentType = adAsyncExecute
should be:
cmd.CommandType = adAsyncExecute

pootle_flumpCommented:
Hi lin100

If AngelIIIs code does not help then easiest way to sort this is to follow the path of execution. If you don't know what I mean by this then please google VB debug and breakpoint

Set a breakpoint at the line
Set cnn = New ADODB.Connection

When the code breaks press F8. Each time you press F8 the highlighted line of code will execute and the next line to be executed will be highlighted. This way you can see exactly what execution path vb is following. Does the flow of execution go into the error trap? If so - which line immediately precedes it? You can also debug variables (e.g. ?err.number) in the immediate window

HTH
lin100Author Commented:
Is there a method to print a field in a table using the command function as in cmd.CommandText, or do I have to create a recordset ?
NetminderCommented:
Closed, 50 points refunded.
Netminder
Site Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.