Updating multiple records in one form

[Bwintech]

Hi

I want to create one form that has an text entry for each record, the user to enter a value for each record in a seperate text box and have one submit button to process an db update for each record.

I am very comfortable doing this for one record but unsure how to do it for many records within one form.

Thank you for your help.

 

[FesterSXS]

if you post the code you have used for a single item then I will modify it for you to include multiple items Tony

http://www.phoenix-paints.co.uk/portfolio

 

[Jonax]

I woul do a for-to-next loop to output the formfields.
Name them something containing the counter ie.:

<input type=text name=field1 />

...and then on the 'next' page loop through them with

request.form("Field" & t)

and build updatestatements from that...
This is not a bug - it's an undocumented feature...
;-)

 

[Bwintech]

Hi, thank you for your help.

Currently I have a initial screen to choose the user you wish to modify.

Once a user is selected, you enter the user's details in a form as below..

<FORM METHOD = 'GET' ACTION = 'ConfirmChange.asp'>
Enter New value </font><INPUT TYPE = HIDDEN SIZE = '1' NAME = 'USERID' VALUE = &quot;&ID&&quot;>
<INPUT TYPE = TEXT SIZE = '10' NAME = 'NET'>
INPUT TYPE = SUBMIT VALUE = 'CHANGE'></FORM>

Then the newpage ConfirmChange.asp will have the SQL update statement.

What I want to do is have many users and update fields in this form so that they can be processed together such as:

user1 input
user2 input
user3 input
....
SUBMIT

Is this possible? How can I then build this into a SQL Update script (to update each user's record in the db)?

Thank you kindly for your help

 

[Jonax]

Hi again,
please tell me what fields you have in your table, and what the name of your table is (in the db) This is not a bug - it's an undocumented feature...
;-)

 

[Bwintech]

Hi Jonax

Thanks again for your help.

The unique identifier for these users is userid column, I want to update the AmountOwed and TotalPaid columns as text boxes next to each userid.

The table is in a SQL2000 db, name is ClientPayment.

Cheers

 

[Jonax]

Okay I haven't tested this, but something along these lines:

<%
'ThisPage.asp
dim strSQL, strConn, Conn, rs, arrResults, bolFail
strSQL = &quot;SELECT UserId, Net FROM tbSomeTable&quot;
strConn = &quot;your connection-string&quot;
set Conn = server.createobject(&quot;ADODB.CONNECTION&quot;)
Conn.open strConn
set rs = server.createobject(&quot;ADODB.RECORDSET&quot;)
rs.Open strSQL, Conn, 1, 3
If NOT(rs.bof AND rs.eof) then
	arrResults = rs.getrows
Else
	bolFail = TRUE
End if
rs.close
set rs = nothing
conn.close
set conn = nothing

if bolFail then
	response.write &quot;DATABASE RETURNED NOTHING!&quot;
	response.end
end if

response.write &quot;<form name=theForm action=otherpage.asp method=post>&quot;
for t = 0 to ubound(arrResults,2)
	response.write &quot;<input type=hidden name='userid&quot; & t & &quot;' value='&quot; & arrResults(0,t) & &quot;'>&quot; & vbcrlf
	response.write &quot;UserId: &quot; & arrResults(0,t) & &quot; <input type=text size=10 name='net&quot; & t & &quot;' value='&quot; & arrResults(1,t) & &quot;'><BR>&quot; & vbcrlf
next
response.write &quot;<input type=hidden name=counter value='&quot; & ubound(arrResults,2) & &quot;'><input type=submit></form>&quot;
%>

------------------------------- CUT HERE ----------------------------------

<%
'OtherPage.asp
strConn = &quot;your connection-string&quot;
set Conn = server.createobject(&quot;ADODB.CONNECTION&quot;)
Conn.open strConn
for t = 0 to cint(request.form(&quot;counter&quot;))
	strSQL = &quot;UPDATE tbSomeTable SET Net = '&quot; & request.form(&quot;Net&quot; & t) & &quot;' WHERE UserId = &quot; & request.form(&quot;UserId&quot; & t)
	Conn.Execute strSQL
next
Conn.close
set Conn = Nothing
%>

This is not a bug - it's an undocumented feature...
;-)

 

[Jonax]

Btw:
Some of the lines get wrapped rather uncool... Do a view source on this page to see what fits on what lines... This is not a bug - it's an undocumented feature...
;-)

 

[Jonax]

Updated version:

<%
'ThisPage.asp
dim strSQL, strConn, Conn, rs, arrResults, bolFail, t
strSQL = &quot;SELECT UserId, AmountOwed, TotalPaid FROM ClientPayment&quot;
strConn = &quot;your connection-string&quot;
set Conn = server.createobject(&quot;ADODB.CONNECTION&quot;)
Conn.open strConn
set rs = server.createobject(&quot;ADODB.RECORDSET&quot;)
rs.Open strSQL, Conn, 1, 3
If NOT(rs.bof AND rs.eof) then
	arrResults = rs.getrows
Else
	bolFail = TRUE
End if
rs.close
set rs = nothing
conn.close
set conn = nothing

if bolFail then
	response.write &quot;DATABASE RETURNED NOTHING!&quot;
	response.end
end if

response.write &quot;<form name=theForm action=otherpage.asp method=post><table>&quot;
for t = 0 to ubound(arrResults,2)
	response.write &quot;<tr><td><input type=hidden name='userid&quot; & t & &quot;' value='&quot; & arrResults(0,t) & &quot;'>&quot; & vbcrlf
	response.write &quot;UserId: &quot; & arrResults(0,t) & &quot;</td><td>Amount owed <input type=text size=10 name='amnt&quot; & t & &quot;' value='&quot; & arrResults(1,t) & &quot;'></td>&quot; & vbcrlf
	response.write &quot;<td>Total paid: <input type=text size=10 name='tpaid&quot; & t & &quot;' value='&quot; & arrResults(2,t) & &quot;'></td></tr>&quot; & vbcrlf
next
response.write &quot;</table><input type=hidden name=counter value='&quot; & ubound(arrResults,2) & &quot;'><input type=submit></form>&quot;
%>

------------------------------- CUT HERE ----------------------------------

<%
'OtherPage.asp
dim strConn, Conn, t, strSQL
strConn = &quot;your connection-string&quot;
set Conn = server.createobject(&quot;ADODB.CONNECTION&quot;)
Conn.open strConn
for t = 0 to cint(request.form(&quot;counter&quot;))
	strSQL = &quot;UPDATE ClientPayment SET AmountOwed = '&quot; & request.form(&quot;amnt&quot; & t) & &quot;', TotalPaid = '&quot; & request.form(&quot;tpaid&quot; & t) & &quot;' WHERE UserId = &quot; & request.form(&quot;UserId&quot; & t)
	Conn.Execute strSQL
next
Conn.close
set Conn = Nothing
%>

This is not a bug - it's an undocumented feature...
;-)

 

[Bwintech]

Hi Jonax

This worked like a charm!

Thank you so much for your help.