6 Quick Tour with Visual Basic

About the Employee Database Application

The employee database application lets the user do the following:

• Browse through data

• Add records

• Update records

• Query the database

• Add records in a batch operation

To provide these functions, this example uses the following forms:

• Employee Form

• Batch Insert Form

Employee Form

The Employee Form displays the fields of the database EMP table and has functional buttons that allow the user to browse, add, update, and query records.

Figure 6-1 shows the Employee Form.

Figure 6-1 Employee Form

Description of the illustration emp8.gif

See Also: "Completed Sample Form_Load Procedure" for the code for the Form_Load procedure that initializes the Employee Form "Programming a Data Entry Form" for a detailed description of the Employee Form and code for the navigational buttons

Batch Insert Form

The Batch Insert Form allows users to enter records in a batch operation.

See Also: "Programming a Batch Form" for a detailed description of the Batch Insert Form and code for its commands

Figure 6-2 shows the Batch Insert Form.

Figure 6-2 Batch insert Form

Description of the illustration batch2.gif

Completed Sample Form_Load Procedure

In the employee application described in the previous section, the Form_Load() procedure creates the OIP server, connects to the database, creates a global dynaset, and calls the EmpRefresh function to display the field values on the Employee Form. The following is an example of a Form_Load() procedure:

Private Sub Form_Load()
'OraSession and OraDatabase are global
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 Set OraDatabase = OraSession.OpenDatabase("Exampledb", "scott/tiger", 0&)
 Set EmpDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 
 
 Call EmpRefresh
 
End Sub

The following variables must be defined globally in EMP_QT.BAS:

Global OraSession As Object
Global OraDatabase As Object
Global EmpDynaset As Object

About the Employee Form

The Employee form displays the fields of the database EMP table and has functional buttons that allow the user to browse, add, update, and query records.

Each field corresponds to a column in the database EMP table. The Employee field (ENAME) is the indexed column and is mandatory for each record. The field data types and sizes are defined as follows in the EMP table:

Name                     Null?       Type
-----------------------   ----------  --------------------------
EMPNO                     NOT NULL    NUMBER(4)
ENAME                                 VARCHAR2(10)
JOB                                   VARCHAR2(9)
MGR                                   NUMBER(4)
HIREDATE                              DATE
SAL                                   NUMBER(7,2)
COMM                                  NUMBER(7,2)
DEPTNO                    NOT NULL    NUMBER(2)

The Employee Number (EMPNO) and Department (DEPTNO) columns are NOT NULL, and, therefore, always require a value when a record is added. The length of each field is enforced by setting the MaxLength property of each TextBox to the appropriate number.

Figure 6-3 shows the Employee Form.

Figure 6-3 Employee Form

Description of the illustration emp8.gif

The initial code for the actual Form_Load procedure is provided in "Completed Sample Form_Load Procedure".

The Employee form is initialized by the Form_Load() procedure and includes the following features:

• Navigating Through Data

• Adding Records

• Updating Records

• Deleting Records

• Querying the Database

Navigating Through Data

Database applications typically require that the user be able to view data in the database. The Employee form has four buttons that let the user scroll through data. Table 6-1 lists the buttons, what they do, which dynaset move method enables the action of the button, and where to look for further information.

To enable navigation through the records of the Employee database, you must first create a global dynaset that selects all the records (rows). Then use the dynaset move methods to program the navigation buttons.

Private Sub cmdFirst_Click() 
 EmpDynaset.MoveFirst
 Call EmpRefresh
 
End Sub

Private Sub cmdLast_Click()
 
 EmpDynaset.MoveLast
 Call EmpRefresh
 
End Sub

Private Sub cmdPrevious_Click()
 
 If EmpDynaset.BOF <> True Then
  EmpDynaset.DbMovePrevious
  If EmpDynaset.BOF = True Then
   MsgBox WarnFirstEmp$
   EmpDynaset.DbMoveFirst
  End If
 End If

Private Sub cmdNext_Click()
 
 If EmpDynaset.EOF <> True Then
  EmpDynaset.DbMoveNext
  If EmpDynaset.EOF = True Then
   MsgBox WarnLastEmp$
   EmpDynaset.DbMoveLast
  End If
 End If

Adding Records

In the example application, the following buttons allow users to add employee records to the database:

• Add

• Commit

To add a record, the user clicks on the Add button, enters the new fields in the text boxes, and then clicks the Commit button to save the data to the database.

Private Sub AddNew_Click()
'Blank out the fields
  txtEmpno = ""
  txtEname = ""
  txtJob = ""
  txtMgr = ""
  txtHireDate = ""
  txtSal = ""
  txtComm = ""
  txtDeptno = ""
    
'Disable the Add button and enable the commit button
  AddNew.Enabled = False
  Commit.Enabled = True
'Disable the navigation buttons
  DisableNavButtons
'Set doadd to true for commit procedure
  DoAdd = True
End Sub

Private Sub Commit_Click()
 
On Error GoTo err_commit
 
ErrMsg = ""
'Do validation checks on entered data
If DoValidationChecks Then 'If validation checks have passed
    
'Add the new record to dynaset
EmpDynaset.AddNew
 
'Update the dynaset fields and then update database if there is no error.
If UpdateDynasetFields Then

'Update the database
  EmpDynaset.Update
 
Commit.Enabled = False
AddNew.Enabled = True
 
Exit Sub
 
err_commit:
    If ErrMsg <> "" Then
        MsgBox ErrMsg
    Else
        MsgBox Error$
    End If
 
End Sub

Function DoValidationChecks() As Boolean
 
Dim DupDyn As Object
Dim DupDynQry As String
 
On Error GoTo err_ValidationCheck
 
ErrMsg = ""
'Empno cannot be changed while in Update mode, so we can skip over validation
If DoAdd Then
    If txtEmpno = "" Then
        ErrMsg = "You must enter a value for Employee Number"
        Error 1
    End If
End If
 
If txtHireDate <> "" And Not IsDate(txtHireDate) Then
    ErrMsg = "Enter date as dd-mmm-yy."
    Error 2
End If
 
If txtDeptno = "" Then
    ErrMsg = "You must enter a value for Department Number"
    Error 3
End If
 
'If adding a record, check for Duplicate empno value by
'attempting to count rows with same value
'Build Query:
If DoAdd Then
    DupDynQry = "select count(*) from emp where empno = " & txtEmpno
    Set DupDyn = OraDatabase.CreateDynaset(DupDynQry, ORADYN_NOCACHE)
 
    If DupDyn.Fields(0).Value <> 0 Then
        ErrNum = DUPLICATE_KEY
        ErrMsg = "Employee Number already exists."
        Error ErrNum
    End If
End If
'Succesful validation with no errors returns True
DoValidationChecks = True
Exit Function
 
err_ValidationCheck:
    If ErrMsg <> "" Then
        MsgBox ErrMsg
    Else
        MsgBox Error$
    End If
    'Validation returns false on failure
    DoValidationChecks = False
    
End Function

Function UpdateDynasetFields() As Integer
'This function sets the dynaset field value to those entered in the text boxes. 
'The function returns true on success, false on error.
 
ErrMsg = ""
 
On Error GoTo err_updatedynasetfields
 
EmpDynaset.Fields("empno").Value = txtEmpno
EmpDynaset.Fields("ename").Value = txtEname
EmpDynaset.Fields("job").Value = txtJob
EmpDynaset.Fields("mgr").Value = txtManager
EmpDynaset.Fields("hiredate").Value = txtHireDate
EmpDynaset.Fields("sal").Value = txtSal
EmpDynaset.Fields("comm").Value = txtComm
EmpDynaset.Fields("deptno").Value = txtDeptno
 
UpdateDynasetFields = True
 
Exit Function
 
err_updatedynasetfields:
    If ErrMsg <> "" Then
        MsgBox ErrMsg
    Else
        MsgBox Error$
    End If
    UpdateDynasetFields = False

Updating Records

To allow users to update existing records in the database, you need to include an Update button in the Employee Form. Users navigate to a particular record, click the Update button, make changes, and then click the Commit button.

While in update mode, the application makes the following restrictions:

• Users cannot navigate to another record or perform another function.

• Users cannot change the employee number because this is the primary key.

To program the Update function, write an event procedure for the Update button and modify the Commit procedure so that it handles both updating and adding records.

Private Sub cmdUpdate_Click()
'Disable the Update button and enable the commit button
  cmdUpdate.Enabled = False
  Commit.Enabled = True
'Disable all other buttons
  DisableNavButtons
  
  txtEmpno.Enabled = False
  DoUpdate = True
End Sub

Private Sub DisableNavButtons()
'disable all buttons while adding and updating
cmdFirst.Enabled = False
cmdPrevious.Enabled = False
cmdNext.Enabled = False
cmdLast.Enabled = False
cmdFind.Enabled = False
cmdUpdate.Enabled = False
AddNew.Enabled = False
 
End Sub

Private Sub Commit_Click()
 
On Error GoTo err_commit 
 
ErrMsg = ""
'Do validation checks on entered data
If DoValidationChecks Then 'If validation checks have passed
   
    'If we are adding a record use AddNew
    If DoAdd = True Then
        EmpDynaset.AddNew
    End If
    'If we are updating a record use Edit
    If DoUpdate = True Then
       EmpDynaset.Edit
    End If
    'Update the dynaset fields and then update database if there is no error.
    If UpdateDynasetFields Then
        EmpDynaset.Update
    End If
 
    SetAfterCommitFlags
 
End If 'Endif for DoValidationChecks
 
Exit Sub
 
err_commit:
    If ErrMsg <> "" Then
        MsgBox ErrMsg
    Else
        MsgBox Error$
    End If
 
End Sub

Sub SetAfterCommitFlags()
'disable commit and re-enable add and update buttons
Commit.Enabled = False
AddNew.Enabled = True
cmdUpdate.Enabled = True
 
'enable the other buttons
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = True
cmdLast.Enabled = True
cmdFind.Enabled = True
cmdUpdate.Enabled = True
AddNew.Enabled = True
 
DoUpdate = False
DoAdd = False
 
txtEmpno.Enabled = True
 
End Sub

Deleting Records

Users can delete records by navigating to a particular record and clicking the Delete button. The application prompts the user to verify the deletion, then the application deletes the record using the Delete method. The program then refreshes the screen with the next record or with the previous record if the user deleted the last record in the dynaset.

The following example shows the delete-click event procedure:

Private Sub cmdDelete_Click()
'prompt user
Response = MsgBox("Do you really want to Delete?", vbYesNo + vbExclamation)
    
If Response = vbYes Then
    EmpDynaset.Delete
    'attempt to move to next record
    EmpDynaset.MoveNext
    If EmpDynaset.EOF Then 'If deleted last record
        EmpDynaset.MovePrevious
    End If
    Call EmpRefresh
End If
End Sub

Querying the Database

The employee application can be configured to allow users to search for particular records in the database. For demonstration purposes, a Find button is included to allow users to query only employee names. At any time, the user can enter the query in the Employee Name field, and click the Find button. The application then displays the result or displays a message if the name cannot be found.

To search for records, the FindFirst method is used. When the find operation succeeds, the record is displayed. If the find fails, a message is displayed. The current row is reset to the first row, because failures cause the dynaset to be BOF (beginning-of-file), effectively making the current row invalid.

The Find_Click() event procedure must do the following:

1. Build a find clause to find the record where the ENAME column matches the entered string.

2. Execute the find using the FindFirst method.

3. Display the record if it is found; if the record was not found, display a message and reset the current row to the first row.

The following example shows a typical find click event procedure:

Private Sub cmdFind_Click()
Dim FindClause As String
Dim SingleQuote As String
 
ErrMsg = ""
SingleQuote = "'"
 
On Error GoTo err_find
'build the find clause:
'Can make our query case insensitive by converting the names to upper case
'FindClause = "UPPER(ename) = " & SingleQuote & UCase(txtEname) & SingleQuote
FindClause = "ename = " & SingleQuote & txtEname & SingleQuote
   
EmpDynaset.DbFindFirst FindClause
 
If EmpDynaset.NoMatch Then
    MsgBox "Could not find record matching Employee Name " & txtEname
    EmpDynaset.DbMoveFirst
End If
 
Call EmpRefresh
 
Exit Sub

Using Batch Insert

A typical command to load the Batch Insert form looks like this:

Private Sub BInsert_Click()
  Load BatchInsert
  BatchInsert.Show
End Sub

About the Batch Insert Form

The Batch Insert Form allows users to insert rows in a batch operation, that is, to insert more than one record into the database by using only one command. This feature is implemented using parameter arrays and SQL statements.

Table 6-1 shows a typical Batch Insert Form:

Figure 6-4 Batch Insert Form

Description of the illustration batch2.gif

Users navigate to the Batch Insert Form by clicking the Batch Insert button on the Employee Form. The Batch Insert Form has a grid that displays the entered data and a row of fields where the user enters each record. To keep the example simple, users are only allowed to enter information into the Employee Number, Employee Name, and Department Number fields.

Users enter records in the fields and click the Add to Grid button. The program displays the entered records in the grid. To insert the entire batch to the database, users click the CommitGrid button.

The Batch Insert Form uses three procedures. The Form_Load() procedure initializes the grid with the column headers. The CmdAddtoGrid_click() procedure copies the entered data from the fields to the grid. The CommitGrid_Click() procedure contains the parameter array and SQL statements used to make the batch insert.

These procedures are described as follows:

• Coding the Batch Insert Form_Load( ) Procedure

• Coding the CmdAddtoGrid( ) Procedure

• Coding the CommitGrid_Click( ) Procedure

Coding the Batch Insert Form_Load( ) Procedure

The following examples show how the Batch Insert Form_Load() procedure sets the column headings for the grid:

Private Sub Form_Load()
Grid1.Enabled = True
CurrRow = 0  'Top row
ReadRow = 0
ReadCol = 0
    
'Set column headings
 
Grid1.Row = CurrRow
Grid1.Col = 0
Grid1.Text = "Employee Number"
 
Grid1.Col = 1
Grid1.Text = "Employee Name"
 
Grid1.Col = 2
Grid1.Text = "Department Number"
 
NoOfCols = 3
 
CurrRow = CurrRow + 1
    
End Sub

Coding the CmdAddtoGrid( ) Procedure

The CmdAddtoGrid_Click() procedure copies the data entered in the fields to the next empty grid row. The global variable CurrRow always points to the first empty row in the grid.

The following example shows the CmdAddtoGrid_Click():

Private Sub CmdAddtoGrid_Click()
 
'Update the grid
'Update Empno column
Grid1.Row = CurrRow
Grid1.Col = 0
Grid1.Text = txtEmpno
'Update Ename column
Grid1.Row = CurrRow
Grid1.Col = 1
Grid1.Text = txtEname
'Update Deptno column
Grid1.Row = CurrRow
Grid1.Col = 2
Grid1.Text = txtDeptno
 
'Increment CurrCol
CurrRow = CurrRow + 1
 
NoOfRows = CurrRow - 1
 
End Sub

Coding the CommitGrid_Click( ) Procedure

The CommitGrid_Click() procedure inserts the grid data into the database. To do so, this procedure creates a parameter array object for each column in the EMP table that corresponds to a column in the grid. The OraParameters.AddTable() method defines each parameter array. For example, a parameter array called EMPNO_ARR holds all Employee Number column elements.

After the parameter arrays are defined, the Put_Value method populates them with grid column elements.

To commit the parameter array elements to the database, this procedure uses the CreateSQL() method with a SQL INSERT statement containing the parameter arrays. Because the CreateSQL() method executes the SQL INSERT statement in addition to creating a SQL statement object, all column elements (parameter array elements) are inserted into the EMP table with this one statement.

If an error occurs during a SQL INSERT statement that contains parameter arrays, the SQL statement object is still created with no explicitly raised error. To identify such errors, always check the OraDatabase.LastServerErr and OraDatabase.LastServerErrText properties immediately after executing the CreateSQL method.

The CreateSQL method updates the database directly and has no effect on the dynaset. The EmpDynaset. Refresh method must used to refresh this dynaset so that it reflects the newly inserted records.

The CommitGrid_Click() event procedure must do the following:

1. Define a parameter array for each grid (database) column, using the AddTable method.

2. Copy grid column elements into parameter arrays, using the Put_Value method within a nested loop.

3. Create a SQL statement object using the CreateSQL method to insert parameter array elements into the EMP table.

4. Check the LastServerErrText and LastServerErr properties to catch SQL statement execution errors.

5. Refresh the global dyanset to reflect newly inserted records, using the Refresh method.

The following example shows a typical cmdCommitGrid_Click() procedure:

Private Sub cmdCommitGrid_Click()
Dim OraSqlStmt As Object
Dim OraPArray(2) As Object
 
On Error GoTo err_CommitGrid
ErrMsg = ""
 
'Define parameter arrays, one for each column
OraDatabase.Parameters.AddTable "EMPNO_ARR", ORAPARM_INPUT, ORATYPE_NUMBER, _
            NoOfRows
OraDatabase.Parameters.AddTable "ENAME_ARR", ORAPARM_INPUT, ORATYPE_VARCHAR2, _ 
            NoOfRows, 10
OraDatabase.Parameters.AddTable "DEPTNO_ARR", ORAPARM_INPUT, ORATYPE_NUMBER, _
            NoOfRows
If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then
Error 1
End If
 
'Initialize local array to hold parameter arrays
Set OraPArray(0) = OraDatabase.Parameters("EMPNO_ARR")
Set OraPArray(1) = OraDatabase.Parameters("ENAME_ARR")
Set OraPArray(2) = OraDatabase.Parameters("DEPTNO_ARR")
 
'Init the param array variables. Add loop to read thru grid ROWS
For ReadRow = 0 To (NoOfRows - 1)
Grid1.Row = ReadRow + 1
'Loop to read thru grid CELLS
    For ReadCol = 0 To NoOfCols - 1
        Grid1.Col = ReadCol
        OraPArray(ReadCol).Put_Value Grid1.Text, ReadRow
    Next ReadCol
Next ReadRow
 
'create a sqlstmt to insert array values into table
Set OraSqlStmt = OraDatabase.CreateSql("insert into emp(empno,ename,deptno)" & _
        "values(:EMPNO_ARR,:ENAME_ARR,:DEPTNO_ARR)", 0&)
If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then
  ErrMsg = OraDatabase.LastServerErrText
  Error 1
End If
 
'Refresh the Dynaset
EmpDynaset.Refresh

OraDatabase.Parameters.Remove "EMPNO_ARR"
OraDatabase.Parameters.Remove "ENAME_ARR"
OraDatabase.Parameters.Remove "DEPTNO_ARR" 

Exit Sub
 
err_CommitGrid:
    If ErrMsg <> "" Then
        MsgBox ErrMsg
    Else
        MsgBox Error$
    End If
 
End Sub