Hi,
I'm having a problem with an SQL statement, I have a statement that goes
like this:
"INSERT INTO table VALUES('TransID','CID',etc...);"
it basically adds new data to a table. When the form is posted it adds
the data just fine. However, when you try to add more new data it adds
the data and it duplicates it again with different transaction ID.
Basically it executes the SQL statement twice eventhough there's no
loops or anything going on.
This is being done for the check table. The code is below:
strSQL="Select * From Customers Where CustomerID='" &
Request.Form("CustomerID") & "';"
rs.Close
rs.Open strSQL, adoCon, 3, 3
If Request.Form("Employer")=rs("CompanyID1") Then
If Request.Form("added")="Yes" Then
strSQLExec="UPDATE Customers SET CompanyID1Scanned='Yes'
Where
CustomerID='" & Request.Form("CustomerID") & "';"
rsExec.Open strSQLExec, adoCon
rs.Close
rs.Open strSQL, adoCon, 3, 3
End If
If rs("CompanyID1Scanned")="No" Then
Session("Option")="1-0-2"
Session("TempData")=rs("CustomerID") & "-" &
rs("CompanyID1")
Session("FieldA")=Request.Form("Employer")
Session("FieldB")=Request.Form("CheckNum")
Session("FieldC")=Request.Form("CheckAmount")
Session("FieldD")=Request.Form("CustomerType")
Session("FieldE")=Request.Form("AmountEarned")
Response.Redirect(mainURL)
End If
Else If Request.Form("Employer")=rs("CompanyID2") Then
If Request.Form("added")="Yes" Then
strSQLExec="UPDATE Customers SET CompanyID2Scanned='Yes'
Where
CustomerID='" & Request.Form("CustomerID") & "';"
rsExec.Open strSQLExec, adoCon
rs.Close
rs.Open strSQL, adoCon, 3, 3
End If
If rs("CompanyID2Scanned")="No" Then
Session("Option")="1-0-2"
Session("TempData")=rs("CustomerID") & "-" &
rs("CompanyID2")
Session("FieldA")=Request.Form("Employer")
Session("FieldB")=Request.Form("CheckNum")
Session("FieldC")=Request.Form("CheckAmount")
Session("FieldD")=Request.Form("CustomerType")
Session("FieldE")=Request.Form("AmountEarned")
Response.Redirect(mainURL)
End If
Else If Request.Form("Employer")=rs("CompanyID3") Then
If Request.Form("added")="Yes" Then
strSQLExec="UPDATE Customers SET CompanyID3Scanned='Yes'
Where
CustomerID='" & Request.Form("CustomerID") & "';"
rsExec.Open strSQLExec, adoCon
rs.Close
rs.Open strSQL, adoCon, 3, 3
End If
If rs("CompanyID3Scanned")="No" Then
Session("Option")="1-0-2"
Session("TempData")=rs("CustomerID") & "-" &
rs("CompanyID3")
Session("FieldA")=Request.Form("Employer")
Session("FieldB")=Request.Form("CheckNum")
Session("FieldC")=Request.Form("CheckAmount")
Session("FieldD")=Request.Form("CustomerType")
Session("FieldE")=Request.Form("AmountEarned")
Response.Redirect(mainURL)
End If
Else If Request.Form("Employer")=rs("CompanyID4") Then
If Request.Form("added")="Yes" Then
strSQLExec="UPDATE Customers SET CompanyID4Scanned='Yes'
Where
CustomerID='" & Request.Form("CustomerID") & "';"
rsExec.Open strSQLExec, adoCon
rs.Close
rs.Open strSQL, adoCon, 3, 3
End If
If rs("CompanyID4Scanned")="No" Then
Session("Option")="1-0-2"
Session("TempData")=rs("CustomerID") & "-" &
rs("CompanyID4")
Session("FieldA")=Request.Form("Employer")
Session("FieldB")=Request.Form("CheckNum")
Session("FieldC")=Request.Form("CheckAmount")
Session("FieldD")=Request.Form("CustomerType")
Session("FieldE")=Request.Form("AmountEarned")
Response.Redirect(mainURL)
End If
Else If Request.Form("Employer")=rs("CompanyID5") Then
If Request.Form("added")="Yes" Then
strSQLExec="UPDATE Customers SET CompanyID5Scanned='Yes'
Where
CustomerID='" & Request.Form("CustomerID") & "';"
rsExec.Open strSQLExec, adoCon
rs.Close
rs.Open strSQL, adoCon, 3, 3
End If
If rs("CompanyID5Scanned")="No" Then
Session("Option")="1-0-2"
Session("TempData")=rs("CustomerID") & "-" &
rs("CompanyID5")
Session("FieldA")=Request.Form("Employer")
Session("FieldB")=Request.Form("CheckNum")
Session("FieldC")=Request.Form("CheckAmount")
Session("FieldD")=Request.Form("CustomerType")
Session("FieldE")=Request.Form("AmountEarned")
Response.Redirect(mainURL)
End If
Else If Request.Form("Employer")=rs("CompanyID6") Then
If Request.Form("added")="Yes" Then
strSQLExec="UPDATE Customers SET CompanyID6Scanned='Yes'
Where
CustomerID='" & Request.Form("CustomerID") & "';"
rsExec.Open strSQLExec, adoCon
rs.Close
rs.Open strSQL, adoCon, 3, 3
End If
If rs("CompanyID6Scanned")="No" Then
Session("Option")="1-0-2"
Session("TempData")=rs("CustomerID") & "-" &
rs("CompanyID6")
Session("FieldA")=Request.Form("Employer")
Session("FieldB")=Request.Form("CheckNum")
Session("FieldC")=Request.Form("CheckAmount")
Session("FieldD")=Request.Form("CustomerType")
Session("FieldE")=Request.Form("AmountEarned")
Response.Redirect(mainURL)
End If
End If
End If
End If
End If
End If
End If
rs.Close
strSQL="Select * From Checks;"
rs.Open strSQL, adoCon, 3, 3
strSQLExec="INSERT INTO Checks VALUES('" & Session("StoreID") &
100000+rs.RecordCount & "','" & Request.Form("CustomerID") & "','" &
Request.Form("Employer") & "','" & Request.Form("CheckNum") & "','" &
Request.Form("CheckAmount") & "','" & Request.Form("CustomerType") &
"','Good','" & Request.Form("AmountEarned") & "','" & Session("StoreID")
& "','" & Session("Username") & "','" & Time & "','" & Date &
"','','','');"
rs.Close
rsExec.Open strSQLExec, adoCon
Session("Message")="1-0-1-9-0"
Session("Option")="1-0-0"
Session("TempData")=Request.Form("CheckNum")
Response.Redirect(mainURL)
Please let me know what in the world is going on, I've never seen a
problem like this.
I also have another page that inserts into the Companies table and it
does the same thing.
What happense exactly is:
1. I add a new row and it adds it just fine.
2. When I try to add another new row it adds it fine but it creates a
duplicate of that row with different TransactionID's.
I'm using MS Access as the database hooked up through a DSN
Any help would be greatly appreciated.
Thanks,
Suler Abou
President/CEO
Suler's Computer Services
Su***@SulersCS.com - 248-760-4454
AIM: SulersCS
MSN: Su***@SulersCS.com
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!