Solved

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

Posted on 2006-06-11
10
355 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:lin100
  • 3
  • 3
  • 2
  • +1
10 Comments
 

Author Comment

by:lin100
ID: 16881913
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."

   
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16881920
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
0
 
LVL 11

Expert Comment

by:pootle_flump
ID: 16882033
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

??
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 11

Expert Comment

by:pootle_flump
ID: 16882083
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
0
 

Author Comment

by:lin100
ID: 16882565
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 "

0
 
LVL 142

Expert Comment

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

0
 
LVL 11

Expert Comment

by:pootle_flump
ID: 16883702
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
0
 

Author Comment

by:lin100
ID: 16931332
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 ?
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17220463
Closed, 50 points refunded.
Netminder
Site Admin
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question