Solved

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

Posted on 2006-06-11
10
349 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now