469,631 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

Single INSERT statement creates Duplicate Entries!

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
0 2668

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Gary Lundquest | last post: by
18 posts views Thread by Elroyskimms | last post: by
3 posts views Thread by Hai Nguyen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.