By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 1,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Single INSERT statement creates Duplicate Entries!

P: n/a
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!
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.