Database Double Entry
I have a strange problem that just started affecting one of my pages. It doesn't affect any other page or site. When a web form is completed and parsed into the database, it shows up twice. This behavior is the same for Access and SQL 7, so... doubtful its a database problem.
Here is the code that parses the form:
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<%
'// Variables MUST be declared
Option Explicit
'// Kill intruders & wake the dead
If (Not Session("blnValidUser")) OR (Not CInt(Session("UserID")) > 0) OR (CInt(Session("UserType")) <> 1) Then
Response.Redirect "xreLogin.asp?BadPage=True"
End If
'// Variable Declarations
Dim strConnect ' String: Connection String for database communication
Dim objCommand ' Object: Server Object for executing commands on the database
Dim rsListings ' Recordset: Recordset for Listings
Dim intNoOfRecords ' Integer: Number of records affected by update
Dim ListingID ' Integer: Listing to modify
Dim FirmID ' Integer: FirmID for database
Dim UserID ' Integer: UserID from Session cookie
Dim rsFirms ' Recordset: Recordset for Firms
Dim strFirmSQL ' String: SQL String to query database of Firms
Dim blnNew ' Boolean: Is this a new listing?
Dim DateListed ' String: Date Property was listed - put together from three form inputs
Dim DateModified ' String: Date Property was modified - Now()
'// Initialize the connection string
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\DB\Database\xtest.mdb; " & _
"Persist Security Info=False"
'strConnect = "Provider=SQLOLEDB;Persist Security Info=False; " & _
' "User ID=sa;Initial Catalog=XrealEstate; " & _
' "Initial File Name=E:\MSSQL7\Data\xtest.mdf"
'// Create the Command object
Set objCommand = Server.CreateObject("ADODB.Command")
'// Initialize the Command object
objCommand.ActiveConnection = strConnect
objCommand.CommandType = adCmdText
'// Find out who is using us
UserID = Session("UserID")
'// Prepare Firms Recordset to get Firm data
objCommand.CommandText = "SELECT * FROM Firms WHERE UserID = " & UserID
Set rsFirms = objCommand.Execute
'// If FirmID cannot be found, throw an error
If rsFirms.EOF Then
Response.Redirect "xreErrPage.asp?Error=USER_NOT_FOUND_IN_FIRMS_ " & UserID
End If
FirmID = rsFirms.Fields("FirmID").value
'// If FirmID is not numeric, present error
If FirmID <= 0 Then
Response.Redirect "xreErrPage.asp?Error=No_Firm_Listed_" & FirmID
End If
'// Close Firms Database
Set rsFirms = Nothing
'// Determine whether to create a new row in the Listings table
If IsNumeric(Request.Form("ListingID")) Then
ListingID = CInt(Request.Form("ListingID"))
blnNew = False
Else
blnNew = True
End If
'// Initialize dates for database
DateListed = Request.Form("DateListedMonth") & "/" & Request.Form("DateListedDay") & "/" & Request.Form("DateListedYear")
DateModified = Now()
'// Create CommandText based on whether this is a new or updated listing
If blnNew Then
objCommand.CommandText = "INSERT INTO Listings (FirmID, MLXID, PropertyTypeID, StateID, AgentID, DateListed, DateModified, UserID) " & _
"VALUES (" & FirmID & ", '" & Request.Form("MLXID") & "', " & CInt(Request.Form("PropertyTypeID")) & _
", " & CInt(Request.Form("StateID")) & ", " & CInt(Request.Form("AgentID")) & ", #" & DateListed & "#, #" & _
DateModified & "#, " & UserID & ")"
Else
objCommand.CommandText = "UPDATE Listings SET FirmID = " & FirmID & ", MLXID = '" & Request.Form("MLXID") & _
"', PropertyTypeID = " & CInt(Request.Form("PropertyTypeID")) & ", StateID = " & CInt(Request.Form("StateID")) & _
", AgentID = " & CInt(Request.Form("AgentID")) & ", DateListed = #" & DateListed & "#, DateModified = #" & _
DateModified & "#, UserID = " & UserID & " WHERE ListingID = " & ListingID & " AND FirmID = " & FirmID
End If
'// Execute the command on the database & get number of records affected
objCommand.Execute intNoOfRecords
'// Clean up the Command Object
Set objCommand = Nothing
'// Get outta here
Response.Redirect "xreErrPage.asp?Error=Test_" & intNoOfRecords
%>
...
Now, what's interesting is that the intNoOfRecords holds "1", but there are two identical rows added to the database. I'm going cross-eyed, so if anyone has any ideas, they'll be very appreciated.
Robert
|
