Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
ByVal sCusID As String, _
ByVal sDBNameA As String)
' ---------------------------------------------------------------------
' Incoming Parameters:
' oThisFormA - a reference to the current form
' of type frmDataCapture
' sStudentNumberA - The student number
' sDBNameA - The Database File Name
' Return value :
' if save successfull (no validation issues) return empty string, ""
' otherwise return error message
'------------------------------------------------------------
Dim sSaveSql As String
Dim sConnection As String
Dim sErrorMessage As String
Dim sSQL As String
Dim bStudentNumberExists As Boolean
Dim oConn As OleDbConnection 'To reference a Connection obj.
Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj used to execute the Select SQL.
Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj used to execute the Insert/Update SQL.
'sErrorMessage = ""
oConn = Nothing
oCmd_Select = Nothing
oDataReader = Nothing
oCmd_Update = Nothing
' --- create a database command object and set the SQL statement
' --- it will execute, and the database connection object associated to it
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnection = sConnection & "User ID=Admin;"
sConnection = sConnection & "Data Source="
sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
oConn = New OleDbConnection(sConnection)
oConn.Open()
oCmd_Select = oConn.CreateCommand()
sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
'sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
oCmd_Select.CommandText = sSQL
' --- execute SQL command and place results into a datareader object
oDataReader = oCmd_Select.ExecuteReader()
bStudentNumberExists = (oDataReader.Read() = True)
' --- Build Insert or Update Sql depending if record retrieved with select query is found
If bStudentNumberExists Then
If bStudentNumberExists Then
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
End Using
End If
' ' Be careful however with the commas and the closing bracket
End If
oDataReader.Close()
oCmd_Select.Dispose()
' Execute sql update or insert query:
oCmd_Update = oConn.CreateCommand()
oCmd_Update.CommandText = sSaveSql
oCmd_Update.ExecuteNonQuery()
End Sub
Originally Posted by coccoster
Yep ive had Option Strict on the whole time.
Reply With Quote