• We can now combine the GUI components of a program (see VBGUI.htm) with the DB (SQL) communications parts of a program (see VBDB.htm).
  
  
• Let’s build a simple interface that consists of a window with a Query button, a Close button and an empty list box. When the user clicks the Query button, the database is queried. The results get displayed in the list box as shown below:

• Using the techniques you learned in VBGUI.htm set up a new Windows Application project.
  
  
• Set up a basic Form interface with a Query button, a Close button and a ListBox.
  
  
• Change the Text and Name attributes of these classes as follows:
  
  
• Form: Name: QueryForm, Text: Sample Query
  
  
• Query Button: Name: QueryButton, Text: Query
  
  
• Close Button: Name: CloseButton, Text: Close
  
  
• List Box: Name: ResultList
  
  
• Write skeleton event handlers for the QueryButton class and the CloseButton class. Add a comment where the code for querying the database will go (we’ll connect and disconnect from Main():

      Private Sub QueryButton_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs)Handles QueryButton.Click

	'code for the query and listing the results in ResultBox goes here

     End Sub


     Private Sub CloseButton_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs)Handles CloseButton.Click

     End Sub

• Now,  add the Main() by creating a new class. In the Solution Explorer right click on the Windows Application --> Add --> New Item --> Class. Name is DBMain.vb. Include the database connect and disconnect calls as demonstrated in VBDB.htm (make sure you have a database to connect; the example below assumes you have a c:\temp\foo.mdb database):

  Option Strict on
  Option Explicit on
  
  Public Class DBMain
  	Public Shared MyForm As QueryForm
  	Public Shared Connection As System.Data.OleDb.OleDbConnection
  
  	Public Shared Sub Main()
  		Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  		"Data Source=c:\temp\foo.mdb"
  		
  		Connection = New System.Data.OleDb.OleDbConnection(ConnectString)
  		Try
  			Connection.Open()
  		Catch e As Exception
  			MsgBox("Problem opening database..." + e.Message)
  			System.Environment.Exit(1)
  		End Try
  
  	End Sub
  
  	Public Shared Sub Close()
  		Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  		"Data Source=c:\temp\foo.mdb"
  		
  		Connection = New System.Data.OleDb.OleDbConnection(ConnectString)
  		Try
  			Connection.Close()
  		Catch e As Exception
  			MsgBox("Problem closing database..." + e.Message)
  			System.Environment.Exit(1)
  		End Try
  		
  		'if all's well, exit with status 0
  		System.Environment.Exit(0)
  	End Sub
  
  End Class

  
  
  • Change the skeleton code for the QueryButton class so that there is a call to the Main() method.
    

        
    Private Sub QueryButtonClick(ByVal sender As System.Object, _
    	ByVal e As System.EventArgs)Handles QueryButton.Click 
    	
    		DBMain.Main()
    
        	'code for the query and listing the results in ResultBox goes here
    End Sub
    

  • Change the skeleton code for the CloseButton class so that there is a call to the Main() method.
    

        
    Private Sub CloseButtonClick(ByVal sender As System.Object, _
    	ByVal e As System.EventArgs)Handles CloseButton.Click
    		
    	DBMain.Close()
    End Sub
     

  • Test the application. Clicking the Query button should connect to the database; clicking the Close button takes the QueryForm away after closing the database connection.
    
    
  • Now add the database query functionality to the QueryButtonClick() event handler (check the SQL query in the code below and make sure that either your database supports it or change the query such that your database supports it):

  
  	Private Sub QueryButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles QueryButton.Click
  		
  		Dim SQLCommand As System.Data.OleDb.OleDbCommand
  		Dim SQLDataReader As System.Data.OleDb.OleDbDataReader
  		Dim result_string As String
  
  		DBMain.Main()
  
  		SQLCommand = New System.Data.OleDb.OleDbCommand("select name from foo", DBMain.Connection)
  
  		Try
  			SQLDataReader = SQLCommand.ExecuteReader()
  		Catch f As Exception
  			MsgBox("Problem in ExecuteReader..." + f.Message)
  			DBMain.Connection.Close()
  			System.Environment.Exit(1)		
  		End Try
  
  		Try
  			Do While SQLDataReader.Read()
  				result_string = SQLDataReader.GetString(0)
  				ResultList.Items.Add(result_string)
  			Loop
  			SQLDataReader.Close()		
  		Catch g As Exception
  			
  			MsgBox("Problems reading data..." + g.Message)
  			DBMain.Connection.Close()
  			System.Environment.Exit(1)		
  		End Try
  	End Sub
  
      

  • To top this off, add a Clear button to the QueryForm. Then write an event handler for that button clearing the result list.

  
        Private Sub ClearButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearButton.Click
  		
  	ResultList.Items.Clear()
  
       End Sub
  
      

  • Notice how the interface now clears out the result of the previous query when the Clear button is clicked and how new queries get issued when the Query button  receives a click event.
    
    
  • You now know pretty much everything you need to write a GUI-based VB 2005 application that interfaces with any SQL database (provided there is a VB 2005/OLE driver available for that database).
    
    
  • Also note that the above techniques, although slightly different from language to language and from database to database, can be used in most other programming languages and database platforms.
    
    
  • However, VB 2005 also offers a few idiosyncratic ways of displaying database query results. For instance, it offers the DataGrid class . This class will automatically display multi-column, multi-row query results in a table format as shown below (the data were chosen from a little demonstration database that illustrates the BLT system).

    

    • To use a DataGridView, modify the application above such that rather than including a ListBox on the Form window, you include a DataGridView object (see picture above. Note that when you create the object using your VB 2005 ToolBox, the DataGridView will be empty; in fact, it won't even show a grid):
      
      
    • In your DBMain class, keep the code for opening and closing  the database connection and form.
      
      
    • Delete the code for the clear button click event and delete the clear button from the form.
      
      
    • Change the name of the Data Grid to DataGrid.
      
      
    • Now modify the code for the Query button to do the query and load the results in the DataGridView using a so-called DataAdapter and a DataTable. The DataAdapter creates the DataTable which is then pointed to (referenced) by the DataGrid:

    
    	Private Sub QueryButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles QueryButton.Click
    		
    		Dim dt As DataTable
    		Dim myAdapter As OleDb.OleDbDataAdapter
    		Dim QueryString As String = "select name from foo"
    		
    		DBMain.Main()
    
    		Try
    			myAdapter = New OleDb.OleDbDataAdapter(QueryString, DBMain.Connection) 
    		Catch g As Exception
    			MsgBox("Problem creating dataAdapter..." + g.Message)
    			DBMain.Connection.Close()
    			System.Environment.Exit(1)
    		End Try
    
    		dt = New DataTable()
    		
    		myAdapter.Fill(dt)
    		myAdapter.Dispose()
    		DataGrid.DataSource = dt
    		Refresh()
    	End Sub

    • Build and run.