Mark Hensler — Wed, 04 Apr 2001 17:45:31 +0000

ewww yuck. I didn't even notice the access part. (I guess I just didn't want to see that )

akohl, I'm no expert . I even forgot to turn the page while looking up the rs.open....

LockType:
~~adLockReadOnly~~ = 1 = Records are read-only and cannot be changed.
~~adLockPessimistic~~ = 2 = Records are locked when you start editing them.
~~adLockOptimistic~~ = 3 = Records are locked when you call the Update method to commit your changes.
~~adLockatchOptimistic~~ = 4 = Required if you are performing batch updates to a set of records.

Peter J. Boettcher — Wed, 04 Apr 2001 13:59:09 +0000

When you are working with an Access db backend, things can get pretty strange. Sometimes it throws you error descriptions that have nothing to do with the actual error that is happenning. Maybe you had accidently locked that table without knowing it (a crashed asp page can do that), and it didn't unlock until it timed out.

I can't offer you any pills (besides aspirin ) but I will suggest you move to a better backend (Microsoft SQL Server or MySQL) as soon as possible, it will save you a lot of headaches, plus with a real DBMS you can use stored procedures. Once you start using stored procs you'll never go back to doing the SQL in the ASP page.

really Puzzleing!

akohl — Wed, 04 Apr 2001 13:46:08 +0000

What you guys are saying is really strange to me since, on the one hand, you are the experts (and what you are saying is in the visual interdev documentation) and I am just a newbie. And yet, something is missing here because what you are saying just doesn't seem to be happening.

The problem I had has been solved. How? No idea. I just redid everything and got it to work. I have no idea where the mistake was. Maybe I tried to delete or change a primary key? Would that have given me the error messages I was getting?

Below is the code that works.

It deletes records from recordset objects of ms access db file, and changes them. I guess that means that the changes are being saved to the db file.

Every record set is opened with a 3 cursor setting.

It works! So what is does it mean that the 3 is a "read only" cursor setting?

By the way; Do you guys know of a pill that I could take to make working with database files and asp easier, and less painfull?

------
case request.Form("sub_deletepurchases")<>""
requested_purchase=request.Form("text101")
requested_book=request.Form("text107")
requested_user=request.Form("text105")
'when we delete a purchase listing we must refund the cost*quantity
'to the account associated with the user associated with the purchase
'listing. we must also return to stock of book table the number of books
'where purchase was cancelled
'rs1 has all purchase fields except purchase_id, which is autonumber/primary key
sqlstr="SELECT Purchases.date, Purchases.book_id, Purchases.quantity, Purchases.user_id FROM Purchases WHERE (((Purchases.purchase_id)="&requested_purchase&"));"
rs1.Open sqlstr,con,3,3
'rs2 has price of book and quantity
sqlstr="SELECT Books.price, Purchases.quantity FROM Books INNER JOIN Purchases ON Books.book_id = Purchases.book_id WHERE (((Books.book_id)="&requested_book&"));"
rs2.Open sqlstr,con,3,3
'rs3 has one field, the customer who cancelled a puerchase account balance
sqlstr="SELECT Accounts.balance FROM Accounts WHERE (((Accounts.user_id)="&requested_user&"));"
rs3.Open sqlstr,con,3,3

'rs4 has one field, stock of book where purchase was deleted

sqlstr="SELECT Books.stock FROM Books WHERE (((Books.book_id)="&requested_book&"));"
rs4.Open sqlstr,con,3,3

Response.write("balance is "&rs3.Fields(0)&"")
Response.write("quantity is "&rs2.Fields(1)&"")

'here we update account in light of deleted purchase
rs3.fields(0)=cint(rs3.fields(0))+cint(rs2.Fields(0)*rs2.Fields(1))
rs3.update
rs3.MoveFirst

Response.write("new balance is "&rs3.Fields(0)&"")
Response.write("stock is "&rs4.Fields(0)&"")

'here we update stock in light of cancelled purchase
rs4.Fields(0)=cint(rs4.Fields(0))+cint(rs2.Fields(1))
rs4.update
rs4.MoveFirst
Response.write("updated stock is "&rs4.Fields(0)&"")
'here we actually delete the data in the chosen purchase listing
rs1.delete
rs1.MoveFirst

rs1.Close
set rs1=nothing
rs2.Close
set rs2=nothing
rs3.Close
set rs3=nothing
rs4.Close
set rs4=nothing
Response.Write ("that purchase was wiped out dhhuuudde. go check if details from puechase no. "&requested_purchase&" are gone")
------------

Peter J. Boettcher — Wed, 04 Apr 2001 12:42:31 +0000

Mark was right about the cursor, it should be 2 and the locktype should be fine at 3. Have you saved anything to this database yet? It almost sounds like wherever it is located there is no "write" permissions set, only read. To save into an Access db the folder must have write permissions. If that's not it make sure you didn't save it as read-only.

I don't undestand

akohl — Wed, 04 Apr 2001 10:10:10 +0000

you wrote;

>rs.Open sqlstr, con,3,3 = rs.Open slstr, con, >adOpenStatic, adOpenStatic

first of all, the second 3 is for lock type and not cursor type, isn't it?

Second of all, the change you suggest didn't help.

>you want:
>rs.Open sqlstr, con,2 = rs.Open slstr, con, adOpenKeyset

By the way, you didn't mean to leave off the lock type value, did you? I tried it like this;

rs.Open sqlstr, con,2,3

and like this;

rs.Open sqlstr, con,2

The first gives me, "database or object is read only"

the second gives me, "The operation requested by the application is not supported by the provider"

Besides I have been using rs.open sqlstr,3,3 for adding, updating and deleteing fields in the very same application, in fact, on the very same asp page, and it has been working fine, until now.

This one has really got me! I'm totally stuck here!

Please keep on trying to help me out until we get to the bottom of this!

Thanks in advance for your efforts.

Mark Hensler — Wed, 04 Apr 2001 06:14:53 +0000

rs.Open sqlstr, con,3,3 = rs.Open slstr, con, adOpenStatic, adOpenStatic

you want:
rs.Open sqlstr, con,2 = rs.Open slstr, con, adOpenKeyset

~~adOpenForwardOnly~~ = 0 = Creates a read-only recordset that can only scroll forward
~~adOpenKeySet~~ = 1 = Curor enables you to add, modify, and delete records, but you won't see changes maade by other users while your recordset is open.
~~adOpenDynamic~~ = 2 = Cursor enables you to add, modify, and delete records, and you will see any changes made by other users.
~~adOpenStatic~~ = 3 = Creates a read-only reacordset that has all capabilities for positioning; that is forward and backward, as well as bookmarking.