Update Multiple Records with SQL Update and Where/In Clause

There may be instances where one wants to update a field value for a large, select group of records. Doing this one record at a time is a killer on productivity. However, using checkboxes and a drop-down list, one can select a large group of records and then define a DMX Command to update the records in one fell swoop. This builds upon an article written by Marcellino Bommezijn concerning the deletion of multiple records and uses the SQL WHERE IN clause that he used in that tutorial.

Updating Multiple Records using SQL Update and the Where/In Clause with Checkboxes

There may be instances where one wants to update a field value for a large, select group of records. Doing this one record at a time is a killer on productivity. However, using checkboxes and a drop-down list, one can select a large group of records and then define a DMX Command to update the records in one fell swoop. This builds upon an article written by Marcellino Bommezijn concerning the deletion of multiple records and uses the SQL WHERE IN clause that he used in that tutorial.

Assumptions:

  1. The person using this tutorial is familiar with databases and connection strings/DSNs. These will not be covered in this tutorial. I assume that one can setup or already has setup these conventions to follow the tutorial.
  2. This tutorial references Microsoft Access as the database of choice for this tutorial, however, this example can be done in MySQL or SQL databases. I assume one has the knowledge to setup the databases as necessary for whatever server application is being used.
  3. The person using this tutorial is familiar with Dreamweaver.

Step 1: Building the Form

In the following example, I will assign students to classroom groups. I will use checkboxes to assign students to a selected classroom group. I am using a simple Microsoft Access database designed like the following:

Setup a table named students with the following field names, types:
  • student_id, Autonumber - Primary Key
  • student_name, text
  • assigned_group, text

Start by saving a new ASP file as frm_assign_group_students.asp.

Create a recordset named rs_students that pulls all records from the student table.

SELECT *
FROM students

On our form, we will use a list menu to choose which group we are assigning. In this example, we simply add values to the list, though this could also be populated by another recordset using Dyanmic assignments in DMX. We will also setup a repeating region for all records in rs_students. The method of submission will be GET.

The list menu is a simple select tag that looks like this:

<select name="group_option">
<option value="Mathematicians">Mathematicians</option>
<option value="Writers">Writers</option>
<option value="Scientists">Scientists</option>
</select>


The checkbox on the form will be named "update_group" and we will assign the student_id as a unique value from the recordset. This will look like the following in the HTML code:

<input name="update_group" type="checkbox" id="update_group" value="<%=(rs_students.Fields.Item("student_id").Value)%>">

The following shows the <form> attributes: <form name="assign_groups_form" method="get" action="proc_student_group_update.asp">

Save this file.

Go to Step 2 >>

Jon T. Heath

Jon T. HeathI left the professional world (read full-time paying job) in 1998, after working for companies such as IBM, Bank of America and BellSouth, to take on a new role as the homemaker responsible for parenting a 8-year old boy and 5-year old girl.

To keep my sanity, I started Nexsys Design, Inc. As a certified Dreamweaver MX Developer, I design web-based applications in ASP, administer hosting accounts, and manage the majority of my customer accounts from my home office. I can't remember having this much fun. I also hold a part-time position as the Director of Information Technology for my church.

See All Postings From Jon T. Heath >>

Place your comment about Update Multiple Records with SQL Update and Where/In Clause




You must me logged in to write a comment
Comments
Update Multiple Records
March 22, 2007 by Shields Dana

I don't think this tutorial is disappointing because it's poorly written; in fact, it's an easy, simple tutorial to follow, especially if you're not adept with writing code and purchased Ultradev/Dreamweaver specifically for that reason.

However, it addresses only a situation where a recordset is updated to the identical value; this is far rarer than a multiple recordset update where each record is updated differently. Currently, using Ultradev's functionality, you have to have a submit button on each and every record. So, for example, if I have a repeating region of addresses and I want to update the ZIP Code column to a different value for each record, I have to submit the button for each record.

I've seen some tutorials that try to address looped recordset updates, but I'm sort of confounded that such a common task--I.E. your online banking allows you to do this--is completely missed in Ultradev/Dreamweaver core functionality. No functionality, no extensions. Just extraordinarily complicated code tutorials that I can't understand.

I mean if you pay this much for software, you'd think it would include such basic functionality.

I have ultradev 4.0. Do the newert versions of Dreamweaver feature this functionality. Again, to me, it seems like such a core thing.

Amazing...
January 21, 2004 by ed hidden
Thank you for such a great tutorial. Very simple to follow and with how little code is actually here, it's incredibly simple to tailor for specific uses. Thank you for taking the time to put this out here for us. I have used other methods to do this previously, but actually follow and easily understand what is happening here!!!!