Multiple record update with parameter 2

[JamesAlex]

Hello - please forgive the long description below...

I am trying to create a web page that will allow me to update multiple database records simultaneously. I have created the pages using Dreamweaver Ultradev 4, with additional code for the multi update function.

I have a search page, where the user enters their unique ID, a results page, where all DISTINCT records (in this case Purchase Orders) belonging to this ID are displayed, and then a detail page, accessed by following a "go to detail" link from the Purchase Order number field, which shows all Purchase Order lines belonging to that Purchase Order number.

The data on the detail page shows correctly, and I have added checkboxes, the names of which incorporate the unique identifier of the DB table in order to make them unique, which should set each record to a status of "Closed" when selected.

Here lies the problem: when I submit the page, which should link back to itself, and display only records not designated "Closed" from the DB, it simply returns the message "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record." . I think this is due to the page expecting a URL parameter for the Purchase Order number, which it is not receiving on linking back to itself, but I am not sure how to get around this, and have been searching for a solution to no avail. I can post the code if necessary. Can anyone help?

Many thanks,

-James

 

[guitardave78]

can you show us the code you are using to update the records.

to update multiple records you tend to need to loop through the sql statment a few times
On page one you have 5 check boxes all called the same (order) but with your unique id as their value (creates an array)

you can then do somthing like this

orderArr = split request("order")
for n = 0 to ubound(orderArr)

strSQL = "UPDATE tbl_orders " & _
	"SET order_closed = 'checked', "& _
	"WHERE order_id = "&orderArr(n)
conn.execute(strSQL)

next 
[code]

Only the checkboxes that are ticked will be submitted so you will only close those records.

}...the bane of my life!
[URL unfurl="true"]http://www.fuzzyd.co.uk[/URL]

 

[jennuhw]

Are you needing to put an if statement in there as follows?

if not loRS.eof then
'something is in recordset, do something
else
'it's an empty recordset
end if

 

[guitardave78]

or you can use

for n= 1 to request("order").count
strSQL = "UPDATE tbl_orders " & _
    "SET order_closed = 'checked', "& _
    "WHERE order_id = "&request("order")(n)
conn.execute(strSQL)
next

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[JamesAlex]

Hi all,

the code I am trying to use is as follows:

<%

if request("selected") <> "" Then
rs.MoveFirst
while not rs.eof
ID=rs("ID")
rs("Status")= DOCheck (Request("Status" & ID))
rs.update
rs.Movenext
wend
response.redirect "Newest_Detail.asp"
end if



function DoCheck(fld)
if fld <> "" then
alert("Do Check")
DoCheck="Closed"
else
DoCheck=rs("Status")
end if
end function
%>

It's an adaptation of the code I found at

http://www.charon.co.uk/content.aspx?CategoryID=27&ArticleID=41

It did work for a test project, but not for what I'm currently attempting (probably my fault, not that of the author!!!).

Regards,

-James

 

[guitardave78]

try it without the rs.MoveFirst and rs.Movenext

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[guitardave78]

if the record set has no results the move first will give you an error

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[JamesAlex]

I tried removing the rs.MoveFirst and rs.Movenext, and the page now re-loads without error on submission, but with no data (as I would expect without these commands). Unfortunately the records selected prior to submission have not been updated though!

On initial access, the page displays a list of records matching the URL parameter passed to it, with a checkbox added to each row, the selection value of which is "Closed". When submitted, this should update the "Status" field in the DB to that value. The checkbox name is "status_box<%=(rs.Fields.Item("ID").Value)%>", "ID" being the unique identifier field in the DB. Given this, can you see an obvious error in my code above?

Many thanks,

-James

 

[JamesAlex]

Hello,

OK, I have fixed my update statement so that it works, and have found through experimenting that my problem is actually that the page expects a URL parameter, which is referenced in the SELECT statement to return matching records. This works when first accessing the page, but on submission, I can't seem to pass the same parameter value back again to return the remainder of the same recordset, hence the empty recordset which was causing me problems.

Can anyone suggest a way to retain the URL parameter?

Thanks,

-James

 

[guitardave78]

you could use a session variable

session("blah") = request.querystring("blah")

"select xxx fromm xxx where x = " & session("blah")

This will keep the variable untill you exit the browser or change the variable.

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[Sheco]

If all four records are in the same table, could you just do something like:[tt]
UPDATE MyTable SET MyStatus = 'Closed' WHERE MyKeyID in (abc, def, ghi, jkl)[/tt]

 

[guitardave78]

oo i been trying to remember that way for ages!!! Have a star

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[JamesAlex]

Hi all,

thanks for all your help so far. I actually have the page working using an array of selected records in a similar manner to what Sheco suggested now. I have been experimenting with using session variables instead of URL parameters, and I think this looks like the best way of passing data in my application. I may have some more questions in a while, but for now thanks again for your assistance in an area which is definitely new territory for me!

- James