I get a Error Message when I get to intRecordAffected. The Error Message is Syntax Error.
Here is the code for Update:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
'cmd.CommandText = strSQL
'Declare and Set the RecordSet
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'UpDating Record
strSQL = "Update percap2 Set "
strSQL = strSQL & "Dist = "
strSQL = strSQL & "'" & gstrDIST & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "GEN = "
strSQL = strSQL & "'" & strGEN & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "SUB = "
strSQL = strSQL & "'" & strSUB & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDR1 = "
strSQL = strSQL & "'" & strADDR1 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDR2 = "
strSQL = strSQL & "'" & strADDR2 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDR3 = "
strSQL = strSQL & "'" & strADDR3 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ZIP = "
strSQL = strSQL & "'" & strZIP & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ZIP4 = "
strSQL = strSQL & "'" & strZIP4 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "OCCUPCODE = "
strSQL = strSQL & "'" & strOCC & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "OCCVALUE = "
strSQL = strSQL & "'" & strOCCVAL & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDDATE = "
strSQL = strSQL & "'" & strDate1 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "SYSDATE = "
strSQL = strSQL & "'" & strSYSDATE & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "SYSTYPE = "
strSQL = strSQL & "'" & strSYSTYPE & "'"
strSQL = strSQL & " Where Name = "
strSQL = strSQL & "'" & strName & "'"
'Declare and set the Command
cmd.CommandText = strSQL
cmd.Execute intRecordAffected
Set cmd = Nothing
'Checking to see if record failed to UpDate
If (intRecordAffected = 0) Then
MsgBox "Update Failed", _
vbInformation, "Error"
End If
'Checking to see if record UpDate
If (intRecordAffected = 1) Then
MsgBox "Update Completed", _
vbInformation, "UpDate"
End If
Here is the code for Update:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
'cmd.CommandText = strSQL
'Declare and Set the RecordSet
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'UpDating Record
strSQL = "Update percap2 Set "
strSQL = strSQL & "Dist = "
strSQL = strSQL & "'" & gstrDIST & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "GEN = "
strSQL = strSQL & "'" & strGEN & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "SUB = "
strSQL = strSQL & "'" & strSUB & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDR1 = "
strSQL = strSQL & "'" & strADDR1 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDR2 = "
strSQL = strSQL & "'" & strADDR2 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDR3 = "
strSQL = strSQL & "'" & strADDR3 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ZIP = "
strSQL = strSQL & "'" & strZIP & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ZIP4 = "
strSQL = strSQL & "'" & strZIP4 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "OCCUPCODE = "
strSQL = strSQL & "'" & strOCC & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "OCCVALUE = "
strSQL = strSQL & "'" & strOCCVAL & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "ADDDATE = "
strSQL = strSQL & "'" & strDate1 & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "SYSDATE = "
strSQL = strSQL & "'" & strSYSDATE & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "SYSTYPE = "
strSQL = strSQL & "'" & strSYSTYPE & "'"
strSQL = strSQL & " Where Name = "
strSQL = strSQL & "'" & strName & "'"
'Declare and set the Command
cmd.CommandText = strSQL
cmd.Execute intRecordAffected
Set cmd = Nothing
'Checking to see if record failed to UpDate
If (intRecordAffected = 0) Then
MsgBox "Update Failed", _
vbInformation, "Error"
End If
'Checking to see if record UpDate
If (intRecordAffected = 1) Then
MsgBox "Update Completed", _
vbInformation, "UpDate"
End If