Wrox Programmer Forums
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 10th, 2007, 01:50 PM
Authorized User
Join Date: Jan 2007
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default update mulitple rows in SQL 2K

Some background: I use a stored procedure to get the data from SQL, and a loop using ASP to display a table with multiple columns in rows based on site number and unit number. The first column of each unique row contains a checkbox, and subsequent columns are either editable text-box fields or simply display the data.

My goal: I want the user to be able to edit the data in multiple columns and/or multiple rows, then put a check in the checkbox for each row to be updated, then press SUBMIT and have the updates take place.

Future goals: to be able to do some data verification and prompt the user for correction (ie: date field value is not a date) prior to the update.

I'm new to ASP, but strong in SQL. The update stored procedure should be a piece of cake, but I'm not sure how to get the data into parameter form for multiple rows. I have been able to pass the data from multiple text boxes on previous pages where there was a single row with a radio button and text-boxes into SQL without any problem, and am certain I can get the data from a single row passed into a stored proc without any problem. It is the multiple rows part that I'm stuck on.

Any ideas? I'm not even sure my approach is correct. It seemed like a good idea until I hit the update piece, and I have concerns about being able to verify the data is correct once I get to that part. I've done a lot of searching online and in books without finding what I'm looking for, and hope someone here has solved this problem in the past. Thanks for your help.

Old January 10th, 2007, 02:15 PM
Wrox Author
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

Actually this is very easy. Generally speaking you are going to have one control loop that looks similar to this:

For x = 1 to Request.Form.Count
    Response.Write Request.Form.Key(x) & " = "
    Response.Write Request.Form.Item(x) & "<br>"

Your key is the name of the item you are requesting and the Item is the value so say you have a text box named txt1 and someone puts test in that box and then hits submit, the above statement would literally write out

txt1 = test

What I would suggest is that when you generate the form for the user to update you name all of your controls the same BUT increment a counter as the loop executes and append the counter to the name so:

checkbox1 checkbox2 etc

You then have an easy way to deal with data that should be updated:
Dim i
i = 1
For x = 1 to Request.Form.Count
    If Request.Form.Key(x) = "checkbox" & CStr(i) AND Request.Form.Item(x) = "on" Then
      'You now know that the row associated with the check box should be updated so you could begin
      'Doing something like
       somevalue = Request.Form("date" & CStr(i))
       'etc etc etc
       i = i + 1
    Else If Request.Form.Key(x) = "checkbox" & CStr(i) AND Request.Form.Item(x) <> "on" Then
       i = i + 1
    End If

i is your control variable here and you only ever want to increment when you get to a new row which is denoted by a checkbox.

Lastly, the value of the checkbox item will only be "on" if you dont supply a value parameter in code. If you do something like:

<input name="found" type="checkbox" value="Data">Data

Request.Form.Item(x) will return "Data" if the box is checked.

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Old January 16th, 2007, 12:14 PM
Authorized User
Join Date: Jan 2007
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for your help.
I did run into the problem of the checkbox not returning any value when it was unchecked, so I never entered the Else statement.
I got around it by setting "checkbox=Request.Form.Item("checkbox" & Cstr(i))", then testing for checkbox = "on" and passing all the column variables into my stored procedure inside that test. I also found that setting "Session("RecordCount") = ( i - 1 )" on the page prior to the update page, and then using "FOR x = 1 TO record_count" (where record_count=Session("RecordCount")) gave me better row control than using "FOR x = 1 TO Request.Form.Count".
But you definitely sent me down a good path, and I appreciate your help.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime Access 2K and Sql 2K laure Access 0 April 3rd, 2006 08:32 AM
How can I update 2 rows in one column in SQL table babou Classic ASP Databases 6 March 9th, 2005 11:13 AM
Copying Multiple Rows in MS Excel 2K using VB .Net Azhar Akbari Excel VBA 2 January 27th, 2005 10:04 AM
SQL 2K and VB6 FrontEnd vinceclo SQL Server 2000 0 February 4th, 2004 10:29 PM
Connectivity Problem To SQL Server 2k ctanchan Pro VB Databases 4 October 7th, 2003 11:29 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.