Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Location
    Holland
    Posts
    16

    Unhappy Unanswered: Updating Multiple Records

    I am trying to update multiple records, but with no success yet. I have 2 columns named rbs and code. And I have an asp-page that generates random login codes.

    Now if I run this code everything works fine. But if I add records after running the code I would like to give them the same logincode if the rbs value is the same or generate new logincodes if the rbs is new.

    Here is my code.

    <!--#include file="Adovbs.inc" -->
    <HTML><BODY><TABLE>

    <%

    Randomize()

    set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="&Server.MapPath("../db/codes3.mdb")
    set rs = Server.CreateObject("ADODB.Recordset")
    set rs2 = Server.CreateObject("ADODB.Recordset")
    rs.open "makecode ORDER BY rbs",conn, adOpenForwardOnly,adLockPessimistic,adCmdTable
    rbs = ""
    code = ""
    while not rs.EOF
    if rs("rbs") <> rbs then
    do
    code = ""
    for i = 1 to 8
    getal = Int(Rnd()*102)
    if getal>75 then
    getal=getal+21
    elseif getal>49 then
    getal=getal+47
    elseif getal>39 then
    getal=getal+8
    elseif getal>29 then
    getal=getal+18
    elseif getal>19 then
    getal=getal+28
    elseif getal>9 then
    getal=getal+38
    else
    getal=getal+48
    end if
    code = code & chr(getal)
    next
    rs2.open "SELECT rbs FROM makecode WHERE rbs<>'"&rs("rbs")&"' AND code='"&code&"'",conn
    if rs2.eof then
    ok=true
    else
    ok=false
    response.write("<TR><TD>CODE EXISTS</TD><TD>REMAKING CODE</TD></TR>")
    end if
    rs2.close
    loop until ok
    end if
    rbs = rs("rbs")
    rs("code") = code
    response.write("<TR><TD>"&rbs&"</TD><TD>"&code&"</TD></TR>")
    rs.update
    rs.movenext
    wend

    rs.close
    set rs=Nothing
    set rs2=Nothing
    %>
    </TABLE</BODY></HTML>

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Well, I was in the middle of posting a response on Friday night and BAM - dbforums had a problem.

    Why don't you just delete the records that repeat instead of updating the new one and having duplicates ? Are you using Access (which version) and what value does the field code have when a record is first created (null ?) ? Basically, the solution requires 2 steps. First, update the new records in the database that have a corresponding rbs. Once this is completed, you can use the same script that is already posted but using a where clause that looks for nulls in the code field.

  3. #3
    Join Date
    Aug 2002
    Location
    Holland
    Posts
    16
    I now understand it's probably simplest to create 2 pages. The first will have to compare the logincodes and the second will generate new ones.

    But how can I compare and update?

    I can not delete the records that appear to be double because that only goes for the rbs column. There are also adressee field that differ.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    When a new record is added - what is the value of code (null ...) ?

  5. #5
    Join Date
    Aug 2002
    Location
    Holland
    Posts
    16
    Yes, the value of the code field is empty.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following as a template:

    UPDATE table1 AS b
    LEFT JOIN table1 AS a
    ON b.rbs=a.rbs
    SET a.code = b.code
    WHERE (((a.code) Is Null) AND ((b.code) Is Not Null));

    Create a recordset object to handle this. Next just execute your asp code but looking for nulls. You should be able to handle this in one page.

    Good luck.

  7. #7
    Join Date
    Aug 2002
    Location
    Holland
    Posts
    16

    Thumbs up

    You deserve that 5th star!!! Fantastic. I posted my problem on 6 different forums, but nobody came up with a good solution!!! Many thanks to you.........

Posting Permissions

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