ADODB - SQL Query Sample
Posted on 5/5/2004 at 10:53:38 PM by KevinWilson
Option Explicit
Private WithEvents objConn As ADODB.Connection
'_________________________________________________________________________________________________________________________
Private Sub Form_Load()
On Error GoTo ErrorTrap
Dim objRS As ADODB.Recordset
' Setup the connection to the database
Set objConn = New ADODB.Connection
objConn.CommandTimeout = 120
objConn.ConnectionTimeout = 120
objConn.CursorLocation = adUseClient
objConn.ConnectionString = "Driver={SQL Server};SERVER=localhost;DATABASE=pubs;UID=;PWD=;"
objConn.Open
' Query the database for how many records are in the "titles" table
Set objRS = objConn.Execute("SELECT COUNT(*) AS TotalRecords FROM [titles]")
If Not objRS Is Nothing Then
If objRS.State <> adStateClosed Then
If objRS.BOF = False And objRS.EOF = False Then
MsgBox Trim(objRS("TotalRecords").Value & "") & " records found in the 'Titles' table"
End If
' Instead of closing the recordset and setting it to nothing, you could hang on to it for future use.
' If you don't close and destroy it... set the "ActiveConnection" property equal to NOTHING (this
' disconnects the recordset from the database)
objRS.Close
End If
End If
Set objRS = Nothing
' Query the database for the total sales
Set objRS = objConn.Execute("SELECT SUM([ytd_sales]) AS TotalSales FROM [titles]")
If Not objRS Is Nothing Then
If objRS.State <> adStateClosed Then
If objRS.BOF = False And objRS.EOF = False Then
MsgBox Trim(objRS("TotalSales").Value & "") & " total sales for all books in the 'Titles' table"
End If
objRS.Close
End If
End If
Set objRS = Nothing
Exit Sub
ErrorTrap:
MsgBox "The following error occured:" & vbCrLf & vbCrLf & _
"Error Number = " & CStr(Err.Number) & vbCrLf & _
"Error Source = " & Err.Source & vbCrLf & _
"Error Description = " & Err.Description, vbOKOnly Or vbExclamation, " Error"
Err.Clear
End Sub
'_________________________________________________________________________________________________________________________
Private Sub Form_Unload(Cancel As Integer)
If Not objConn Is Nothing Then
If objConn.State <> adStateClosed Then objConn.Close
Set objConn = Nothing
End If
End Sub
'_________________________________________________________________________________________________________________________
Replies:
There have been no replies.
You must register before you can post on this board. You can register here.
Post a reply:
Click Here to view the "old"messageboard
MAIN | DOWNLOADS | SAMPLE CODE | STEP BY STEP | DISCUSSION BOARD | LINKS | AUTHOR
