Updating Multiple Records [Archive]

View Full Version : Updating Multiple Records

EvE

08-18-02, 08:42

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.


<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>

rnealejr

08-18-02, 20:38

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.

EvE

08-19-02, 04:33

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.

rnealejr

08-19-02, 20:19

When a new record is added - what is the value of code (null ...) ?

EvE

08-20-02, 03:39

Yes, the value of the code field is empty.

rnealejr

08-20-02, 22:50

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.

EvE

08-21-02, 09:02

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.........