· 7 years ago · Feb 11, 2019, 10:38 PM
1' --------------------------------------------------------------
2' Module: mDataCapture Tutorial 8
3' Created: 18/4/2007 Programmer: Vince Bruno
4' Purpose: This module contains Functions used in data capture forms
5' --------------------------------------------------------------
6Option Explicit On
7Option Strict On
8Imports System.Windows.Forms
9Imports System.Data.OleDb
10
11Module mDataCapture
12
13 Public Sub g_InitialiseForm(ByVal oThisFormA As frmDataCapture)
14 ' --- avoid clearing labels that do not display info ...
15 oThisFormA.lblStatus.Tag = "*"
16
17 End Sub
18
19 Public Sub g_LoadStudentRecord(ByVal oThisFormA As frmDataCapture, _
20 ByVal sStudentNoA As String, _
21 ByVal sDBNameA As String)
22 ' ----------------------------------------------------------
23 ' Author : Ian SStorey & Vince Bruno
24 ' Date : 1/8/2007
25 ' Aim : Load data from database into textboxes
26 ' -----------------------------------------------------------
27 ' Incoming Parameters:
28 ' oThisFormA - a reference to the current form
29 ' of type frmDataCapture
30 ' sStudentNo - The student number of the student
31 ' sDBNameA - The Database File Name
32 '------------------------------------------------------------
33 Dim sCurrFieldName As String
34 Dim sCurrFieldValue As String
35 Dim sConnection As String
36 Dim sSQL As String
37
38 Dim oConn_Student As OleDbConnection 'To reference a Connection obj.
39 Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj.
40 Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
41
42 oConn_Student = Nothing
43 oCmd_Select = Nothing
44 oDataReader = Nothing
45 ' --- create a database command object and set the SQL statement
46 ' it will execute, and the database connection object
47 ' associated to it
48 sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
49 sConnection = sConnection & "User ID=Admin;"
50 sConnection = sConnection & "Data Source="
51 sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
52 'Debug.WriteLine("sConnection=" & sConnection)
53 oConn_Student = New OleDbConnection(sConnection)
54 oConn_Student.Open()
55
56 oCmd_Select = oConn_Student.CreateCommand()
57
58 '**** Make the SQL query
59 sSQL = "SELECT * " & _
60 "FROM STUDENT" & _
61 " WHERE StudentNo = """ & sStudentNoA & """;"
62
63 oCmd_Select.CommandText = sSQL
64
65 ' --- execute SQL command and place results into a datareader object
66 oDataReader = oCmd_Select.ExecuteReader()
67
68 ' --- Read every field from the DataReader object
69 If oDataReader.Read() Then
70 ' load txtSurname with value from database record
71 sCurrFieldName = "Surname"
72 sCurrFieldValue = oDataReader.Item(sCurrFieldName).ToString
73 oThisFormA.txtSurname.Text = sCurrFieldValue
74
75 ' load txtGiven with value from database record
76 sCurrFieldName = "Given"
77 sCurrFieldValue = oDataReader.Item(sCurrFieldName).ToString()
78 oThisFormA.txtGiven.Text = sCurrFieldValue
79
80 ' load txtDOB with value from database record
81 sCurrFieldName = "DOB"
82 sCurrFieldValue = oDataReader.Item(sCurrFieldName).ToString
83 oThisFormA.txtDOB.Text = sCurrFieldValue
84
85 ' load txtPostcode with value from database record
86 sCurrFieldName = "Postcode"
87 sCurrFieldValue = oDataReader.Item(sCurrFieldName).ToString()
88 oThisFormA.txtPostcode.Text = sCurrFieldValue
89
90 oThisFormA.lblStatus.Text = "Existing Record"
91 Else
92 ' no record found in database, clear text boxes
93 oThisFormA.txtSurname.Text = ""
94 oThisFormA.txtGiven.Text = ""
95 oThisFormA.txtPostcode.Text = ""
96 oThisFormA.txtDOB.Text = ""
97
98 ' Notify the user of the error
99 oThisFormA.lblStatus.Text = "Unknown Student Number: " & sStudentNoA
100 MsgBox("Student with student number, " & sStudentNoA & ", does not exist")
101
102 End If
103 If Not oDataReader Is Nothing Then oDataReader.Close()
104 If Not oCmd_Select Is Nothing Then oCmd_Select.Dispose()
105 If Not oConn_Student Is Nothing Then oConn_Student.Close()
106
107 End Sub
108
109 Public Sub g_bValidateAllFields(ByVal oThisFormA As frmDataCapture)
110
111 '------------------------------------------------------------
112 ' Author : Ian Storey and Vince Bruno
113 ' Date : 1/1/2009
114 ' Aim : validate fields on form according to table given below
115 ' The method throws an exception if any field is invalidated
116 '------------------------------------------------------------
117 ' Incoming Parameters : oThisFormA - data capture form reference
118 ' Outgoing Parameters : (none)
119 ' Return data : True or False
120 '------------------------------------------------------------
121 ' FIELD NAME FIELD TYPE REQUIRED MIN MAX MIN MAX
122 ' LEN LEN RANGE RANGE
123 ' txtSurname Alphabetic Mandatory 1 40
124 ' txtGiven Alphabetic Mandatory 1 30
125 ' txtDOB Date Optional
126 ' txtPostcode Numeric Optional 500 9000
127 '------------------------------------------------------------
128
129 ' --- validate textboxes on form
130 ' validate txtSurname
131 oThisFormA.txtSurname.BackColor = Color.White
132 If g_bIsValid(oThisFormA.txtSurname.Text, "X", "M", "1", "40", "", "", "") = False Then
133 Throw New Exception("Invalid Surname")
134 End If
135
136 ' validate txtGiven
137 oThisFormA.txtGiven.BackColor = Color.White
138 If g_bIsValid(oThisFormA.txtGiven.Text, "X", "M", "1", "30", "", "", "") = False Then
139 Throw New Exception("Invalid Given Name")
140 End If
141
142 ' validate txtDOB
143 oThisFormA.txtDOB.BackColor = Color.White
144 If g_bIsValid(oThisFormA.txtDOB.Text, "D", "M", "", "", "", "", "") = False Then
145 Throw New Exception("Invalid Date")
146 End If
147
148 ' validate txtPostcode
149 oThisFormA.txtPostcode.BackColor = Color.White
150 If g_bIsValid(oThisFormA.txtPostcode.Text, "N", "M", "", "", "1000", "9999", "") = False Then
151 Throw New Exception("Invalid Postcode")
152 End If
153
154
155 End Sub
156
157 Public Sub g_sSaveStudentRecord(ByVal oThisFormA As frmDataCapture, _
158 ByVal sStudentNoA As String, _
159 ByVal sDBNameA As String)
160
161 ' --------------------------------------------------------------------
162 ' Authors: Ians Storey & Vince Bruno
163 ' Date : 1/8/2009
164 ' Aim : Save changes made to textbox values
165 ' to a record in Database
166 ' ---------------------------------------------------------------------
167 ' Incoming Parameters:
168 ' oThisFormA - a reference to the current form
169 ' of type frmDataCapture
170 ' sStudentNumberA - The student number
171 ' sDBNameA - The Database File Name
172 ' Return value :
173 ' if save successfull (no validation issues) return empty string, ""
174 ' otherwise return error message
175 '------------------------------------------------------------
176 Dim sSaveSql As String
177 Dim sConnection As String
178 Dim sErrorMessage As String
179 Dim sSQL As String
180 Dim bStudentNumberExists As Boolean
181
182 Dim oConn As OleDbConnection 'To reference a Connection obj.
183 Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj used to execute the Select SQL.
184 Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
185 Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj used to execute the Insert/Update SQL.
186
187 'sErrorMessage = ""
188 oConn = Nothing
189 oCmd_Select = Nothing
190 oDataReader = Nothing
191 oCmd_Update = Nothing
192
193 ' --- create a database command object and set the SQL statement
194 ' --- it will execute, and the database connection object associated to it
195 sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
196 sConnection = sConnection & "User ID=Admin;"
197 sConnection = sConnection & "Data Source="
198 sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
199 oConn = New OleDbConnection(sConnection)
200 oConn.Open()
201
202 oCmd_Select = oConn.CreateCommand()
203
204 sSQL = "SELECT studentNo from STUDENT WHERE studentNo=""" & sStudentNoA & """"
205 oCmd_Select.CommandText = sSQL
206
207 ' --- execute SQL command and place results into a datareader object
208 oDataReader = oCmd_Select.ExecuteReader()
209
210 bStudentNumberExists = (oDataReader.Read() = True)
211
212
213
214 ' --- Build Insert or Update Sql depending if record retrieved with select query is found
215 If bStudentNumberExists Then
216 ' --- if record already exists in the DB,
217 ' update record with values from the textboxes
218 ' add update fields after "sd_given" and before "where", seperated by commas
219 sSaveSql = "UPDATE student SET " _
220 & "Surname = """ & oThisFormA.txtSurname.Text.Trim & """, " _
221 & "Given = """ & oThisFormA.txtGiven.Text.Trim & """, " _
222 & "DOB = #" & oThisFormA.txtDOB.Text.Trim & "#, " _
223 & "Postcode = " & oThisFormA.txtPostcode.Text.Trim & " " _
224 & "WHERE studentno = """ & oThisFormA.txtStudentNo.Text.Trim & """;"
225 Else
226 ' --- If record does not already exist in the DB,
227 ' --- Build INSERT SQL query using values from the textboxes
228 ' [xxx] add insert fields after "sd_given" and value after txtGiven, seperated by commas
229 sSaveSql = "insert into student(studentno, surname, given, DOB, Postcode) " _
230 & " values(""" & oThisFormA.txtStudentNo.Text & """," _
231 & """" & oThisFormA.txtSurname.Text.Trim & """," _
232 & """" & oThisFormA.txtGiven.Text.Trim & """," _
233 & """" & oThisFormA.txtDOB.Text.Trim & """," _
234 & """" & oThisFormA.txtPostcode.Text.Trim & " "")"
235 '[xxx] Add DOB and postcode fields here,
236 ' Be careful however with the commas and the closing bracket
237 End If
238
239 ' Execute sql update or insert query:
240 oCmd_Update = oConn.CreateCommand()
241 oCmd_Update.CommandText = sSaveSql
242 oCmd_Update.ExecuteNonQuery()
243
244
245 oDataReader.Close()
246 oCmd_Select.Dispose()
247
248
249 Debug.WriteLine(sSaveSql)
250
251
252 End Sub
253
254 Public Sub g_deleteStudentRecord(ByVal oThisFormA As frmDataCapture, _
255 ByVal sStudentNoA As String, _
256 ByVal sDBNameA As String)
257
258 ' ----------------------------------------------------------
259 ' Author : Ian SStorey & Vince Bruno
260 ' Date : 1/8/2007
261 ' Aim : Load data from database into textboxes
262 ' -----------------------------------------------------------
263 ' Incoming Parameters:
264 ' oThisFormA - a reference to the current form
265 ' of type frmDataCapture
266 ' sStudentNo - The student number of the student
267 ' sDBNameA - The Database File Name
268 '------------------------------------------------------------
269
270 Dim sConnection As String
271 Dim sSQL As String
272
273 Dim oConn_Student As OleDbConnection 'To reference a Connection obj.
274 Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj.
275
276 oConn_Student = Nothing
277 oCmd_Update = Nothing
278
279 ' --- create a database command object and set the SQL statement
280 ' it will execute, and the database connection object
281 ' associated to it
282 sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
283 sConnection = sConnection & "User ID=Admin;"
284 sConnection = sConnection & "Data Source="
285 sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
286 'Debug.WriteLine("sConnection=" & sConnection)
287 oConn_Student = New OleDbConnection(sConnection)
288 oConn_Student.Open()
289
290 oCmd_Update = oConn_Student.CreateCommand()
291
292 '**** Make the SQL queries
293
294 ' 1. Delete from ENROL
295 sSQL = "DELETE " & _
296 "FROM enrol " & _
297 "WHERE StudentNo = """ & sStudentNoA & """;"
298
299 Debug.WriteLine(sSQL)
300
301 oCmd_Update.CommandText = sSQL
302
303 oCmd_Update.ExecuteNonQuery()
304
305 ' 2. Delete from STUDENT
306
307 '**** Make the SQL query
308 sSQL = "DELETE " & _
309 "FROM student " & _
310 "WHERE StudentNo = """ & sStudentNoA & """;"
311
312 Debug.WriteLine(sSQL)
313
314 oCmd_Update.CommandText = sSQL
315
316 oCmd_Update.ExecuteNonQuery()
317
318
319
320 End Sub
321
322End Module