Update a record and it's duplicates at same time

[joelo]

what I am trying to do is:...I have a users table and list of user...one user is assigned to different facility
ie

Lname Fname UID Pword Facility
GOO MOO M MEE LAG
GOO MOO M MEE MAG
GOO MOO M MEE WAG

I want to be able change Pword all at same time

I don't know if I am making any sense

This is my code:
-------------------
Set conn = Server.CreateObject("ADODB.Connection")
conn.open xDb_Conn_Str

strsql = "select all * from [usersdatabase]" _
& " where userid = '" & Session("userid") & "' AND lastname = '" & session("lastname") & "' AND firstname = '" & session("firstname") & "'"
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn, 1, 2
if (rs.BOF and rs.EOF) then
Response.Write("<font size='3' color='red'><b>Userid '" & userid & "' not found, try again.....</b></font><p>")
elseif Request.form("password") <> rs("PASSWORD") then
Response.Write("<font size='3' color='red'><b>Incorrect Current password, Please try again....</b></font><p>")
else
'Update record with new password.
rs("USERID") = Session("userid")
rs("PASSWORD") = Request.Form("newpass1")
rs.Update
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Write("<font size='3' color='red'><b>Your password has been successfully changed.....Thank You</b></font><p>")
end if
end if
end if

[Daniel Klann]

Hello,

If I understand you correctly then you just need to loop through your recordset and update the password field in each record (I won't repost the whole script, just the relevant part):-


'Update record with new password.
While Not rs.EOF
'rs("USERID") = Session("userid") I don't think this is necessary
rs("PASSWORD") = Request.Form("newpass1")
rs.Update
rs.movenext
Wend


A preferable way might be to use an UPDATE query instead using the Execute method of the connection object e.g.


conn.Execute "UPDATE usersdatabase SET [usersdatabase].[PASSWORD]='" & _
Request.Form("newpass1") & "' WHERE userid='" & Session("userid") & _
"' AND lastname = '" & Session("lastname") & "' AND firstname = '" & _
Session("firstname") & "'"


Hope that helps you out,

Daniel

[CardboardHammer]

Better yet, normalize your tables and get rid of the problem entirely. Users and facilities are separate entities. Keep them in separate tables and associate users to facilities in a third table of the form User_ID, Facility_ID. If you need to change any aspect of the user (last name, password, whatever) or facility (name, phone number, whatever), you only need to change one record, not many.

It'll be a bit of work to normalize what already exists, but the sooner it's dealt with, the better off in the long run (and when adding new tables, make sure they're normalized from the start).

There are good reasons not to normalize in certain situations (I've actually got a system that has parts which fall into this category), but this isn't one of those situations.