Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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
0
lin100
Asked:
lin100
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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."

   
0
 
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
0
 
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

??
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
0
 
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 "

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

0
 
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
0
 
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 ?
0
 
NetminderCommented:
Closed, 50 points refunded.
Netminder
Site Admin
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now