record won't go away right away

akohl posted this at 18:04 — 16th January 2002.

Joined: Feb 2001

this script is supposed to update a recordset from a db junction table containing a listing for each category associated with a given book. If the number of book categories sent by the form is less than currently in the database, it is supposed to delete the next listings until the end of the file.
It seems to work ok. However, when the command to delete the current line runs and then I check the db table in ms access,
the first field only of the records that were supposed to be deleted in fact appear to be deleted.

I wanted the entire listing to get deleted.
But then, when the update form that calls this script is submitted again, the records that wouldn't seem to go away, are now gone.

Can anyone explain this?

In the script running as it should?

		
		sqlStr="SELECT [books/categories].book_id, [books/categories].category_id"
		sqlStr=sqlStr&" FROM [books/categories]"
		sqlStr=sqlStr&" WHERE ((([books/categories].book_id)="&strBookId&"));"
		rs1.open sqlStr,con1, 3,3

		for i=0 to ubound(intCategoryIdAr)
			if not rs1.eof then
				rs1.Fields("book_id")=strBookId
				rs1.Fields("category_id")=intCategoryIdAr(i)
				out=out&"<td>"&intCategoryIdAr(i)&"</td>"
				rs1.Update 
				rs1.movenext 
			else
				rs1.addnew
					rs1.Fields("book_id")=strBookId
					rs1.Fields("category_id")=intCategoryIdAr(i)
					out=out&"<td>"&intCategoryIdAr(i)&"</td>"
				rs1.update
				rs1.movenext
			end if	
		next'i
		if not rs1.eof then
			do until rs1.eof
				rs1.delete
'				rs1.update
				rs1.movenext
			loop
		end if
	rs1.Close 

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 14:41 — 17th January 2002.

They have: 812 posts

Joined: Feb 2000

This script looks ok. Are you using adLockOptimistic locktype? Do you have On Error Resume Next, maybe it's trapping an error and you're not seeing it?

Are you using Access or SQL Server? If you're using SQL Server then you should start using Stored Procedures/Views they are so much faster than using the RecordSet object, and are easier to manage.

PJ | Are we there yet?
pjboettcher.com

akohl posted this at 06:48 — 18th January 2002.

They have: 117 posts

Joined: Feb 2001

I'm using locktype number 3.
ms access 2000 database.

I didn't quite understand what you meant by trapping an error. Would you please elaborate on that point.

The field has a look up combo box to display, in access, the names of the categories and books rather than the numbers, even though the fields contain only numbers.

Could this be an issue here?

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 14:06 — 18th January 2002.

They have: 812 posts

Joined: Feb 2000

By error trapping I meant, you might be using "On Error REsume Next" in your asp code then all errors would be ignored (or handled by the code in your page). When I'm having a problem the first thing I do is comment out that line so I can see the real error.

The fact that you're using names instead of numbers shouldn't be a problem as long as your relationships are setup correctly.

PJ | Are we there yet?
pjboettcher.com