Greetings All,
I have a rather daunting problem that I cannot seem to figure out. I have a table [dbo.SupplyRequest] that will accomodate supply requests (SQL Server) This table has an ID, a Supply ID and a Quantity. There is another table [dbo.SupplyReference] that is the reference for this- it contains a SupplyID (referenced to supplyID) and a text description.

The .asp web page that I created displays a quantity text field next to each text description and a hidden field with the supplyID, and uses a repeat region to display all of the different supplies. What you end up with is a list of all the supplies with a textfield before it prompting for the quantity of the supply, this works fine.
Unfortunately I have yet to find a way to update the table properly, since it can only accomodate one update at a time instead of the whole list- so I guess I need some kind of code snippet that goes through the form and puts in each value one after another until it is done down the list--

Has anyone created a server behavior for taking a dynamic listing of objects and updating a single database table multiple times from it? Or is there a better way for me to go about this?

Thank you in advance for any help provided!



Edited by - jdehaven on 23 Dec 2002 18:07:09

Well, you're half way there, you allready have a repeat region within a form.

So, set up a stored procedure, something like:
----------------------------------------------------
CREATE PROCEDURE spInsertSupplyQuantities

@SupplyID int,
@Quantity int

AS

SET NOCOUNT ON

UPDATE SupplyRequest SET Quantity = @Quantity WHERE SupplyID = @SupplyID

SET NOCOUNT OFF
Go
------------------------------------------------------


Make the form submit back to the same page, now use <b>command (Stored Procedure)</b> to call the stored procedure from the page, then move it within the repeat region and place a condition around it so it only executes once the form is submitted, something like:
------------------------------------------------------------------------------------------
&lt;form name="form1" method="post" action="ThisPage.asp"&gt;
&lt;table width="400" border="0" cellspacing="0" cellpadding="5"&gt;
&lt;%
While ((Repeat1__numRows &lt;&gt; 0) AND (NOT Recordset1.EOF))
%&gt;
&lt;tr&gt;
&lt;td width="138"&gt;&lt;%=(Recordset1.Fields.Item("Whatever".Value)%&gt;&lt;/td&gt;
&lt;td width="242"&gt;
&lt;input type="text" name="txtQuantity&lt;%= <b>Repeat1__numRows</b> %&gt;"&gt; &lt;input type="hidden" name="hidSupplyID&lt;%= <b>Repeat1__numRows</b> %&gt;"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;% If Request("Submit" &lt;&gt; "" Then '<b>The UPDATE command is only executed once the form is submited </b> %&gt;
&lt;%

Dim Command1__SupplyID
Command1__SupplyID = ""
if(Request("SupplyID" & <b>Repeat1__numRows</b> &lt;&gt; "" then Command1__SupplyID = Request("SupplyID" & <b>Repeat1__numRows</b>

Dim Command1__Quantity
Command1__Quantity = "0"
if(Request("Quantity" & <b>Repeat1__numRows</b> &lt;&gt; "" then Command1__Quantity = Request("Quantity" & <b>Repeat1__numRows</b>

%&gt;
&lt;%

set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_YourConnection_STRING
Command1.CommandText = "dbo.spInsertSupplyQuantities"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@SupplyID", 3, 1,4,Command1__SupplyID)
Command1.Parameters.Append Command1.CreateParameter("@Quantity", 3, 1,4,Command1__Quantity)
Command1.Execute()

%&gt;
&lt;% End If %&gt;
&lt;%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%&gt;
&lt;tr&gt;
&lt;td&gt; &lt;/td&gt;
&lt;td&gt;
&lt;input type="submit" name="Submit" value="Update"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/form&gt;
------------------------------------------------------------------------------------------

Notice how I've re-used the <b>Repeat1__numRows</b> variable throughout to provide a unique number for the hidden field and textfield each time through the loop.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 23 Dec 2002 19:32:56

You are the man!! Thank you for your assistance!!