Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 13th, 2003, 01:29 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old June 13th, 2003, 04:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:16 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.