no duplicate records

Results 1 to 7 of 7

Thread: no duplicate records

  1. #1
    calmcp Guest

    Default no duplicate records

    I am trying to make sure that a user does not enter a record twice. Using stored query in access. Please help. Here is code:<BR><BR><BR>&#039;Connection set up completed <BR>&#039;Create command object to pass stored parameters to stored query <BR>Set objCommand = Server.CreateObject("ADODB.Command") <BR>objCommand.CommandText = "CheckOwnerProc" <BR>objCommand.CommandType = &H0004 <BR>objCommand.Parameters.Append objCommand.CreateParameter("OUserName", 200, &H0001, 20) <BR>objCommand.Parameters.Append objCommand.CreateParameter("OUserPassword", 200, &H0001, 20) <BR>objCommand("OUserName") = Request.Form("UserName") <BR>objCommand("OUserPassword") = Request.Form("UserPassword")objCommand.ActiveConne ction = Conn <BR>objCommand.execute <BR><BR>&#039;Create recordset of owner&#039;s and properties listed <BR>Set SelProperties = Server.CreateObject("ADODB.Recordset") <BR>SelProperties.CursorType = 2 <BR>SelProperties.LockType = 3 <BR>SelProperties.Open objCommand <BR><BR>&#039;Check to see if user is listed <BR>If SelProperties.EOF Then <BR>Response.Write "&#060;P&#062;&#060;FONT SIZE=+2&#062; You must register to list your property! &#060;/FONT&#062;&#060;/P&#062;" <BR>Else <BR>RegOK = True <BR>End If <BR><BR>Now here is where I&#039;m confused I have this recordset that might have one or many records in it. How can I make sure that the user doesn&#039;t list the property again. I have already run my sql to get this recordset. If I added property address to sql to limit the recordset to just that one property how would i know if property has already been listed or if owner hasn&#039;t registered. Is it necessary to open one recordset to check if owner is registered and then close it and open another one to see if property is listed. Seems like to much work and to many calls to the db.

  2. #2
    calmcp Guest

    Default Does anybody know how to do this? Desert Ghost?

    This is a suggestion from the asp boaard. Does anybody know how to make a sql that returns what is suggested below. I have access db.<BR><BR>I am *NOT* a SP user. I tend to use MySQL. <BR><BR>Having said that, and having looked at the SP that Steve suggested... <BR><BR>I guess that I would suggest that you expand the capabilities of the SP! <BR><BR>Pass in both the user registration info *and* the property to be added. <BR><BR>And come back with one of *THREE* answers from the SP instead of only two: <BR><BR>"Not registered" <BR>"Already in database" <BR>"OKAY" <BR><BR>(Personally, I&#039;d just come back with a number, instead of a string, but that&#039;s me.) <BR><BR>If you can&#039;t figure out how to write the SP to do that, then I&#039;d go ask the question in the Databases forum, instead of here. And/or hit up Steve for help. (grin...but I think he&#039;s gone for the weekend.) <BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: no duplicate records

    Just check if there is a match UPDATE or whatever,if not INSERT...<BR>try it first with a query in your page and then with sp.

  4. #4
    felix perro Guest

    Default RE: no duplicate records

    ...also when checking if the record exists as Greg mentioned, you might want to count only the id field, or at least an integer field. Do not count everything as it will slow down your queries.<BR><BR>e.g. this is what I use in SQL stored proc<BR>-------<BR><BR>create procedure [procedureName]<BR> @inputField1 int,<BR> @inputFiled2 int<BR><BR>as<BR><BR>if ((select count(id) where something = somethingelse) &#062; 0)<BR> begin<BR> /* do update since record exists */<BR> update [table] set this = that<BR> end<BR>else<BR> begin<BR> /* record doesn&#039;t exists so insert data */<BR> insert into [table]<BR> (id, field2)<BR> values<BR> (@inputField1, @inputField2)<BR> end<BR>-------<BR><BR>See the MS Knowledge base or the SQL Books Online for more info.<BR><BR>Cheers,<BR><BR>felix perro

  5. #5
    calmcp Guest

    Default RE: no duplicate records

    can u give me a example or tutorial to look at. some pseudo anything. this is driving me crazy. i am almost ready to open two recordsets but that seems so unnecessary.

  6. #6
    felix perro Guest

    Default RE : RE : no duplicate records

    That kind of was psuedo-code, at least I thought.<BR><BR>I am not familiar with the differences between Access and SQL server but here is the concept behind your stored proc you need :<BR><BR>1. Pass the data to the procedure the same for inserts *and* update. Try to make your code more generic this way.<BR><BR>2. But, in the stored procedure analyze the data in a way that checks if the data you are given already exists.<BR><BR>3. For example, do a count of the id [ count(id) as this is fastest ] to see if the this data exists.<BR><BR>4. If the count is greater than 0, then a record exists so just do an update. If the count is less than or equal to 0, then the record does not exist so insert this new data.<BR><BR>This if..else logic is performed inside the stored procedure so that your calling ASP code does not have to adjust anything and can be more generic (a good programming practice). The bulk of the work is done by the DB in terms of a compiled stored procedure and only needs 1 call to the DB.<BR><BR>Does this help?...<BR><BR>felix perro

  7. #7
    calmcp Guest

    Default RE: no duplicate records

    In earlier post I say that this is an access database which I believe does not support sp&#039;s. These are stored queries which are different then stored queries. I am a newbie so forgive me if I am not correct. But if I understand what you are saying I should query for the record and do an IF/THEN to determine if the record exist if it does just update it and if it doesn&#039;t add it. The only thing I wondering is this would allow a user to enter the record again and again without ever letting them know the record has already been entered.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts