• 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 a Main() that we'll add later:

      Private Sub QueryButton_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs)Handles QueryButton.Click
             'database query code will go here...
     End Sub


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

• As demonstrated in the previous lab, when we have a database app, we generally do not want to connect to the database every time we do a query. Instead, we want to connect once at the start of the program, do all our queries, and disconnect on the way out. In our Console app we did this connecting and disconnecting in Main().
  

• Declare the ConnectString and the Connection at the top the QueryForm class:

Private ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\temp\foo.mdb"

Private Connection as New System.Data.OleDb.OleDbConnection(ConnectString)

• Add the database connection code to the QueryForm_Load()method:

   Try
	Connection.Open()
   Catch f As System.Exception
	MsgBox("Problem opening database..." + f.Message)
	System.Environment.Exit(1)
   End Try

• Write the code for the QueryForm_FormClosing() method:
  

    Private sub QueryForm_FormClosing(ByVal sender as Object, Byval e as System.Windows.Forms.FormClosingEventArgs) _
                                      handles Me.FormClosing
    	Try
		Connection.Close()
    	Catch g As System.Exception
		MsgBox("Problem closing database..." + g.Message)
		System.Environment.Exit(1)
    	End Try
    End Sub

• Test the application. As the form appears on the screen, its QueryForm_Load() method is called, which tries to connect to the database. Test both successful and unsuccessful connections. Clicking the Query button should do nothing yet. Clicking the Close button calls the Close() method on the form, which in turn calls the QueryForm_FormClosing() method which closes the database connection.
  
  
• Now add the database query functionality to the QueryButton_Click() event handler (check the SQL query in the code below and make sure that your database supports it:

	Private Sub QueryButton_Click(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

		SQLCommand = New System.Data.OleDb.OleDbCommand("select name from foo", Connection)

		Try
			SQLDataReader = SQLCommand.ExecuteReader()
		Catch h As Exception
			MsgBox("Problem in ExecuteReader()..." + h.Message)
			Connection.Close()
			System.Environment.Exit(1)		
		End Try

		Try
			While SQLDataReader.Read()
				result_string = SQLDataReader.GetString(0)
				ResultList.Items.Add(result_string)
			End while
			SQLDataReader.Close()		
		Catch l As Exception
			MsgBox("Problems reading data from the SQlDataReader..." + l.Message)
			Connection.Close()
			System.Environment.Exit(1)		
		End Try
	End Sub
    

• Test the application and verify that the records retrieved from the database are written to the textbox.
• 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):
    
    
  • 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 QueryButton_Click(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"
  		
  		Try
  			myAdapter = New OleDb.OleDbDataAdapter(QueryString, Connection) 
  		Catch g As Exception
  			MsgBox("Problem creating dataAdapter..." + g.Message)
  			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.