by Jason van Domburgh (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Wed 17th September 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
ADO SQL connection, populating a list box with data, Running multiple SQL statements based on list box selection, then a Crystal report for
API Declarations
'also need Crystal reports control component
'need to have a list box on form called list1
'and a status bar called status
'and also an invisible label called reflbl
Public objCommand As New ADODB.Command
Public objRS As New ADODB.Recordset
VBcoders
Dim strConnection, Password, User, InitialC, Source
Source = "SERVER" 'our SQL server host
User = "sa" 'user name
Password = "" 'password if applicable
InitialC = "YourDB" 'Your production database
strConnection = "Provider=SQLOLEDB;Persist Security Info=False;Password=" & Password & ";User ID=" & User & ";Initial Catalog=" & InitialC & ";Data Source=" & Source
objCommand.ActiveConnection = strConnection
End Sub
Private Sub Form_Load()
'this routine populates the list box list1 with data on load of form
Status.SimpleText = "by [email protected]"
Dim Teller, Reference(500)
OptDB
objCommand.CommandText = "Select distinct Reference from YourTable"
objCommand.CommandTimeout = 600
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
While Not objRS.EOF
Reference(Teller) = objRS("Reference")
Teller = Teller + 1
objRS.MoveNext
Wend
objRS.Close
Set objCommand = Nothing
Set objRS = Nothing
Teller = Teller - 1
For Display = 0 To Teller
List1.AddItem Reference(Display)
Next
End Sub
Private Sub List1_DblClick()
'here's what happens when you double click an item in the list box
'all the status.simpletext changes are to update the status bar with progree of update
Status.SimpleText = "Perofming Audit, Please Wait..."
List1.Enabled = False
Me.Refresh
Dim ref As String
ref = List1.Text
RefLbl.Caption = ref
Dim sql As String
Dim sql2 As String
Dim sql3 As String
Dim sql4 As String
Dim sql5 As String
Dim sql6 As String
sql = "Enter your 1st SQL query in here"
sql2 = "Enter your 2nd SQL query in here"
sql3 = "Enter your 3rd SQL query in here"
sql4 = "Enter your 4th SQL query in here"
sql5 = "Enter your 5th SQL query in here"
sql6 = "Enter your 6th SQL query in here"
'remember that you can use the parameter 'ref' to set a condition based on that
'piece of data
Status.SimpleText = "Initializing, running 1st query..."
Me.Refresh
OptDB 'connects to SQL server
objCommand.CommandText = sql 'sets command to 1st sql text
objCommand.CommandTimeout = 120 'sets command timeout
objCommand.CommandType = adCmdText 'sets type of command
Set objRS = objCommand.Execute 'executes the command
Set objCommand = Nothing 'clean up
Set objRS = Nothing 'clean up
Status.SimpleText = "Running the 2nd query..."
Me.Refresh
OptDB 'same as above for next sql query
objCommand.CommandText = sql2
objCommand.CommandTimeout = 120
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
Set objRS = Nothing
Status.SimpleText = "Running the 2nd query..."
Me.Refresh
OptDB 'doing it again
objCommand.CommandText = sql3
objCommand.CommandTimeout = 120
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
Set objRS = Nothing
Status.SimpleText = "Running the 4th query..."
Me.Refresh
OptDB 'and again
objCommand.CommandText = sql4
objCommand.CommandTimeout = 120
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
Set objRS = Nothing
Status.SimpleText = "Running the 5th query..."
Me.Refresh
OptDB 'play it again Sam
objCommand.CommandText = sql5
objCommand.CommandTimeout = 120
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
Set objRS = Nothing
Status.SimpleText = "Running the 6th query..."
Me.Refresh
OptDB 'last one
objCommand.CommandText = sql6
objCommand.CommandTimeout = 120
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
Set objRS = Nothing
Status.SimpleText = "Process Complete, Generating Report..."
Me.Refresh
'this bit calls the Crystal report to display results, as this is the goal
Dim file As String
file = "c:\your_report_path\your_report.rpt"
CrystalReport1.ReportFileName = file
CrystalReport1.PrintReport
Status.SimpleText = "Process Complete."
List1.Enabled = True
End Sub
No comments have been posted about ADO SQL connection, populating a list box with data, Running multiple SQL statements based on list . Why not be the first to post a comment about ADO SQL connection, populating a list box with data, Running multiple SQL statements based on list .
