· 9 years ago · May 03, 2017, 01:46 PM
1Imports ADOX
2Imports System.IO
3Imports System.Data.OleDb
4Imports System.Text
5Imports System.Security.Cryptography
6
7
8
9Public Class LogIn
10 Public ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
11 Dim cat As Catalog = New Catalog()
12 Dim SQLCommand As String
13 Dim con As New OleDbConnection(ConnectionString)
14 Dim cmd As New OleDbCommand
15 Dim uname As String
16 Dim pword As String
17
18 'This sub creates all the tables within the database
19 Private Sub LogIn_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
20 Dim cat As Catalog = New Catalog()
21 Dim SQLCommand As String = "CREATE TABLE Staff(Username VarChar(255), Pword VarChar(255));"
22
23 Dim con As New OleDbConnection(ConnectionString)
24 Dim cmd As New OleDbCommand
25 If File.Exists("N:\TestDatabase.accdb") = True Then
26 MsgBox("already exists")
27 Else
28 cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
29 "Data Source=N:\TestDatabase.accdb;" & _
30 "Jet OLEDB:Engine Type=5")
31 MsgBox("Made")
32
33 cat = Nothing
34 End If
35
36 con.Open()
37 cmd.Connection = con
38 cmd.CommandText = (SQLCommand)
39 cmd.ExecuteNonQuery()
40
41 uname = "Admin"
42 pword = HashPass("pass1")
43 cmd.CommandText = "INSERT INTO Staff(Username, Pword) VALUES ('" & uname & "','" & pword & "')"
44 cmd.ExecuteNonQuery()
45 con.Close()
46
47 CreateAllTables()
48
49 End Sub
50
51 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
52 CheckLogIn()
53 End Sub
54 ' This sub checks if the password and login that have been entered are equal to the one in the Staff table.
55 Private Sub CheckLogIn()
56 Dim uname As String = "Admin"
57
58 If txtpassword.Text = "pass1" Then
59 pword = HashPass("pass1")
60 End If
61
62 Dim pass As String
63 If txtusername.Text = "" Or txtpassword.Text = "" Then
64 MsgBox("Please fill in all the provided fields to continue!")
65 Else
66
67 Dim querry As String = "Select pword From Staff where Username= '" & uname & "';"
68 Dim dbsource As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb"
69 Dim conn = New OleDbConnection(dbsource)
70 Dim cmd As New OleDbCommand(querry, conn)
71 conn.Open()
72 Try
73 pass = cmd.ExecuteScalar().ToString
74 Catch ex As Exception
75 MsgBox("Username does not exit")
76 End Try
77 If (pword = pass) Then
78 MsgBox("Login success")
79 Main_Menu.Show()
80 If Main_Menu.Visible Then
81 Me.Hide()
82 End If
83
84 Else
85 MsgBox("login Failed")
86 txtusername.Clear()
87 txtpassword.Clear()
88 End If
89 End If
90
91 con.Close()
92
93 End Sub
94 ' Grade A Hashing Function
95 Private Function HashPass(ByVal Content As String) As String
96 Dim MoLeCuL3 As New Security.Cryptography.SHA1CryptoServiceProvider
97 Dim ByteString() As Byte = System.Text.Encoding.ASCII.GetBytes(Content)
98 ByteString = MoLeCuL3.ComputeHash(ByteString)
99
100 Dim FinalString As String = Nothing
101 For Each bt As Byte In ByteString
102 FinalString &= bt.ToString("x2")
103 Next
104 Return FinalString
105 End Function
106 'Grade A Using SQL
107 ' This Sub creates all the tables within the database.
108 Private Sub CreateAllTables()
109 SQLCommand = "CREATE TABLE Supplier(SupplierID varchar(255) PRIMARY KEY, BusinessName VarChar(255), ContactName Varchar(255)," &
110 "BusinessEmail Varchar(255), BusinessAddress Varchar(255), BusinessCity Varchar(255), BusinessPostcode Varchar(255));"
111
112 con.Open()
113 cmd.Connection = con
114 cmd.CommandText = (SQLCommand)
115 Try
116 cmd.ExecuteNonQuery()
117 Catch ex As Exception
118 End Try
119 con.Close()
120
121
122
123 SQLCommand = "CREATE TABLE Customer(CustomerID varchar(255) PRIMARY KEY, FirstName VarChar(255), LastName Varchar(255), Email Varchar(255)," &
124 "Address Varchar(255), City Varchar(255), Postcode Varchar(255));"
125 con.Open()
126 cmd.Connection = con
127 cmd.CommandText = (SQLCommand)
128 Try
129 cmd.ExecuteNonQuery()
130 Catch ex As Exception
131 End Try
132 con.Close()
133
134
135
136 SQLCommand = "CREATE TABLE Inventory(ProductID varchar(255) PRIMARY KEY, ProductName VarChar(255), ItemCost varchar(255)," &
137 "ItemPrice varchar(255), ItemQuantity varchar(255), SupplierID varchar(255), FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID))"
138
139
140 con.Open()
141 cmd.Connection = con
142 cmd.CommandText = (SQLCommand)
143 Try
144 cmd.ExecuteNonQuery()
145 Catch ex As Exception
146 End Try
147 con.Close()
148
149
150 SQLCommand = "CREATE TABLE Orders(OrderID varchar(255) PRIMARY KEY, ProductID varchar(255), FOREIGN KEY (ProductID) REFERENCES Inventory(ProductID)," &
151 "CustomerID varchar(255), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID), DateofPurchase varchar(255), TimeofPurchase varchar(255))"
152
153
154 con.Open()
155 cmd.Connection = con
156 cmd.CommandText = (SQLCommand)
157 Try
158 cmd.ExecuteNonQuery()
159 Catch ex As Exception
160 End Try
161 con.Close()
162 End Sub
163End Class
164
165
166Imports ADOX
167Imports System.IO
168Imports System.Data.OleDb
169
170
171Public Class Suppliers
172 Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
173 Dim cat As Catalog = New Catalog()
174 Dim SQLCommand As String
175 Dim con As New OleDbConnection(ConnectionString)
176 Dim cmd As New OleDbCommand
177 Private Sub Suppliers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
178
179 End Sub
180
181 'When the add button is clicked, the supplier table is updated and the datagrid view is updated too.
182 Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
183
184 AddSupplier()
185 UpdatetableS()
186
187 End Sub
188
189 'This sub puts the table in the database into the datagridview
190 Private Sub UpdatetableS()
191 Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
192 Dim MyConn As OleDbConnection
193 Dim ds As DataSet
194 Dim tables As DataTableCollection
195 Dim source1 As New BindingSource
196 Dim da As OleDbDataAdapter
197
198 MyConn = New OleDbConnection
199 MyConn.ConnectionString = ConnString
200 ds = New DataSet
201 tables = ds.Tables
202
203 da = New OleDbDataAdapter("Select * from [Supplier]", MyConn)
204 da.Fill(ds, "Supplier")
205 Dim view As New DataView(tables(0))
206 source1.DataSource = view
207 DatagridviewSup.DataSource = view
208
209
210
211 End Sub
212
213 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
214 UpdatetableS()
215 End Sub
216
217 ' this sub allwows the user to go back to the main menu
218 Private Sub btnCustback_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCustback.Click
219 If Me.Visible() Then
220 Me.Close()
221 Main_Menu.Show()
222 End If
223 End Sub
224
225 Private Sub SeachTable()
226
227 Dim sqlsearch As String
228 sqlsearch = "SELECT * FROM Supplier WHERE SupplierID = '" & SupID.Text & "'" & " OR BusinessName = '" & supbusinessname.Text & "'" & " OR ContactName = '" & SupContactName.Text & "'" & " OR BusinessEmail = '" & SupEmailAddress.Text & "'" & " OR BusinessAddress = '" & SupAddress.Text & "'" & " OR BusinessCity = '" & SupCity.Text & "'" & " OR BusinessPostcode = '" & SupPost.Text & "'"
229
230 ' execute the SQL statements against the dataBase
231 Dim adapter As New OleDbDataAdapter(sqlsearch, con)
232 ' Shows the records and updates the DataGridView
233 Dim dt As New DataTable("Supplier")
234 adapter.Fill(dt)
235 DatagridviewSup.DataSource = dt
236
237 End Sub
238
239 Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
240 SeachTable()
241 End Sub
242
243 'This sub adds the information typed into the textboxes into a table in the database
244 Private Sub AddSupplier()
245 Dim con As New OleDbConnection(ConnectionString)
246 Dim cmd As New OleDbCommand
247 If SupID.Text = "" Or supbusinessname.Text = "" Or SupContactName.Text = "" Or SupEmailAddress.Text = "" Or SupAddress.Text = "" Or SupCity.Text = "" Or SupPost.Text = "" Then
248 MsgBox("Please fill in all given fields!")
249
250 Else
251
252 con.Open()
253 cmd.Connection = con
254
255 cmd.CommandText = "INSERT INTO Supplier(SupplierID,BusinessName,ContactName,BusinessEmail,BusinessAddress,BusinessCity,BusinessPostcode) VALUES ('" & SupID.Text & "','" & supbusinessname.Text & "', '" & SupContactName.Text & "', '" & SupEmailAddress.Text & "','" & SupAddress.Text & "','" & SupCity.Text & "', '" & SupPost.Text & "')"
256 SupID.Clear()
257 supbusinessname.Clear()
258 SupContactName.Clear()
259 SupEmailAddress.Clear()
260 SupAddress.Clear()
261 SupCity.Clear()
262 SupPost.Clear()
263
264 cmd.ExecuteNonQuery()
265 ' UpdateTable()
266 con.Close()
267 End If
268 End Sub
269
270End Class
271
272Imports ADOX
273Imports System.IO
274Imports System.Data.OleDb
275
276Public Class orders
277 Public ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
278 Dim cat As Catalog = New Catalog()
279 Dim SQLCommand As String
280 Dim con As New OleDbConnection(ConnectionString)
281 Dim cmd As New OleDbCommand
282 Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
283 If Me.Visible() Then
284 Me.Close()
285 Main_Menu.Show()
286 End If
287 End Sub
288
289 Private Sub orders_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
290
291
292 End Sub
293
294 'This sub fills the combobox with information other tables.
295 Private Sub FillComboProduct()
296 Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
297 Dim MyConn As OleDbConnection
298 Dim ds As DataSet
299 Dim tables As DataTableCollection
300 'Dim source1 As New BindingSource
301 Dim da As OleDbDataAdapter
302
303 MyConn = New OleDbConnection
304 MyConn.ConnectionString = ConnString
305 ds = New DataSet
306 tables = ds.Tables
307
308 da = New OleDbDataAdapter("SELECT ProductID from Inventory", MyConn)
309 da.Fill(ds, "Inventory")
310 Dim view As New DataView(tables(0))
311
312 Try
313 With cmbProduct
314
315 .DataSource = ds.Tables("Inventory")
316 .DisplayMember = "ProductID"
317 .ValueMember = "productID"
318
319 .SelectedIndex = 0
320 .AutoCompleteMode = AutoCompleteMode.SuggestAppend
321 .AutoCompleteSource = AutoCompleteSource.ListItems
322 End With
323 Catch ex As Exception
324 MsgBox("No ProductID has been created")
325 End Try
326
327 End Sub
328 Private Sub btnGetIDs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetIDs.Click
329 FillComboProduct()
330 FillComboCustomer()
331 End Sub
332
333 Private Sub FillComboCustomer()
334 Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
335 Dim MyConn As OleDbConnection
336 Dim ds As DataSet
337 Dim tables As DataTableCollection
338
339 Dim da As OleDbDataAdapter
340
341 MyConn = New OleDbConnection
342 MyConn.ConnectionString = ConnString
343 ds = New DataSet
344 tables = ds.Tables
345
346 da = New OleDbDataAdapter("SELECT CustomerID from Customer", MyConn)
347 da.Fill(ds, "Customer")
348 Dim view As New DataView(tables(0))
349
350 Try
351 With cmbCustomer
352
353 .DataSource = ds.Tables("Customer")
354 .DisplayMember = "CustomerID"
355 .ValueMember = "CustomerID"
356
357 .SelectedIndex = 0
358 .AutoCompleteMode = AutoCompleteMode.SuggestAppend
359 .AutoCompleteSource = AutoCompleteSource.ListItems
360 End With
361 Catch ex As Exception
362 MsgBox("No CustomerID has been created")
363 End Try
364 End Sub
365
366
367 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
368 UpdateOrders()
369 End Sub
370
371 Private Sub UpdateOrders()
372 Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
373 Dim MyConn As OleDbConnection
374 Dim ds As DataSet
375 Dim tables As DataTableCollection
376 Dim source1 As New BindingSource
377 Dim da As OleDbDataAdapter
378
379 MyConn = New OleDbConnection
380 MyConn.ConnectionString = ConnString
381 ds = New DataSet
382 tables = ds.Tables
383
384 da = New OleDbDataAdapter("Select * from [Orders]", MyConn)
385 da.Fill(ds, "Orders")
386 Dim view As New DataView(tables(0))
387 source1.DataSource = view
388 DataGridView1.DataSource = view
389 End Sub
390
391 Private Sub AddOrder()
392 Dim con As New OleDbConnection(ConnectionString)
393 Dim cmd As New OleDbCommand
394 If txtOrderID.Text = "" Or txtDateOfP.Text = "" Or cmbCustomer.Text = "" Or cmbProduct.Text = "" Then
395 MsgBox("Please fill in all given fields!")
396
397 Else
398
399 con.Open()
400 cmd.Connection = con
401
402 cmd.CommandText = "INSERT INTO Orders(OrderID,ProductID,CustomerID,DateofPurchase,TimeofPurchase) VALUES ('" & txtOrderID.Text & "','" & cmbProduct.Text & "','" & cmbCustomer.Text & "', '" & txtDateOfP.Text & "','" & txtTimeOfP.Text & "')"
403
404
405 cmd.ExecuteNonQuery()
406 ' UpdateTable()
407 con.Close()
408 End If
409 End Sub
410
411 Private Sub btnAddOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddOrder.Click
412 AddOrder()
413 UpdateOrders()
414 End Sub
415 'This sub allows the user to search through the table and dispays the result
416 Private Sub SearchTable()
417 Dim sqlsearch As String
418 sqlsearch = "SELECT * FROM Order WHERE OrderID = '" & txtOrderID.Text & "'" & " OR ProductID = '" & cmbProduct.Text & "'" & " OR CustomerID = '" & cmbCustomer.Text & "');" & " OR DateofPurchase = '" & txtDateOfP.Text & "'" & " OR TimeofPurchase = '" & txtTimeOfP.Text & "'"
419
420 ' execute the SQL statements against the dataBase
421 Dim adapter As New OleDbDataAdapter(sqlsearch, con)
422 ' Shows the records and updates the DataGridView
423 Dim dt As New DataTable("Order")
424 adapter.Fill(dt)
425 DataGridView1.DataSource = dt
426 End Sub
427
428 Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
429 SearchTable()
430 End Sub
431
432 Private Sub TimeDate()
433 txtTimeOfP.Text = Now.ToShortDateString
434 txtDateOfP.Text = Now.ToShortTimeString
435
436 End Sub
437
438 Private Sub txtDateOfP_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtDateOfP.TextChanged
439
440 End Sub
441
442 Private Sub TimenDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TimenDate.Click
443 TimeDate()
444 End Sub
445 'Grade B (writing to a file)
446 Private Sub PrintToFile()
447
448 ' This goes through the rows and columns of the datagridview and then turns into string and tthen adds it the text file.
449 Dim file As TextWriter = New StreamWriter("N:\Orders.txt")
450
451 For i As Integer = 0 To DataGridView1.Rows.Count - 2 Step +1
452
453 For j As Integer = 0 To DataGridView1.Columns.Count - 1 Step +1
454
455 file.Write(vbTab & DataGridView1.Rows(i).Cells(j).Value.ToString() & vbTab & "|")
456
457 Next
458
459 file.WriteLine("")
460 file.WriteLine("-----------------------------------------------------------------------------------------------------------")
461
462 Next
463 file.Close()
464 MessageBox.Show("Data Exported")
465
466 End Sub
467 ' This sub checks whether the datagrid is empty and if it is, it will tell the user.
468 Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
469 If DataGridView1.Rows.Count > 0 Then
470 PrintToFile()
471 Else
472 MsgBox("Please fill/update the table to continue.")
473
474
475 End If
476
477
478 End Sub
479End Class
480
481Imports ADOX
482Imports System.IO
483Imports System.Data.OleDb
484
485
486
487Public Class Customer
488 Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
489 Dim cat As Catalog = New Catalog()
490 Dim SQLCommand As String
491 Dim con As New OleDbConnection(ConnectionString)
492 Dim cmd As New OleDbCommand
493 Private Sub Customer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
494
495 End Sub
496
497
498 Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
499 Addrow()
500 UpdateTable()
501 End Sub
502
503
504 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
505 UpdateTable()
506 End Sub
507 'This sub gets the latest version of the table from te database and puts it into the datagrid view. It selects every column from the table.
508 Private Sub UpdateTable()
509 Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
510 Dim MyConn As OleDbConnection
511 Dim ds As DataSet
512 Dim tables As DataTableCollection
513 Dim source1 As New BindingSource
514 Dim da As OleDbDataAdapter
515
516 MyConn = New OleDbConnection
517 MyConn.ConnectionString = ConnString
518 ds = New DataSet
519 tables = ds.Tables
520
521 da = New OleDbDataAdapter("Select * from [Customer]", MyConn)
522 da.Fill(ds, "Customer")
523 Dim view As New DataView(tables(0))
524 source1.DataSource = view
525 custdatagrid.DataSource = view
526
527 End Sub
528
529 Private Sub SearchTable()
530
531 'SQL Statement so our User can search for either CustomerID or FirstName
532 Dim sqlsearch As String
533 sqlsearch = "SELECT * FROM Customer WHERE CustomerID = '" & CustID.Text & "'" & " OR FirstName = '" & CustFirstName.Text & "'" & " OR LastName = '" & CustSecondName.Text & "'" & " OR Email = '" & CustEmailAddress.Text & "'" & " OR LastName = '" & CustSecondName.Text & "'" & " OR Address = '" & CustHomeAddress.Text & "'" & " OR City = '" & CustCity.Text & "'" & " OR Postcode = '" & CustPost.Text & "'"
534
535 ' execute the SQL statements against the dataBase
536 Dim adapter As New OleDbDataAdapter(sqlsearch, con)
537 ' Shows the records and updates the DataGridView
538 Dim dt As New DataTable("Customer")
539 adapter.Fill(dt)
540 custdatagrid.DataSource = dt
541
542 End Sub
543
544 Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
545 SearchTable()
546 End Sub
547
548 Private Sub btnCustback_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCustback.Click
549 If Me.Visible() Then
550 Me.Close()
551 Main_Menu.Show()
552 End If
553
554
555 End Sub
556 'This sub deletes the selected row from the datagrid view table and also deletes it from the table in the database.
557 Private Sub DeleteRow()
558 Dim con As New OleDbConnection(ConnectionString)
559 Dim cmd As New OleDbCommand
560
561 con.Open()
562 cmd.Connection = con
563
564 cmd.CommandText = "DELETE * from [Customer] where CustomerID = '" & custdatagrid.SelectedRows(0).Cells(0).Value.ToString() & "'"
565
566 cmd.ExecuteNonQuery()
567 UpdateTable()
568 con.Close()
569
570
571 End Sub
572
573 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
574 DeleteRow()
575 End Sub
576
577 Private Sub Addrow()
578 Dim con As New OleDbConnection(ConnectionString)
579 Dim cmd As New OleDbCommand
580 If CustFirstName.Text = "" Or CustSecondName.Text = "" Or CustEmailAddress.Text = "" Or CustHomeAddress.Text = "" Or CustCity.Text = "" Or CustPostcode.Text = "" Then
581 MsgBox("Please fill in all given fields!")
582
583 Else
584
585 con.Open()
586 cmd.Connection = con
587
588 cmd.CommandText = "INSERT INTO Customer(CustomerID,FirstName,LastName,Email,Address,City,Postcode) VALUES ('" & CustID.Text & "','" & CustFirstName.Text & "', '" & CustSecondName.Text & "', '" & CustEmailAddress.Text & "','" & CustHomeAddress.Text & "','" & CustCity.Text & "', '" & CustPost.Text & "')"
589 CustID.Clear()
590 CustFirstName.Clear()
591 CustSecondName.Clear()
592 CustEmailAddress.Clear()
593 CustHomeAddress.Clear()
594 CustCity.Clear()
595 CustPost.Clear()
596
597 cmd.ExecuteNonQuery()
598 UpdateTable()
599 con.Close()
600
601 End If
602 End Sub
603
604
605 ' This puts all the firstnames into an array and sorts it.
606 Public Sub SortName()
607
608 Dim n As Integer = 0
609 Dim i As Integer = -1
610 Dim Reader As OleDbDataReader
611
612 SQLCommand = "SELECT FirstName FROM Customer"
613 con.Open()
614 cmd.Connection = con
615 cmd.CommandText = (SQLCommand)
616 Reader = cmd.ExecuteReader
617
618 Do While Reader.Read()
619 i = i + 1
620 Loop
621 con.Close()
622
623 Dim ArrayFirstname(i) As String
624 SQLCommand = "SELECT FirstName FROM Customer"
625 con.Open()
626 cmd.Connection = con
627 cmd.CommandText = (SQLCommand)
628 Reader = cmd.ExecuteReader
629
630 Do While Reader.Read()
631 ArrayFirstname = (Reader("FirstName"))
632 n = n + 1
633 Loop
634 con.Close()
635
636 MsgBox(ErrorToString)
637
638 MergeSort(ArrayFirstname)
639
640 For m = 0 To UBound(ArrayFirstname)
641 custdatagrid.Rows.Item(m).Cells(i).Value = ArrayFirstname(m)
642 Next
643
644 For j = 0 To UBound(ArrayFirstname)
645
646 SQLCommand = "SELECT CustomerID,LastName,Email,Address,City,Postcode FROM Customer WHERE FirstName = '" & ArrayFirstname(j) & "'"
647 con.Open()
648 cmd.Connection = con
649 cmd.CommandText = (SQLCommand)
650 Reader = cmd.ExecuteReader
651
652
653 Do While Reader.Read()
654 custdatagrid.Rows.Item(j).Cells(0).Value = (Reader("CustomerID"))
655 custdatagrid.Rows.Item(j).Cells(0).Value = (Reader("LastName"))
656 custdatagrid.Rows.Item(j).Cells(0).Value = (Reader("Email"))
657 custdatagrid.Rows.Item(j).Cells(0).Value = (Reader("Address"))
658 custdatagrid.Rows.Item(j).Cells(0).Value = (Reader("City"))
659 custdatagrid.Rows.Item(j).Cells(0).Value = (Reader("Postcode"))
660
661
662 Loop
663
664 Next
665
666 End Sub
667
668
669
670 'Grade A
671 Public Sub MergeSort(ByRef data1 As String())
672
673 Dim Csize As Integer
674 Dim L As Integer
675
676 Csize = 1
677 While Csize <= data1.Length - 1
678 L = 0
679 While Left < data1.Length - 1
680 Dim Mid As Integer = L + Csize - 1
681 Dim Right As Integer = Math.Min(Left + 2 * Csize - 1, data1.Length - 1)
682
683 Merge(data1, L, Mid, Right)
684 L += 2 * Csize
685 End While
686 Csize = 2 * Csize
687 End While
688
689
690
691 End Sub
692
693 Private Sub btnMergeSort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMergeSort.Click
694 SortName()
695 End Sub
696
697 ' Compares the items and outs them in order
698 Private Sub Merge(ByRef data1 As String(), ByVal L As Integer, ByVal Mid As Integer, ByVal right As Integer)
699
700 Dim i As Integer
701 Dim j As Integer
702 Dim k As Integer
703
704 Dim Nom1 As String = Mid - L + 1
705 Dim Nom2 As String = right - Mid
706
707 Dim leftpointer As String() = New String(Nom1 - 1) {}
708 Dim rightpointer As String() = New String(Nom2 - 1) {}
709
710 For i = 0 To Nom1 - 1
711 leftpointer(i) = data1(L - i)
712 Next
713
714 For j = 0 To Nom2 - 1
715 rightpointer(j) = data1(Mid + 1 + j)
716 Next
717
718
719 i = 0
720 j = 0
721 k = L
722
723 While i < Nom1 AndAlso j < Nom2
724
725 If leftpointer(i) <= rightpointer(j) Then
726 data1(k) = leftpointer(i)
727 i += 1
728 Else
729 data1(k) = rightpointer(j)
730 j += 1
731
732 End If
733
734 k += 1
735 End While
736
737 While i < Nom1
738 data1(k) = leftpointer(i)
739 i += 1
740 k += 1
741 End While
742
743 While j < Nom2
744 data1(k) = rightpointer(j)
745 j += 1
746 k += 1
747 End While
748
749
750 End Sub
751End Class
752
753
754Imports ADOX
755Imports System.IO
756Imports System.Data.OleDb
757
758Public Class Inventory
759 Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
760 Dim cat As Catalog = New Catalog()
761 Dim SQLCommand As String
762 Dim con As New OleDbConnection(ConnectionString)
763 Dim cmd As New OleDbCommand
764 Private Sub Orders_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
765
766 End Sub
767
768 Private Sub btnCustback_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCustback.Click
769 If Me.Visible Then
770 Me.Hide()
771 Main_Menu.Show()
772 End If
773 End Sub
774
775 Private Sub ComboBoxsupplier_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxsupplier.SelectedIndexChanged
776
777 End Sub
778
779 Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
780 UpdateTable()
781 AddInventory()
782
783 End Sub
784
785 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
786 UpdateTable()
787 End Sub
788
789 Private Sub UpdateTable()
790 Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
791 Dim MyConn As OleDbConnection
792 Dim ds As DataSet
793 Dim tables As DataTableCollection
794 Dim source1 As New BindingSource
795 Dim da As OleDbDataAdapter
796
797 MyConn = New OleDbConnection
798 MyConn.ConnectionString = ConnString
799 ds = New DataSet
800 tables = ds.Tables
801
802 da = New OleDbDataAdapter("Select * from [Inventory]", MyConn)
803 da.Fill(ds, "Inventory")
804 Dim view As New DataView(tables(0))
805 source1.DataSource = view
806 Datagridview.DataSource = view
807
808 End Sub
809
810 Private Sub GetSupplierID_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetSupplierID.Click
811 FillCombo()
812
813 End Sub
814
815 Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
816
817 End Sub
818
819 ' This sub fills the combo box/drop drop down
820 Private Sub FillCombo()
821 Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestDatabase.accdb;"
822 Dim MyConn As OleDbConnection
823 Dim ds As DataSet
824 Dim tables As DataTableCollection
825 'Dim source1 As New BindingSource
826 Dim da As OleDbDataAdapter
827
828 MyConn = New OleDbConnection
829 MyConn.ConnectionString = ConnString
830 ds = New DataSet
831 tables = ds.Tables
832
833 da = New OleDbDataAdapter("SELECT SupplierID from Supplier", MyConn)
834 da.Fill(ds, "Supplier")
835 Dim view As New DataView(tables(0))
836
837 Try
838 With ComboBoxsupplier
839
840 .DataSource = ds.Tables("Supplier")
841 .DisplayMember = "SupplierID"
842 .ValueMember = "SupplierID"
843
844 .SelectedIndex = 0
845 .AutoCompleteMode = AutoCompleteMode.SuggestAppend
846 .AutoCompleteSource = AutoCompleteSource.ListItems
847 End With
848 Catch ex As Exception
849 MsgBox("No SupplierID created")
850 End Try
851
852 End Sub
853
854 Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
855 searchtable()
856 End Sub
857
858 ' This sub searches for records in a table.
859 Private Sub searchtable()
860 Dim sqlsearch As String
861 sqlsearch = "SELECT * FROM Inventory WHERE ProductID = '" & ItemID.Text & "'" & " OR ProductName = '" & ItemName.Text & "'" & " OR ItemPrice = '" & ProductPrice.Text & "'" & " OR ItemCost = '" & ProductCost.Text & "'" & " OR ItemQuantity = '" & ProductQuantity.Text & "'"
862
863 ' execute the SQL statements against the dataBase
864 Dim adapter As New OleDbDataAdapter(sqlsearch, con)
865 ' Shows the records and updates the DataGridView
866 Dim dt As New DataTable("Inventory")
867 adapter.Fill(dt)
868 Datagridview.DataSource = dt
869 End Sub
870 ' This sub adds data from the textbox into the table.
871 Private Sub AddInventory()
872 Dim con As New OleDbConnection(ConnectionString)
873 Dim cmd As New OleDbCommand
874 If ItemID.Text = "" Or ItemName.Text = "" Or ProductCost.Text = "" Or ProductPrice.Text = "" Or ProductQuantity.Text = "" Or ComboBoxsupplier.Text = "" Then
875 MsgBox("Please fill in all given fields!")
876
877 Else
878
879 con.Open()
880 cmd.Connection = con
881
882 cmd.CommandText = "INSERT INTO Inventory(ProductID,ProductName,ItemCost,ItemPrice,ItemQuantity,SupplierID) VALUES ('" & ItemID.Text & "','" & ItemName.Text & "', '" & ProductCost.Text & "', '" & ProductPrice.Text & "','" & ProductQuantity.Text & "','" & ComboBoxsupplier.Text & "')"
883 ItemID.Clear()
884 ItemName.Clear()
885 ProductCost.Clear()
886 ProductPrice.Clear()
887 ProductQuantity.Clear()
888
889
890 cmd.ExecuteNonQuery()
891 ' UpdateTable()
892 con.Close()
893 End If
894
895 End Sub
896End Class