Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Creating a form to Edit Multiple Records Simultaneously
Data Manipulation
Ok, so you have a huge table full of records and you need to create an editing screen for them. But rather than make the user edit one record at a time, or delete one record at a time, or add one record from a completely different location, you want to allow the user to make all the changes they can think of and then submit them all at once.
Sounds easy, right?
Ok, well here is a sample table that people might want to have editing capabilities on, a user table:
TableName: UserAccount
Fields:
[color #800000]
ua_id - autonumber or seeded integer
ua_firstname - text or varchar
ua_middleinitial - text or varchar
ua_lastname - text or varchar
ua_email - text or varchar
ua_login_name - text or varchar
ua_password - text or varchar
ua_enabled - True/False
[/color]
Quick Note: The reason I have odd names for my fields is because I have dealt too much with MS Access in the past, throwing underscores in field names generally keeps me from running into that 'Reserved Word' issue that crops up so often, plus it helps later when trying to remember which table I am getting a field from. Ie., a field starting with ua_ in this case is coming from UserAccount, nifty eh?
Quick Note 2: I dislike deleting users from the database becaus they are often attached to other records. In this case we are going to put an enabled boolean in with each user. If it is true then they are allowed to log in, if it is false they are, in essence, deleted or not allowed to login, while we don't lose any records dependant on that user.
Onward and upward.
Planning
So we want to have one page where all the current records are displayed in textboxes for editing except maybe the password. And we want the capability to reset a password (lets use a checkbox) and a method to mark someone for deletion (again, the checkbox is our friend). Plus we want to have some blanks at the bottom to allow the user to enter a new user record to the list.
Before jumping into the creation of an HTML table and form, we need to stop and think about how we are going to handle submitting the changes on the next page.
Firstly, we need to be able to differentiate which last name input belongs to which user, the next consideration is do we want to simply update every single user that was displayed or take the more complicated (and ultimately more efficient) route of only updating those that have changed?
Planning - Input Naming Technique
Lets start with input differentiation. We have two options, either name them all the same so that we end up with arrays of values being passed for first name, last name, etc which we can simply loop through:
[color #000000][color #800000]<%
[color #008000]'etc[/color]
firstName = Split(Request.Form([color #FF00FF]"txtFirstName"[/color]), [color #FF00FF]", "[/color])
lastName = Split(Request.Form([color #FF00FF]"txtLastName"[/color]), [color #FF00FF]", "[/color])
[color #008000]'if we now loop through the arrays, firstName(i) should belong to the same record as lastName(i)...usually[/color]
%>[/color][/color]
Not bad, but the biggest problem your going to face with this method is the user that puts a comma in one of the entries. Suddenly that one entry will be split in half and every single later entry will be shifted to the next record, resuilting in chaos and a huge mess to cleanup, probably by hand. Ick.
The other method is to put all of the record id's into a comma-delimited list, but then append the id to each field in your form like so:
[color #000000][color #008000]<!--somewhere inside your future recordset loop that we both know we'll need -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"ua_id"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs([color #FF00FF]"ua_id"[/color])%>[/color]"[/color]>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_[color #800000]<%=rs([color #FF00FF]"ua_id"[/color])%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs([color #FF00FF]"ua_firstname"[/color])%>[/color]"[/color]>[/color][/color]
This will then be used on the next page to loop through the ua_id's and allow you to select the input values for that exact id:
[color #000000][color #800000]<%
Dim ua_ids, id
ua_ids = Split(Request.Form([color #FF00FF]"ua_id"[/color]), [color #FF00FF]", "[/color])
For Each id in ua_ids
Response.Write [color #FF00FF]"The first name for ua_id "[/color] & id & [color #FF00FF]" is most definately "[/color] & Request.Form([color #FF00FF]"txtFirstName_"[/color] & id)
Next
%>[/color][/color]
So if we concatenate the id onto every field we will be able to not only differentiate them, we will be able to access them at will without worrying about things like extraneous commas.
Building The Form
First we need to get the data we will use to build the form, so lets grab it from our pretend Access Database
[color #000000][color #800000]<%
Option Explicit
[color #008000]'Dim some variables[/color]
Dim conn, conn_str, sql_users, rs_users
conn_str = [color #FF00FF]"Provider=Microsoft.Jet.OLEDB.4.0;"[/color] & _
[color #FF00FF]"Data Source="[/color] & Server.MapPath([color #FF00FF]"myData.mdb"[/color]) & [color #FF00FF]";"[/color] & _
[color #FF00FF]"Persist Security Info=False"[/color]
[color #008000]'setup the connection[/color]
Set conn = Server.CreateObject([color #FF00FF]"ADODB.Connection"[/color])
conn.Open(conn_str)
[color #008000]'make our SQL Statement[/color]
sql_users = [color #FF00FF]"SELECT * FROM UserAccount ORDER BY ua_lastname"[/color]
[color #008000]'pull in the data[/color]
Set rs_users = conn.Execute(sql_users)
%>[/color][/color]
Note for efficiencies sake, we should use the GetRows() method of the Recordst object to pull that data into an array. It's not much harder to loop through an array than it is to loop through a Recordset, but it is faster.
Anyways, lets start our form now and add all the inputs for the user to edit:
[color #000000][color blue]<form[color #FF0000] method[/color]=[color #FF00FF]"POST"[/color][color #FF0000] action[/color]=[color #FF00FF]"MySubmitPage.asp"[/color][color #FF0000] name[/color]=[color #FF00FF]"frmEdit"[/color]>[/color]
[color blue]<table>[/color]
[color blue]<tr>[/color]
[color blue]<th>[/color]Last Name[color blue]</th>[/color]
[color blue]<th>[/color]First Name[color blue]</th>[/color]
[color blue]<th>[/color]Middle Initial[color blue]</th>[/color]
[color blue]<th>[/color]Email Address[color blue]</th>[/color]
[color blue]<th>[/color]Login Name[color blue]</th>[/color]
[color blue]<th>[/color]Reset Password?[color blue]</th>[/color]
[color blue]<th>[/color]Delete?[color blue]</th>[/color]
[color blue]</tr>[/color]
[color #800000]<%
[color #008000]'queue up to the top record of the recordset[/color]
If Not rs_users.EOF Then rs_users.MoveFirst
[color #008000]'make a temporary variable for user id because the less typing the better
[/color]
dim t_id
[color #008000]'loop through all the records outputting a row for each one[/color]
Do Until rs_users.EOF
t_id = rs_users([color #FF00FF]"ua_id"[/color])
%>[/color]
[color blue]<tr>[/color]
[color blue]<td>[/color]
[color #008000]<!-- Here is the id for this loop -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"ua_id"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=t_id%>[/color]"[/color]>[/color]
[color #008000]<!-- Very important, don't forget length and maxlengths -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLastName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_lastname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_firstname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtMiddleInitial_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_middleinitial"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"1"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"1"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtEmail_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_email"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"30"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"50"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLoginName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_login_name"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"14"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"14"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkResetPass_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkEnabled_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color][color #800000]<%
[color #008000]'if the value is true, output checked[/color]
If rs_users([color #FF00FF]"ua_enabled"[/color]) = True The Response.Write [color #FF00FF]" checked"[/color]%>[/color]>[/color]
[color blue]</td>[/color]
[color blue]</tr>[/color]
[color #800000]<%
rs_users.MoveNext
Loop
%>[/color]
[color blue]<tr>[/color][color blue]<td[color #FF0000] colspan[/color]=[color #FF00FF]""[/color]>[/color][color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"submit"[/color][color #FF0000] value[/color]=[color #FF00FF]"Save My Changes"[/color]>[/color][color blue]</td>[/color][color blue]</tr>[/color]
[color blue]</table>[/color]
[color blue]</form>[/color][/color]
For now we will forgo adding a blank line to let users add new records, that part is a relatively easy addition we will make in a few minutes.
So now we have our dynamic form that is being populated from the database and is allowing the user to edit entries to their hearts content. It's time to move on to the page that will handle sending the actual updates to the website.
At this point there is no way to tell if only one record has been edited or if all records have been edited. I'll show some examples later that are javascript dependant that will allow you to speify how to only update changed rows, but for now we will simply update every record in the table.
I'll do this as commented code so you can follow along the whole flow rather than brief spurts:
MySubmitPage.asp
[color #000000][color #800000]<%
Option Explicit
[color #008000]'first things first, Dim some variables.[/color]
[color #008000]'We need an array for id's, a database connection, and an SQL string[/color]
Dim arr_ids, conn, conn_str, sql_update
[color #008000]'Lets make a function to reduce the number of times we have to type Request.Form and Replace functions[/color]
Function CleanInput(strReqName)
CleanInput = Replace(Request.Form(strReqName),[color #FF00FF]"'"[/color],"'")
End Function
[color #008000]'now lets go ahead and put together our connection, pretend the location for the db is correct[/color]
conn_str = [color #FF00FF]"Provider=Microsoft.Jet.OLEDB.4.0;"[/color] & _
[color #FF00FF]"Data Source="[/color] & Server.MapPath([color #FF00FF]"myDatabase.mdb"[/color]) & [color #FF00FF]";"[/color] & _
[color #FF00FF]"Persist Security Info=False"[/color]
conn.Open conn_str
[color #008000]'and fill our arr_ids with the ids - split on comma space because when we have multiple form[/color]
[color #008000]' inputs with the same name the value comes through as a comma-space delimited string. [/color]
[color #008000]' Someone somewhere decided we should be gramatically correct with multiple form inputs, [/color]
[color #008000]'I'm not bitter...)[/color]
arr_ids = Split(Request.Form([color #FF00FF]"ua_id"[/color]),[color #FF00FF]", "[/color])
[color #008000]'now loop through each id, build the sql, and execute the sql[/color]
Dim id
For Each id in arr_ids
sql_update = [color #FF00FF]"UPDATE UserAccount SET ua_firstname = '"[/color] & CleanInput([color #FF00FF]"txtFirstName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_middleinitial = '"[/color] & CleanInput([color #FF00FF]"txtMiddleInitial_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_lastname = '"[/color] & CleanInput([color #FF00FF]"txtLastName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_email = '"[/color] & CleanInput([color #FF00FF]"txtEmail_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_login_name = '"[/color] & CleanInput([color #FF00FF]"txtLoginName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_enabled = "[/color]
[color #008000]'if there is a value for the enabled checkbox, ua_enabled is true, otherwise it is false[/color]
If Request.Form([color #FF00FF]"chkEnabled_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]"True"[/color]
Else
sql_update = sql_update & [color #FF00FF]"False"[/color]
End If
[color #008000]'Don't forget about the possible password reset[/color]
If Request.Form([color #FF00FF]"chkResetPass_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]", ua_password = '"[/color] & & [color #FF00FF]"'"[/color]
End If
[color #008000]'And finally the WHERE portion[/color]
sql_update = sql_update & [color #FF00FF]" WHERE ua_id = "[/color] & id
[color #008000]'Make it so![/color]
conn.Execute sql_update
Next
[color #008000]'And don't forget to clean up after yourself[/color]
conn.Close
Set conn = Nothing
[color #008000]'and just to be nice, lets redirect them back to display page. [/color]
[color #008000]'we'll pretend I specified somewhere above that it was named Display.asp[/color]
Response.Redirect [color #FF00FF]"Display.asp"[/color]
%>[/color][/color]
Well, that wsn't to difficult.
Now for the additions.
New Record Creation Option:
First, we want to add a blank section for users to add new entries, so lets make the necessary additions to the two files:
Display.asp
...
[color #000000] [color #800000]<%
rs_users.MoveNext
Loop
%>[/color]
[color #008000]<!-- BEGINNING OF NEW CODE ------------------------------>[/color]
[color blue]<td>[/color]
[color #008000]<!-- This record has no id, it will be new, but we still want an identifier so we will use 'n' -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLastName_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtMiddleInitial_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"1"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"1"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtEmail_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"30"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"50"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLoginName_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"14"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"14"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color #008000]<!-- obviously they need a password, so we will ignore this checkbox but make it obvious to the user
that the starting password will be the same as if they had reset the pass for someone els, ie the last name -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkResetPass_n"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color] checked readonly>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkEnabled_n"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color]>[/color]
[color blue]</td>[/color]
[color blue]</tr>[/color]
[color #008000]<!-- END OF NEW CODE --------------------------------------->[/color]
[color blue]<tr>[/color][color blue]<td[color #FF0000] colspan[/color]=[color #FF00FF]""[/color]>[/color][color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"submit"[/color][color #FF0000] value[/color]=[color #FF00FF]"Save My Changes"[/color]>[/color][color blue]</td>[/color][color blue]</tr>[/color]
[color blue]</table>[/color]
[color blue]</form>[/color][/color]
Then in our processing file we will need to do something with this section:
MySubmitPage.asp
[color #000000][color #800000]<%
[color #008000]'This comes after the loop statement in the original code[/color]
[color #008000]'-- START OF ADDITION ------------------------------------------[/color]
[color #008000]'First we need to see if the entris have been filled, if they have then we will add this record[/color]
Dim str_fnm, str_mi, str_lnm, str_eml, str_lgn, bln_enabled
str_fnm = CleanInput([color #FF00FF]"txtFirstName_n"[/color])
str_lnm = CleanInput([color #FF00FF]"txtLastName_n"[/color])
str_mi = CleanInput([color #FF00FF]"txtMiddleInitial_n"[/color])
str_eml = CleanInput([color #FF00FF]"txtEmaile_n"[/color])
str_lgn = CleanInput([color #FF00FF]"txtLoginName_n"[/color])
If Request.Form([color #FF00FF]"chkEnabled_n"[/color]) <> [color #FF00FF]""[/color] Then bln_enabled = True Else bln_enabled = False
[color #000000][color #800000]<%
[color #008000]'we'll ignore middle initial[/color]
If len(str_fnm) > 0 And len(str_lnm) > 0 And len(str_eml) > 0 And len(str_lgn) > 0 Then
[color #008000]'We can go ahead and insert[/color]
sql_update = [color #FF00FF]"INSERT INTO UserAccount(ua_firstname, ua_middleinitial, ua_lastname, ua_email, ua_login_name, ua_password, ua_enabled)"[/color] & _
[color #FF00FF]"VALUES('"[/color] & str_fnm & [color #FF00FF]"','"[/color] & str_mi & [color #FF00FF]"','"[/color] & str_lnm & [color #FF00FF]"','"[/color] & str_eml & [color #FF00FF]"','"[/color] & str_lgn & [color #FF00FF]"','"[/color] & str_lnm & [color #FF00FF]"',"[/color] & bln_enabledm & [color #FF00FF]")"[/color]
[color #008000]'send it to the database[/color]
conn.Execute sql_update
End If
[color #008000]'-- END OF ADDITION --------------------------------------------[/color]
[color #008000]'don't forget to clean up after yourself[/color]
conn.Close
Set conn = Nothing
[color #008000]'and just to be nice, lets redirect them back to display page. [/color]
[color #008000]'we'll pretend I specified somewhere above that it was named Display.asp[/color]
Response.Redirect [color #FF00FF]"Display.asp"[/color]
%>[/color][/color][/color]
Only Update Records That Have Changed:
This one takes some javascript and the addition of a new hidden input to keep track of the change state, so here is the change to the first file:
Display.asp
[color #000000][color #800000]<%
[color #008000]'loop through all the records outputting a row for each one[/color]
Do Until rs_users.EOF
t_id = rs_users([color #FF00FF]"ua_id"[/color])
%>[/color]
[color blue]<tr>[/color]
[color blue]<td>[/color]
[color #008000]<!-- BEGINNING OF CHANGES ---------------------->[/color]
[color #008000]<!-- Here is the id for this loop -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"ua_id"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=t_id%>[/color]"[/color]>[/color]
[color #008000]<!-- ADDTN: We are adding a hidden input to track the change state -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"change_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]""[/color]>[/color]
[color #008000]<!-- ADDTN: We are adding a javascript onChange to all inputs to update the change input -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLastName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_lastname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_firstname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtMiddleInitial_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_middleinitial"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"1"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"1"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtEmail_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_email"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"30"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"50"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLoginName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_login_name"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"14"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"14"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkResetPass_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color][color #FF0000] onClick[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkEnabled_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color][color #800000]<%
[color #008000]'if the value is true, output checked[/color]
If rs_users([color #FF00FF]"ua_enabled"[/color]) = True The Response.Write [color #FF00FF]" checked"[/color]%>[/color] [color #FF0000] onClick[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]</tr>[/color]
[color #008000]<!-- END OF CHANGES ---------------------------------->[/color]
[color #800000]<%
rs_users.MoveNext
Loop
%>[/color][/color]
And then for our processing page we can simply add an if statement to only update records where their change_# has a value:
MySubmitPage.asp
[color #000000][color #800000]<%
[color #008000]'...[/color]
Dim id
For Each id in arr_ids
[color #008000]'-- BEGINNING OF CHANGES ------------------------------------[/color]
[color #008000]' if the change field has a value, then update it, otherwise do nothing[/color]
If Request.Form([color #FF00FF]"change_"[/color] & id) <> [color #FF00FF]""[/color]
sql_update = [color #FF00FF]"UPDATE UserAccount SET ua_firstname = '"[/color] & CleanInput([color #FF00FF]"txtFirstName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_middleinitial = '"[/color] & CleanInput([color #FF00FF]"txtMiddleInitial_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_lastname = '"[/color] & CleanInput([color #FF00FF]"txtLastName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_email = '"[/color] & CleanInput([color #FF00FF]"txtEmail_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_login_name = '"[/color] & CleanInput([color #FF00FF]"txtLoginName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_enabled = "[/color]
[color #008000]'if there is a value for the enabled checkbox, ua_enabled is true, otherwise it is false[/color]
If Request.Form([color #FF00FF]"chkEnabled_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]"True"[/color]
Else
sql_update = sql_update & [color #FF00FF]"False"[/color]
End If
[color #008000]'don't forget about the possible password reset[/color]
If Request.Form([color #FF00FF]"chkResetPass_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]", ua_password = '"[/color] & & [color #FF00FF]"'"[/color]
End If
[color #008000]'And finally the WHERE portion[/color]
sql_update = sql_update & [color #FF00FF]" WHERE ua_id = "[/color] & id
[color #008000]'Make it so![/color]
conn.Execute sql_update
End If
[color #008000]'-- END OF CHANGES ------------------------------------------[/color]
Next
[color #008000]'...[/color]
%>[/color][/color]
And there we have it, a (mostly) full functional pair of pages that will allow your users to update multiple records at a time and submit their changes or additions.
I will leaveit up to you to make this work for your individual databases and table layouts, as well as putting all the pieces together. Try extending the code to allow a user to actually delete a record rather than just disable it.
Hint: Another if statement where we just added one, even if you don't use the javascript additions, may be a good start on this
Sounds easy, right?
Ok, well here is a sample table that people might want to have editing capabilities on, a user table:
TableName: UserAccount
Fields:
[color #800000]
ua_id - autonumber or seeded integer
ua_firstname - text or varchar
ua_middleinitial - text or varchar
ua_lastname - text or varchar
ua_email - text or varchar
ua_login_name - text or varchar
ua_password - text or varchar
ua_enabled - True/False
[/color]
Quick Note: The reason I have odd names for my fields is because I have dealt too much with MS Access in the past, throwing underscores in field names generally keeps me from running into that 'Reserved Word' issue that crops up so often, plus it helps later when trying to remember which table I am getting a field from. Ie., a field starting with ua_ in this case is coming from UserAccount, nifty eh?
Quick Note 2: I dislike deleting users from the database becaus they are often attached to other records. In this case we are going to put an enabled boolean in with each user. If it is true then they are allowed to log in, if it is false they are, in essence, deleted or not allowed to login, while we don't lose any records dependant on that user.
Onward and upward.
Planning
So we want to have one page where all the current records are displayed in textboxes for editing except maybe the password. And we want the capability to reset a password (lets use a checkbox) and a method to mark someone for deletion (again, the checkbox is our friend). Plus we want to have some blanks at the bottom to allow the user to enter a new user record to the list.
Before jumping into the creation of an HTML table and form, we need to stop and think about how we are going to handle submitting the changes on the next page.
Firstly, we need to be able to differentiate which last name input belongs to which user, the next consideration is do we want to simply update every single user that was displayed or take the more complicated (and ultimately more efficient) route of only updating those that have changed?
Planning - Input Naming Technique
Lets start with input differentiation. We have two options, either name them all the same so that we end up with arrays of values being passed for first name, last name, etc which we can simply loop through:
[color #000000][color #800000]<%
[color #008000]'etc[/color]
firstName = Split(Request.Form([color #FF00FF]"txtFirstName"[/color]), [color #FF00FF]", "[/color])
lastName = Split(Request.Form([color #FF00FF]"txtLastName"[/color]), [color #FF00FF]", "[/color])
[color #008000]'if we now loop through the arrays, firstName(i) should belong to the same record as lastName(i)...usually[/color]
%>[/color][/color]
Not bad, but the biggest problem your going to face with this method is the user that puts a comma in one of the entries. Suddenly that one entry will be split in half and every single later entry will be shifted to the next record, resuilting in chaos and a huge mess to cleanup, probably by hand. Ick.
The other method is to put all of the record id's into a comma-delimited list, but then append the id to each field in your form like so:
[color #000000][color #008000]<!--somewhere inside your future recordset loop that we both know we'll need -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"ua_id"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs([color #FF00FF]"ua_id"[/color])%>[/color]"[/color]>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_[color #800000]<%=rs([color #FF00FF]"ua_id"[/color])%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs([color #FF00FF]"ua_firstname"[/color])%>[/color]"[/color]>[/color][/color]
This will then be used on the next page to loop through the ua_id's and allow you to select the input values for that exact id:
[color #000000][color #800000]<%
Dim ua_ids, id
ua_ids = Split(Request.Form([color #FF00FF]"ua_id"[/color]), [color #FF00FF]", "[/color])
For Each id in ua_ids
Response.Write [color #FF00FF]"The first name for ua_id "[/color] & id & [color #FF00FF]" is most definately "[/color] & Request.Form([color #FF00FF]"txtFirstName_"[/color] & id)
Next
%>[/color][/color]
So if we concatenate the id onto every field we will be able to not only differentiate them, we will be able to access them at will without worrying about things like extraneous commas.
Building The Form
First we need to get the data we will use to build the form, so lets grab it from our pretend Access Database
[color #000000][color #800000]<%
Option Explicit
[color #008000]'Dim some variables[/color]
Dim conn, conn_str, sql_users, rs_users
conn_str = [color #FF00FF]"Provider=Microsoft.Jet.OLEDB.4.0;"[/color] & _
[color #FF00FF]"Data Source="[/color] & Server.MapPath([color #FF00FF]"myData.mdb"[/color]) & [color #FF00FF]";"[/color] & _
[color #FF00FF]"Persist Security Info=False"[/color]
[color #008000]'setup the connection[/color]
Set conn = Server.CreateObject([color #FF00FF]"ADODB.Connection"[/color])
conn.Open(conn_str)
[color #008000]'make our SQL Statement[/color]
sql_users = [color #FF00FF]"SELECT * FROM UserAccount ORDER BY ua_lastname"[/color]
[color #008000]'pull in the data[/color]
Set rs_users = conn.Execute(sql_users)
%>[/color][/color]
Note for efficiencies sake, we should use the GetRows() method of the Recordst object to pull that data into an array. It's not much harder to loop through an array than it is to loop through a Recordset, but it is faster.
Anyways, lets start our form now and add all the inputs for the user to edit:
[color #000000][color blue]<form[color #FF0000] method[/color]=[color #FF00FF]"POST"[/color][color #FF0000] action[/color]=[color #FF00FF]"MySubmitPage.asp"[/color][color #FF0000] name[/color]=[color #FF00FF]"frmEdit"[/color]>[/color]
[color blue]<table>[/color]
[color blue]<tr>[/color]
[color blue]<th>[/color]Last Name[color blue]</th>[/color]
[color blue]<th>[/color]First Name[color blue]</th>[/color]
[color blue]<th>[/color]Middle Initial[color blue]</th>[/color]
[color blue]<th>[/color]Email Address[color blue]</th>[/color]
[color blue]<th>[/color]Login Name[color blue]</th>[/color]
[color blue]<th>[/color]Reset Password?[color blue]</th>[/color]
[color blue]<th>[/color]Delete?[color blue]</th>[/color]
[color blue]</tr>[/color]
[color #800000]<%
[color #008000]'queue up to the top record of the recordset[/color]
If Not rs_users.EOF Then rs_users.MoveFirst
[color #008000]'make a temporary variable for user id because the less typing the better
[/color]dim t_id
[color #008000]'loop through all the records outputting a row for each one[/color]
Do Until rs_users.EOF
t_id = rs_users([color #FF00FF]"ua_id"[/color])
%>[/color]
[color blue]<tr>[/color]
[color blue]<td>[/color]
[color #008000]<!-- Here is the id for this loop -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"ua_id"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=t_id%>[/color]"[/color]>[/color]
[color #008000]<!-- Very important, don't forget length and maxlengths -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLastName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_lastname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_firstname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtMiddleInitial_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_middleinitial"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"1"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"1"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtEmail_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_email"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"30"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"50"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLoginName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_login_name"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"14"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"14"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkResetPass_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkEnabled_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color][color #800000]<%
[color #008000]'if the value is true, output checked[/color]
If rs_users([color #FF00FF]"ua_enabled"[/color]) = True The Response.Write [color #FF00FF]" checked"[/color]%>[/color]>[/color]
[color blue]</td>[/color]
[color blue]</tr>[/color]
[color #800000]<%
rs_users.MoveNext
Loop
%>[/color]
[color blue]<tr>[/color][color blue]<td[color #FF0000] colspan[/color]=[color #FF00FF]""[/color]>[/color][color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"submit"[/color][color #FF0000] value[/color]=[color #FF00FF]"Save My Changes"[/color]>[/color][color blue]</td>[/color][color blue]</tr>[/color]
[color blue]</table>[/color]
[color blue]</form>[/color][/color]
For now we will forgo adding a blank line to let users add new records, that part is a relatively easy addition we will make in a few minutes.
So now we have our dynamic form that is being populated from the database and is allowing the user to edit entries to their hearts content. It's time to move on to the page that will handle sending the actual updates to the website.
At this point there is no way to tell if only one record has been edited or if all records have been edited. I'll show some examples later that are javascript dependant that will allow you to speify how to only update changed rows, but for now we will simply update every record in the table.
I'll do this as commented code so you can follow along the whole flow rather than brief spurts:
MySubmitPage.asp
[color #000000][color #800000]<%
Option Explicit
[color #008000]'first things first, Dim some variables.[/color]
[color #008000]'We need an array for id's, a database connection, and an SQL string[/color]
Dim arr_ids, conn, conn_str, sql_update
[color #008000]'Lets make a function to reduce the number of times we have to type Request.Form and Replace functions[/color]
Function CleanInput(strReqName)
CleanInput = Replace(Request.Form(strReqName),[color #FF00FF]"'"[/color],"'")
End Function
[color #008000]'now lets go ahead and put together our connection, pretend the location for the db is correct[/color]
conn_str = [color #FF00FF]"Provider=Microsoft.Jet.OLEDB.4.0;"[/color] & _
[color #FF00FF]"Data Source="[/color] & Server.MapPath([color #FF00FF]"myDatabase.mdb"[/color]) & [color #FF00FF]";"[/color] & _
[color #FF00FF]"Persist Security Info=False"[/color]
conn.Open conn_str
[color #008000]'and fill our arr_ids with the ids - split on comma space because when we have multiple form[/color]
[color #008000]' inputs with the same name the value comes through as a comma-space delimited string. [/color]
[color #008000]' Someone somewhere decided we should be gramatically correct with multiple form inputs, [/color]
[color #008000]'I'm not bitter...)[/color]
arr_ids = Split(Request.Form([color #FF00FF]"ua_id"[/color]),[color #FF00FF]", "[/color])
[color #008000]'now loop through each id, build the sql, and execute the sql[/color]
Dim id
For Each id in arr_ids
sql_update = [color #FF00FF]"UPDATE UserAccount SET ua_firstname = '"[/color] & CleanInput([color #FF00FF]"txtFirstName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_middleinitial = '"[/color] & CleanInput([color #FF00FF]"txtMiddleInitial_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_lastname = '"[/color] & CleanInput([color #FF00FF]"txtLastName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_email = '"[/color] & CleanInput([color #FF00FF]"txtEmail_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_login_name = '"[/color] & CleanInput([color #FF00FF]"txtLoginName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_enabled = "[/color]
[color #008000]'if there is a value for the enabled checkbox, ua_enabled is true, otherwise it is false[/color]
If Request.Form([color #FF00FF]"chkEnabled_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]"True"[/color]
Else
sql_update = sql_update & [color #FF00FF]"False"[/color]
End If
[color #008000]'Don't forget about the possible password reset[/color]
If Request.Form([color #FF00FF]"chkResetPass_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]", ua_password = '"[/color] & & [color #FF00FF]"'"[/color]
End If
[color #008000]'And finally the WHERE portion[/color]
sql_update = sql_update & [color #FF00FF]" WHERE ua_id = "[/color] & id
[color #008000]'Make it so![/color]
conn.Execute sql_update
Next
[color #008000]'And don't forget to clean up after yourself[/color]
conn.Close
Set conn = Nothing
[color #008000]'and just to be nice, lets redirect them back to display page. [/color]
[color #008000]'we'll pretend I specified somewhere above that it was named Display.asp[/color]
Response.Redirect [color #FF00FF]"Display.asp"[/color]
%>[/color][/color]
Well, that wsn't to difficult.
Now for the additions.
New Record Creation Option:
First, we want to add a blank section for users to add new entries, so lets make the necessary additions to the two files:
Display.asp
...
[color #000000] [color #800000]<%
rs_users.MoveNext
Loop
%>[/color]
[color #008000]<!-- BEGINNING OF NEW CODE ------------------------------>[/color]
[color blue]<td>[/color]
[color #008000]<!-- This record has no id, it will be new, but we still want an identifier so we will use 'n' -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLastName_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtMiddleInitial_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"1"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"1"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtEmail_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"30"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"50"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLoginName_n"[/color][color #FF0000] size[/color]=[color #FF00FF]"14"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"14"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color #008000]<!-- obviously they need a password, so we will ignore this checkbox but make it obvious to the user
that the starting password will be the same as if they had reset the pass for someone els, ie the last name -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkResetPass_n"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color] checked readonly>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkEnabled_n"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color]>[/color]
[color blue]</td>[/color]
[color blue]</tr>[/color]
[color #008000]<!-- END OF NEW CODE --------------------------------------->[/color]
[color blue]<tr>[/color][color blue]<td[color #FF0000] colspan[/color]=[color #FF00FF]""[/color]>[/color][color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"submit"[/color][color #FF0000] value[/color]=[color #FF00FF]"Save My Changes"[/color]>[/color][color blue]</td>[/color][color blue]</tr>[/color]
[color blue]</table>[/color]
[color blue]</form>[/color][/color]
Then in our processing file we will need to do something with this section:
MySubmitPage.asp
[color #000000][color #800000]<%
[color #008000]'This comes after the loop statement in the original code[/color]
[color #008000]'-- START OF ADDITION ------------------------------------------[/color]
[color #008000]'First we need to see if the entris have been filled, if they have then we will add this record[/color]
Dim str_fnm, str_mi, str_lnm, str_eml, str_lgn, bln_enabled
str_fnm = CleanInput([color #FF00FF]"txtFirstName_n"[/color])
str_lnm = CleanInput([color #FF00FF]"txtLastName_n"[/color])
str_mi = CleanInput([color #FF00FF]"txtMiddleInitial_n"[/color])
str_eml = CleanInput([color #FF00FF]"txtEmaile_n"[/color])
str_lgn = CleanInput([color #FF00FF]"txtLoginName_n"[/color])
If Request.Form([color #FF00FF]"chkEnabled_n"[/color]) <> [color #FF00FF]""[/color] Then bln_enabled = True Else bln_enabled = False
[color #000000][color #800000]<%
[color #008000]'we'll ignore middle initial[/color]
If len(str_fnm) > 0 And len(str_lnm) > 0 And len(str_eml) > 0 And len(str_lgn) > 0 Then
[color #008000]'We can go ahead and insert[/color]
sql_update = [color #FF00FF]"INSERT INTO UserAccount(ua_firstname, ua_middleinitial, ua_lastname, ua_email, ua_login_name, ua_password, ua_enabled)"[/color] & _
[color #FF00FF]"VALUES('"[/color] & str_fnm & [color #FF00FF]"','"[/color] & str_mi & [color #FF00FF]"','"[/color] & str_lnm & [color #FF00FF]"','"[/color] & str_eml & [color #FF00FF]"','"[/color] & str_lgn & [color #FF00FF]"','"[/color] & str_lnm & [color #FF00FF]"',"[/color] & bln_enabledm & [color #FF00FF]")"[/color]
[color #008000]'send it to the database[/color]
conn.Execute sql_update
End If
[color #008000]'-- END OF ADDITION --------------------------------------------[/color]
[color #008000]'don't forget to clean up after yourself[/color]
conn.Close
Set conn = Nothing
[color #008000]'and just to be nice, lets redirect them back to display page. [/color]
[color #008000]'we'll pretend I specified somewhere above that it was named Display.asp[/color]
Response.Redirect [color #FF00FF]"Display.asp"[/color]
%>[/color][/color][/color]
Only Update Records That Have Changed:
This one takes some javascript and the addition of a new hidden input to keep track of the change state, so here is the change to the first file:
Display.asp
[color #000000][color #800000]<%
[color #008000]'loop through all the records outputting a row for each one[/color]
Do Until rs_users.EOF
t_id = rs_users([color #FF00FF]"ua_id"[/color])
%>[/color]
[color blue]<tr>[/color]
[color blue]<td>[/color]
[color #008000]<!-- BEGINNING OF CHANGES ---------------------->[/color]
[color #008000]<!-- Here is the id for this loop -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"ua_id"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=t_id%>[/color]"[/color]>[/color]
[color #008000]<!-- ADDTN: We are adding a hidden input to track the change state -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"hidden"[/color][color #FF0000] name[/color]=[color #FF00FF]"change_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]""[/color]>[/color]
[color #008000]<!-- ADDTN: We are adding a javascript onChange to all inputs to update the change input -->[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLastName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_lastname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtFirstName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_firstname"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"20"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"30"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtMiddleInitial_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_middleinitial"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"1"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"1"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtEmail_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_email"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"30"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"50"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"text"[/color][color #FF0000] name[/color]=[color #FF00FF]"txtLoginName_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"[color #800000]<%=rs_users([color #FF00FF]"ua_login_name"[/color])%>[/color]"[/color][color #FF0000] size[/color]=[color #FF00FF]"14"[/color][color #FF0000] maxlength[/color]=[color #FF00FF]"14"[/color][color #FF0000] onChange[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkResetPass_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color][color #FF0000] onClick[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]<td>[/color]
[color blue]<input[color #FF0000] type[/color]=[color #FF00FF]"checkbox"[/color][color #FF0000] name[/color]=[color #FF00FF]"chkEnabled_[color #800000]<%=t_id%>[/color]"[/color][color #FF0000] value[/color]=[color #FF00FF]"yep"[/color][color #800000]<%
[color #008000]'if the value is true, output checked[/color]
If rs_users([color #FF00FF]"ua_enabled"[/color]) = True The Response.Write [color #FF00FF]" checked"[/color]%>[/color] [color #FF0000] onClick[/color]=[color #FF00FF]"frmEdit.change_[color #800000]<%=t_id%>[/color].value='edited';"[/color]>[/color]
[color blue]</td>[/color]
[color blue]</tr>[/color]
[color #008000]<!-- END OF CHANGES ---------------------------------->[/color]
[color #800000]<%
rs_users.MoveNext
Loop
%>[/color][/color]
And then for our processing page we can simply add an if statement to only update records where their change_# has a value:
MySubmitPage.asp
[color #000000][color #800000]<%
[color #008000]'...[/color]
Dim id
For Each id in arr_ids
[color #008000]'-- BEGINNING OF CHANGES ------------------------------------[/color]
[color #008000]' if the change field has a value, then update it, otherwise do nothing[/color]
If Request.Form([color #FF00FF]"change_"[/color] & id) <> [color #FF00FF]""[/color]
sql_update = [color #FF00FF]"UPDATE UserAccount SET ua_firstname = '"[/color] & CleanInput([color #FF00FF]"txtFirstName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_middleinitial = '"[/color] & CleanInput([color #FF00FF]"txtMiddleInitial_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_lastname = '"[/color] & CleanInput([color #FF00FF]"txtLastName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_email = '"[/color] & CleanInput([color #FF00FF]"txtEmail_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_login_name = '"[/color] & CleanInput([color #FF00FF]"txtLoginName_"[/color] & id) & [color #FF00FF]"', "[/color] & _
[color #FF00FF]"ua_enabled = "[/color]
[color #008000]'if there is a value for the enabled checkbox, ua_enabled is true, otherwise it is false[/color]
If Request.Form([color #FF00FF]"chkEnabled_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]"True"[/color]
Else
sql_update = sql_update & [color #FF00FF]"False"[/color]
End If
[color #008000]'don't forget about the possible password reset[/color]
If Request.Form([color #FF00FF]"chkResetPass_"[/color] & id) <> [color #FF00FF]""[/color] Then
sql_update = sql_update & [color #FF00FF]", ua_password = '"[/color] & & [color #FF00FF]"'"[/color]
End If
[color #008000]'And finally the WHERE portion[/color]
sql_update = sql_update & [color #FF00FF]" WHERE ua_id = "[/color] & id
[color #008000]'Make it so![/color]
conn.Execute sql_update
End If
[color #008000]'-- END OF CHANGES ------------------------------------------[/color]
Next
[color #008000]'...[/color]
%>[/color][/color]
And there we have it, a (mostly) full functional pair of pages that will allow your users to update multiple records at a time and submit their changes or additions.
I will leaveit up to you to make this work for your individual databases and table layouts, as well as putting all the pieces together. Try extending the code to allow a user to actually delete a record rather than just disable it.
Hint: Another if statement where we just added one, even if you don't use the javascript additions, may be a good start on this