Single INSERT statement creates Duplicate Entries!

From: Suler Abou (sulerscs_at_gmail.com)
Date: 08/09/04

Next message: gordy: "Re: converting log file to db table"
Previous message: Andrew: "Summing tables in a UDF"
Next in thread: Uri Dimant: "Re: Single INSERT statement creates Duplicate Entries!"
Reply: Uri Dimant: "Re: Single INSERT statement creates Duplicate Entries!"
Reply: Suler Abou: "Re: Single INSERT statement creates Duplicate Entries!"
Messages sorted by: [ date ] [ thread ]

Date: Sun, 08 Aug 2004 19:24:58 -0700

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.

Any help would be greatly appreciated.

Thanks,
Suler Abou
Suler@SulersCS.com - 248-760-4454

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Next message: gordy: "Re: converting log file to db table"
Previous message: Andrew: "Summing tables in a UDF"
Next in thread: Uri Dimant: "Re: Single INSERT statement creates Duplicate Entries!"
Reply: Uri Dimant: "Re: Single INSERT statement creates Duplicate Entries!"
Reply: Suler Abou: "Re: Single INSERT statement creates Duplicate Entries!"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: Automating a query
... good luck, ... I guess I did not have the right code in for the SQL statement? ... then set index of the fieldin "test" ... "No Duplicates" ...
(microsoft.public.access.queries)
Re: Check table for like values
... am looking for a way to find more than just duplicates. ... FROM tblImported INNER JOIN tblContacts ... If you are trying to do this programmatically, copy the SQL statement above ... Dim strSql As String ...
(microsoft.public.access.modulesdaovba)
Re: Check table for like values
... am looking for a way to find more than just duplicates. ... FROM tblImported INNER JOIN tblContacts ... If you are trying to do this programmatically, copy the SQL statement above ... Dim strSql As String ...
(microsoft.public.access.modulesdaovba)
Re: Dublicate Records in Combo Box
... >>Try this SQL statement: ... >>DISTINCTROW returns records that are unique for all ... I can't get those duplicates to go away, ...
(microsoft.public.access.forms)
Re: is there a better way to do this?
... to avoid confusion and forgot to remove them from the output of the ... duplicates are 1 and 2. ... or 2 for B. I needed a sql statement to return that information ...
(comp.databases.oracle.server)