MPZgALQF

1Imports System.Data.SQLite
2Public Class C_Booking
3    Dim location As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
4    Dim filename As String = "Bookings.db"
5    Dim fullpath As String = System.IO.Path.Combine(location, filename)
6    '//Connection string Is to establish connection to the Database
7    Public connectionstring As String = String.Format("Data Source=" + fullpath)
8
9    Private Sub Button6_Click(sender As Object, e As EventArgs)
10
11    End Sub
12
13    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
14        Me.Hide()
15        Menu_Screen.Show()
16        MessageBox.Show("You've Been Logged Out Successfully!")
17
18    End Sub
19    Private Function Duplicate_Db(fullpath As String) As Boolean
20        Return System.IO.File.Exists(fullpath)
21
22    End Function
23    Public Sub CreateDatabase()
24
25        '//Check Database file exists, And create the table once for the first time
26        If Not Duplicate_Db(fullpath) Then
27            Dim str As String = "Create Table Bookings(Id integer primary key autoincrement,Title text,Firstname text,Surname text, Mobile text, Date Text, Time Text, Guests Text, Notes text);"
28
29            Using Sqlconn As New SQLiteConnection(connectionstring)
30                Dim cmd As New SQLiteCommand(str, Sqlconn)
31                Sqlconn.Open()
32                cmd.ExecuteNonQuery()
33            End Using
34        End If
35    End Sub
36
37    Public Sub LoadDatabase()
38        Dim str_view As String = "Select * from Bookings"
39        Dim dt As DataTable = Nothing
40        Dim ds As New DataSet
41
42        '//Try catch method is to handle the errors for (ie) for checking the Database connection,it is connected or not
43        Try
44            Using Sqlconn As New SQLiteConnection(connectionstring)
45                Dim cmd As New SQLiteCommand(str_view, Sqlconn)
46                Sqlconn.Open()
47                Using da As New SQLiteDataAdapter(cmd)
48                    da.Fill(ds)
49                    dt = ds.Tables(0)
50
51                End Using
52            End Using
53
54
55            Dgrid.DataSource = dt
56
57            '//To hide the column "ID" field
58            ' Dgrid.Columns(0).Visible = False
59
60        Catch ex As Exception
61            MessageBox.Show(ex.Message)
62        End Try
63
64
65    End Sub
66    Private Sub C_Booking_Load(sender As Object, e As EventArgs) Handles MyBase.Load
67        CreateDatabase()
68        LoadDatabase()
69    End Sub
70    Private Sub ClearAll_textboxes()
71
72        tx_id.Text = ""
73        tx_title.Text = ""
74        tx_name.Text = ""
75        tx_surname.Text = ""
76        tx_mobile.Text = ""
77        tx_time.Text = ""
78        tx_guest.Text = ""
79        tx_notes.Text = ""
80
81
82    End Sub
83
84    Public Function validate() As Integer
85        validate = 1
86        'carries out presence check 
87        If validate = 1 Then
88            If Trim(tx_title.Text.Length) < 1 Then
89                validate = 0
90                MessageBox.Show("You have not completed the Title field. This is a mandatory field")
91            Else
92                validate = 1
93            End If
94
95        End If
96
97        If validate = 1 Then
98            If Trim(tx_name.Text.Length) < 1 Then
99                validate = 0
100                MessageBox.Show("You have not completed the Firstname field. This is a mandatory field")
101            Else
102                validate = 1
103            End If
104
105        End If
106
107        If validate = 1 Then
108            If Trim(tx_surname.Text.Length) < 1 Then
109                validate = 0
110                MessageBox.Show("You have not completed the Surname field. This is a mandatory field")
111            Else
112                validate = 1
113            End If
114
115        End If
116
117        '// Range Check
118        If validate = 1 Then
119            If Trim(tx_mobile.Text.Length) = 11 Then
120                validate = 1
121            Else
122                MessageBox.Show("Please check the number")
123                validate = 0
124            End If
125
126        End If
127
128        If validate = 1 Then
129            If Trim(tx_time.Text.Length) < 1 Then
130                validate = 0
131                MessageBox.Show("You have not completed the Time field. This is a mandatory field")
132            Else
133                validate = 1
134            End If
135
136        End If
137
138        If validate = 1 Then
139            If Trim(tx_guest.Text.Length) < 1 Then
140                validate = 0
141                MessageBox.Show("You have not completed the Number of Guest field. This is a mandatory field")
142            Else
143                validate = 1
144            End If
145
146        End If
147
148    End Function
149
150    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
151        Dim valid As Integer
152        valid = validate()
153        If valid = 1 Then
154            Using Sqlconn As New SQLiteConnection(connectionstring)
155                Dim insert_str As String = "INSERT into Bookings(Title,Firstname,Surname,Mobile,Date,Time,Guests,Notes) Values(@title,@firstname,@surname,@mobile,@date,@time,@guest,@notes)"
156                Dim cmd As New SQLiteCommand(insert_str, Sqlconn)  '// takes the value from the gui and stores it to the database.
157
158
159                cmd.Parameters.AddWithValue("@title", tx_title.Text)
160                cmd.Parameters.AddWithValue("@firstname", tx_name.Text)
161                cmd.Parameters.AddWithValue("@surname", tx_surname.Text)
162                cmd.Parameters.AddWithValue("@mobile", tx_mobile.Text)
163                cmd.Parameters.AddWithValue("@date", tx_date.Value)
164                cmd.Parameters.AddWithValue("@time", tx_time.Text)
165                cmd.Parameters.AddWithValue("@guest", tx_guest.Text)
166                cmd.Parameters.AddWithValue("@notes", tx_notes.Text)
167
168                Sqlconn.Open()
169
170                cmd.ExecuteNonQuery()
171
172                MessageBox.Show("Records are added Successfully")
173
174                '//To display the Database in the Datagridview
175                LoadDatabase()
176
177                '//To clear the textboxes after adding to the database and datagrid
178                ClearAll_textboxes()
179            End Using
180        Else
181            MessageBox.Show("Validation Failed")
182        End If
183
184
185    End Sub
186
187    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
188
189        Dim str_view As String = "Select * from Bookings where Id='" + tx_id.Text + "';"
190        Dim str_mobile As String = "Select * from Bookings where Mobile='" + tx_mobile.Text + "';"
191        Dim dt As DataTable = Nothing
192        Dim ds As New DataSet
193
194
195        '//Try catch method is to handle the errors for (ie) for checking the Database connection,it is connected or not
196
197        Using Sqlconn As New SQLiteConnection(connectionstring)
198            Dim cmd As New SQLiteCommand(str_view, Sqlconn)
199            Dim cmdMobile As New SQLiteCommand(str_mobile, Sqlconn)
200
201            Sqlconn.Open()
202            Using da As New SQLiteDataAdapter(cmd)
203                da.Fill(ds)
204                dt = ds.Tables(0)
205
206            End Using
207
208            Using da As New SQLiteDataAdapter(cmdMobile)
209                da.Fill(ds)
210                dt = ds.Tables(0)
211
212            End Using
213
214        End Using
215
216
217        Dgrid.DataSource = dt
218
219
220
221        '//To display the Database in the Datagridview
222
223        'LoadDatabase()
224
225
226
227    End Sub
228
229    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
230        LoadDatabase()
231    End Sub
232
233    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
234        Using Sqlconn As New SQLiteConnection(connectionstring)
235            Dim delete_id As String = "DELETE from Bookings where Id=@id"
236            Dim delete_mobile As String = "DELETE from Bookings where Mobile=@mobile"
237            Dim cmdId As New SQLiteCommand(delete_id, Sqlconn)
238            Dim cmdMobile As New SQLiteCommand(delete_mobile, Sqlconn)
239
240            cmdId.Parameters.AddWithValue("@id", tx_id.Text)
241            cmdMobile.Parameters.AddWithValue("@mobile", tx_mobile.Text)
242
243            Sqlconn.Open()
244
245            cmdId.ExecuteNonQuery()
246            cmdMobile.ExecuteNonQuery()
247
248            MessageBox.Show("Record deleted Successfully")
249
250            '//To display the Database in the Datagridview
251            LoadDatabase()
252
253            ClearAll_textboxes()
254        End Using
255    End Sub
256
257    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
258        Dim valid As Integer
259        valid = validate()
260        If valid = 1 Then
261            Using Sqlconn As New SQLiteConnection(connectionstring)
262                Dim update_Id As String = "UPDATE Bookings set Title='" + tx_title.Text + "',Firstname='" + tx_name.Text + "',Surname='" + tx_surname.Text + "',Mobile='" + tx_mobile.Text + "',Date ='" + tx_date.Value + "',Time='" + tx_time.Text + "',Guests='" + tx_guest.Text + "',Notes='" + tx_notes.Text + "' where Id='" + tx_id.Text + "';"
263                Dim update_mobile As String = "UPDATE Bookings set Title='" + tx_title.Text + "',Firstname='" + tx_name.Text + "',Surname='" + tx_surname.Text + "',Mobile='" + tx_mobile.Text + "',Date ='" + tx_date.Value + "',Time='" + tx_time.Text + "',Guests='" + tx_guest.Text + "',Notes='" + tx_notes.Text + "' where Mobile='" + tx_mobile.Text + "';"
264
265                Dim cmdId As New SQLiteCommand(update_Id, Sqlconn)
266                Dim cmdMobile As New SQLiteCommand(update_mobile, Sqlconn)
267
268                'cmd.Parameters.AddWithValue("@name", txt_name.Text)
269                cmdId.Parameters.AddWithValue("@mobile", tx_mobile.Text)
270
271                Sqlconn.Open()
272
273                cmdId.ExecuteNonQuery()
274                cmdMobile.ExecuteNonQuery()
275
276                MessageBox.Show("Record Updated Successfully")
277
278
279                '//To display the Database in the Datagridview
280                LoadDatabase()
281                ClearAll_textboxes()
282            End Using
283        Else
284            MessageBox.Show("Validation Failed")
285        End If
286    End Sub
287    Private Sub TextBox7_TextChanged(sender As Object, e As EventArgs) Handles tx_guest.TextChanged
288
289    End Sub
290
291    Private Sub tx_name_TextChanged(sender As Object, e As EventArgs) Handles tx_name.TextChanged
292
293    End Sub
294    Private Sub TextBox1_KeyPress(sender As Object, e As KeyPressEventArgs) _
295                              Handles tx_name.KeyPress
296
297        If Not (Asc(e.KeyChar) = 8) Then
298            Dim allowedChars As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-<SPACE BAR>"
299            If Not allowedChars.Contains(e.KeyChar.ToString.ToLower) Then
300                e.KeyChar = ChrW(0)
301                e.Handled = True
302                MessageBox.Show("No numerical values allowed in firstname field")
303
304            End If
305        End If
306
307    End Sub
308    Private Sub TextBox2_KeyPress(sender As Object, e As KeyPressEventArgs) _
309                              Handles tx_surname.KeyPress
310
311        If Not (Asc(e.KeyChar) = 8) Then
312            Dim allowedChars As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-<SPACE BAR>"
313            If Not allowedChars.Contains(e.KeyChar.ToString.ToLower) Then
314                e.KeyChar = ChrW(0)
315                e.Handled = True
316                MessageBox.Show("No numerical values allowed in surname field")
317
318            End If
319        End If
320
321    End Sub
322    Private Sub TextBox3_KeyPress(sender As Object, e As KeyPressEventArgs) _
323                              Handles tx_mobile.KeyPress
324
325        If Not (Asc(e.KeyChar) = 8) Then
326            Dim allowedChars As String = "1234567890"
327            If Not allowedChars.Contains(e.KeyChar.ToString.ToLower) Then
328                e.KeyChar = ChrW(0)
329                e.Handled = True
330                MessageBox.Show("No characters allowed in mobile field")
331
332            End If
333        End If
334
335    End Sub
336    Private Sub TextBox4_KeyPress(sender As Object, e As KeyPressEventArgs) _
337                              Handles tx_guest.KeyPress
338
339        If Not (Asc(e.KeyChar) = 8) Then
340            Dim allowedChars As String = "1234567890-<SPACE BAR>"
341            If Not allowedChars.Contains(e.KeyChar.ToString.ToLower) Then
342                e.KeyChar = ChrW(0)
343                e.Handled = True
344                MessageBox.Show("No characters allowed in guest field")
345
346            End If
347        End If
348
349    End Sub
350
351    Private Sub tx_surname_TextChanged(sender As Object, e As EventArgs) Handles tx_surname.TextChanged
352
353    End Sub
354End Class