how can we update multiple values of one column?

I would need more detail of what exactly you want.  But lets say you want to update a field for ALL rows in your table.  You would simply loop through the update command just like you know how in the insert.  

If you are using checkbox's for instance, that would give you a comma delimited field of data that needs to be split and thrown into an array then a for each statement is used to update.  

Please submit more details and I can give you better info.

Like these?

UPDATE TableName
SET Column1 = @InputParameter1
,Column2 = @InputParameter2
,Column3 = @InputParameter3
,Column4 = @InputParameter4
WHERE {Condition here}

Or you want to check column values first if there are changes before updating it?

I'm sorry, I am still missing something.    Are you inputing data from a form?  Or do you have 2 similar tables and you want to sync them?

@padas

I am trying do like that using counter loop. there is only one table..yes i am inputing data from form..
But updating is failing..

@faiga:
it is only column1 ...(i don't have multiple columns,only multiple rows,)

table1
col1
col2..

i am only updating col2( all rows at a time/or just modified row.)

Lucky, so you have one form field and that form field needs to simply update all rows in a given field?  

Basically you will do something like,


if form is submitted then
do until rs.eof

'place update info here

rs.movenext
loop
end if

I know that is pretty generic.  But if you can paste some of your code and where you are getting stuck it would help me give you more detail.  Is the form field a checkbox or text field?

UPDATE YourTable
SET YourColumn = @YourValue

@padas
this is my code ,using for update..
It is not updating any value..

<%
If Trim(Request("Save")) = "Save" then

	For Each quantity in Request.Form("quantity")
	intCounter = intCounter + 1
	Next
	Do While intcounter > 0
	If len(Request.Form("quantity")(intCounter)) > 0 Then
	OpenSQLConn
	sql = "Select * from items where itemid = " & Request.Form("itemid")(intCounter)
	Set RS=Conn.Execute(sql)
	Response.Write "Debug:"& sql &"HR"
	Response.End
	itemid=RS("itemid")
	Sqlupdate = "Update items set quantity="& Request.form("quantity")(intCounter) &" where itemid="& itemid
	Conn.Execute(Sqlupdate)
	End If
	intcounter=intcounter-1
	Loop
			
end if 
%>

Select allOpen in new window

@padas..
I don't have check box.. Quantity field is a text box..

I think the problem is in what is updating.  In your sql statemtent, "sql = "Select * from items where itemid = " & Request.Form("itemid")(intCounter)" I don't think you can actually match up the item id by using a random counter.  

What if you have your form set up like below where you are actually looping through all of your items via the database and use a unique form field like quantity_<%recordid%> and then use that info in your sql select statement in updating.
 

<form action="" method="post">
<%if not rs.eof or not rs.bof then%>
<%=rs("item")%> <input name="quantity_<%=rs("item")%>" type="text" id="quantity_1">
<input name="save" type="hidden" value="Save">
<br>
<%
rs.movenext
loop
end if
%>

  <input type="submit" name="saved" id="saved" value="save">
</form>

Select allOpen in new window

@padas

I gave the code for update..the same way I did for insert..

If I do i like this it will effect to other part of program..
I need that way with counter only..

Lucky,

Try logging in what itemid you are getting,so we will get an idea if the proper id is used or not.

Thanks

Lucky,
There were some other questions here in EE that I answered for you that seem to be related to this.   Based on the other answers I gave I think you must be very close if you have not figured everything out yet.    What I think you are trying to do all on one page is to have a form with a drop down of items with an "add" button.  Then each time you add an item it displays below on another form that you can then edit the  item and if you have multiple items you only want to use one submit button.  Does that sound right?  

One of your questions was about adding rows to a table and I think we answered that where the rows get automatically each time you add an item and I gave you that code.   However, now that I think I see what you are trying to do by piecing all of this together, that code I gave you will only work if you update each item at a time.  There is a way to have multiple items (that will be in separate rows on the database)  in an form and edit them all with just one button.  

Can you please confirm this is what you want to do and I think I can help you complete this.  Basically you will want to but the selected items in an array, then after the edit button is selected run through the array and  update each item.    I will be happy to give the exact code help if this is what you need.

I will give the complete details here...

I am displaying all my data in tabular format.(this data is coming from databse).
and I am displaying quantity values in text box..
So that If I make any changes it will update the values to databse..

So the SQL table

Column1      Column2      Quantity
A      Ab      12.01
B      Fd      0.21
C      Tr      35.7


here i need to update all quantity column values at a time..that means I need to update one column values.

I only know,I can update one row at a time..

But here It is all rows for same column..

can any one know how to do this?

Is there any logic for quantity or user enters the quantity?

First, you need 2 tables.  One for products and one for items "in the basket".  Your products table will have an item id, product name and price.  Your items tabel wil have a unique row ID, customer id, and quantity.  Lets assume you have a way to know which customer is on the page.  So you have 2 recordsets something like below.

To make a drop down of your products you will need a query like:
rsProducts = SELECT ID, ProductName +' '+ cast(Price as varchar) as fullname FROM dbo._tProducts

And to view all of the items your customer as selected you will need a query like:
rsItems = SELECT * FROM dbo._tSalesTrans where customer_id = ?

Next you will need to  handle 2 input forms. One for adding an item to the "basket" and one for updating the qty for each item in the baseket.

 

<%
if request.form("add_item")="yes" then
	'Place your insert query here to insert one new item to the items field
	'insert item_id, qty, customer_id
	'now you will have a new row inserted into the selected items area
end if

if request.form("update_items")="yes" then
	rsItems.requery
	rsItems.filter="Customer_id = '"&customer_id&"'"
	if not rsItems.bof or not rsItems.eof then
	Do until rsItems.eof
	' Add your update query here
	 '  update rsItems set Qty = request.form(""&rsItems.ID&"") 
	
	rsItems.movenext
	Loop


end if
%>

Select allOpen in new window

First a form to add items to the basket.
 

<table width="600" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>Item</td>
    <td>Enter a quantity</td>
    <td>&nbsp;</td>
  </tr>
 <form method="post" name="frmAdd" id="frmAdd">
  <tr>
    <td><select name="item" id="item">
      <%
While (NOT rsProducts.EOF)
%><option value="<%=(rsProducts.Fields.Item("ID").Value)%>"><%=(rsProducts.Fields.Item("fullname").Value)%></option>
      <%
  rsProducts.MoveNext()
Wend
If (rsProducts.CursorType > 0) Then
  rsProducts.MoveFirst
Else
  rsProducts.Requery
End If
%>
    </select></td>
    <td><input name="new_qty" type="text" id="new_qty" size="10" maxlength="10">
      <input name="add_item" type="hidden" id="add_item" value="yes">
      <input name="customer_id" type="hidden" id="customer_id" value="<%=customer_id%>"></td>
    <td><input type="submit" name="button" id="button" value="Add Item"></td>
  </tr>
  </form>
</table>
<hr>

Select allOpen in new window

and one to list the current items and have the ability to update the qty.  You will notice I for the qty field, I named it the ID for the rsItems row. This is one of multiple ways you can update these rows with just one submit button.  The previous examples I gave you were to update one row at a time.  As I said before, if you are having problems with this part, go back to just updating one at a time so you can eliminate errors. Then come back to multiple inserts like this.
 

<form method="post" name="frmUpdate" id="frmUpdate">
<table width="600" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>Item</td>
    <td>Price</td>
    <td>Quantity</td>
  </tr>
  <%
  rsItems.requery
  if not rsItems.bof or not rsItems.eof then
  do until rsItems.eof
  %>
  <tr>
    <td><%=(rsItems.Fields.Item("ID").Value)%></td>
    <td><%=(rsItems.Fields.Item("Price").Value)%></td>
    <td><input name="<%=(rsItems.Fields.Item("ID").Value)%>" type="text"  value="<%=(rsItems.Fields.Item("Qty").Value)%>"></td>
  </tr>
  <%
  rsItems.movenext
  Loop
  end if
  %>
</table>
<input name="btnUpdate" type="submit" id="btnUpdate" value="UPDATE">
<input name="update_items" type="hidden" id="update_items" value="yes">
</form>

Select allOpen in new window

@padas:

It is confusing me where to start..
What is requery stands for..
I didn't see any sql query here..

Is it update "quantity" column values at a time whern click on save button

this is my actual program..
here first we need to select drop down values and click on show values..
It will display all the values in tabular format..
The quantity field is in textbox..
Here  I am editing quantity values.. then I click on save button.. It should save to database.

<!-- #INCLUDE FILE="include/adovbs.asp" -->
<!-- #INCLUDE FILE="include/functions.asp" -->

<html>
<body>

<form method="post" action="itemvalues.asp" name="form1" onsubmit="validateform(this)">
<table border="0" cellpadding="0" cellspacing="5">
            
<tr><td>Choose Items
  <%
OpenSQLConn						
SQL = "select Item from Items where categoryid=1 union all select Item from Items1"
Set rs = Conn.Execute(SQL)%>
 <td> <select name="selectitem" >
<option value="">Please Choose</option>
 <% Do While Not rs.EOF %>
<option value='<%=rs("Item")%>'> <%=rs("Item")%></option>
<%
rs.MoveNext
Loop
												
%>
</select></td>
<td><input type="submit" value="Show Values"></td>
</tr></table>

</form>

<%
selectitem=request.form("selectitem")
If optitem <> "" then
sql=" SELECT a.id, a.Item,b.Item, b.quantity,b.measure FROM Items1 AS a JOIN Items AS b ON (a.id = b.id) where Item='"&selectitem&"'"  
			
Set rs=Conn.Execute(sql)
%>
<table id="mytable">

 <tr>
      <th>Item</th>
       <th>Quantity</th>
<th>Measurement</th>
    </tr>

<%
do until rs.EOF
response.write("<tr>")
     
 response.write("<td>" & rs("Item") & "</td>")
%>

<td><input type="text" value="<%= rs("quantity") %>" /></td>
 <%
response.write("<td>" & rs("measure") & "</td>")
 response.write("</tr>")
rs.MoveNext
        loop
        rs.close
        Conn.Close
  %>

   <table> <tr>
       <td><input type="submit" value="Save" name="Save"></td></tr></table>
        </table>
  <%    
end if %>
</table>


</body>
</html>

Select allOpen in new window

Lucky, I used partial pseudo code and partial real code.   The very first part I showed the two querys to make up a recordset.


To make a drop down of your products you will need a query like:
rsProducts = SELECT ID, ProductName +' '+ cast(Price as varchar) as fullname FROM dbo._tProducts

And to view all of the items your customer as selected you will need a query like:
rsItems = SELECT * FROM dbo._tSalesTrans where customer_id = ?

Lucky, the logic in your code is not going to work.    You are trying to do 2 things here, 1) Add items items to some type of shopping cart for lack of a better term.  2) The ability to update the quantities of all selected items with just one update button.

Step one is easy.  You simply need to use your form1 to post to code that will insert a record to a 2nd table that contains items added by that customer.  That 2nd table at a minimum should have a unique ID, the customer id, and an item id.  

When you do your insert from your form1 you should be inserting the customer id, item id and quantity.  

If you take a look at the third code box in my post, I have a form set up that is used to update the selected items.  I am naming the quantity field with the ID of the items table so that can be used to uniquely  identify the row.

If you look in my first code box starting at line 8, I show how to do the update.  It uses pseudo code so you will need to make your own insert code there.  I can see that I left out a where close on my update code on line 14 of the first code box.  

 '  update rsItems set Qty = request.form(""&rsItems.ID&"")
should be
 '  update rsItems set Qty = request.form(""&rsItems.ID&"") where ID = request.form(""&rsItems.ID&"")

As I said, there are more then one way to update multiple rows in a table with unique data and this has worked for me in the past.  Please rework your code using the concepts here.

Step one is easy.  You simply need to use your form1 to post to code that will insert a record to a 2nd table that contains items added by that customer.  That 2nd table at a minimum should have a unique ID, the customer id, and an item id.    

Correction: at a minimum that 2nd table at a minimum should have a unique ID, the customer id, an item id and the quantity.

@padas:
I am not inserting all data..I am just updating only quantity column values..remaining column values are constant

@padas

where did you write the sql query in form1 to display the values..
What is requery stands for?

Lucky, you are using your form1 to "add rows below" and to do that you need to use your form1 to insert one record to a new table that holds the client id, item id and quantity.  

Then in your table below form1 that needs to be a table inside a form.  Each rows in that table are created from a query of the items table with a where clause something like, "client_id - x" so you show only the rows for that client.  I am using this for simplicity.   If this is for some type of shopping cart then you actually want to use an order id and link the order id to the client.  But lets stay with just the client id for simplicity.  

Normally, each row in the table of selected items would be a separate form where you would submit the items.id and quantity and update just that row.

Since you want to update all rows for that client (or however you determine what is selected for this session) you need a way to cycle through all the selected items and update the correct row.  You can not simply make one form and have multiple rows that need unique numbers in the quantity field with one input button. Somehow, you need a way to update the correct row.    

If you were updating all selected rows with the SAME quantity, it would be a little easier.  But it looks like the sample you have shown will require unique values. If I want to update item Fd from 0.21 to 0.83, that needs to be matched up with column B and I am assuming you have column1 as a unique ID.

I just made an image that should better illustrate.  

The add button creates a new row in the shopping basket.
The update button needs to update ALL the qty's where customer=1 but they need to be matched up with the proper id.  (shoppingbasket.id).  

We could use some ajax/javascript to figure out if only one qty was updated, but if javascript is turned off then it will not work so updating the entire query (customers =1) is the way to go.  Remember, they could change more then one qty.  If the basket will only have a handful of items, this is find.  If you need to update hundreds or thousands of items at once like this, I would say this solution is not a good method.

What I have not shown here is how you determine which customer is using the page,  This could be done by a log in, a session variable or special cookie.

 

Requery is to use the same recordset multiple times.  You could also just create a duplicate recordset.

http://www.w3schools.com/ado/met_rs_requery.asp 

Going back to your first comment

UPDATE TableName
SET Column1 = @InputParameter1
,Column2 = @InputParameter2
,Column3 = @InputParameter3
,Column4 = @InputParameter4
WHERE {Condition here}

becomes

for each item in the shoppingbasket  (where customer = some ID)

UPDATE TableName
SET Column1 = @InputParameter1
,Column2 = @InputParameter2
,Column3 = @InputParameter3
,Column4 = @InputParameter4
WHERE shoppingbasket.id = fieldname (see my post id 35819841)

next

@padas..
Thanks for that details of each step..I am doing this now..I will follow upwith u  if i get any error..

@padas
This is my design...
I am not at all adding items..
I am just updating the quantity fields..
When I execute my file this is the output I got....


Region.png
Region1.png

I need to  bulk update the quantity column.. It is failing to update the values..

@padas

Ok I will add this feature to my form (adding new items too..like u r adding new items to basket.. )..I hope this will give better feature to my form..
Can you combine all ur code and show me ..

The code is in my post 35819841.  If you just combine the 3 box's you will get  what you need.  Keep in mind I have partial pseudo code you where you will need to create for your exact use.  

Your easy parts are creating the drop down to 'show items", inserting the show items into some type of table, temp table or maybe an array and listing the selected items.  

My sample for the mass update may not be exact to your needs but the idea is there.  The main points are the "selected items" table needs to be inside of one form, You need some way to identify each row by some type of id and you need a field for qty.   My sample shows naming the quantity field with the unique id of the item.  This could be the ID of the actual item (if you need to update the qty for all rows with the same item) or it could be the ID of the "basket" if you need to only update the qty for the customer or session.  

Lastly you need to update something like what I have in the first box of post 35819841.  Line 14 of that code is not working code.  You will need to write your own update code.   The Where clause determines if you are updating just for that one transaction (using the cart id) or for all items (if you use the item id).