duplicate records on INSERT command

Question

duplicate records on INSERT command

cakegal

SSC Enthusiast

Points: 162
More actions
October 27, 2005 at 12:57 am

#66829

Hi All. I'm new to this forum and hope that anyone can help.
I have a access db and are inserting records to a table via Classic ASP. Whenever I run this code below, sometimes I get duplicates of the same record (with a different id #) and sometimes I don't. I can't figure out to save my life what is wrong with this code. Any suggestions welcomed! -- Thanks!
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/theone.mdb") & ";"
set my_Connn = Server.CreateObject("ADODB.Connection")
my_Connn.Open strConnString
'check to see if password exists
set csPassword = Server.CreateObject("ADODB.Recordset")
strSql1 = "Select * from Employees where Password='" & strPassword & "'"
set csPassword = my_Connn.Execute(strsql1)
'if password okay, then insert record
If not(csPassword.eof) then
     set csInsert= Server.CreateObject("ADODB.Recordset")
     strSql = "Insert into Comments (OrderID, EmployeeID, CommentDate, Comments, Status) Values ('" & orderid & "','" & csPassword("EmployeeID") & "','" & Now() & "'"
     If tacomment <> "" Then
          strSql = strSql & ",'" & tacomment & "'"
     Else
          strSql = strSql & ",''"
     End if
     strSql = strSql & ",'" & rStatus & "')"
set csInsert=my_Connn.Execute(strSql)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steven Bense Valued Member Points: 72 More actions · Answer 1

Try the following:

I assume that this code is run when you click a Submit button on your asp web page. Are you sure that the user is not double-clicking? Or browser refreshing the page (F5 in IE), thus causing the page to be resubmitted? Both these actions will create a duplicate record.

A safer option would be to actually do a check to see if the record exists, BEFORE you call the insert. That way you will catch cases where users accidentally double click or refresh the page.

Assuming this is all your code, I would suggest changes as shown in bold.

'check to see if password exists
Set csPassword = Server.CreateObject("ADODB.Recordset")
strsql1 = "Select * from Employees where Password='" & strPassword & "'"
Set csPassword = my_Connn.Execute(strsql1)
'if password okay, then insert record
If Not (csPassword.EOF) Then
    'Check of this record exists
    Set csCheck = Server.CreateObject("ADODB.Recordset")
    strsql1 = "Select * from Comments where OrderID='" & orderid & "'"  
    strsql1 =strsql1 & " AND EmployeeID='" & csPassword("EmployeeID") & "'" & " AND Status='" & rStatus & "'"

    Set csCheck = my_Connn.Execute(strsql1)
    'If not, then insert new record
    If (csCheck.EOF) Then

        Set csInsert = Server.CreateObject("ADODB.Recordset")
        strSql = "Insert into Comments (OrderID, EmployeeID, CommentDate, Comments, Status) Values ('" & orderid & "','" & csPassword("EmployeeID") & "','" & Now() & "'"
        If tacomment <> "" Then
             strSql = strSql & ",'" & tacomment & "'"
        Else
             strSql = strSql & ",''"
        End If
        
        strSql = strSql & ",'" & rStatus & "')"
        Set csInsert = my_Connn.Execute(strSql)


    End If
End If

Hope this helps or gets you on the right track.

cakegal SSC Enthusiast Points: 162 More actions · Answer 2

cakegal

SSC Enthusiast

Points: 162

October 29, 2005 at 1:19 am

#601154

Thanks. I will try that.