• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• ADO SQL connection, populating a list box with data, Running multiple SQL statements based on list

ADO SQL connection, populating a list box with data, Running multiple SQL statements based on list

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



Rate ADO SQL connection, populating a list box with data, Running multiple SQL statements based on list

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

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