Learning from a Hotel Reservation System

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

In this article I will explain the important procedure or function that I used in a Hotel Reservation System. I wrote this article with the understanding that you will be able to use some of the code from my application. Even if you are doing a payroll system, inventory system, or any application that has the same concepts such as filling a Listview or Combobox, then you should find some useful Visual Basic code here.

Background

This software was written in Visual Basic 6.0 and upgraded to Visual Basic .NET. I feel that it is important to update my knowledge to the newest version as possible so that I will not be left behind other developer.

This is a complete software package with free source code. I will explain in this article the useful code that can be found in Hotel Reservation System.

Using the Software

My Hotel Reservation System will help you manage a collection of data in your hotel. Moreover, you can record a reservation, do check in, check out, payments, etc.

Since the purpose of this article is to teach you the importance of source code within this program, I attached here a link where you can read simple tutorial from my website. ((Editor’s note: This is the same tutorial you are reading here))

Using the Code

Maybe this code isn’t new to all of you, but I do believe there are still programmers out there who need this. I have three important code snippets to share that I used in this program.

Filling a ListView Control

Overview

The Windows Forms ListView control displays a list of items with icons. You can use a listview to create a user interface like the right side pane of Windows Explorer. The control has four view modes: LargeIcon, SmallIcon, List, and Details. Source: Microsoft Visual Studio 2008 Documentation

Listview is used to display a list of items. In the application I used it to display a list of records from a table with a customize column.

To fill a listview in my program you call it like:

  FillListView(lvList, GetData(sSql))

Where lvList in the first parameter is a ListView control in the FillListView procedure. The second parameter, GetData(sSql), will first call the GetData function and return data using OleDbDataReader.

Before you call the FillListView procedure, call the procedure named FillList. In this procedure you can customize the number of columns that you want to show in a listview.

Here’s the code for FillList procedure:

Public Sub FillList()
	With lvList
	.Clear()

		.View = View.Details
		.FullRowSelect = True
		.GridLines = True
		.Columns.Add("Room Number", 90)
		.Columns.Add("Room Type", 120)
		.Columns.Add("Status", 90)

		FillListView(lvList, GetData(sSql))
	End With
End Sub

This procedure can be found in any form that uses a listview control to list all records from a table. The only difference is that you can set the properties for each column in your listview. Of course, FillList procedure is being called in the form load event, so it will load the data from a table into the listview control before the form is totally displayed.

The following is the code for FillListView procedure:

Public Sub FillListView(ByRef lvList As ListView, ByRef myData As OleDbDataReader)
	Dim itmListItem As ListViewItem

	Dim strValue As String

	Do While myData.Read
		itmListItem = New ListViewItem()
		strValue = IIf(myData.IsDBNull(0), "", myData.GetValue(0))
		itmListItem.Text = strValue

		For shtCntr = 1 To myData.FieldCount() - 1
			If myData.IsDBNull(shtCntr) Then
			itmListItem.SubItems.Add("")
		Else
			itmListItem.SubItems.Add(myData.GetValue(shtCntr))
		End If
		Next shtCntr

		lvList.Items.Add(itmListItem)
	Loop
End Sub

Because ListView from the calling procedure is being referenced in this procedure, you do not need to call the name of a form anymore, say form1.lvList. Instead you fill the listview with:

  lvList.Items.Add(itmListItem)

Points of interest

Because a listview is being used to retrieve the data from the table—especially in the masterfiles—you don’t need to create the same code once again to fill the listview. All you need is to pass the name of a listview as the first parameter and the SQL string returned by OleDbDataReader to fill the listview control.

Filling a Combobox Control

Overview

The Windows Forms ComboBox control is used to display data in a drop-down combo box. By default, the ComboBox control appears in two parts: the top part is a text box that allows the user to type a list item. The second part is a list box that displays a list of items from which the user can select one. Source: Microsoft Visual Studio 2008 Documentation

A ComboBox is again another control that does not need to be filled with data using redundant code. In this code snippet, you can fill the combobox with just one procedure by passing parameters from the calling form.

The procedure is called using this syntax:

FillCombobox(cboCountry, "SELECT * FROM Countries", "Countries", "Country", "CountryID")

Notice that there are five parameters that will be passed to the FillCombobox procedure. The first parameter, cboCountry, is the name of a combobox in your form that will be filled with data from the Countries table in the second parameter. The third parameter is just a dummy, which is actually the name of the table to be used in the datasource in the FillCombobox procedure. The fourth (Country) is the name of a field that has an actual data. The fifth parameter (CountryID) will be used to tag the actual data in Country field.

The FillCombobox Procedure:

Public Sub FillCombobox(ByVal cboCombo As ComboBox, ByVal sSQL As String, ByVal strTable As String, _
ByVal strDisplayMember As String, ByVal strValueMember As String)
	Dim cnHotel As OleDbConnection
	cnHotel = New OleDbConnection

	Try
		With cnHotel
			If .State = ConnectionState.Open Then .Close()

			.ConnectionString = cnString
			.Open()
		End With

		Dim da As OleDbDataAdapter = New OleDbDataAdapter(sSQL, cnHotel)
		Dim dt As New DataSet

		da.Fill(dt, strTable)

		cboCombo.DataSource = dt.Tables(strTable).DefaultView
		cboCombo.DisplayMember = strDisplayMember
		cboCombo.ValueMember = strValueMember
	Catch ex As Exception
		MessageBox.Show(ex.Message)
	Finally
		cnHotel.Close()
	End Try
End Sub

As you can see in this code:

	cboCombo.DataSource =  dt.Tables(strTable).DefaultView
  	cboCombo.DisplayMember =  strDisplayMember
  	cboCombo.ValueMember =  strValueMember

The three properties of cboCombo will use the parameter that is being passed from the calling procedure. The best part here is the DisplayMember and ValueMember, which actually hold the data. Filling a combobox with data in Visual Basic 6.0 took a lot of code, but now it’s a lot easier with VB.NET!

DisplayMember is actually the dummy field for this combobox because the actual data that is saved in the database is the value from ValueMember.

Example:

ValueMember (CountyID)		DisplayMember (Country)
1 					United States
2 					India
3 					Canada

This means that only the value of the CountryID will be saved in the table and not the Country.

Counting the Number of Records in a Table

In Visual Basic 6.0, if you are going to determine the number of records in a recordset, all you have to do is add code like rs.recordcount, where rs is the name of a variable that holds the data. However, in VB.NET this functionality has been removed. So, how can you figure out the number of recordset then?

The following is an example again from the hotel reservation system.

Dim sqlQRY As String = "SELECT * FROM [Room_Rates] WHERE RoomNumber = " & RoomNumber & " AND RateTypeID = " & cboRateType.SelectedValue
Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, cnHotel)
Dim RecordCount As Integer

RecordCount = CountRows("SELECT Count(*) FROM [Room_Rates]")

This is very important if you want to first determine if the recordset contains a data to avoid errors later in your code.

This is just a few of the useful code which can be found in Hotel Reservation System.

The following are some of the common question which I found very useful.

1. How to subtract a date in a DatePicker

   dtpDateOut.Value.AddDays(-1)

2. How to add days in a DatePicker

   dtpDateOut.Value = dtpDateIn.Value.AddDays(1)

To Do List

1. Add Login Form
   1. Add Toolbar
   2. Fix Room Inventory
   3. Reports
      • Folio Report
      • Reservation Report
      • Account Receivable Report
      • Check In Guest Report
      • Check Out Report
      • Guest List Report
      • Other Charges Report
      • Room History