ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

Search Availability Application in VB.NET

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.
CREATE TABLE IF NOT EXISTS `tblroom` (
 `ROOMID` int(11) NOT NULL AUTO_INCREMENT,
 `ROOMNUM` int(11) NOT NULL,
 `ROOMTYPE` varchar(60) NOT NULL,
 `ROOM` varchar(30) NOT NULL,
 `NUMPERSON` int(11) NOT NULL,
 `PRICE` double NOT NULL,
 PRIMARY KEY (`ROOMID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;


INSERT INTO `tblroom` (`ROOMID`, `ROOMNUM`, `ROOMTYPE`, `ROOM`, `NUMPERSON`, `PRICE`) VALUES
(15, 3, 'Standard', 'Standard Room', 2, 500),
(16, 2, 'Standard', 'Standard Room', 2, 500),
(17, 4, 'Single', 'Single Room', 1, 400),
(18, 5, 'Family Deluxe', 'Family Deluxe Room', 5, 1500),
(19, 25, 'Family', 'Family Room', 4, 800),
(20, 24, 'Deluxe', 'Room Deluxe', 4, 1200);
  • Do these following codes for creating and inserting reserve rooms in the database that you have created.
CREATE TABLE IF NOT EXISTS `tblreservation` (
 `RESERVEID` int(11) NOT NULL AUTO_INCREMENT,
 `TRANSNUM` int(11) NOT NULL,
 `TRANSDATE` date NOT NULL,
 `ROOMID` int(11) NOT NULL,
 `ARRIVAL` datetime NOT NULL,
 `DEPARTURE` datetime NOT NULL,
 `RPRICE` double NOT NULL,
 `STATUS` varchar(11) NOT NULL,
 PRIMARY KEY (`RESERVEID`),
 KEY `ROOMID` (`ROOMID`),
 KEY `TRANSNUM` (`TRANSNUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=57 ;

 
INSERT INTO `tblreservation` (`RESERVEID`, `TRANSNUM`, `TRANSDATE`, `ROOMID`, `ARRIVAL`, `DEPARTURE`, `RPRICE`, `STATUS`) VALUES
(29, 156, '2016-03-29', 16, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 500, 'BOOKED'),
(30, 157, '2016-03-29', 18, '2016-03-31 00:00:00', '2016-04-01 00:00:00', 1500, 'BOOKED'),
(31, 158, '2016-03-29', 15, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 500, 'BOOKED'),
(32, 159, '2016-03-29', 18, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 1500, 'BOOKED'),
(33, 160, '2016-03-29', 20, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 1200, 'CHECKED-OUT'),
(34, 161, '2016-03-29', 17, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 400, 'BOOKED'),
(35, 162, '2016-03-29', 19, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 800, 'CHECKED-OUT'),
(36, 163, '2016-03-29', 19, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 800, 'CHECKED-OUT'),
(37, 164, '2016-03-29', 15, '2016-04-02 00:00:00', '2016-04-03 00:00:00', 500, 'BOOKED'),
(38, 165, '2016-03-29', 19, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 800, 'BOOKED'),
(39, 166, '2016-03-29', 20, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 1200, 'RESERVED'),
(40, 167, '2016-03-29', 18, '2016-03-29 00:00:00', '2016-03-30 00:00:00', 1500, 'BOOKED'),
(41, 168, '2016-03-30', 17, '2016-03-30 00:00:00', '2016-03-31 00:00:00', 400, 'BOOKED'),
(42, 169, '2016-03-30', 15, '2016-03-30 00:00:00', '2016-03-31 00:00:00', 500, 'BOOKED'),
(43, 170, '2016-03-30', 16, '2016-03-30 00:00:00', '2016-03-31 00:00:00', 500, 'BOOKED'),
(44, 171, '2016-03-30', 20, '2016-03-30 00:00:00', '2016-03-31 00:00:00', 1200, 'RESERVED'),
(45, 172, '2016-03-30', 19, '2016-03-30 00:00:00', '2016-03-31 00:00:00', 800, 'RESERVED'),
(46, 173, '2016-03-30', 20, '2016-03-30 00:00:00', '2016-03-31 00:00:00', 1200, 'RESERVED'),
(47, 174, '2016-03-30', 19, '2016-03-30 00:00:00', '2016-03-31 00:00:00', 800, 'RESERVED'),
(48, 175, '2016-03-30', 15, '2016-04-01 00:00:00', '2016-04-04 00:00:00', 1500, 'CANCELLED'),
(49, 176, '2016-03-30', 16, '2016-03-30 00:00:00', '2016-04-01 00:00:00', 500, 'BOOKED'),
(50, 177, '2016-03-30', 17, '2016-03-31 00:00:00', '2016-04-02 00:00:00', 800, 'RESERVED'),
(51, 178, '2016-04-03', 17, '2016-04-03 00:00:00', '2016-04-04 00:00:00', 400, 'BOOKED'),
(52, 179, '2016-04-03', 18, '2016-04-03 00:00:00', '2016-04-04 00:00:00', 1500, 'RESERVED'),
(53, 180, '2016-04-08', 17, '2016-04-08 00:00:00', '2016-04-09 00:00:00', 400, 'BOOKED'),
(54, 181, '2016-04-08', 19, '2016-04-08 00:00:00', '2016-04-09 00:00:00', 800, 'BOOKED'),
(55, 182, '2016-05-04', 17, '2016-05-04 00:00:00', '2016-05-05 00:00:00', 400, 'BOOKED'),
(56, 183, '2016-05-20', 18, '2016-05-20 00:00:00', '2016-05-21 00:00:00', 1500, 'BOOKED');
  • 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.
  1. Dim sql As String
  2. Dim cmd As MySqlCommand
  3. Dim da As MySqlDataAdapter
  4. Dim dt As DataTable
  5. Dim server As String = "localhost"
  6. Dim userid As String = "root"
  7. Dim pass As String = ""
  8. Dim databaseName As String = "roomdb"
  9. Dim conString As String = "server=" & server & ";user id=" & userid & ";password=" & pass & ";database=" & databaseName
  10. Dim con As MySqlConnection = New MySqlConnection(conString)
  • Create a sub procedure for retrieving data in the datagridview.
  1. Private Sub reloadDtg(ByVal sql As String, ByVal dtg As DataGridView)
  2. Try
  3. With cmd
  4. .Connection = con
  5. .CommandText = sql
  6. End With
  7. dt = New DataTable
  8. da = New MySqlDataAdapter(sql, con)
  9. da.Fill(dt)
  10. dtg.DataSource = dt
  11. With dtg
  12.  
  13. .Columns(0).Visible = False
  14.  
  15. End With
  16. Catch ex As Exception
  17. MsgBox(ex.Message & "reloadDtg")
  18. End Try
  19. End Sub
  • Create a function for converting datatimepicker to mysql dates.
  1. 'method for converting datetimepicker into mysql date
  2. Public Function MYSQLDATE(ByVal DTP As DateTimePicker, ByVal dateformat As String)
  3. Dim sqldate As String
  4. sqldate = Format(DTP.Value, dateformat)
  5. Return sqldate
  6. End Function
  • Create a function for checking the available rooms.
  1. 'create a funtion for searching the avialable rooms in the date given
  2. Public Function Availability_Search_reserve() As Boolean
  3. Try
  4. 'openning connection
  5. con.Open()
  6.  
  7. With Me
  8. ''Created a query for the availability of room
  9. sql = "SELECT GROUP_CONCAT( `ROOMID` , '' ) FROM `tblreservation` " & _
  10. " WHERE (('" & MYSQLDATE(.dtpCheckin, "yyyy-MM-dd") & _
  11. "'>= DATE(`ARRIVAL`) AND '" & MYSQLDATE(.dtpCheckout, "yyyy-MM-dd") & _
  12. "' <= DATE(`DEPARTURE`)) OR ('" & MYSQLDATE(.dtpCheckin, "yyyy-MM-dd") & _
  13. "' >= DATE(`DEPARTURE`) AND '" & MYSQLDATE(.dtpCheckout, "yyyy-MM-dd") & _
  14. "' <= DATE(`DEPARTURE`) ) OR (DATE(`ARRIVAL`) >='" & MYSQLDATE(.dtpCheckin, "yyyy-MM-dd") & _
  15. "' AND DATE(`ARRIVAL`) <='" & MYSQLDATE(.dtpCheckout, "yyyy-MM-dd") & _
  16. "')) AND NOT STATUS='CHECKED-OUT'"
  17. 'Execute the query
  18. cmd = New MySqlCommand
  19. With cmd
  20. .Connection = con
  21. .CommandText = sql
  22. End With
  23. 'Fill data in the datatable
  24. da = New MySqlDataAdapter
  25. da.SelectCommand = cmd
  26. dt = New DataTable
  27. da.Fill(dt)
  28. 'getting the total row in the table
  29. Dim max As Integer = dt.Rows.Count
  30.  
  31. 'validate the total rows in the table
  32. If max > 0 Then
  33. ''validating rooms
  34. If dt.Rows(0).Item(0).ToString = "" Then
  35. ''display all rooms in the datagridview
  36. sql = "SELECT `ROOMID`,`ROOMTYPE` as 'Type', `ROOMNUM` as 'Room No.', `ROOM` as 'Room', `NUMPERSON` as 'Person', `PRICE` as 'Price' " & _
  37. " FROM `tblroom` "
  38. reloadDtg(sql, .dtgList)
  39. Else
  40. 'diplaying all available room in the datagridview
  41. sql = "SELECT `ROOMID`,`ROOMTYPE` as 'Type', `ROOMNUM` as 'Room No.', `ROOM` as 'Room', `NUMPERSON` as 'Person', `PRICE` as 'Price' " & _
  42. " FROM `tblroom` WHERE ROOMID NOT IN (" & dt.Rows(0).Item(0) & ")"
  43. reloadDtg(sql, .dtgList)
  44. End If
  45. Else
  46. ''display all rooms in the datagridview
  47. sql = "SELECT `ROOMID`,`ROOMTYPE` as 'Type', `ROOMNUM` as 'Room No.', `ROOM` as 'Room', `NUMPERSON` as 'Person', `PRICE` as 'Price' " & _
  48. " FROM `tblroom"
  49. reloadDtg(sql, .dtgList)
  50. End If
  51.  
  52. End With
  53. Catch ex As Exception
  54. MsgBox(ex.Message)
  55. Finally
  56. con.Close()
  57. da.Dispose()
  58. End Try
  59. End Function
  • Go back to the design view and double click a button and and the following codes in the method.
  1. Private Sub btnAvailSeach_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAvailSeach.Click
  2. Availability_Search_reserve()
  3. End Sub

 

 

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 – jannopalacios@gmail.com
Mobile No. – 09305235027 – tnt

Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!