• 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:

	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 a new class DB that we will use to hold the database connection as well as the code for establishing and closing the connection:
  

Public Class DB

	Public Shared Connection As System.Data.OleDb.OleDbConnection

	Public Shared Sub DBOpen()
	  'Access 2003 
          'Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
	  '"Data Source=c:\temp\foo.accdb"

          'Access 2007
          Dim ConnectString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
          "Data Source=c:\temp\foo.accdb"

          Connection = New System.Data.OleDb.OleDbConnection(ConnectString)

	  Try
	   Connection.Open()
	  Catch e As Exception
	    'Pass on the exception to the caller
          Throw e
	    End Try
	End Sub

	Public Shared Sub DBClose()
	  Try
	    Connection.Close()
	  Catch e As Exception
	    'Pass on the exception to the caller
            Throw e 
	  End Try
	End Sub

End Class

  

• Change the skeleton code for the QueryButton class so that it opens the connection, does the query and closes the connection:
  

   Private Sub QueryButton_Click(ByVal sender As System.Object, _
                 ByVal e As System.EventArgs)Handles QueryButton.Click
       DB.DBopen()
       'query goes here
       DB.DBclose()
  End Sub

• Notice how in the above code we call the Public function DBOpen(), on the DB class.
• Also notce that Db.DBO
  
• Change the code for the CloseButton class so that it closes the dialog.
  

     
  	Private Sub CloseButton_Click(ByVal sender As System.Object, _
  		ByVal e As System.EventArgs)Handles CloseButton.Click
  	  Dispose()
  	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):

		Dim SQLCommand As System.Data.OleDb.OleDbCommand
		Dim SQLDataReader As System.Data.OleDb.OleDbDataReader = nothing
		Dim result_string As String

                'Open the database connection. Exit the app if this fails

                Try
                 DB.DBOpen()
                Catch ex as Exception
                  MsgBox("Problem opening database..." + ex.Message)
                  'Since we have a problem, we exit with a nonzero status.
		  System.Environment.Exit(1)
                End Try
		
                'Connection is open; now do the query
                SQLCommand = New System.Data.OleDb.OleDbCommand("select my_name from foo", DB.Connection) 'modify this query to fit your database
		Try
		  SQLDataReader = SQLCommand.ExecuteReader()
		Catch f As Exception
		  MsgBox("Problem in ExecuteReader..." + f.Message)
                  'Query failed; try closing the database connection on our way out
                  Try
		    DB.DBClose()
                  Catch g as Exception
                    MsgBox("Problem closing database..." + g.Message)
                  End Try
		  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)
                  'Query failed; try closing the database connection on our way out
		  Try
                    DB.DBClose()
                  Catch h as Exception
                     MsgBox("Problem closing database..." + g.Message)
                  End Try
		  System.Environment.Exit(1)
		End Try

                'Query succeeded; close the database connection

                Try
                 DB.DBClose()
                Catch ex as Exception
                  MsgBox("Problem closing database..." + ex.Message)
                  'Since we have a problem, we exit with a nonzero status.
		  'Failed closure is not as bad as a failed opening; let's not exit and have the OS do the close
                End Try
	

• 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.




• 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 = nothing
		Dim QueryString As String = "select my_name from foo" 'modify this query to fit your database
		
		Try
                  DB.DBOpen()
                Catch ex as Exception           
                   MsgBox("Problem opening database..." + ex.Message)
                   System.Environment.Exit(1)
                End Try

		Try
		  myAdapter = New OleDb.OleDbDataAdapter(QueryString, DB.Connection) 
		Catch g As Exception
		  MsgBox("Problem creating dataAdapter..." + g.Message)
                  Try
		    DB.DBClose()
                  Catch ex as Exception
                    MsgBox("Problem closing database..." + ex.Message)
                  End Try
		  System.Environment.Exit(1)
		End Try

		dt = New DataTable()
		
		myAdapter.Fill(dt)
		myAdapter.Dispose()
		DataGrid.DataSource = dt
		Refresh()
		
	End Sub

• Build and run.

• Explore the Datagrid a bit more. For instance, add few more colums to your database table, populate them with data and replace the SQL in the program above with the following: select * from your_table_name. Build and run and see what happens.