Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Batch UPDATE with DELETE function

Status
Not open for further replies.
A1Pat

A1Pat

IS-IT--Management
Jun 7, 2004
454
US
Hello all,

I'm not sure whether this question should be for this forum or should it belong to SQL forum, so please bear with me here...

Code: 
rs.MoveFirst
		while not rs.eof
			ProductID=rs("ProductID")
			if Session("esku") = 1 then
				rs("SKU")=Request("SKU" & ProductID)
			end if
			if Session("ename") = 1 then
				rs("Name")=Request("Name" & ProductID)
			end if
			if Session("ecost") = 1 then
				rs("Cost")=Request("Cost" & ProductID)
			end if
			if Session("estock") = 1 then
				rs("Stock")=Request("Stock" & ProductID)
			end if
			if Session("especial") = 1 then
				rs("Special")=DoCheck(Request("Special" & ProductID))
			end if
			if Session("eactive") = 1 then
				rs("Active")=DoCheck(Request("Active" & ProductID))
			end if
			if Session("enew") = 1 then
				rs("New")=DoCheck(Request("New" & ProductID))
			end if
			rs.update
			rs.Movenext
		wend

Above is a code I use to do batch update for a product page and it's working just fine. However, I now would like to add another function to delete product(s) if one or more DELETE checkbox(s) of the product(s) are selected, then continue to update other products at the same time.
How would I go from the above code to do just that?

Thanks!
 
Sort by date Sort by votes
Code: 
		rs.MoveFirst
		while not rs.eof
			ProductID=rs("ProductID")
			if len(Request("delete" & ProductID)) > 0 then
				'Delete records from Categories_Products
				mySQL = "DELETE FROM tblCatProd " _
						& "WHERE ProductID = " &  trim(Request("delete" & ProductID))
				objConn.Execute mySQL
			
				'Delete records from Products
				mySQL = "DELETE FROM tblProducts " _
					  & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
				objConn.Execute mySQL
			end if
			if Session("esku") = 1 then
				rs("SKU")=Request("SKU" & ProductID)
			end if
			if Session("ename") = 1 then
				rs("Name")=Request("Name" & ProductID)
			end if
			if Session("ecost") = 1 then
				rs("Cost")=Request("Cost" & ProductID)
			end if
			if Session("estock") = 1 then
				rs("Stock")=Request("Stock" & ProductID)
			end if
			if Session("especial") = 1 then
				rs("Special")=DoCheck(Request("Special" & ProductID))
			end if
			if Session("eactive") = 1 then
				rs("Active")=DoCheck(Request("Active" & ProductID))
			end if
			if Session("enew") = 1 then
				rs("New")=DoCheck(Request("New" & ProductID))
			end if
			rs.update
			rs.Movenext
		wend
Above is a little modification I added to delete product if checked.

Result: product is deleted
Error: Microsoft Cursor Engine (0x80040E38)
Row cannot be located for updating. Some values may have been changed since it was last read.

So, if I understand the error correctly, problem appears to be when the code trying to update the product that I selected to delete.
Question: How can I get the loop to skip the product that I deleleted?

 
Upvote 0 Downvote
After you do your delete (inside the if statement), wend to the next record in the recordset. I've not tested but I suspect it's something like this:
Code: 
rs.MoveFirst
        while not rs.eof
            ProductID=rs("ProductID")
            if len(Request("delete" & ProductID)) > 0 then
                'Delete records from Categories_Products
                mySQL = "DELETE FROM tblCatProd " _
                        & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
                objConn.Execute mySQL
            
                'Delete records from Products
                mySQL = "DELETE FROM tblProducts " _
                      & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
                objConn.Execute mySQL
                [COLOR=red]wend[/color]
            end if
            if Session("esku") = 1 then
                rs("SKU")=Request("SKU" & ProductID)
            end if
            if Session("ename") = 1 then
                rs("Name")=Request("Name" & ProductID)
            end if
            if Session("ecost") = 1 then
                rs("Cost")=Request("Cost" & ProductID)
            end if
            if Session("estock") = 1 then
                rs("Stock")=Request("Stock" & ProductID)
            end if
            if Session("especial") = 1 then
                rs("Special")=DoCheck(Request("Special" & ProductID))
            end if
            if Session("eactive") = 1 then
                rs("Active")=DoCheck(Request("Active" & ProductID))
            end if
            if Session("enew") = 1 then
                rs("New")=DoCheck(Request("New" & ProductID))
            end if
            rs.update
            rs.Movenext
        wend

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
I'm getting this:
Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/admin/products/default.asp, line 407
wend

 
Upvote 0 Downvote
I changed the "wend" you recommended to rs.movenext, the code proceeds properly until I try to delete the last item in the list. Now I'm getting a error message saying

ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
 
Upvote 0 Downvote
[blush]Whoops, my bad. It should be to move to the next record. Instead of wend, it should be rs.Movenext.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
What you'll have to do is to protect it against being the last record in the case of a delete. In that case, do something like:
Code: 
If not rs.EOF then
  rs.Movenext
else
  'exit your loop
end if

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
'exit you loop"

I looked it up and many online tutorials suggested I should change to utilize Do... Loop instead of While loop. Therefore, I can seem to find a good Exit While that works yet.

So this is what I did
Code: 
		rs.MoveFirst
		Do while not rs.eof
			ProductID=rs("ProductID")
			if len(Request("delete" & ProductID)) > 0 then
				'Delete records from Categories_Products
				mySQL = "DELETE FROM tblCatProd " _
						& "WHERE ProductID = " &  trim(Request("delete" & ProductID))
				objConn.Execute mySQL
			
				'Delete records from Products
				mySQL = "DELETE FROM tblProducts " _
					  & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
				objConn.Execute mySQL
				
				if not rs.eof then
					rs.MoveNext
				else
					Exit Do
				end if
			end if
			if Session("esku") = 1 then
				rs("SKU")=Request("SKU" & ProductID)
			end if
			if Session("ename") = 1 then
				rs("Name")=Request("Name" & ProductID)
			end if
			if Session("ecost") = 1 then
				rs("Cost")=Request("Cost" & ProductID)
			end if
			if Session("estock") = 1 then
				rs("Stock")=Request("Stock" & ProductID)
			end if
			if Session("especial") = 1 then
				rs("Special")=DoCheck(Request("Special" & ProductID))
			end if
			if Session("eactive") = 1 then
				rs("Active")=DoCheck(Request("Active" & ProductID))
			end if
			if Session("enew") = 1 then
				rs("New")=DoCheck(Request("New" & ProductID))
			end if
			rs.update
			rs.Movenext
		Loop

The problem BOF or EOF is still there, and I can't seem to delete other files anymore. Is there something incorrect in the code I changed?
 
Upvote 0 Downvote
Where are you hitting the error message (specifically, which line)? When you find that, then try to use some response.write statements to see what your values are and where you are in the recordset (and see what may be happening). I'm not seeing anything immediately but it's been a while since I've done this...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
I don't know!!! From the original While Loop we're working on, I got this message:

Microsoft VBScript compilation (0x800A0400)
Expected statement
/admin/products/default.asp, line 410
wend


which is the one you recommended. I don't know exactly why the error's there while the wend at the end of the while loop has not been the problem before.
 
Upvote 0 Downvote
Try this. What it does is check to see if you're on the last record before you attempt to delete it. If it is the last record, then it will delete it and then exit the do loop, else it will continue on its merry way until the end. I'll warn you that this may not be the most elegant solution but should help to point you in the right direction. I've also not tested it so you'll have to play with it a little...
Code: 
ProductID=rs("ProductID")
  if len(Request("delete" & ProductID)) > 0 then
    if not rs.eof then
    'Delete records from Categories_Products
       mySQL = "DELETE FROM tblCatProd " _
       & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
       objConn.Execute mySQL
          
      'Delete records from Products
       mySQL = "DELETE FROM tblProducts " _
       & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
       objConn.Execute mySQL

       rs.MoveNext
    else
'Delete records from Categories_Products
       mySQL = "DELETE FROM tblCatProd " _
       & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
       objConn.Execute mySQL
          
      'Delete records from Products
       mySQL = "DELETE FROM tblProducts " _
       & "WHERE ProductID = " &  trim(Request("delete" & ProductID))
       objConn.Execute mySQL
      Exit Do
    end if
  end if


------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
I haven't do thorought test yet, but what I did is I moved the whole delete function to the bottom of the code right after rs.update, and it works so far. So the mentality is that I just have to update the product first then delete it, if checked. Waste time, you bet! but it works for now.

:)

Thank you for trying, Chopstik! I understand what you are trying to do at the last attempt, but I'm not interested in utilizing Do loop instead of what I'm having right now.

Thanks again!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
236
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
166
JScannell
JScannell
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
132
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
257
xwb
xwb
leifoet
  • Locked
  • Question
Classic ASP multiple record update with Access
Replies
7
Views
208
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top