HgvAw439

1All code/procedures for project
2Pastebin links:
3clsDBConnector.vb
4https://pastebin.com/65MdKRSQ
5Public variables and misc functions
6https://pastebin.com/iuyNcxGa
7RefreshFunctions
8https://pastebin.com/MbDaVdrS
9SendEmailsModule
10https://pastebin.com/5bYJqWQv
11FindDatabaseFile
12https://pastebin.com/VSMnqjaF
13Login
14https://pastebin.com/1Lxg61Jq
15EmployeeMenu
16https://pastebin.com/TczUBWms
17TechSupportMenu
18https://pastebin.com/XAneXriP
19ReplyToEmployee
20https://pastebin.com/9VszJjTt
21ReplyToEmployeeAdditionalNotes
22https://pastebin.com/gGxegnHB
23NewEmployeeError
24https://pastebin.com/d21qjTN7
25clsDBConnector.vb
261. Imports System.Data.OleDb 'So the public sub can work properly across all forms 2. Public Class clsDBConnector 3. 'These are the only public variables that need to be given so that my public class procedure can 4. 'recall my database connection variables correctly. 5. Dim dbProvider, dbSource, sql As String 6. Dim da, da2 As OleDb.OleDbDataAdapter 7. Dim ds, ds2 As New DataSet 8. Sub connect() 9. 'Declares what connection method to use. MainDatabaseLocation is a text file which 10. 'defines where the database is located 11. 12. dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" 13. con.ConnectionString = dbProvider & MainDatabaseLocation 'Database location defined as the string in the text file 14. con.Open() 'Opens connection 15. 16. End Sub 17. Sub Close() 18. con.Close() 19. End Sub 20. 21. 'These functions are all the same, except that they use different datasets. 22. 'My listRefreshReplies function uses two different datasets at once, so 23. 'I have created another function with the same code that uses dataset 2 instead of 1 24. 25. Function sqltblEmployee_Login_Details(ByVal sql As String) 26. 27. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 28. da = New OleDbDataAdapter(sql, con) 29. 30. 'wipe the dataset clean 31. ds.Clear() 32. 33. 'execute the SQL query and fill the dataset "Results" with the data 34. da.Fill(ds, "tblEmployee_Login_Details") 35. 36. 'send the data back to the calling code 37. Return ds 38. 39. End Function 40. 41. Function sqltblTechSupport_Login_Details(ByVal sql As String) 42. 43. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 44. da = New OleDbDataAdapter(sql, con) 45. 46. 'wipe the dataset clean 47. ds.Clear() 48. 49. 'execute the SQL query and fill the dataset "Results" with the data 50. da.Fill(ds, "tblTechsupport_Login_Details") 51. 52. 'send the data back to the calling code 53. Return ds 54. 55. End Function 56. 57. Function sqlTotalTickets(ByVal sql As String) 58.
2759. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 60. da = New OleDbDataAdapter(sql, con) 61. 62. 'wipe the dataset clean 63. ds.Clear() 64. 65. 'execute the SQL query and fill the dataset "Results" with the data 66. da.Fill(ds, "TotalTickets") 67. 68. 'send the data back to the calling code 69. Return ds 70. 71. End Function 72. 73. Function sqltblSubmitted_Ticket_Details(ByVal sql As String) 74. 75. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 76. da = New OleDbDataAdapter(sql, con) 77. 78. 'wipe the dataset clean 79. ds.Clear() 80. 81. 'execute the SQL query and fill the dataset "Results" with the data 82. da.Fill(ds, "tblSubmitted_Ticket_Details") 83. 84. 'send the data back to the calling code 85. Return ds 86. 87. End Function 88. 89. Function sqltblSubmitted_Ticket_DetailsSql2(ByVal sql2 As String) 90. 91. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 92. da = New OleDbDataAdapter(sql2, con) 93. 94. 'wipe the dataset clean 95. ds.Clear() 96. 97. 'execute the SQL query and fill the dataset "Results" with the data 98. da.Fill(ds, "tblSubmitted_Ticket_Details") 99. 100. 'send the data back to the calling code 101. Return ds 102. 103. End Function 104. 105. Function sqlComputerID(ByVal sql As String) 106. 107. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 108. da = New OleDbDataAdapter(sql, con) 109. 110. 'wipe the dataset clean 111. ds.Clear() 112. 113. 'execute the SQL query and fill the dataset "Results" with the data 114. da.Fill(ds, "ComputerID") 115. 116. 'send the data back to the calling code 117. Return ds 118. 119. End Function 120.
28121. Function sqltblEmployeeDetails(ByVal sql As String) 122. 123. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 124. da = New OleDbDataAdapter(sql, con) 125. 126. 'wipe the dataset clean 127. ds.Clear() 128. 129. 'execute the SQL query and fill the dataset "Results" with the data 130. da.Fill(ds, "tblEmployeeDetails") 131. 132. 'send the data back to the calling code 133. Return ds 134. 135. End Function 136. 137. Function sqlDetails(ByVal sql As String) 138. 139. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 140. da = New OleDbDataAdapter(sql, con) 141. 142. 'wipe the dataset clean 143. ds.Clear() 144. 145. 'execute the SQL query and fill the dataset "Results" with the data 146. da.Fill(ds, "Details") 147. 148. 'send the data back to the calling code 149. Return ds 150. 151. End Function 152. 153. Function sqlNames(ByVal sql As String) 154. 155. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 156. da2 = New OleDbDataAdapter(sql, con) 157. 158. 'wipe the dataset clean 159. ds2.Clear() 160. 161. 'execute the SQL query and fill the dataset "Results" with the data 162. da2.Fill(ds2, "Names") 163. 164. 'send the data back to the calling code 165. Return ds2 166. 167. End Function 168. 169. Function sqltblTechSupport_Details(ByVal sql As String) 170. 171. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 172. da2 = New OleDbDataAdapter(sql, con) 173. 174. 'wipe the dataset clean 175. ds2.Clear() 176. 177. 'execute the SQL query and fill the dataset "Results" with the data 178. da2.Fill(ds2, "tblTechsupport_Details") 179. 180. 'send the data back to the calling code 181. Return ds2 182.
29183. End Function 184. 185. Function sqlTicketSolvedByNames(ByVal sql As String) 186. 187. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 188. da = New OleDbDataAdapter(sql, con) 189. 190. 'wipe the dataset clean 191. ds.Clear() 192. 193. 'execute the SQL query and fill the dataset "Results" with the data 194. da.Fill(ds, "TicketSolvedByNames") 195. 196. 'send the data back to the calling code 197. Return ds 198. 199. End Function 200. 201. Function sqlEmployeeEmailSql2(ByVal sql2 As String) 202. 203. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 204. da = New OleDbDataAdapter(sql2, con) 205. 206. 'wipe the dataset clean 207. ds.Clear() 208. 209. 'execute the SQL query and fill the dataset "Results" with the data 210. da.Fill(ds, "EmployeeEmail") 211. 212. 'send the data back to the calling code 213. Return ds 214. 215. End Function 216. 217. Function sqlNumRecordsSql2(ByVal sql2 As String) 218. 219. 'create a new dataadapter object & tell it to use the SQL statement and connection info provided 220. da = New OleDbDataAdapter(sql2, con) 221. 222. 'wipe the dataset clean 223. ds.Clear() 224. 225. 'execute the SQL query and fill the dataset "Results" with the data 226. da.Fill(ds, "NumRecords") 227. 228. 'send the data back to the calling code 229. Return ds 230. 231. End Function 232. 233. End Class
30Database directory module
311. Module Database_directory_module 2. Public MainDatabaseLocation As String 3. Public Provider As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" 4. Public TextFileForDatabaseLocation As String = "DataBaseLocation.txt" 5. Public MyDocumentsFolder As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) 6. Public con As New OleDb.OleDbConnection 7. Public BackupDatabaseLocation As String = IO.Directory.GetCurrentDirectory() + "\Ticket System Database 2002.mdb" 8. End Module
32Public variables and misc functions
331. Module Public_variables_and_misc_functions 2. Public UserIDDatabase As String 3. Public db As New clsDBConnector 4. Public ds, ds2 As New DataSet 5. 6. Public DatabaseDirectory As String 7. Public DatabaseDirectoryTextFile As String = "DatabaseDirectory.txt" 8. Public con As New OleDb.OleDbConnection 9. 10. 'Public misc functions 11. Public Function lstMyRepliesBubbleSort() 12. 'Bubble sort algorithm to sort listview by newest date first 13. Dim Sorted As Boolean = False 14. 15. Do While Sorted = False 'While the list is not sorted 16. Sorted = True 'The list is sorted until otherwise stated 17. 18. For ItemPosition As Integer = 0 To TechSupportMenu.lstMyReplies.Items.Count - 2 'Go through each item in listv 19. 20. If CDate(TechSupportMenu.lstMyReplies.Items(ItemPosition).SubItems(6).Text) <CDate(TechSupportMenu.lstMyReplies.Items(ItemPosition + 1).SubItems(6).Text) Then 'If first item is smaller than next item 21. 22. Dim tempName As String = TechSupportMenu.lstMyReplies.Items(ItemPosition + 1).Text 'Set the placeholder to hold the current item 23. Dim tempSubItems(5) 'Create an array for the sub items sub item. 5 instead of 6 as array starts at 0 24. For SubItem As Integer = 1 To 6 'For each sub item in the current item
3425. tempSubItems(SubItem - 1) = TechSupportMenu.lstMyReplies.Items(ItemPosition + 1).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem 26. Next 27. 28. TechSupportMenu.lstMyReplies.Items(ItemPosition + 1).Text = TechSupportMenu.lstMyReplies.Items(ItemPosition).Text 'Set the current item text as equal to the next item 29. For SubItem As Integer = 1 To 6 'For each sub item in the current item 30. TechSupportMenu.lstMyReplies.Items(ItemPosition + 1).SubItems(SubItem).Text =TechSupportMenu.lstMyReplies.Items(ItemPosition).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem 31. Next 32. 33. TechSupportMenu.lstMyReplies.Items(ItemPosition).Text = tempName 'Set the next item to be the placeholder (what the old value was) 34. For SubItem As Integer = 1 To 6 'For each subitem in the temporary subitems array 35. TechSupportMenu.lstMyReplies.Items(ItemPosition).SubItems(SubItem).Text = tempSubItems(SubItem - 1) 'Set the array entry to be equal to that of its corresponding temporary subitem 36. Next 37. 'This rotates / swaps the two values 38. Sorted = False 'Show that the list was not sorted and another check needs to be performed 39. End If 40. Next 41. Loop 42. Return TechSupportMenu.lstMyReplies 43. End Function 44. Public Function ReplyChangesSaved() 45. ReplyToEmployee.chkSolved.AutoCheck = False 'Can't change state of checkbox once confirm button is clicked 46. ReplyToEmployee.chkSolved.ForeColor = Color.White 'Color of text turns white again 47. ReplyToEmployee.btnConfirm.Visible = False 'Makes confirm button invisible when clicked, making the 'Edit' button visible again 48. ReplyToEmployee.btnConfirm.Enabled = False 49. ReplyToEmployee.dtpDeadline.Enabled = False 'Makes it so you can't change the value on the combo box once confirm is clicked
3550. Return ReplyToEmployee 51. End Function 52. Public Function ReplyID() 53. 'Feature to have an reply ID that increments based on how many records are in the access table 54. 'But ID would go down for new tickets if records were deleted, causing exceptions 55. 'so feature would cause unneccessary issues since ID feature is working fine already 56. Dim NumRecords As Integer 57. Dim sql, sql2 As String 58. 59. sql = "" 60. 61. sql2 = "SELECT COUNT (*) FROM tblTicket_Reply_Details" 62. db.connect() 63. ds = db.sqlNumRecordsSql2(sql2) 64. db.Close() 65. 66. NumRecords = (ds.Tables("EmployeeEmail").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 67. Return NumRecords 68. End Function 69. End Module
36RefreshFunctions
371. Module RefreshFunctions 2. 3. Public Function RefreshDataGridView() 4. '--------------------REFRESHES DATAGRIDVIEW-------------------- 5. Dim ConnString, dbProvider As String 6. Dim con As OleDb.OleDbConnection 7. Dim da As OleDb.OleDbDataAdapter 'Connects to the database 8. Dim DataTable As DataTableCollection 9. Dim Source As New BindingSource 10. 11. dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" 12. ConnString = dbProvider & MainDatabaseLocation 13. 14. TechSupportMenu.TblSubmitted_Ticket_DetailsTableAdapter.Fill(TechSupportMenu.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details) 15. con = New OleDb.OleDbConnection 16. con.ConnectionString = ConnString 17. ds = New DataSet 18. DataTable = ds.Tables 19. 20. 'da = New OleDb.OleDbDataAdapter("SELECT * FROM tblSubmitted_Ticket_Details", con) 'Selects all items from table 21. Dim sql As String = "SELECT * FROM tblSubmitted_Ticket_Details" 22. da = New OleDb.OleDbDataAdapter(sql, con)
3823. da.Fill(ds, "tblSubmitted_Ticket_Details") 24. 25. Dim view As New DataView(DataTable(0)) 26. Source.DataSource = view 27. TechSupportMenu.grdTickets.DataSource = view 28. 29. 'Sorts DataGridView by Date Ascending 30. With TechSupportMenu.grdTickets 31. TechSupportMenu.grdTickets.Sort(.Columns(6), System.ComponentModel.ListSortDirection.Descending) 32. End With 33. 34. Return TechSupportMenu.grdTickets 35. End Function 36. 37. Public Function RefreshlstMyReplies() 38. 'Variables to connect to database need to be declared here too, to prevent errors 39. Dim recordCount As Integer 'and 'cause it's a function 40. Dim XCounter As Integer = 0 41. Dim sql As String 42. 43. sql = "SELECT tblTicket_Reply_Details.Reply_ID, tblTicket_Reply_Details.Employee_ID, tblTicket_Reply_Details.TechSupport_ID, " _ 44. & "tblTicket_Reply_Details.Reply_Description, tblTicket_Reply_Details.Reply_Date FROM tblTicket_Reply_Details WHERE tblTicket_Reply_Details.TechSupport_ID = '" & UserIDDatabase & "'" 45. db.connect() 46. ds = db.sqlDetails(sql) 47. db.Close() 48. 49. sql = "SELECT TechSupport_FirstName, TechSupport_LastName FROM tblTechSupport_Details WHERE TechSupport_ID = '" & UserIDDatabase &"'" 50. db.connect() 51. ds2 = db.sqlNames(sql) 52. db.Close() 53. 54. '#Region "Add items to listview loop" 55. recordCount = ds.Tables("Details").Rows.Count 56. TechSupportMenu.lstMyReplies.Items.Clear() 'Clears the items in the listview 57. Do Until XCounter = recordCount 58. TechSupportMenu.lstMyReplies.Items.Add(ds.Tables("Details").Rows(XCounter).Item(0)) 59. TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(1)) 60. TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(2)) 61. TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds2.Tables("Names").Rows(0).Item(0)) 'Puts the value 'first name' from the 'Names' dataset containing the techsupport details values 62. TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds2.Tables("Names").Rows(0).Item(1)) 'Puts the value 'last name' from the 'Names' dataset containing the techsupport details values 63. TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(3)) 64. TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(4)) 65. XCounter = XCounter + 1 66. Loop 67. '#End Region# 68. 69. Return TechSupportMenu.lstMyReplies 70. End Function 71. 72. End Module
39SendEmailsModule
401. Module SendEmailsModule 2. 3. Public Function SendEmailTimescaleChangedAddNotes() 4. 'ConnectToDatabase() 5. Dim TicketDetails, ReplyDate, Sql As String 6. ReplyDate = Format(Now, "dd/MM/yyyy HH:mm:ss") 7. 8. TicketDetails = ReplyToEmployeeAdditionalNotes.txtAdditionalDetails.Text 9. 10. Sql = "INSERT INTO tblTicket_Reply_Details (Employee_ID, TechSupport_ID, Reply_Description, Reply_Date) " _ 11. & "VALUES ('" & TechSupportMenu.grdTickets.CurrentRow.Cells(1).Value.ToString() & "','" & UserIDDatabase & "','" &ReplyToEmployeeAdditionalNotes.txtAdditionalDetails.Text & "' , '" & ReplyDate & "')" 12. ReplyToEmployeeAdditionalNotes.Close() 13. db.connect() 14. ds = db.sqltblSubmitted_Ticket_Details(Sql) 15. db.Close() 16. 17. 18. Dim EmployeeNumbers As String 'This code makes a variable to skip an amount of characters in a label, so just the numbers in my label are used in my sql 19. 'ticketnumbers2 is made because otherwise multiple variable declarations will be in the same enclosing block 20. EmployeeNumbers = Mid(ReplyToEmployee.lblEmployeeID.Text, 14, 18) 21. Dim sql2 As String 22. sql2 = "SELECT Employee_Email FROM tblEmployee_Details WHERE Employee_ID = '" & EmployeeNumbers & "'" 23. db.connect() 24. ds = db.sqlEmployeeEmailSql2(sql2) 25. db.Close() 26. 27. Dim EmployeeEmail As String 28. EmployeeEmail = (ds.Tables("EmployeeEmail").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable
4129. 30. 31. Dim Outlook As Microsoft.Office.Interop.Outlook._Application 32. Outlook = New Microsoft.Office.Interop.Outlook.Application 33. 34. Dim Msg As Microsoft.Office.Interop.Outlook.MailItem 35. Msg = Outlook.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem) 36. 37. Msg.Subject = "Details Concerning Your Ticket" 38. Msg.Body = TicketDetails 39. 'Sends custom message where controls = text user puts in 40. Msg.To = EmployeeEmail 41. ' Msg.CC = "fredsls1234@gmail.com" 42. 43. Msg.Send() 44. MessageBox.Show("An Email has also been sent successfully to the Employee!", ReplyToEmployee.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 45. 'Verifies if email has been sent successful or not 46. con.Close() 47. Return ReplyToEmployee.Text 48. End Function 49. 50. Public Function SendEmailTicketSolved() 51. Dim TicketDetails, ReplyDate As String 52. ReplyDate = Format(Now, "dd/MM/yyyy HH:mm") 53. 54. TicketDetails = ReplyToEmployeeAdditionalNotes.txtAdditionalDetails.Text 55. 56. ReplyToEmployeeAdditionalNotes.Close() 57. 58. Dim EmployeeNumbers As String 'This code makes a variable to skip an amount of characters in a label, so just the numbers in my label are used in my sql 59. 'ticketnumbers2 is made because otherwise multiple variable declarations will be in the same enclosing block 60. EmployeeNumbers = Mid(ReplyToEmployee.lblEmployeeID.Text, 14, 18) 61. Dim sql2 As String 62. sql2 = "SELECT Employee_Email FROM tblEmployee_Details WHERE Employee_ID = '" & EmployeeNumbers & "'"
4263. db.connect() 64. ds = db.sqlEmployeeEmailSql2(sql2) 65. db.Close() 66. 67. Dim EmployeeEmail As String 68. EmployeeEmail = (ds.Tables("EmployeeEmail").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 69. 'Connects to outlook------------------------------------------ 70. Dim Outlook As Microsoft.Office.Interop.Outlook._Application 71. Outlook = New Microsoft.Office.Interop.Outlook.Application 72. Dim Msg As Microsoft.Office.Interop.Outlook.MailItem 73. Msg = Outlook.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem) 74. 75. Msg.Subject = "Details Concerning Your Ticket" 76. Msg.Body = "Your ticket has been solved. Thank you for using GE Tech Support." 77. 'Sends custom message where controls = text user puts in 78. Msg.To = EmployeeEmail 79. ' Msg.CC = "fredsls1234@gmail.com" 80. 81. Msg.Send() 82. MessageBox.Show("An Email has also been sent successfully to the Employee!", ReplyToEmployee.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 83. 'Verifies if email has been sent successful or not 84. con.Close() 85. Return ReplyToEmployee.Text 86. End Function 87. 88. Public Function SendEmailTimescaleChangedNoNotes() 89. Dim TicketDetails, ReplyDate, Sql As String 90. ReplyDate = Format(Now, "dd/MM/yyyy HH:mm") 91. 92. TicketDetails = ReplyToEmployeeAdditionalNotes.txtAdditionalDetails.Text 93. 94. Sql = "INSERT INTO tblTicket_Reply_Details (Employee_ID, TechSupport_ID, Reply_Description, Reply_Date) VALUES " _ 95. & "('" & TechSupportMenu.grdTickets.CurrentRow.Cells(1).Value.ToString() & "','" & UserIDDatabase & "', ""Your Ticket is taking longer than expected to solve and your Timescale has been changed. Thank you for your patience!"" , '" & ReplyDate & "')"
4396. ReplyToEmployeeAdditionalNotes.Close() 97. db.connect() 98. ds = db.sqltblSubmitted_Ticket_Details(Sql) 99. db.Close() 100. 101. 102. Dim EmployeeNumbers As String 'This code makes a variable to skip an amount of characters in a label, so just the numbers in my label are used in my sql 103. 'ticketnumbers2 is made because otherwise multiple variable declarations will be in the same enclosing block 104. EmployeeNumbers = Mid(ReplyToEmployee.lblEmployeeID.Text, 14, 18) 105. Dim sql2 As String 106. sql2 = "SELECT Employee_Email FROM tblEmployee_Details WHERE Employee_ID = '" & EmployeeNumbers & "'" 107. db.connect() 108. ds = db.sqlEmployeeEmailSql2(sql2) 109. db.Close() 110. 111. Dim EmployeeEmail As String 112. EmployeeEmail = (ds.Tables("EmployeeEmail").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 113. 114. 115. Dim Outlook As Microsoft.Office.Interop.Outlook._Application 116. Outlook = New Microsoft.Office.Interop.Outlook.Application 117. 118. Dim Msg As Microsoft.Office.Interop.Outlook.MailItem 119. Msg = Outlook.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem) 120. 121. Msg.Subject = "Details Concerning Your Ticket" 122. Msg.Body = "Your ticket is taking slightly longer than usual to solve and we have extended the deadline. Please see your sent tickets to see the new deadline." 123. 'Sends custom message where controls = text user puts in 124. Msg.To = EmployeeEmail 125. ' Msg.CC = "fredsls1234@gmail.com" 126. 127. Msg.Send()
44128. MessageBox.Show("An Email has also been sent successfully to the Employee!", ReplyToEmployee.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 129. 'Verifies if email has been sent successful or not 130. Return ReplyToEmployee.Text 131. End Function 132. 133. Public Function SendEmailToCorrectEmployee() 134. Dim EmployeeNumbers, EmployeeEmail, sql2, TicketDetails As String 'This code makes a variable to skip an amount of characters in a label, so just the numbers in my label are used in my sql 135. 'ticketnumbers2 is made because otherwise multiple variable declarations will be in the same enclosing block 136. 137. EmployeeNumbers = Mid(ReplyToEmployee.lblEmployeeID.Text, 14, 18) 138. TicketDetails = ReplyToEmployeeAdditionalNotes.txtAdditionalDetails.Text 139. con.Open() 140. sql2 = "SELECT Employee_Email FROM tblEmployee_Details WHERE Employee_ID = '" & EmployeeNumbers & "'" 141. db.connect() 142. ds = db.sqlEmployeeEmailSql2(sql2) 143. db.Close() 144. 145. EmployeeEmail = (ds.Tables("EmployeeEmail").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 146. 147. 148. Dim Outlook As Microsoft.Office.Interop.Outlook._Application 149. Outlook = New Microsoft.Office.Interop.Outlook.Application 150. 151. Dim Msg As Microsoft.Office.Interop.Outlook.MailItem 152. Msg = Outlook.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem) 153. 154. Msg.Subject = "Details Concerning Your Ticket" 155. Msg.Body = TicketDetails 156. 'Sends custom message where controls = text user puts in 157. Msg.To = EmployeeEmail 158.
45159. Msg.Send() 160. MessageBox.Show("An Email has also been sent successfully to the Employee!", ReplyToEmployee.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 161. 'Verifies if email has been sent successful or not 162. Return ReplyToEmployee.Text 163. End Function 164. 165. Public Function SendEmailToAllTechSupport() 166. Dim sql As String = "SELECT TechSupport_Email FROM tblTechSupport_Details" 167. db.connect() 168. ds = db.sqlTechSupportEmail(sql) 169. db.Close() 170. Return ds 171. End Function 172. End Module
46FindDatabaseFile
471. Public Class FindDatabaseFile 2. 3. Private Sub btnContinue_Click(sender As Object, e As EventArgs) Handles btnContinue.Click 4. If IO.File.Exists(DatabaseDirectoryTextFile) = True Then 'if the text file exists, then 5. Dim WriteToTextFile As New IO.StreamWriter(DatabaseDirectoryTextFile) 'make new file writer 6. WriteToTextFile.Write(txtDatabaseLocation.Text) 'copy directory in textbox 7. WriteToTextFile.Close() 'close the writer 8. 9. Dim ReadToTextFile As New IO.StreamReader(DatabaseDirectoryTextFile) 'new text reader 10. DatabaseDirectory = ReadToTextFile.ReadToEnd 'put the dbsource as what's in the text file 11. ReadToTextFile.Close() 'close reader 12. Login.Show() 13. Close() 14. End If 15. End Sub 16.
4817. Private Sub btnLocateFile_Click(sender As Object, e As EventArgs) Handles btnLocateFile.Click 18. ofdDatabaseDirectory.Title = "Find and select the .mdb Access Database file" 19. ofdDatabaseDirectory.ShowDialog() 20. End Sub 21. 22. Private Sub LocateDatabaseFile_Load(sender As Object, e As EventArgs) Handles MyBase.Load 23. Me.CenterToScreen() 'Opens form in the centre of the screen 24. Login.Close() 25. btnLocateFile.Visible = True 26. 27. If IO.File.Exists(DatabaseDirectoryTextFile) = True Then 'If the database location text file exists then 28. Dim ReadToTextFile As New IO.StreamReader(DatabaseDirectoryTextFile) 'creates a streamreader which reads the content/string in the text file 29. txtDatabaseLocation.Text = ReadToTextFile.ReadToEnd 'show the current saved location 30. ReadToTextFile.Close() 'close textfilereader 31. Else 'If the text file doesn't exist: 32. Try 33. IO.File.Create(DatabaseDirectoryTextFile).Dispose() 'Create a text file in ...\debug folder of project 34. Catch ex As Exception 35. MessageBox.Show(ex.Message) 36. Exit Sub 37. End Try 38. End If 39. End Sub 40. 41. Private Sub ofdDatabaseLocation_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles ofdDatabaseDirectory.FileOk 42. txtDatabaseLocation.ReadOnly = False 'text can be input in the text box 43. txtDatabaseLocation.Text = ofdDatabaseDirectory.FileName 'the selected filename of the .mdb file is put in the text box to point to the file 44. txtDatabaseLocation.ReadOnly = True 'No user input allowed in the text box 45. End Sub 46. End Class
49Login
501. Public Class Login 2. 3. Private Sub Login_Load(sender As Object, e As EventArgs) Handles MyBase.Load 4. Dim Provider As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" 5. Me.CenterToScreen() 6. 'If the text file exists 7. If IO.File.Exists(DatabaseDirectoryTextFile) = True Then 8. Dim ReadToTextFile As New IO.StreamReader(DatabaseDirectoryTextFile) 'the streamreader reads the string within the text file 9. DatabaseDirectory = ReadToTextFile.ReadToEnd 'dbsource = the string/directory in the text file 10. ReadToTextFile.Close() 'close reader 11. 'if the text file doesn't exist: 12. Else 13. IO.File.Create(DatabaseDirectoryTextFile).Dispose() 'creates a new text file 14. FindDatabaseFile.Show() 'opens this form so the database can be found and its directory put in the text file 15. End If 16. Try 17. con.ConnectionString = Provider & DatabaseDirectory 18. con.Open() 'open connection 19. 20. Catch ex As Exception 21. MessageBox.Show(ex.Message) 'Error message to display "database not found" error 22. FindDatabaseFile.Show() 'If text file not found then open this form 23. End Try 24. 25. Try 26. con.Close() 27. Catch ex As Exception 28. MessageBox.Show(ex.Message And "The program will now close") 29. Close() 30. End Try 31. Me.AcceptButton = btnLogin 'Makes enter key press the login button 32. End Sub 33.
5134. Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click 35. 36. Dim sql As String 37. Dim recordCount As Integer 38. Dim UserID As String 39. Dim Password As String 40. 41. UserID = txtUser_ID.Text 'Putting the userid and password into variables just in case to prevent errors 42. Password = txtPassword.Text 43. 44. If chkTechSupport.Checked = True Then 'If checkbox 'I am techsupport' is ticked, then only read off the techsupport login table 45. 46. sql = "SELECT * FROM tblTechSupport_Login_Details WHERE TechSupport_ID = '" & UserID & "' AND TechSupport_Password = '" &Password & "'" 47. db.connect() 48. ds = db.sqltblTechSupport_Login_Details(sql) 49. db.Close() 50. 51. recordCount = ds.Tables("tblTechSupport_Login_Details").Rows.Count 52. 53. '------------------------------------------------------------------------------------ 54. If recordCount = 0 Then 55. Dim result As Integer = MsgBox("Invalid User_ID or Password, do you want to email TechSupport so they can veryify your details and add you to the system?", MessageBoxButtons.YesNo) 56. If result = DialogResult.Yes Then '^ Declares result as a msgbox with yes and no buttons 57. NewEmployeeError.Show() 'If Yes button on msgbox is clicked, new form opens so user can email TechSupport 58. Me.Hide() 59. End If 60. Else 61. UserIDDatabase = txtUser_ID.Text 'Stores ID so it can be put into the access table later when sending tickets/replies
5262. Me.Hide() 'If info correct, hide login form, open techsupportmenu form and clear login text boxes for when user logs out 63. TechSupportMenu.Show() 64. txtUser_ID.Clear() 65. txtPassword.Clear() 66. End If 67. '------------------------------------------------------------------------------------- 68. 69. ElseIf chkTechSupport.Checked = False Then 70. 71. 'But if checkbox not ticked, read off of the employeelogindetails table 72. sql = "SELECT * FROM tblEmployee_Login_Details WHERE Employee_ID = '" & UserID & "' AND Employee_Password = '" & Password & "'" 73. db.connect() 74. ds = db.sqltblEmployee_Login_Details(sql) 75. db.Close() 76. recordCount = ds.Tables("tblEmployee_Login_Details").Rows.Count 77. 78. '---------------------------------------------------------------------------------- 79. 80. If recordCount = 0 Then 81. Dim result As Integer = MsgBox("Invalid User_ID or Password, do you want to email TechSupport so they can veryify your details and add you to the system?", MessageBoxButtons.YesNo) 82. If result = DialogResult.Yes Then 83. NewEmployeeError.Show() 'If Yes button on msgbox is clicked, new form opens so user can email TechSupport 84. Me.Hide() 85. End If 86. Else 87. UserIDDatabase = txtUser_ID.Text 'Stores ID so it can be put into the access table later when sending tickets/replies 88. Me.Hide() 'If info correct, hide login form, open employeemenu form and clear login text boxes for when user logs out 89. EmployeeMenu.Show() 90. txtUser_ID.Clear()
5391. txtPassword.Clear() 92. End If 93. 94. '-------------------------------------------------------------------------------- 95. 96. End If 97. If chkTechSupport.Checked = True Then 98. If recordCount > 1 Then 99. con.Close() 100. con.Open() 101. sql = "SELECT * FROM tblTechSupport_Login_Details WHERE TechSupport_ID = '" & UserID & "' AND TechSupport_Password = '" &Password & "'" 102. End If 103. End If 104. 105. If chkTechSupport.Checked = True Then 'fix for database to only load correct tables when techsupport is checked/unchecked 106. If recordCount > 1 Then 107. con.Close() 108. con.Open() 109. sql = "SELECT * FROM tblEmployee_Login_Details WHERE Employee_ID = '" & UserID & "' AND Employee_Password = '" & Password &"'" 110. con.Close() 111. End If 112. End If 113. 114. End Sub 115. 116. Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click 117. End 118. End Sub 119. End Class
54EmployeeMenu
551. Public Class EmployeeMenu 2. 3. Private Sub btnLogout_Click(sender As Object, e As EventArgs) Handles btnLogout.Click 4. con.Close() 5. Me.Close()
566. Login.Show() 7. Login.Refresh() 8. End Sub 9. 10. Private Sub cmbSTTitles_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbSTTitles.SelectedIndexChanged 11. txtTitle.Clear() 12. 13. If cmbSTTitles.Text = "Other" Then 14. txtTitle.Enabled = True 'show and enable custom title text box when other is selected 15. txtTitle.Visible = True 16. Else 17. txtTitle.Visible = False 'hide and disable custom title text box when other isn't selected 18. txtTitle.Enabled = False 19. End If 20. End Sub 21. 22. Private Sub EmployeeMenu_Load(sender As Object, e As EventArgs) Handles MyBase.Load 23. 24. Me.CenterToScreen() 'Opens form in the centre of the screen 25. Dim TotalTickets As String 26. 27. Dim sql As String 28. sql = "SELECT COUNT(*) FROM tblSubmitted_Ticket_Details WHERE Employee_ID = '" & UserIDDatabase & "'" 'Counts number of tickets send to display "x number Of tickets have been sent" 29. db.connect() 30. ds = db.sqlTotalTickets(sql) 31. 32. db.Close() 33. 34. TotalTickets = (ds.Tables("TotalTickets").Rows(0).Item(0)) 35. lblOverviewAmountSent.Text = TotalTickets & " tickets have been sent in total" 36. 37. LoadSentTickets() 38. lstSentTicketsBubbleSort() 'Bubble sort algorithm to sort lstSentTickets listview by most recent date 39. LoadLastTicket() 40. LoadTicketReplies() 41. lstTicketRepliesBubbleSort() 'Bubble sort algorithm to sort lstTicketReplies listview by most recent date 42. con.Close() 43. 44. End Sub 45. 'Below are the functions to display the sent tickets, last ticket sent, and the ticket replies from the access tables into 46. 'three different listview controls. I have put this into a function so that I can recall it without typing out the 47. 'whole code again, making the code more efficient and being more time efficient for me: the programmer 48. 49. Public Function LoadSentTickets() 50. Dim recordCount As Integer 51. Dim XCounter As Integer = 0 52. 53. Dim sql As String = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details ORDER BY Ticket_Date ASC" 'Function to load data into listview ordered by the time 54. db.connect() 55. ds = db.sqltblSubmitted_Ticket_Details(sql) 56. db.Close() 57. 58. recordCount = ds.Tables("tblSubmitted_Ticket_Details").Rows.Count 59. lstSentTickets.Items.Clear()
5760. Do Until XCounter = recordCount 61. lstSentTickets.Items.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(0)) 62. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(1)) 63. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(2)) 64. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(3)) 65. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(4)) 66. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(5)) 67. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(6)) 68. XCounter = XCounter + 1 69. Loop 70. 71. Return lstSentTickets.Text 72. End Function 73. Public Function LoadLastTicket() 74. Dim recordCount As Integer 75. Dim XCounter As Integer = 0 76. 77. Dim sql As String = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM " _ 78. & "tblSubmitted_Ticket_Details WHERE Employee_ID = '" & UserIDDatabase & "' ORDER BY Ticket_Date DESC" 'Function to load data into listview ordered by the time 79. db.connect() 80. ds = db.sqltblSubmitted_Ticket_Details(sql) 81. db.Close() 82. 83. recordCount = ds.Tables("tblSubmitted_Ticket_Details").Rows.Count 84. If (ds.Tables("tblSubmitted_Ticket_Details").Rows.Count) = 0 Then 85. 86. Else 87. lstLastTicket.Items.Clear() 88. 'Do Until XCounter = recordCount 89. lstLastTicket.Items.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(0)) 90. lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(1)) 91. lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(2)) 92. lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(3)) 93. lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(4)) 94. lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(5)) 95. lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(6)) 96. XCounter = XCounter + 1 97. ' Loop 'No loop = no more than 1 record showing 98. End If 99. 100. Return lstLastTicket.Text 101. End Function 102. Public Function LoadTicketReplies() 103. 104. Dim recordCount As Integer 105. Dim XCounter As Integer = 0 106. 107. 108. Dim sql As String = "SELECT Reply_ID, TechSupport_ID, Reply_Description, Reply_Date FROM tblTicket_Reply_Details " _
58109. & "WHERE tblTicket_Reply_Details.Employee_ID = '" & UserIDDatabase & "'" 110. 111. db.connect() 112. ds = db.sqlDetails(sql) 113. db.Close() 114. 115. '#Region "Add items to listview loop" 116. recordCount = ds.Tables("Details").Rows.Count 117. lstTicketReplies.Items.Clear() 118. Do Until XCounter = recordCount 119. lstTicketReplies.Items.Add(ds.Tables("Details").Rows(XCounter).Item(0)) 120. lstTicketReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(1)) 121. lstTicketReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(2)) 122. lstTicketReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(3)) 123. 124. XCounter = XCounter + 1 125. Loop 126. '#End Region 127. 128. Return lstTicketReplies 129. End Function 130. 131. Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click 132. '#Region "Control Variables" 133. Dim Title, TitleOther, ComputerID, Department, DepartmentOther, Desc, SystemDate, TicketDeadline As String 134. Dim NullDate As Date 135. NullDate = #12/31/9999 11:59:59# 136. Title = cmbSTTitles.Text 137. TitleOther = txtTitle.Text 138. Desc = txtSTDescription.Text 139. SystemDate = lblSTDate.Text 140. TicketDeadline = dtpDeadline.Value.ToString 141. Department = cmbDepartment.Text 142. DepartmentOther = txtDepartmentOther.Text 143. 'Declaring the controls on the submit ticket screen (variables) 144. '#End Region 145. 146. '#Region "PRESENCE CHECKS FOR ALL POSSIBLE COMBINATIONS WITH SQLs" 147. 148. 'if the combobox is "Other" then use the sql to include the custom text box instead of the combo box 149. 'but if the combolist isn't "other" or "" then it is everything else in there, so if all of the other text is filled in, the sql 150. 'without the custom text box is used as the items in the combobox is recognised as anything that isn't "other" and "" 151. 152. 153. If cmbSTTitles.Text = "" Or cmbDepartment.Text = "" Or txtSTDescription.Text = "" Or dtpDeadline.Value < SystemDate Then 154. MsgBox("You haven't filled in all of the required fields, please recheck your submission.") 'If something hasn't been filled in, tell the user and do not send a ticket 155. ElseIf cmbSTTitles.Text = "Other" And (txtTitle.Text = "" Or txtSTDescription.Text = "" Or dtpDeadline.Value < SystemDate) Then 156. MsgBox("You haven't filled in all of the required fields, please recheck your submission.") 157. ElseIf cmbDepartment.Text = "Other" And (txtSTDescription.Text = "" Or dtpDeadline.Value < SystemDate) Then 158. MsgBox("You haven't filled in all of the required fields, please recheck your submission.")
59159. ElseIf cmbSTTitles.Text = "Other" And cmbDepartment.Text = "Other" And (txtTitle.Text = "" Or txtDepartmentOther.Text = "" Or txtSTDescription.Text = "") Then 160. MsgBox("You haven't filled in all of the required fields, please recheck your submission.") 161. ElseIf cmbSTTitles.Text = "Other" And cmbDepartment.Text = "Other" Then 162. 163. 164. Dim sql As String 165. sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'" 166. db.connect() 167. ds = db.sqlComputerID(sql) 168. db.Close() 169. 170. ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 171. 172. Dim sql2 As String 173. sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Ticket_Title, Department, Ticket_Description, Ticket_Date, Ticket_Deadline, " _ 174. & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & TitleOther & " ',' " & DepartmentOther & " ',' "& Desc & "','" & SystemDate & "','" & TicketDeadline & "','" & NullDate & "')" 175. db.connect() 176. ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2) 177. db.Close() 178. 179. MsgBox("Ticket sent successfully!") 180. tabOverview.Show() 'Automatically navigates back to the homescreen 181. ClearSendTicketFields() 'Delete the text in the fields so nothing is in them 182. 183. ElseIf cmbSTTitles.Text = "Other" Then 184. Dim sql As String 185. sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'" 186. db.connect() 187. ds = db.sqlComputerID(sql) 188. db.Close() 189. 190. ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 191. 192. Dim sql2 As String 193. sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, " _ 194. & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & Department & "','" & TitleOther & "','" & Desc &"','" & SystemDate & "','" & TicketDeadline & "','" & NullDate & "')" 195. db.connect() 196. ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2) 197. db.Close() 198. 199. MsgBox("Ticket sent successfully!") 200. tabOverview.Show() 'Automatically navigates back to the homescreen 201. ClearSendTicketFields() 'Delete the text in the fields so nothing is in them 202. 203. ElseIf cmbDepartment.Text = "Other" Then 204. Dim sql As String 205. sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'" 206. db.connect() 207. ds = db.sqlComputerID(sql) 208. db.Close()
60209. ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 210. 211. Dim sql2 As String 212. sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, " _ 213. & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & DepartmentOther & "','" & Title & " ',' " & Desc & "','" & SystemDate & "','" & TicketDeadline & "','" & NullDate & "')" 214. db.connect() 215. ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2) 216. db.Close() 217. 218. MsgBox("Ticket sent successfully!") 219. tabOverview.Show() 'Automatically navigates back to the homescreen 220. ClearSendTicketFields() 'Delete the text in the fields so nothing is in them 221. 222. Else 223. 224. Dim sql As String 225. sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'" 226. db.connect() 227. ds = db.sqlComputerID(sql) 228. db.Close() 229. 230. ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0)) 'Navigates to value in ds array and stores it as a string variable 231. 232. Dim sql2 As String 233. sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, " _ 234. & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & Department & "','" & Title & "','" & Desc & "','"& SystemDate & "','" & TicketDeadline & "','" & NullDate & "')" 235. db.connect() 236. ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2) 237. db.Close() 238. 239. MsgBox("Ticket sent successfully!") 240. tabOverview.Show() 'Automatically navigates back to the homescreen 241. ClearSendTicketFields() 'Delete the text in the fields so nothing is in them 242. 243. End If 244. '#End Region 245. 246. LoadSentTickets() 247. lstSentTicketsBubbleSort() 248. LoadLastTicket() 249. con.Close() 250. 251. End Sub 252. 253. Private Sub tmrDate_Tick(sender As Object, e As EventArgs) Handles tmrDate.Tick 254. lblSTDate.Text = Format(Now, "dd/MM/yyyy HH:mm") 'Shows current system date - updates once per second 255. lblOverviewDate.Text = "The current date is " & Format(Now, "dd/MM/yyyy HH:mm:ss") 'Shows current system date along with "the current time is" - updates once per second 256. End Sub 257. 258. Private Sub btnLoadReplies_Click(sender As Object, e As EventArgs) 259. LoadSentTickets() 260. lstSentTicketsBubbleSort()
61261. End Sub 262. 263. Private Sub cmbDepartment_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbDepartment.SelectedIndexChanged 264. txtDepartmentOther.Clear() 265. 266. If cmbDepartment.Text = "Other" Then 267. txtDepartmentOther.Enabled = True 268. txtDepartmentOther.Visible = True 269. Else 'Department combobox will only be visible when "Other" is selected from "Area of business/department" 270. txtDepartmentOther.Enabled = False 271. txtDepartmentOther.Visible = False 272. End If 273. End Sub 274. 275. Private Sub btnMyTicketSearch_Click(sender As Object, e As EventArgs) Handles btnMyTicketSearch.Click 276. SearchSentTickets() 277. End Sub 278. 279. Private Sub txtTicketSearchOther_TextChanged(sender As Object, e As EventArgs) Handles txtTicketSearchOther.TextChanged 280. SearchSentTickets() 281. End Sub 282. 283. Private Sub cmbTicketSearch_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbTicketSearch.SelectedIndexChanged 284. txtTicketSearchOther.Clear() 285. 286. Dim TicketSearch As String = cmbTicketSearch.Text 287. Dim TicketSearchOther As String = txtTicketSearchOther.Text 288. 289. If cmbTicketSearch.Text = "Other" Then 290. txtTicketSearchOther.Enabled = True 291. txtTicketSearchOther.Visible = True 292. Else 293. txtTicketSearchOther.Enabled = False 294. txtTicketSearchOther.Visible = False 295. End If 296. SearchSentTickets() 297. End Sub 298. 299. Private Sub btnRefreshReplies_Click(sender As Object, e As EventArgs) Handles btnRefreshReplies.Click 300. LoadTicketReplies() 301. lstTicketRepliesBubbleSort() 302. End Sub 303. 304. 'Functions to be recalled so code is more efficient and not repeated 305. Function SearchSentTickets() 306. 307. Dim sql As String 308. Dim recordCount As Integer 309. Dim XCounter As Integer = 0 310. Dim TicketSearch As String = cmbTicketSearch.Text 311. Dim TicketSearchOther As String = txtTicketSearchOther.Text 312. 313. 314. If cmbTicketSearch.Text = "Other" Then 315. 316. sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details WHERE Ticket_Title LIKE '%" & TicketSearchOther & "%' ORDER BY Ticket_Date ASC" 317. ElseIf cmbTicketSearch.Text = "All" Then
62318. sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details ORDER BY Ticket_Date ASC" 319. Else 320. sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details WHERE Ticket_Title LIKE '%" & TicketSearch & "%' ORDER BY Ticket_Date ASC" 321. End If 322. 323. 'listview will load records from the text in the combobox but if 'other' is selected in the combo box it will search by the textbox instead 324. If cmbTicketSearch.Text = "" Then 325. sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details ORDER BY Ticket_Date ASC" 326. End If 327. db.connect() 328. ds = db.sqltblSubmitted_Ticket_Details(sql) 329. db.Close() 330. 331. 'If nothing is typed in, then the listview will load all records from the table 332. '#Region "Add items to list view loop" 333. 334. recordCount = ds.Tables("tblSubmitted_Ticket_Details").Rows.Count 335. lstSentTickets.Items.Clear() 336. Do Until XCounter = recordCount 337. lstSentTickets.Items.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(0)) 338. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(1)) 339. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(2)) 340. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(3)) 341. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(4)) 342. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(5)) 343. lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(6)) 344. XCounter = XCounter + 1 345. Loop 346. '#End Region 347. con.Close() 'Closes the connection 348. lstSentTicketsBubbleSort() 'After the items are re-added to the listview, sort them again using this bubble sort algorithm 349. 350. Return lstSentTickets 351. End Function 352. 353. Function lstSentTicketsBubbleSort() 354. 'Bubble sort algorithm to sort listview by newest date first 355. Dim Sorted As Boolean = False 356. 357. Do While Sorted = False 'While the list is not sorted 358. Sorted = True 'The list is sorted until otherwise stated 359. 360. For ItemPosition As Integer = 0 To lstSentTickets.Items.Count - 2 'Go through each item in listv 361. 362. If CDate(lstSentTickets.Items(ItemPosition).SubItems(4).Text) < CDate(lstSentTickets.Items(ItemPosition +1).SubItems(4).Text) Then 'If first item is smaller than next item 363.
63364. Dim tempName As String = lstSentTickets.Items(ItemPosition + 1).Text 'Set the placeholder to hold the current item 365. Dim tempSubItems(3) 'Create an array for the sub items sub item. 5 instead of 6 as array starts at 0 366. For SubItem As Integer = 1 To 4 'For each sub item in the current item 367. tempSubItems(SubItem - 1) = lstSentTickets.Items(ItemPosition + 1).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem 368. Next 369. 370. lstSentTickets.Items(ItemPosition + 1).Text = lstSentTickets.Items(ItemPosition).Text 'Set the current item text as equal to the next item 371. For SubItem As Integer = 1 To 4 'For each sub item in the current item 372. lstSentTickets.Items(ItemPosition + 1).SubItems(SubItem).Text = lstSentTickets.Items(ItemPosition).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem 373. Next 374. 375. lstSentTickets.Items(ItemPosition).Text = tempName 'Set the next item to be the placeholder (what the old value was) 376. For SubItem As Integer = 1 To 4 'For each subitem in the temporary subitems array 377. lstSentTickets.Items(ItemPosition).SubItems(SubItem).Text = tempSubItems(SubItem - 1) 'Set the array entry to be equal to that of its corresponding temporary subitem 378. Next 379. 'This rotates / swaps the two values 380. Sorted = False 'Show that the list was not sorted and another check needs to be performed 381. End If 382. Next 383. Loop 384. Return lstSentTickets 385. End Function 386. 387. Function lstTicketRepliesBubbleSort() 388. 'Bubble sort algorithm to sort listview by newest date first 389. Dim Sorted As Boolean = False 390. 391. Do While Sorted = False 'While the list is not sorted 392. Sorted = True 'The list is sorted until otherwise stated 393. 394. For ItemPosition As Integer = 0 To lstTicketReplies.Items.Count - 2 'Go through each item in listv 395. 396. If CDate(lstTicketReplies.Items(ItemPosition).SubItems(3).Text) < CDate(lstTicketReplies.Items(ItemPosition +1).SubItems(3).Text) Then 'If first item is smaller than next item 397. 398. Dim tempName As String = lstTicketReplies.Items(ItemPosition + 1).Text 'Set the placeholder to hold the current item 399. Dim tempSubItems(3) 'Create an array for the sub items sub item. 5 instead of 6 as array starts at 0 400. For SubItem As Integer = 1 To 3 'For each sub item in the current item 401. tempSubItems(SubItem - 1) = lstTicketReplies.Items(ItemPosition + 1).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem 402. Next 403. 404. lstTicketReplies.Items(ItemPosition + 1).Text = lstTicketReplies.Items(ItemPosition).Text 'Set the current item text as equal to the next item 405. For SubItem As Integer = 1 To 3 'For each sub item in the current item
64406. lstTicketReplies.Items(ItemPosition + 1).SubItems(SubItem).Text = lstTicketReplies.Items(ItemPosition).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem 407. Next 408. 409. lstTicketReplies.Items(ItemPosition).Text = tempName 'Set the next item to be the placeholder (what the old value was) 410. For SubItem As Integer = 1 To 3 'For each subitem in the temporary subitems array 411. lstTicketReplies.Items(ItemPosition).SubItems(SubItem).Text = tempSubItems(SubItem - 1) 'Set the array entry to be equal to that of its corresponding temporary subitem 412. Next 413. 'This rotates / swaps the two values 414. Sorted = False 'Show that the list was not sorted and another check needs to be performed 415. End If 416. Next 417. Loop 418. Return lstTicketReplies 419. End Function 420. 421. Function ClearSendTicketFields() 422. txtDepartmentOther.Clear() 423. cmbSTTitles.SelectedIndex = 0 424. txtTitle.Clear() 425. txtSTDescription.Clear() 426. Return TechSupportMenu 427. End Function 428. 429. End Class
65TechsupportMenu
661. Imports Microsoft.Office.Interop 2. Imports System.IO 3. 4. Public Class TechSupportMenu 5. 6. Private Sub btnLogout_Click(sender As Object, e As EventArgs) Handles btnLogout.Click 7. Me.Close() 'This button closes the current form and returns to the login screen 8. Login.Show() 9. End Sub 10. 11. Private Sub TechSupportMenu_Load(sender As Object, e As EventArgs) Handles MyBase.Load 12. Me.CenterToScreen() 'Opens in centre of the screen 13. 14. 'This line of code loads data into the 'Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details' table. 15. Me.TblSubmitted_Ticket_DetailsTableAdapter.Fill(Me.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details) 16. 17. '#Region "Load first graph" 18. '-----------Loads first graph----------- 19. 20. Dim sql As String = "SELECT * FROM tblSubmitted_Ticket_Details" 21. db.connect() 22. ds = db.sqltblSubmitted_Ticket_Details(sql)
6723. db.Close() 24. 25. Dim Solved As Integer = 0 26. Dim Unsolved As Integer = 0 27. 28. For Each dataa As DataRow In ds.Tables("tblSubmitted_Ticket_Details").Rows 29. 30. If dataa.Item("Ticket_Solved") = True Then 31. Solved += 1 32. Else 33. Unsolved += 1 34. End If 35. 36. Next 37. With chrTicketsSolved.Series(0) 38. .Points(0).YValues.SetValue(Solved, 0) 'Set first value to be tickets solved 39. .Points(1).YValues.SetValue(Unsolved, 0) 40. End With 41. '#End Region 42. 43. 44. '#Region "Load second graph" 45. '-----------Load Second graph----------- 46. 47. Dim Solved2 As Integer = 0 48. Dim Unsolved2 As Integer = 0 49. 50. For Each dataa As DataRow In ds.Tables("tblSubmitted_Ticket_Details").Rows 51. 52. If dataa.Item("Ticket_Solved") = True Then 53. Solved2 += 1 54. Else 55. Unsolved2 += 1 56. End If 57. 58. Next 59. With chrTicketsSolved2.Series(0) 60. .Points(0).YValues.SetValue(Solved2, 0) 'Set first value to be tickets solved 61. .Points(1).YValues.SetValue(Unsolved2, 0) 62. End With 63. '------------------------------------- 64. '#End Region 65. 66. 67. '#Region "Combo boxes staff loop" 68. '-----------Loop to add staff from access table into both combo boxes----------- 69. cmbGraphStaff.Items.Clear() 70. cmbGraphStaff.Items.Add("All Staff") 71. 72. cmbGraphStaff2.Items.Clear() 73. cmbGraphStaff2.Items.Add("All Staff") 74. 75. sql = "SELECT * FROM tblTechSupport_Details" 76. db.connect() 77. ds = db.sqltblTechSupport_Details(sql) 78. db.Close() 79. 80. For Each Staff As DataRow In ds.Tables("tblTechSupport_Details").Rows 81. Dim Employee As String = Staff.Item("TechSupport_ID") 82. Employee &= " " & Staff.Item("TechSupport_FirstName") 83. 84. Employee &= " " & Staff.Item("TechSupport_LastName") 85. cmbGraphStaff.Items.Add(Employee) 86. cmbGraphStaff2.Items.Add(Employee) 87. Next 88. '-------------------------------------------------------------------------------
6889. '#End Region 90. 91. 92. 'Region "Loop to add employee IDs to myreplies combobox 93. '-----------Loop to add employee IDs to myreplies combobox---------------------- 94. cmbRepliesSearch.Items.Clear() 95. cmbRepliesSearch.Items.Add("All Staff") 96. 97. sql = "SELECT * FROM tblEmployee_Details" 98. db.connect() 99. ds = db.sqltblEmployeeDetails(sql) 100. db.Close() 101. 102. For Each Staff As DataRow In ds.Tables("tblEmployeeDetails").Rows 'Linear search to search dataset and output Employee_IDs into combobox 103. Dim Employee As String = Staff.Item("Employee_ID") 104. cmbRepliesSearch.Items.Add(Employee) 105. Next 106. '------------------------------------------------------------------------------- 107. '#End Region 108. 109. 110. 'Make default selection on all comboboxes be the first value 111. cmbGraphStaff.SelectedIndex = 0 112. cmbGraphStaff2.SelectedIndex = 0 113. cmbRepliesSearch.SelectedIndex = 0 114. cmbSearchTickets.SelectedIndex = 0 115. 116. 117. RefreshDataGridView() 118. RefreshlstMyReplies() 'Function to load data from dataset into the list view 119. lstMyRepliesBubbleSort() 'Bubble sort algorithm to sort items in list view by newest date first 120. con.Close() 'Close the connection 121. End Sub 122. 123. Private Sub grdTickets_CellContentClick(sender As System.Object, e As DataGridViewCellEventArgs) _ 124. Handles grdTickets.CellContentClick 125. Dim senderGrid = DirectCast(sender, DataGridView) 126. 127. If TypeOf senderGrid.Columns(e.ColumnIndex) Is DataGridViewButtonColumn AndAlso 128. e.RowIndex >= 0 Then 129. 'Puts following column data from access table into each column as string format 130. ReplyToEmployee.Show() 131. ReplyToEmployee.lblTicketID.Text = "Ticket ID: " & grdTickets.CurrentRow.Cells(0).Value.ToString() 132. ReplyToEmployee.lblEmployeeID.Text = "Employee ID: " & grdTickets.CurrentRow.Cells(1).Value.ToString() 133. ReplyToEmployee.lblComputerID.Text = "Computer ID: " & grdTickets.CurrentRow.Cells(2).Value.ToString() 134. ReplyToEmployee.txtDepartment.Text = grdTickets.CurrentRow.Cells(3).Value.ToString() 135. ReplyToEmployee.txtTicketTitle.Text = grdTickets.CurrentRow.Cells(4).Value.ToString() 136. ReplyToEmployee.txtTicketDescription.Text = grdTickets.CurrentRow.Cells(5).Value.ToString() 137. ReplyToEmployee.lblTicketDate.Text = "Ticket Date: " & grdTickets.CurrentRow.Cells(6).Value.ToString() 138. ReplyToEmployee.dtpDeadline.Value = grdTickets.CurrentRow.Cells(7).Value.ToString()
69139. ReplyToEmployee.chkSolved.Checked = grdTickets.CurrentRow.Cells(8).Value.ToString() 140. End If 141. End Sub 142. 143. Private Sub btnSearchTickets_Click(sender As Object, e As EventArgs) Handles btnSearchTickets.Click 144. SearchDataGridView() 145. End Sub 146. 147. Private Sub cmbSearchTickets_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbSearchTickets.SelectedIndexChanged 148. 'Clear text in the textbox so it is empty when "Other" is selected again 149. txtColumnSearchOther.Clear() 150. 151. 'Make textbox visible if "Other" is selected in combobox 152. If cmbSearchTickets.Text = "Other" Then 153. txtColumnSearchOther.Enabled = True 154. txtColumnSearchOther.Visible = True 155. Else 156. txtColumnSearchOther.Enabled = False 157. txtColumnSearchOther.Visible = False 158. End If 159. 160. SearchDataGridView() 161. End Sub 162. 163. Private Sub btnLoadReplies_Click(sender As Object, e As EventArgs) 164. 165. Dim MyConn As OleDb.OleDbConnection 166. Dim da As OleDb.OleDbDataAdapter 'Connects to the database 167. 'Dim ds As DataSet 168. Dim tables As DataTableCollection 169. Dim source1 As New BindingSource 170. 171. 'This line of code loads data into the 'Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details' table. 172. Me.TblSubmitted_Ticket_DetailsTableAdapter.Fill(Me.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details) 173. 174. Dim connString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" + MainDatabaseLocation 175. Me.TblSubmitted_Ticket_DetailsTableAdapter.Fill(Me.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details) 176. MyConn = New OleDb.OleDbConnection 177. MyConn.ConnectionString = connString 178. ds = New DataSet 179. tables = ds.Tables 180. da = New OleDb.OleDbDataAdapter("SELECT * FROM tblSubmitted_Ticket_Details ORDER BY Ticket_Date ASC", MyConn) 'Selects all items from table 181. da.Fill(ds, "tblSubmitted_Ticket_Details") 182. Dim view As New DataView(tables(0)) 183. source1.DataSource = view 184. grdTickets.DataSource = view 185. 186. With grdTickets 187. grdTickets.Sort(.Columns(6), System.ComponentModel.ListSortDirection.Ascending) 'Sorts datagridview by Date Ascending 188. con.Close() 189. End With 190. con.Close() 191. End Sub 192. 193. Private Sub cmbRepliesSearch_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRepliesSearch.SelectedIndexChanged 194.
70195. Dim recordCount As Integer 196. Dim XCounter As Integer = 0 197. Dim sql As String 198. 199. If cmbRepliesSearch.Text = "All Staff" Then 200. RefreshlstMyReplies() 201. lstMyRepliesBubbleSort() 'Algorithm to sort lstMyReplies by newest date first 202. Else 203. 204. sql = "SELECT tblTicket_Reply_Details.Reply_ID, tblTicket_Reply_Details.Employee_ID, tblTicket_Reply_Details.TechSupport_ID, " _ 205. & "tblTicket_Reply_Details.Reply_Description, tblTicket_Reply_Details.Reply_Date FROM tblTicket_Reply_Details WHERE (tblTicket_Reply_Details.TechSupport_ID = '" & UserIDDatabase & "') AND (Employee_ID = '" & cmbRepliesSearch.Text & "')" 206. db.connect() 207. ds = db.sqlDetails(sql) 'Puts results into first dataset called 'details' 208. db.Close() 209. 210. sql = "SELECT TechSupport_FirstName, TechSupport_LastName FROM tblTechSupport_Details WHERE TechSupport_ID = '" & UserIDDatabase & "'" 211. db.connect() 212. ds2 = db.sqlNames(sql) 'Puts results into second dataset called 'names' 213. db.Close() 214. 215. 216. '#Region "Add items to listview loop" 217. recordCount = ds.Tables("Details").Rows.Count 218. lstMyReplies.Items.Clear() 'Clears the items in the listview 219. Do Until XCounter = recordCount 220. lstMyReplies.Items.Add(ds.Tables("Details").Rows(XCounter).Item(0)) 221. lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(1)) 222. lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(2)) 223. lstMyReplies.Items(XCounter).SubItems.Add(ds2.Tables("Names").Rows(0).Item(0)) 'Puts the value 'first name' from the 'Names' dataset containing the techsupport details values 224. lstMyReplies.Items(XCounter).SubItems.Add(ds2.Tables("Names").Rows(0).Item(1)) 'Puts the value 'last name' from the 'Names' dataset containing the techsupport details values 225. lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(3)) 226. lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(4)) 227. XCounter = XCounter + 1 228. Loop 229. lstMyRepliesBubbleSort() 230. End If 231. End Sub 232. 233. Private Sub btnRefreshReplies_Click(sender As Object, e As EventArgs) Handles btnRefreshReplies.Click 234. RefreshlstMyReplies() 235. lstMyRepliesBubbleSort() 'Algorithm to sort lstMyReplies by newest date first 236. con.Close() 'Closes connection 237. End Sub 238. 239. Private Sub cmbGraphStaff_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbGraphStaff.SelectedIndexChanged 240.
71241. 'SQL not needed because it and the dataset has been filled in the form_load sub procedure 242. 243. 'Sets the title text of the graph to the selected item in the combo box 244. chrTicketsSolved.Titles("Title1").Text = cmbGraphStaff.Text 245. 246. 247. Dim Solved As Integer = 0 248. Dim Unsolved As Integer = 0 249. 250. If cmbGraphStaff.Text = "" Or cmbGraphStaff.Text = "All Staff" Then 251. 252. 253. Dim sql As String = "SELECT * FROM tblSubmitted_Ticket_Details" 254. db.connect() 255. ds = db.sqltblSubmitted_Ticket_Details(sql) 256. db.Close() 257. 258. 259. 'Linear search to scan through each row and return a value + 1 260. For Each dataa As DataRow In ds.Tables("tblSubmitted_Ticket_Details").Rows 261. If dataa.Item("Ticket_Solved") = True Then 262. Solved += 1 263. Else 264. Unsolved += 1 265. End If 266. 267. Next 268. '#Region "chrTicketsSolved properties" 269. With chrTicketsSolved 270. 'We need to manually delete and remake the series and load the data in it (basically make a new graph) for the data to refresh, because VB.net 271. 'has trouble with refreshing controls whilst the program is still running 272. .Series.Clear() 'Clears anything in the series 273. .Series.Add("Series1") 'Adds a series 274. .Series("Series1").ChartType = DataVisualization.Charting.SeriesChartType.Pie 'Declares the type of graph 'pie' 275. .Series("Series1").Points.AddXY("Solved", Solved) 'Makes first point on pie chart the total value of solved tickets 276. .Series("Series1").Points(0).Color = Color.Lime 'Specifies the colour of the first point 277. .Series("Series1").Points.AddXY("Unsolved", Unsolved) 'Makes second point on pie chart the total value of unsolved tickets 278. .Series("Series1").Points(1).Color = Color.DarkOrange 'Specifies the colour of the first point 279. .Series("Series1").Points(0).IsValueShownAsLabel = True 280. .Series("Series1").Points(1).IsValueShownAsLabel = True 281. .Series("Series1").Points(0).CustomProperties = "PieLabelStyle=Outside" 282. chrTicketsSolved.Series("Series1").Points(1).CustomProperties = "PieLabelStyle=Outside" 283. End With 284. '#End Region 285. Else 286. 287. Dim sql As String = "SELECT * FROM tblSubmitted_Ticket_Details" 288. db.connect() 289. ds = db.sqltblSubmitted_Ticket_Details(sql) 290. db.Close() 291. 292. 293. 'Linear search to go through every record of the dataset on the column 'Ticket_Solved' and 294. 'increment 'unsolved' by 1 'Ticket_Solved' is false
72295. For Each dataa As DataRow In ds.Tables("tblSubmitted_Ticket_Details").Rows 296. If dataa.Item("Ticket_Solved") = False Then 297. Unsolved += 1 298. End If 299. 300. Next 301. 302. With chrTicketsSolved.Series(0) 303. .Points(1).YValues.SetValue(Unsolved, 0) 304. End With 305. 306. sql = "SELECT Ticket_SolvedBy FROM tblSubmitted_Ticket_Details WHERE Ticket_Solved = TRUE" 307. db.connect() 308. ds = db.sqlTicketSolvedByNames(sql) 309. db.Close() 310. 311. 312. 'Linear search to go through every record of the dataset on the column 'Ticket_Solved' and 313. 'increment 'solved' by 1 'Ticket_Solved' is true 314. For Each dataa As DataRow In ds.Tables("TicketSolvedByNames").Rows 315. If dataa.Item("Ticket_SolvedBy") = cmbGraphStaff.Text.Substring(0, 3) Then 316. Solved += 1 317. End If 318. Next 319. '#Region "chrTicketsSolved properties" 320. With chrTicketsSolved 321. 'We need to manually delete and remake the series and load the data in it (basically make a new graph) for the data to refresh, because VB.net 322. 'has trouble with refreshing controls whilst the program is still running 323. .Series.Clear() 'Clears anything in the series 324. .Series.Add("Series1") 'Adds a series 325. .Series("Series1").ChartType = DataVisualization.Charting.SeriesChartType.Pie 'Declares the type of graph 'pie' 326. .Series("Series1").Points.AddXY("Solved", Solved) 'Makes first point on pie chart the total value of solved tickets 327. .Series("Series1").Points(0).Color = Color.Lime 'Specifies the colour of the first point 328. .Series("Series1").Points.AddXY("Unsolved", Unsolved) 'Makes second point on pie chart the total value of unsolved tickets 329. .Series("Series1").Points(1).Color = Color.DarkOrange 'Specifies the colour of the first point 330. .Series("Series1").Points(0).IsValueShownAsLabel = True 331. .Series("Series1").Points(1).IsValueShownAsLabel = True 332. .Series("Series1").Points(0).CustomProperties = "PieLabelStyle=Outside" 333. chrTicketsSolved.Series("Series1").Points(1).CustomProperties = "PieLabelStyle=Outside" 334. End With 335. '#End Region 336. End If 337. End Sub 338. 339. Private Sub cmbGraphStaff2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbGraphStaff2.SelectedIndexChanged 340. 341. chrTicketsSolved2.Titles("Title1").Text = cmbGraphStaff2.Text 342. 343. Dim Solved As Integer = 0 344. Dim Unsolved As Integer = 0 345. Dim sql As String 346.
73347. If cmbGraphStaff2.Text = "" Or cmbGraphStaff2.Text = "All Staff" Then 348. 349. sql = "SELECT * FROM tblSubmitted_Ticket_Details" 350. db.connect() 351. ds = db.sqltblSubmitted_Ticket_Details(sql) 352. db.Close() 353. 354. 'Linear search to go through every record of the dataset on the column 'Ticket_Solved' and 355. 'increment 'solved' or 'unsolved' each runthrough depending on 'Ticket_Solved's value 356. For Each dataa As DataRow In ds.Tables("tblSubmitted_Ticket_Details").Rows 357. If dataa.Item("Ticket_Solved") = True Then 358. Solved += 1 359. Else 360. Unsolved += 1 361. End If 362. 363. Next 364. '#Region "chrTicketsSolved2 properties" 365. With chrTicketsSolved2 366. 'We need to manually delete and remake the series and load the data in it (basically make a new graph) for the data to refresh, because VB.net 367. 'has trouble with refreshing controls whilst the program is still running 368. .Series.Clear() 'Clears anything in the series 369. .Series.Add("Series1") 'Adds a series 370. .Series("Series1").ChartType = DataVisualization.Charting.SeriesChartType.Pie 'Declares the type of graph 'pie' 371. .Series("Series1").Points.AddXY("Solved", Solved) 'Makes first point on pie chart the total value of solved tickets 372. .Series("Series1").Points(0).Color = Color.Lime 'Specifies the colour of the first point 373. .Series("Series1").Points.AddXY("Unsolved", Unsolved) 'Makes second point on pie chart the total value of unsolved tickets 374. .Series("Series1").Points(1).Color = Color.DarkOrange 'Specifies the colour of the first point 375. .Series("Series1").Points(0).IsValueShownAsLabel = True 376. .Series("Series1").Points(1).IsValueShownAsLabel = True 377. .Series("Series1").Points(0).CustomProperties = "PieLabelStyle=Outside" 378. .Series("Series1").Points(1).CustomProperties = "PieLabelStyle=Outside" 379. End With 380. '#End Region 381. Else 382. 383. sql = "SELECT * FROM tblSubmitted_Ticket_Details" 384. db.connect() 385. ds = db.sqltblSubmitted_Ticket_Details(sql) 386. db.Close() 387. 388. 'Linear search to go through every record of the dataset on the column 'Ticket_Solved' and 389. 'increment 'unsolved' by 1 'Ticket_Solved' is false 390. For Each dataa As DataRow In ds.Tables("tblSubmitted_Ticket_Details").Rows 391. If dataa.Item("Ticket_Solved") = False Then 392. Unsolved += 1 393. End If 394. 395. Next 396. 397. With chrTicketsSolved2.Series(0) 398. .Points(1).YValues.SetValue(Unsolved, 0)
74399. End With 400. 401. sql = "SELECT Ticket_SolvedBy FROM tblSubmitted_Ticket_Details WHERE Ticket_Solved = TRUE" 402. db.connect() 403. ds = db.sqlTicketSolvedByNames(sql) 404. db.Close() 405. 406. 'Linear search to go through every record of the dataset on the column 'Ticket_Solved' and 407. 'increment 'solved' by 1 'Ticket_Solved' is true 408. For Each dataa As DataRow In ds.Tables("TicketSolvedByNames").Rows 409. If dataa.Item("Ticket_SolvedBy") = cmbGraphStaff2.Text.Substring(0, 3) Then 410. Solved += 1 411. End If 412. Next 413. '#Region "chrTicketsSolved2 properties" 414. With chrTicketsSolved2 415. 'We need to manually delete and remake the series and load the data in it (basically make a new graph) for the data to refresh, because VB.net 416. 'has trouble with refreshing controls whilst the program is still running 417. .Series.Clear() 'Clears anything in the series 418. .Series.Add("Series1") 'Adds a series 419. .Series("Series1").ChartType = DataVisualization.Charting.SeriesChartType.Pie 'Declares the type of graph 'pie' 420. .Series("Series1").Points.AddXY("Solved", Solved) 'Makes first point on pie chart the total value of solved tickets 421. .Series("Series1").Points(0).Color = Color.Lime 'Specifies the colour of the first point 422. .Series("Series1").Points.AddXY("Unsolved", Unsolved) 'Makes second point on pie chart the total value of unsolved tickets 423. .Series("Series1").Points(1).Color = Color.DarkOrange 'Specifies the colour of the first point 424. .Series("Series1").Points(0).IsValueShownAsLabel = True 425. .Series("Series1").Points(1).IsValueShownAsLabel = True 426. .Series("Series1").Points(0).CustomProperties = "PieLabelStyle=Outside" 427. .Series("Series1").Points(1).CustomProperties = "PieLabelStyle=Outside" 428. End With 429. '#End Region 430. End If 431. End Sub 432. 433. Private Sub txtColumnSearchOther_TextChanged(sender As Object, e As EventArgs) Handles txtColumnSearchOther.TextChanged 434. SearchDataGridView() 435. End Sub 436. 437. Private Sub btnExportGraphs_Click(sender As Object, e As EventArgs) Handles btnExportGraphs.Click 438. Me.CenterToScreen() 439. 440. Dim oWord As Word.Application 441. Dim oDoc As Word.Document 442. 443. 'Copies the first graph as a bitmap to the clipboard 444. Using ms As New MemoryStream() 445. chrTicketsSolved.SaveImage(ms, DataVisualization.Charting.ChartImageFormat.Png.Bmp) 446. Using bm As New Bitmap(ms) 447. Clipboard.SetImage(bm) 448. End Using 449. End Using
75450. Me.Hide() 451. oWord = CreateObject("Word.Application") 452. oWord.Visible = True 453. oDoc = oWord.Documents.Add 454. 455. ' oDoc = oWord.Documents.Add("E:\A-Levels\Computing\Ticket System V5.1\Graphs.dotx") 456. 457. oWord.Activate() 'Focuses on Word window 458. 459. 'Moves the cursor down so the graph isn't placed at the top of the document 460. My.Computer.Keyboard.SendKeys("{DOWN}", True) ' 461. My.Computer.Keyboard.SendKeys("{DOWN}", True) 462. My.Computer.Keyboard.SendKeys("{DOWN}", True) 463. 464. 'Presses CONTROL + V to paste the first graph into the document 465. My.Computer.Keyboard.SendKeys("^{v}", True) 466. 467. 'Moves the cursor down so the graph isn't placed at the top of the document 468. My.Computer.Keyboard.SendKeys("{DOWN}", True) 469. My.Computer.Keyboard.SendKeys("{DOWN}", True) 470. My.Computer.Keyboard.SendKeys("{DOWN}", True) 471. 472. 'Focuses on the form so the second graph can be copied 473. Me.Show() 474. 475. 'Copies second graph as bitmap to the clipboard 476. Using ms As New MemoryStream() 477. chrTicketsSolved2.SaveImage(ms, DataVisualization.Charting.ChartImageFormat.Png.Bmp) 478. Using bm As New Bitmap(ms) 479. Clipboard.SetImage(bm) 480. End Using 481. End Using 482. Me.Hide() 483. 'Shows and focuses the document so the graph can be pasted 484. oWord.Visible = True 485. 486. 'CONTROL V keystroke to paste the second graph into the document 487. My.Computer.Keyboard.SendKeys("^{v}", True) 488. 489. 'Create a timer to delay the form opening by a set amount of time, so that 490. 'the user has time to see the two graphs on the document 491. Dim instance = Now 492. Do While Now.Subtract(instance).Seconds < 0.5 493. Loop 494. Me.Show() 495. End Sub 496. 'Functions to be recalled (so code is not repeated and more efficient) 497. Public Function lstMyRepliesBubbleSort() 498. 'Bubble sort algorithm to sort listview by newest date first 499. Dim Sorted As Boolean = False 500. 501. Do While Sorted = False 'While the list is not sorted 502. Sorted = True 'The list is sorted until otherwise stated 503. 504. For ItemPosition As Integer = 0 To lstMyReplies.Items.Count - 2 'Go through each item in listv 505. 506. If CDate(lstMyReplies.Items(ItemPosition).SubItems(6).Text) < CDate(lstMyReplies.Items(ItemPosition + 1).SubItems(6).Text)Then 'If first item is smaller than next item 507. 508. Dim tempName As String = lstMyReplies.Items(ItemPosition + 1).Text 'Set the placeholder to hold the current item
76509. Dim tempSubItems(5) 'Create an array for the sub items sub item. 5 instead of 6 as array starts at 0 510. For SubItem As Integer = 1 To 6 'For each sub item in the current item 511. tempSubItems(SubItem - 1) = lstMyReplies.Items(ItemPosition + 1).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem 512. Next 513. 514. lstMyReplies.Items(ItemPosition + 1).Text = lstMyReplies.Items(ItemPosition).Text 'Set the current item text as equal to the next item 515. For SubItem As Integer = 1 To 6 'For each sub item in the current item 516. lstMyReplies.Items(ItemPosition + 1).SubItems(SubItem).Text = lstMyReplies.Items(ItemPosition).SubItems(SubItem).Text'Set the array entry to be equal to that of its corresponding subitem 517. Next 518. 519. lstMyReplies.Items(ItemPosition).Text = tempName 'Set the next item to be the placeholder (what the old value was) 520. For SubItem As Integer = 1 To 6 'For each subitem in the temporary subitems array 521. lstMyReplies.Items(ItemPosition).SubItems(SubItem).Text = tempSubItems(SubItem - 1) 'Set the array entry to be equal to that of its corresponding temporary subitem 522. Next 523. 'This rotates / swaps the two values 524. Sorted = False 'Show that the list was not sorted and another check needs to be performed 525. End If 526. Next 527. Loop 528. Return lstMyReplies 529. End Function 530. Function SearchDataGridView() 531. Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & MainDatabaseLocation 532. Dim con As OleDb.OleDbConnection 533. Dim da As OleDb.OleDbDataAdapter 'Connects to the database 534. 'Dim ds As DataSet 535. Dim tables As DataTableCollection 536. Dim source1 As New BindingSource 537. 538. If cmbSearchTickets.Text = "All" Or cmbSearchTickets.Text = "" Then 539. RefreshDataGridView() 540. ElseIf cmbSearchTickets.Text = "Other" Then 541. If txtColumnSearchOther.Text = "" Then 542. RefreshDataGridView() 543. Else 544. 'This line of code loads data into the 'Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details' table. 545. Me.TblSubmitted_Ticket_DetailsTableAdapter.Fill(Me.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details) 546. connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" + MainDatabaseLocation 547. con = New OleDb.OleDbConnection 548. con.ConnectionString = connString 549. ds = New DataSet 550. tables = ds.Tables 551. 552. da = New OleDb.OleDbDataAdapter("SELECT * FROM tblSubmitted_Ticket_Details WHERE Ticket_Title LIKE '%" & txtColumnSearchOther.Text & "%' ORDER BY Ticket_Date ASC", con) 'Selects all items from table 553. da.Fill(ds, "tblSubmitted_Ticket_Details") 554. 555. Dim view As New DataView(tables(0))
77556. source1.DataSource = view 557. grdTickets.DataSource = view 558. 559. With grdTickets 560. grdTickets.Sort(.Columns(6), System.ComponentModel.ListSortDirection.Ascending) 'Sorts datagridview by Date Ascending 561. con.Close() 562. End With 563. End If 564. Else 565. 566. 'This line of code loads data into the 'Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details' table. 567. Me.TblSubmitted_Ticket_DetailsTableAdapter.Fill(Me.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details) 568. Dim connString1 As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" + MainDatabaseLocation 569. Me.TblSubmitted_Ticket_DetailsTableAdapter.Fill(Me.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details) 570. con = New OleDb.OleDbConnection 571. con.ConnectionString = connString1 572. ds = New DataSet 573. tables = ds.Tables 574. da = New OleDb.OleDbDataAdapter("SELECT * FROM tblSubmitted_Ticket_Details WHERE Ticket_Title = '" & cmbSearchTickets.Text & "' ORDER BY Ticket_Date ASC", con) 'Selects all items from table 575. da.Fill(ds, "tblSubmitted_Ticket_Details") 576. Dim view1 As New DataView(tables(0)) 577. source1.DataSource = view1 578. grdTickets.DataSource = view1 579. 580. With grdTickets 581. grdTickets.Sort(.Columns(6), System.ComponentModel.ListSortDirection.Ascending) 'Sorts datagridview by Date Ascending 582. con.Close() 583. End With 584. 585. End If 586. Return grdTickets 587. End Function 588. End Class
78ReplyToEmployee
791. Imports Microsoft.Office.Interop 2. Public Class ReplyToEmployee 3. Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + MainDatabaseLocation 4. Dim MyConn As OleDb.OleDbConnection 5. Dim da As OleDb.OleDbDataAdapter 'Connects to the database 6. ' Dim ds As DataSet 7. Dim tables As DataTableCollection 8. Dim source1 As New BindingSource 9. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 10. Me.Hide() 11. End Sub 12. 13. Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click 14. 'Make 'Confirm' button visible on top of 'Edit' button. 15. btnConfirm.Enabled = True 16. btnConfirm.Visible = True 17. 'Make CheckBox and DateTimePicker editable 18. dtpDeadline.Enabled = True 19. chkSolved.AutoCheck = True 20. chkSolved.ForeColor = Color.Black
8021. End Sub 22. 23. Private Sub btnConfirm_Click(sender As Object, e As EventArgs) Handles btnConfirm.Click 24. Dim con As New OleDb.OleDbConnection 25. Dim dbProvider, sql, sql2 As String 26. Dim ds As New DataSet 27. Dim ReplyDate As Date 'Puts reply details into ticket replies table 28. ReplyDate = Format(Now, "dd/MM/yyyy HH:mm") 29. 30. dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" 31. con.ConnectionString = dbProvider & MainDatabaseLocation 32. 33. ' con.Open() 34. If chkSolved.Checked = True Then 'If "Solved" is checked and if you click YES on the messagebox: 35. Dim result As Integer = MsgBox("Do you want to save these changes?", MsgBoxStyle.YesNo) 36. If result = DialogResult.Yes Then 37. 38. Dim TicketNumbers As String 'Makes a variable to skip an amount of characters in a label, so just the numbers in my label are used in my sql 39. TicketNumbers = Mid(lblTicketID.Text, 12, 16) 40. 'This sql updates the checkbox and deadline in the record with the matching ticket ID as this column is the unique identifier 41. sql = "UPDATE tblSubmitted_Ticket_Details SET Ticket_Solved = '" & chkSolved.CheckState & "' = YES, " _ 42. & "Ticket_Deadline = '" & dtpDeadline.Value.ToString & "', Ticket_SolveDate = '" & ReplyDate & "', Ticket_SolvedBy = '" & UserIDDatabase & "' WHERE Ticket_ID = " & TicketNumbers 43. da = New OleDb.OleDbDataAdapter(sql, con) 44. da.Fill(ds, "tblSubmitted_Ticket_Details") 'Fills table 45. 46. 47. 'Puts details into ticket reply details table 48. sql2 = "INSERT INTO tblTicket_Reply_Details (Employee_ID, TechSupport_ID, Reply_Description, Reply_Date) VALUES ('" & TechSupportMenu.grdTickets.CurrentRow.Cells(1).Value.ToString() & "','" & UserIDDatabase & "', ""Your Ticket has been solved!"" , '" & ReplyDate & "')" 49. da = New OleDb.OleDbDataAdapter(sql2, con) 50. da.Fill(ds, "tblSubmitted_Ticket_Details") 'fills table 51. 52. Dim TicketDetails As String 53. TicketDetails = ReplyToEmployeeAdditionalNotes.txtAdditionalDetails.Text 54. 55. SendEmailTicketSolved() 56. RefreshDataGridView() 57. RefreshlstMyReplies() 58. lstMyRepliesBubbleSort() 59. ReplyChangesSaved() 'Makes checkbox and combobox uneditable, changes colour of checkbox text, hides confirm button, shows edit button 60. 61. Else 'If the checkbox IS checked and you click NO on the msgbox 62. Me.dtpDeadline.Value = TechSupportMenu.grdTickets.CurrentRow.Cells(7).Value.ToString() 63. Me.chkSolved.Checked = TechSupportMenu.grdTickets.CurrentRow.Cells(8).Value.ToString() 64. 'Puts controls back to what their values were in the datagridview 65. 66. ReplyChangesSaved() 'Makes checkbox and combobox uneditable, changes colour of checkbox text, hides confirm button, shows edit button 67. 68. End If 69. Else 'If checkbox ISNT checked AND if you click YES on the msgbox 70. Dim result2 As Integer = MsgBox("Do you want to save these changes?", MsgBoxStyle.YesNo) 71. If result2 = DialogResult.Yes Then
8172. 'WHEN YOU CLICK YES TO ADD ADDITIONAL NOTES, THE DEADLINE WILL STILL CHANGE AND THE TABLE WILL STILL REFRESH 73. '| | | | | | | | 74. 'V V V V V V V V 75. If dtpDeadline.Value.ToString <> TechSupportMenu.grdTickets.CurrentRow.Cells(7).Value.ToString() Then 76. Dim result3 As Integer = MsgBox("It appears that you have changed the deadline but the ticket hasn't been solved, would you like to add any additional notes as to why this is for the employee?", MsgBoxStyle.YesNo) 77. If result3 = DialogResult.Yes Then 78. ReplyToEmployeeAdditionalNotes.Show() 'opens form to reply with custom message 79. 80. RefreshDataGridView() 81. RefreshlstMyReplies() 82. lstMyRepliesBubbleSort() 83. 84. 85. Else 'If you dont want to add extra details 86. 87. ReplyChangesSaved() 'Makes checkbox and combobox uneditable, changes colour of checkbox text, hides confirm button, shows edit button 88. SendEmailTimescaleChangedNoNotes() 89. RefreshDataGridView() 90. RefreshlstMyReplies() 91. lstMyRepliesBubbleSort() 92. End If 93. 94. ReplyChangesSaved() 'Makes checkbox and combobox uneditable, changes colour of checkbox text, hides confirm button, shows edit button 95. 96. Dim TicketNumbers As String 'This code makes a variable to skip an amount of characters in a label, so just the numbers in my label are used in my sql 97. TicketNumbers = Mid(lblTicketID.Text, 12, 16) 98. ' con.Open() 99. sql = "UPDATE tblSubmitted_Ticket_Details SET Ticket_Solved = '" & chkSolved.CheckState & "' = YES, Ticket_Deadline = '"& dtpDeadline.Value.ToString & "' WHERE Ticket_ID = " & TicketNumbers 100. 'This sql updates the checkbox in the record with the matching ticket ID as this column is the unique identifier 101. 102. da = New OleDb.OleDbDataAdapter(sql, con) 103. da.Fill(ds, "tblSubmitted_Ticket_Details") 'Fills table 104. ' con.Close() 105. 106. RefreshDataGridView() 107. RefreshlstMyReplies() 108. lstMyRepliesBubbleSort() 109. 110. Else 'If checkbox isnt checked AND if you click NO on the msgbox 111. Me.dtpDeadline.Value = TechSupportMenu.grdTickets.CurrentRow.Cells(7).Value.ToString() 'Puts value back to what it was before on datagridview 112. Me.chkSolved.Checked = TechSupportMenu.grdTickets.CurrentRow.Cells(8).Value.ToString() 'Puts value back to what it was before on datagridview 113. 114. ReplyChangesSaved() 'Makes checkbox and combobox uneditable, changes colour of checkbox text, hides confirm button, shows edit button 115. 116. End If 117. ReplyChangesSaved() 'Makes checkbox and combobox uneditable, changes colour of checkbox text, hides confirm button, shows edit button 118. 119. TechSupportMenu.grdTickets.CurrentRow.Cells(8).Value = chkSolved.Checked 'So checkbox goes to false on the datagridview 120.
82121. Dim TicketNumbers2 As String 'This code makes a variable to skip an amount of characters in a label, so just the numbers in my label are used in my sql 122. 'ticketnumbers2 is made because otherwise multiple variable declarations will be in the same enclosing block 123. TicketNumbers2 = Mid(lblTicketID.Text, 12, 16) 124. ' con.Open() 125. sql = "UPDATE tblSubmitted_Ticket_Details SET Ticket_Solved = '" & chkSolved.CheckState & "' = YES, Ticket_Deadline = '" & dtpDeadline.Value.ToString & "' WHERE Ticket_ID = " & TicketNumbers2 126. 'This sql updates the checkbox and deadline in the record with the matching ticket ID as this column is the unique identifier 127. 128. da = New OleDb.OleDbDataAdapter(sql, con) 129. da.Fill(ds, "tblSubmitted_Ticket_Details") 'Fills table 130. ' con.Close() 131. 132. Else 'If checkbox ISNT checked AND you click NO on the msgbox 133. Me.dtpDeadline.Value = TechSupportMenu.grdTickets.CurrentRow.Cells(7).Value.ToString() 'Puts value back to what it was before on datagridview 134. Me.chkSolved.Checked = TechSupportMenu.grdTickets.CurrentRow.Cells(8).Value.ToString() 'Puts value back to what it was before on datagridview 135. ReplyChangesSaved() 136. End If 137. End If 138. 'Changes colour of and makes check box read only after clicking confirm 139. con.Close() 140. End Sub 141. 142. Private Sub ReplyToEmployee_Load(sender As Object, e As EventArgs) Handles MyBase.Load 143. Me.CenterToScreen() 'Opens form in the centre of the screen 144. End Sub 145. 146. End Class
83ReplyToEmployeeAdditionalNotes
841. Public Class ReplyToEmployeeAdditionalNotes 2. Private Sub btnSend_Click(sender As Object, e As EventArgs) Handles btnSend.Click 3. If txtAdditionalDetails.Text = "" Then 4. MsgBox("You must type something!") 5. End If 6. 7. SendEmailTimescaleChangedAddNotes() 8. 9. Me.Close() 10. End Sub 11. 12. Private Sub ReplyToEmployeeAdditionalNotes_Load(sender As Object, e As EventArgs) Handles MyBase.Load 13. Me.CenterToScreen() 'Opens form in the centre of the screen 14. End Sub 15. 16. Private Sub Button1_Click(sender As Object, e As EventArgs) 17. SendEmailTimescaleChangedAddNotes() 'This is a function in a module as I will be recalling this code more than once 18. End Sub 19. End Class
85NewEmployeeError