• 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 QueryButtonClick(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 CloseButtonClick(ByVal sender As System.Object, _

                             ByVal e As System.EventArgs) _

            Handles CloseButton.Click

Dispose()

            End Sub

 

·        Now,  add the Main() using the same approach as used in VBGUI.htm. 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 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=p:\courses\371\notes\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

 

        Myform = New QueryForm()

 

        Myform.ShowDialog()

 

        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

 

·        Test the application. It should  first connect to the database and then pop up the QueryForm. Clicking the Query button does not do anything yet; clicking the Close button takes the QueryForm away after which Main() closes 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 suppots 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

 

        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 ClearButtonClick(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.NET application that interfaces with any SQL database (provided there is a VB.NET/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.NET 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 discussed in class).

• To use a DataGrid, modify the application above such that rather than including a ListBox on the Form window, you include a DataGrid object (see picture above. Note that when you create the object using your VB.NET ToolBox, the DataGrid will be empty; in fact, it won't even show a grid):
• In your DBMain class, keep the code for opening the database connection.
• Now modify the code for the Query button to do the query and load the results in the DataGrid 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 sensor_name, location_name " + "from sensor, location " + "where sensor.location_id = location.location_id"
  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()
  TheDataGrid.DataSource = dt
  Refresh()

End Sub

• Build and run.