Solved

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

Posted on 2006-06-11
10
352 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL joining table to itself to combine rows 5 31
MS SQL Inner Join - Multiple Join Parameters 2 18
TSQL DateADD update Question 4 25
Extract string portion 2 10
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

914 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

15 Experts available now in Live!

Get 1:1 Help Now