INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Manipulation

Creating a form to Edit Multiple Records Simultaneously by Tarwn
Posted: 26 Nov 03 (Edited 14 Dec 03)

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:

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


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:
<%
'etc
firstName = Split(Request.Form("txtFirstName"), ", ")
lastName = Split(Request.Form("txtLastName"), ", ")
'if we now loop through the arrays, firstName(i) should belong to the same record as lastName(i)...usually
%>


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:
<!--somewhere inside your future recordset loop that we both know we'll need -->
<input type="hidden" name="ua_id" value="<%=rs("ua_id")%>">
<input type="text" name="txtFirstName_<%=rs("ua_id")%>" value="<%=rs("ua_firstname")%>">



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:
<%
Dim ua_ids, id
ua_ids = Split(Request.Form("ua_id"), ", ")
For Each id in ua_ids
Response.Write "The first name for ua_id " & id & " is most definately " & Request.Form("txtFirstName_" & id)
Next
%>


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
<%
Option Explicit

'Dim some variables
Dim conn, conn_str, sql_users, rs_users
conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Server.MapPath("myData.mdb") & ";" & _
            "Persist Security Info=False"

'setup the connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(conn_str)

'make our SQL Statement
sql_users = "SELECT * FROM UserAccount ORDER BY ua_lastname"

'pull in the data
Set rs_users = conn.Execute(sql_users)
%>



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:
<form method="POST" action="MySubmitPage.asp" name="frmEdit">
<table>
<tr>
         <th>Last Name</th>
         <th>First Name</th>
         <th>Middle Initial</th>
         <th>Email Address</th>
         <th>Login Name</th>
         <th>Reset Password?</th>
         <th>Delete?</th>
</tr>
<%
'queue up to the top record of the recordset
     If Not rs_users.EOF Then rs_users.MoveFirst
     
'make a temporary variable for user id because the less typing the better :)
dim t_id

'loop through all the records outputting a row for each one
     Do Until rs_users.EOF
     t_id = rs_users("ua_id")
%>

<tr>
         <td>
             <!-- Here is the id for this loop -->
             <input type="hidden" name="ua_id" value="<%=t_id%>">
             <!-- Very important, don't forget length and maxlengths -->
             <input type="text" name="txtLastName_<%=t_id%>" value="<%=rs_users("ua_lastname")%>" size="20" maxlength="30">
</td>
         <td>
             <input type="text" name="txtFirstName_<%=t_id%>" value="<%=rs_users("ua_firstname")%>" size="20" maxlength="30">
</td>
         <td>
             <input type="text" name="txtMiddleInitial_<%=t_id%>" value="<%=rs_users("ua_middleinitial")%>" size="1" maxlength="1">
</td>
         <td>
             <input type="text" name="txtEmail_<%=t_id%>" value="<%=rs_users("ua_email")%>" size="30" maxlength="50">
</td>
         <td>
             <input type="text" name="txtLoginName_<%=t_id%>" value="<%=rs_users("ua_login_name")%>" size="14" maxlength="14">
</td>
         <td>
             <input type="checkbox" name="chkResetPass_<%=t_id%>" value="yep">
</td>
         <td>
             <input type="checkbox" name="chkEnabled_<%=t_id%>" value="yep"<%
'if the value is true, output checked
                 If rs_users("ua_enabled") = True The Response.Write " checked"%>
>

</td>
         </tr>
         <%
rs_users.MoveNext
     Loop
%>

     <tr><td colspan=""><input type="submit" value="Save My Changes"></td></tr>
</table>
</form>



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
<%
Option Explicit

'first things first, Dim some variables.
'We need an array for id's, a database connection, and an SQL string
Dim arr_ids, conn, conn_str, sql_update

'Lets make a function to reduce the number of times we have to type Request.Form and Replace functions
Function CleanInput(strReqName)
CleanInput = Replace(Request.Form(strReqName),"'","''")
End Function

'now lets go ahead and put together our connection, pretend the location for the db is correct
conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("myDatabase.mdb") & ";" & _
"Persist Security Info=False"
conn.Open conn_str

'and fill our arr_ids with the ids - split on comma space because when we have multiple form
'    inputs with the same name the value comes through as a comma-space delimited string.
'    Someone somewhere decided we should be gramatically correct with multiple form inputs,
'I'm not bitter...)
arr_ids = Split(Request.Form("ua_id"),", ")

'now loop through each id, build the sql, and execute the sql
Dim id
For Each id in arr_ids
sql_update = "UPDATE UserAccount SET ua_firstname = '" & CleanInput("txtFirstName_" & id) & "', " & _
"ua_middleinitial = '" & CleanInput("txtMiddleInitial_" & id) & "', " & _
                "ua_lastname = '" & CleanInput("txtLastName_" & id) & "', " & _
                "ua_email = '" & CleanInput("txtEmail_" & id) & "', " & _
                "ua_login_name = '" & CleanInput("txtLoginName_" & id) & "', " & _
                "ua_enabled = "

'if there is a value for the enabled checkbox, ua_enabled is true, otherwise it is false
    If Request.Form("chkEnabled_" & id) <> "" Then
     sql_update = sql_update & "True"
    Else
     sql_update = sql_update & "False"
    End If

'Don't forget about the possible password reset
If Request.Form("chkResetPass_" & id) <> "" Then
sql_update = sql_update & ", ua_password = '" & & "'"
End If

'And finally the WHERE portion
sql_update = sql_update & " WHERE ua_id = " & id

'Make it so!
conn.Execute sql_update
Next

'And don't forget to clean up after yourself
conn.Close
Set conn = Nothing

'and just to be nice, lets redirect them back to display page.
'we'll pretend I specified somewhere above that it was named Display.asp
Response.Redirect "Display.asp"
%>



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
...
<%
rs_users.MoveNext
     Loop
%>

     <!-- BEGINNING OF NEW CODE ------------------------------>
<td>
<!-- This record has no id, it will be new, but we still want an identifier so we will use 'n' -->
<input type="text" name="txtLastName_n" size="20" maxlength="30">
</td>
<td>
<input type="text" name="txtFirstName_n" size="20" maxlength="30">
</td>
<td>
<input type="text" name="txtMiddleInitial_n" size="1" maxlength="1">
</td>
<td>
<input type="text" name="txtEmail_n" size="30" maxlength="50">
</td>
<td>
<input type="text" name="txtLoginName_n" size="14" maxlength="14">
</td>
<td>
     <!-- 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 -->

<input type="checkbox" name="chkResetPass_n" value="yep" checked readonly>
</td>
<td>
<input type="checkbox" name="chkEnabled_n" value="yep">
</td>
</tr>
<!-- END OF NEW CODE --------------------------------------->
<tr><td colspan=""><input type="submit" value="Save My Changes"></td></tr>
</table>
</form>


Then in our processing file we will need to do something with this section:
MySubmitPage.asp
<%
'This comes after the loop statement in the original code

'-- START OF ADDITION ------------------------------------------
'First we need to see if the entris have been filled, if they have then we will add this record
Dim str_fnm, str_mi, str_lnm, str_eml, str_lgn, bln_enabled
str_fnm = CleanInput("txtFirstName_n")
str_lnm = CleanInput("txtLastName_n")
str_mi = CleanInput("txtMiddleInitial_n")
str_eml = CleanInput("txtEmaile_n")
str_lgn = CleanInput("txtLoginName_n")
If Request.Form("chkEnabled_n") <> "" Then bln_enabled = True Else bln_enabled = False


<%

'we'll ignore middle initial
If len(str_fnm) > 0 And len(str_lnm) > 0 And len(str_eml) > 0 And len(str_lgn) > 0 Then
'We can go ahead and insert
sql_update = "INSERT INTO UserAccount(ua_firstname, ua_middleinitial, ua_lastname, ua_email, ua_login_name, ua_password, ua_enabled)" & _
                   "VALUES('" & str_fnm & "','" & str_mi & "','" & str_lnm & "','" & str_eml & "','" & str_lgn & "','" & str_lnm & "'," & bln_enabledm & ")"
    
'send it to the database
conn.Execute sql_update
End If
'-- END OF ADDITION --------------------------------------------

'don't forget to clean up after yourself
conn.Close
Set conn = Nothing

'and just to be nice, lets redirect them back to display page.
'we'll pretend I specified somewhere above that it was named Display.asp
Response.Redirect "Display.asp"
%>



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
<%
'loop through all the records outputting a row for each one
     Do Until rs_users.EOF
     t_id = rs_users("ua_id")
%>

<tr>
         <td>
             <!-- BEGINNING OF CHANGES ---------------------->
             <!-- Here is the id for this loop -->
             <input type="hidden" name="ua_id" value="<%=t_id%>">
             <!-- ADDTN: We are adding a hidden input to track the change state -->
             <input type="hidden" name="change_<%=t_id%>" value="">
             <!-- ADDTN: We are adding a javascript onChange to all inputs to update the change input -->
             <input type="text" name="txtLastName_<%=t_id%>" value="<%=rs_users("ua_lastname")%>" size="20" maxlength="30" onChange="frmEdit.change_<%=t_id%>.value='edited';">
</td>
         <td>
             <input type="text" name="txtFirstName_<%=t_id%>" value="<%=rs_users("ua_firstname")%>" size="20" maxlength="30" onChange="frmEdit.change_<%=t_id%>.value='edited';">
</td>
         <td>
             <input type="text" name="txtMiddleInitial_<%=t_id%>" value="<%=rs_users("ua_middleinitial")%>" size="1" maxlength="1" onChange="frmEdit.change_<%=t_id%>.value='edited';">
</td>
         <td>
             <input type="text" name="txtEmail_<%=t_id%>" value="<%=rs_users("ua_email")%>" size="30" maxlength="50" onChange="frmEdit.change_<%=t_id%>.value='edited';">
</td>
         <td>
             <input type="text" name="txtLoginName_<%=t_id%>" value="<%=rs_users("ua_login_name")%>" size="14" maxlength="14" onChange="frmEdit.change_<%=t_id%>.value='edited';">
</td>
         <td>
             <input type="checkbox" name="chkResetPass_<%=t_id%>" value="yep" onClick="frmEdit.change_<%=t_id%>.value='edited';">
</td>
         <td>
             <input type="checkbox" name="chkEnabled_<%=t_id%>" value="yep"<%
'if the value is true, output checked
                 If rs_users("ua_enabled") = True The Response.Write " checked"%>
onClick="frmEdit.change_<%=t_id%>.value='edited';">

</td>
         </tr>
         <!-- END OF CHANGES ---------------------------------->
         <%
rs_users.MoveNext
     Loop
%>



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
<%
'...
Dim id
For Each id in arr_ids
'-- BEGINNING OF CHANGES ------------------------------------
' if the change field has a value, then update it, otherwise do nothing
If Request.Form("change_" & id) <> ""
sql_update = "UPDATE UserAccount SET ua_firstname = '" & CleanInput("txtFirstName_" & id) & "', " & _
"ua_middleinitial = '" & CleanInput("txtMiddleInitial_" & id) & "', " & _
"ua_lastname = '" & CleanInput("txtLastName_" & id) & "', " & _
"ua_email = '" & CleanInput("txtEmail_" & id) & "', " & _
"ua_login_name = '" & CleanInput("txtLoginName_" & id) & "', " & _
"ua_enabled = "

'if there is a value for the enabled checkbox, ua_enabled is true, otherwise it is false
If Request.Form("chkEnabled_" & id) <> "" Then
sql_update = sql_update & "True"
Else
sql_update = sql_update & "False"
End If

'don't forget about the possible password reset
If Request.Form("chkResetPass_" & id) <> "" Then
sql_update = sql_update & ", ua_password = '" & & "'"
End If

'And finally the WHERE portion
sql_update = sql_update & " WHERE ua_id = " & id

'Make it so!
conn.Execute sql_update
End If
'-- END OF CHANGES ------------------------------------------
Next
'...
%>


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

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close