Wrox Programmer Forums
|
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 13th, 2003, 01:29 AM
Registered User
 
Join Date: Jun 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old June 13th, 2003, 04:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sounds weird. But, hey, a basic INSERT statement like yours can only insert one record so my guess is that, for some reason, this page is being run twice and the bInNew test isn't working properly so the second time it does another insert rather than an update.

that's my best guess at the moment.
hth
Phil





Similar Threads
Thread Thread Starter Forum Replies Last Post
Checking for duplicate entry in Database sharon5469 ASP.NET 1.0 and 1.1 Basics 1 November 18th, 2007 06:08 PM
DOUBLE ENTRY K38AT Access 1 October 23rd, 2007 12:21 PM
Reuse form for database entry kwng Pro VB Databases 0 April 30th, 2007 02:26 PM
Searching a database for an entry sconineuk VB.NET 2002/2003 Basics 6 March 24th, 2006 12:25 AM
Database Entry Problems gmoney060 Classic ASP Databases 4 November 9th, 2004 08:19 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.