VB2022 VB2019 VB6 VB Sample Codes About Us

Lesson 29 : Creating an electronic library


In this lesson, you will learn how to create a more advanced database application using ADO control. The application is an electronic library. This electronic library can accept the user registration as well as handling login command that requires the user to enter username and password, thus enhancing the security of the database. Basically, the application will constitute a welcome menu, a registration menu, a Login menu and the main database menu. The sequence of the menus is illustrated in Figure 29.1 follow:

Figure 29.1

29.1 The Welcome Menu

First of all, you need to design the Welcome menu by following the example as shown in Figure 29.1.

Figure 29.2: The Welcome Menu

Next, insert three command buttons onto the Form and set their properties as shown in Table 29.1 below:


Table 29.1
Propery Name Setting
Form name main_menu
command button 1  Name cmdRegister
command button 1 Caption Register
command button 2 Name cmdLogin
command button 2 Caption Login
command button 3 Name cmdCancel
command button 3 Caption Cancel

The code

Private Sub cmdLogin_Click()
  main_menu.Hide
  Login_form.Show
End Sub

Private Sub cmdRegister_Click()
 main_menu.Hide
 Register.Show
End Sub

29.2 The Registration Form

If a new user click the Register button, the registration form will appear as illustrated in Figure 29.3 below:

Figure 29.3: The Registration Form

This registration form consist of two text boxes , three command buttons and an ADO control. Their properties are set as in Table 29.2 below:


  
Table 29.2
Propery Name Setting
Form name Register
textbox 1 name txtName
textbox 2 name txtpassword
textbox 2 PasswordChar *
command button 1 name cmdConfirm
command button 1 Caption Confirm
command button 2 name cmdClear
command button 2 Caption Clear
command button 3 name cmdCancel
command button 3 Caption Cancel
ADO control name UserInfo

Note that the PasswordChar of textbox 2 is set as * which means actual characters are replaced by * symbol.

The code

Private Sub cancel_Click( )
 End
End Sub

Private Sub cmdClear_Click( )
 txtName.Text = ""
 txtpassword.Text =""
End Sub

Private Sub cmdConfirm_Click()
 UserInfo.Recordset.Fields("username") = txtName.Text
 UserInfo.Recordset.Fields("password") = txtpassword.Text
 UserInfo.Recordset.Update
 Register.Hide
 Login_form.Show
End Sub

Private Sub Form_Load()
 UserInfo.Recordset.AddNew
End Sub

29.3 The Login Menu

The Login menu is illustrated as follow:

Figure 29.4: The Login Menu


There are two text boxes and a command button,  their properties are set as shown in Table 29.3 below:

Table 29.3
Propery Name Setting
Textbox 1 name txtName
Textbox 2 name txtpassword
Command button 1 name cmdLogin
Command button 1 Caption Login
Form name Login_form

The code

Private Sub cmdLogin_Click()
Dim usrname As String
Dim psword As String
Dim usernam As String
Dim pssword As String
Dim Msg As String

Register.UserInfo.Refresh
 usrname = txtName.Text
 psword = txtpassword.Text
Do Until Register.UserInfo.Recordset.EOF
 If Register.UserInfo.Recordset.Fields("username").Value = usrname And_ 
 Register.UserInfo.Recordset.Fields("password").Value = psword Then
 Login_form.Hide
 frmLibrary.Show
 Exit Sub
Else
 Register.UserInfo.Recordset.MoveNext
 End If
Loop
Msg = MsgBox("Invalid password, try again!", vbOKCancel)
 If (Msg = 1) Then
 Login_form.Show
 txtName.Text =""
 txtpassword = ""
Else
 End
End If
End Sub

29.4 The Electronic Library

To design the electronic library interface, insert a couple of labels, a couple of text boxes, three option buttons, a DataGrid control and an ADO control. The name and properties of the controls are listed in Table 29.4.

Table 29.4
Propert Name Setting
Form name frmLibrary
ADO control name adoLibrary
ADO visible False
DataGrid Name DataLibrary
TextBox 1 name txtTitleA
TextBox 2 name txtAuthor
TextBox 3name txtPublisher
TextBox 4 name txtYear
TextBox 5 name txtCategory
Command button 1 name cmdSave
Command button 1 caption &Save
Command button 2 name cmdNew
Command button 2 caption &New
Command button 3 name cmdDelete
Command button 3 caption &Delete
Command button 4 name cmdClr
Command button 4 caption &Clear
Command button 5 name cmdFirst
Command button 5 Caption &First
Command button 6 name cmdNext
Command button 6 caption N&ext
Command button 7 name cmdPrevious
Command button 7 caption &Previous
Command button 8 name cmdLast
Command button 8 caption &Last
Command button 9 name cmdExit
Command button 9 caption E&xit
Command button 10 name CmdViewAll
Command button 10 caption &View All
Command button 11 name CmdSearch
Command button 9 caption Search

The electronic library design interface is illustrated in Figure 29.5.


Figure 29.5

The runtime interface of the elecronic library is shown in Figure 29.6. The library allows users to browse all the books using the navigation buttons. They can add, save and delete books, as well as searching for books based on Author, ISBN or book title.

Figure 29.6

We use SQL keywords to program the search button. The code for the search button is as follows:

Private Sub cmdSearch_Click()
DataLibrary.Visible = True
Dim SearchString As String>
 SearchString = TxtSearch.Text

If Opt_ISBN.Value = True Then

 AdoLibrary.RecordSource = "SELECT * FROM book WHERE ISBN='" & SearchString & "'"
ElseIf Opt_Author.Value = True Then
'Search for Author that starts with the Search String
 AdoLibrary.RecordSource = "SELECT * FROM book WHERE Author Like '" & SearchString & "%'"
ElseIf Opt_Title.Value = True Then
 AdoLibrary.RecordSource = "SELECT * FROM book WHERE Title Like '" & SearchString & "%'"
End If
 AdoLibrary.Refresh
'To reset the column width of datagrid DataLibrary
With DataLibrary

.Columns(0).Width = 2200
.Columns(1).Width = 4500
.Columns(2).Width = 2800
.Columns(3).Width = 2000
.Columns(4).Width = 800
.Columns(5).Width = 1500
End With

DataLibrary.Visible = True

End Sub

* The symbol % is called a wildcard character in SQL. Wildcard characters are used to search for data in a table with the SQL LIKE operator. The wildcard % means a substitute for zero or more characters. In our code above, Using SearchString with % means it will search for a combination of the SearchString with any other characters. For example, SearchString="New", then SearchString&% will include Newyork, Newport, NewCastle, Newton etc.


The rest of the code


Private Sub cmdDelete_Click()
Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo,"Deletion Confirmation")
If Confirm = vbYes Then
 adoLibrary.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
 Else
MsgBox "Record Not Deleted!", , "Message"
 End If
End Sub

Private Sub cmdExit_Click()
 End
End Sub

Private Sub cmdNew_Click()
 adoLibrary.Recordset.AddNew
End Sub

Private Sub cmdFirst_Click()
 AdoLibrary.Recordset.MoveFirst
End Sub

Private Sub cmdNext_Click()
If Not adoLibrary.Recordset.EOF Then
 adoLibrary.Recordset.MoveNext
If adoLibrary.Recordset.EOF Then
 adoLibrary.Recordset.MovePrevious
End If
End If
End Sub

Private Sub cmdPrevious_Click()
If Not adoLibrary.Recordset.BOF Then
 adoLibrary.Recordset.MovePrevious
If adoLibrary.Recordset.BOF Then
 adoLibrary.Recordset.MoveNext
End If
End If
End Sub

Private Sub cmdLast_Click()
 AdoLibrary.Recordset.MoveLast
End Sub

Private Sub cmdSave_Click()
 adoLibrary.Recordset.Fields("Title").Value =txtTitle.Text
 adoLibrary.Recordset.Fields("Author").Value = txtAuthor.Text
 adoLibrary.Recordset.Update
End Sub



Copyright©2008 Dr.Liew Voon Kiong. All rights reserved |Contact|Privacy Policy