how can update the multiple records at a time ?


-----editrecords.asp-----(display all records by loop)------------------

<form method=post action="http://dcilweb/sas/projassign/update.asp">

ssql="select rec_id,empno,aposit,astrdate,aenddate,cshift,acont ract from assignments"

set rs=cn.Execute(ssql)

do while rs.eof=false

Response.Write("<tr>")
Response.Write("<INPUT id=empno name=empno value='" & rs("rec_id") &"'>")
Response.Write("<INPUT id=empno name=empno value='" & rs("empno") &"'>")
Response.Write("<INPUT id=astrdate name=astrdate value='" & rs("astrdate") &"'>")
...........

rs.movenext
loop


------update.asp-----(second page)----------------------------


rec_id=Request.Form("rec_id")
empno=Request.Form("empno")
astrdate=Request.Form("astrdate")


usql1="update assignments set "
usql2="empno='"& empno &"',"
usql3="aposit='"& aposit &"',"
usql4="acontract='"& acontract &"',"
........

usql7="cshift='"& cshift &"' where rec_id='"& rec_id &"'"

usql=usql1+usql2+usql3+usql4+usql5+usql6+usql7

cn.Execute usql
if err.number=0 then
Response.Redirect("update_records.asp")
else
Response.Redirect("wrong_entry.asp")
end if



above update query update only one records when only one rec_id value recived from the previous page.
if multiple rec_id and multiple records value recived from the previous
how can update the records and what query will use ?

regards.

Mateen

You have to wtite loop, and inside that loop, you can use update statements.

Om Prakash

How many records does that select statement return to show them on the page?

Why do you have to name two form controls with the same name? What is the difference between rec_id and empno? or are they both having similar/sequencial numbers? And when you place that in a while loop, in what way can you differentiate the empno control for eah row?

What can be the value of this in your udate page?
Answer for all these could end up saying this approach not the right one.

1) You can have your empno control named within the while loop in editrecords.asp as
<input .... name = "empno" & rs("empno") value>

This way for every record looped through, you would get its name as empno1, empno2, empno3... etc.

2) Initialize a counter variable and increment by 1 for every records in loop. and append it to the "empno" string as its name.

Also in your Edit page, you don't have to display the field like rec_id/EmpNo in textbox, which I assume is not allowed to edit. Instead you can use any one of that in hidden fields, so as to request its value in update page and use that in where clause while constructing the update statement. Just for the sake of differentiating each row, you can do a response.write of EmpNo, other than storing it in a hidden field.


And in update.asp you can request RecCtrVal's value first and based on it value, you can implement a loop from 1 to RecCtrVal
within which you can write an update statement that pertains to each row.

I would prefer the second solution.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

Thanks for your response.

by the loop all table records select statement return to show on the page ie. editrecords.asp

rec_id and empno both are different. different values. it is my mistake.
rec_id means record id which identity value is yes. (yes rec_id not allow to edit)
and empno is employee number.
rec_id is auto value not editable, other records empno, astrdate,.... have to change.


yes, records display in the editrecords.asp page empno1, empno2, empno3,..........etc.

I use your coding like this.

<form method=post action="http://dcilweb/sas/projassign/update.asp">

<%
ssql="select rec_id,empno,aposit,astrdate,aenddate,cshift,acont ract from assignments"
set rs=cn.Execute(ssql)

Dim fielddata
Dim RecCtr
RecCtr=1

do while rs.eof=false

Response.Write("<tr>")
Response.Write("<INPUT id=rec_id name='Rec_id" & cstr(RecCtr) & "' value='" & rs("rec_id") &"'>")
Response.Write("<INPUT id=empno name='empno" & cstr(RecCtr) & "' value='" & rs("empno") &"'>")
Response.Write("<INPUT id=astrdate name='astrdate" & cstr(RecCtr) &"' value='" & rs("astrdate") &"'>")
...............

RecCtr=RecCtr+1
rs.movenext
loop

Response.Write "<input type='hidden' name='RecCtrVal' value='" & RecCtr-1 & "'>"

%>

<INPUT id=submit1 type=submit value=Update name=submit1>

when I press update button values it move to next page ie. update.asp
but it not update the record in table, and give no error .

update.asp page coding like this.
-----------------------------------------------------------------------------------------------

RecCtr=Request.Form("RecCtrVal")

for i = 1 to RecCtr

rec_id=Request.Form("rec_id" & RecCtr)
empno=Request.Form("empno" & RecCtr)
acontract=Request.Form("acontract" & RecCtr)
astrdate=Request.Form("astrdate" & RecCtr)
............

strSql1 ="update assignments set "
strSql2="empno='"& empno &"',"
strSql3="aposit='"& aposit &"',"
strSql4="acontract='"& acontract &"',"

if IsDate(astrdate) then
strSql5="astrdate='" & astrdate & "',"
else
    strSql5="astrdate=NULL,"
end if

if IsDate(aenddate) then
strSql6="aenddate='" & aenddate & "',"
else
    strSql6="aenddate=NULL,"
end if

strSql7="cshift='"& cshift &"' where empno="& empno

strSql=strSql1+strSql2+strSql3+strSql4+strSql5+str Sql6+strSql7

Connectionobject.Execute(strSql)

next

cn.Execute usql
if err.number=0 then
Response.Redirect("update_assign3.asp")
else
Response.Redirect("update_assign4.asp")
end if



when only one records to update, I use rec_id for the update the records in query.
where rec_id='"& rec_id &"' it udpate only one record in table.

now I want to udate all table records. first display all table records in input editable form ie.

Response.Write("<INPUT id=empno name='empno" & cstr(RecCtr) & "' value='" & rs("empno") &"'>")
Response.Write("<INPUT id=astrdate name='astrdate" & cstr(RecCtr) &"' value='" & rs("astrdate") &"'>")
.....

after edit the records press update button it should update all table records at a time.
Please check the coding.

Mateen

Hi Mateen,

That is because, you are running into an infinite loop. May be if you leave that for some more time, you can crash your db/system.;)

Where have you used this?
RecCtr=RecCtr+1

You are supposed to use that in your for loop. that is why I have mentioned those codes in RED. This maybe you yourself could have found out and corrected, on noticing Just one record getting updated.

Cheers!

_________________________
-Vijay G
Strive for Perfection