Search Availability Application in VB.NET

1
1882

CheckAvialabilityApplicationViewcodePI2In this tutorial, I will teach you how to search room availability in VB.Net. This method is very important when you create a Hotel Reservation and Management System. This is the main feature of the system because it is where you can find the available rooms and to avoid duplication of reserved and booked rooms.

 

This application are made by MySQL and Visual Basic 2008.

 

Let’s begin:

 

  • Create a database in the MySQL and name it “roomdb“.
  • Do these following codes for creating and inserting rooms in the database that you have created.

  • Do these following codes for creating and inserting reserve rooms in the database that you have created.

  • Open Visual Basic 2008 and create a new windows form application.
  • Do the form just like as follows:

CheckAvialabilityApplicationPI

  • Go to the solution explorer and click the code view.

CheckAvialabilityApplicationViewcode

  • In the code view, declare all variables and classes that are needed. Then, create a connection between MySQL database and Visual Basic 2008.

[vbnet]

Dim sql As String
Dim cmd As MySqlCommand
Dim da As MySqlDataAdapter
Dim dt As DataTable
Dim server As String = “localhost”
Dim userid As String = “root”
Dim pass As String = “”
Dim databaseName As String = “roomdb”
Dim conString As String = “server=” & server & “;user id=” & userid & “;password=” & pass & “;database=” & databaseName
Dim con As MySqlConnection = New MySqlConnection(conString)

[/vbnet]

  • Create a sub procedure for retrieving data in the datagridview.

[vbnet]

Private Sub reloadDtg(ByVal sql As String, ByVal dtg As DataGridView)
Try
With cmd
.Connection = con
.CommandText = sql
End With
dt = New DataTable
da = New MySqlDataAdapter(sql, con)
da.Fill(dt)
dtg.DataSource = dt
With dtg

.Columns(0).Visible = False

End With
Catch ex As Exception
MsgBox(ex.Message & “reloadDtg”)
End Try
End Sub

[/vbnet]

  • Create a function for converting datatimepicker to mysql dates.

[vbnet]

‘method for converting datetimepicker into mysql date
Public Function MYSQLDATE(ByVal DTP As DateTimePicker, ByVal dateformat As String)
Dim sqldate As String
sqldate = Format(DTP.Value, dateformat)
Return sqldate
End Function

[/vbnet]

  • Create a function for checking the available rooms.

[vbnet]

‘create a funtion for searching the avialable rooms in the date given
Public Function Availability_Search_reserve() As Boolean
Try
‘openning connection
con.Open()

With Me
”Created a query for the availability of room
sql = “SELECT GROUP_CONCAT( ROOMID , ” ) FROM tblreservation ” & _
” WHERE ((‘” & MYSQLDATE(.dtpCheckin, “yyyy-MM-dd”) & _
“‘>= DATE(ARRIVAL) AND ‘” & MYSQLDATE(.dtpCheckout, “yyyy-MM-dd”) & _
“‘ <= DATE(DEPARTURE)) OR (‘” & MYSQLDATE(.dtpCheckin, “yyyy-MM-dd”) & _
“‘ >= DATE(DEPARTURE) AND ‘” & MYSQLDATE(.dtpCheckout, “yyyy-MM-dd”) & _
“‘ <= DATE(DEPARTURE) ) OR (DATE(ARRIVAL) >='” & MYSQLDATE(.dtpCheckin, “yyyy-MM-dd”) & _
“‘ AND DATE(ARRIVAL) <='” & MYSQLDATE(.dtpCheckout, “yyyy-MM-dd”) & _
“‘)) AND NOT STATUS=’CHECKED-OUT'”
‘Execute the query
cmd = New MySqlCommand
With cmd
.Connection = con
.CommandText = sql
End With
‘Fill data in the datatable
da = New MySqlDataAdapter
da.SelectCommand = cmd
dt = New DataTable
da.Fill(dt)
‘getting the total row in the table
Dim max As Integer = dt.Rows.Count

‘validate the total rows in the table
If max > 0 Then
”validating rooms
If dt.Rows(0).Item(0).ToString = “” Then
”display all rooms in the datagridview
sql = “SELECT ROOMID,ROOMTYPE as ‘Type’, ROOMNUM as ‘Room No.’, ROOM as ‘Room’, NUMPERSON as ‘Person’, PRICE as ‘Price’ ” & _
” FROM tblroom
reloadDtg(sql, .dtgList)
Else
‘diplaying all available room in the datagridview
sql = “SELECT ROOMID,ROOMTYPE as ‘Type’, ROOMNUM as ‘Room No.’, ROOM as ‘Room’, NUMPERSON as ‘Person’, PRICE as ‘Price’ ” & _
” FROM tblroom WHERE ROOMID NOT IN (” & dt.Rows(0).Item(0) & “)”
reloadDtg(sql, .dtgList)
End If
Else
”display all rooms in the datagridview
sql = “SELECT ROOMID,ROOMTYPE as ‘Type’, ROOMNUM as ‘Room No.’, ROOM as ‘Room’, NUMPERSON as ‘Person’, PRICE as ‘Price’ ” & _
” FROM `tblroom”
reloadDtg(sql, .dtgList)
End If

End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
da.Dispose()
End Try
End Function

[/vbnet]

  • Go back to the design view and double click a button and and the following codes in the method.

[vbnet]

Private Sub btnAvailSeach_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAvailSeach.Click
Availability_Search_reserve()
End Sub

[/vbnet]

 

 

For all students who need programmer for your thesis system or anyone who needs a sourcecode in any programming languages. You can contact me @ :
Email – [email protected]
Mobile No. – 09305235027 – tnt

Looking for more source code? Type your keyword here here!

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.