July 30, 2003 at 10:05 am
Hi,
I have db driven form on an asp website with three drop-
down boxes that are mapped to one table. I'm having
trouble in finding a way to update the boxes other than
using cursors.
Data can be supplied in either some or all of the boxes.
So I am either updating, inserting or deleting records
each time the form is submitted. Each record has an ID, MID
(which is the drop-down item) and a score. The ID amd MID
combo is primary key and only the ID remains unchanged.
The best idea I can come up with, is to loop through each
record, filtering the ID and updating each one
individually. I was just concerned about the overhead and
record locking since there can be a number of concurrent
users at one time, updating their records.
Any advice is greatly appreciated!
Thanks,
Jenise
July 30, 2003 at 10:12 am
Not quite sure why you couldn't handle most of this in the ASP and not in T-SQL cursor. Could you post the schema and an example of update/insert/delete based on the form values? Also, when you say "mapped to one table", are you saying the form's elements are databound to a client-side Recordset, or simply that for each record in the table, you have a set of three dropdowns in a grid-type layout?
July 30, 2003 at 10:29 am
I don't have complete code yet, but I meant that I have a set of three drop down boxes within a table layout.
My original thought was that creating a stored procedure would be more efficient, but I can create the process in asp instead.
This is my first asp/sql project, so I'm still determining at what point it is best to run my procedures in asp or to create stored procedures within sql server.
Thanks for your input.
July 31, 2003 at 12:59 am
Quick couple of notes:
Regarding locking... you generally don't have to worry a whole lot about this when working with ASP. Unless you're putting the connection and recordset object in Session (this is generally not a good idea) then they release their locks as soon as the page finishes rendering. If you have a very large number of users on the web site, and you don't bottle neck in IIS, then you might have some locking contention in SQL, but if you keep your update transactions as small as possible, then you shouldn't have too many problems.
As far as whether or not to do something in the ASP vs. constantly calling stored procs... it depends. If you're not worried about the amount of memory that holding the data in Session would take, then cache it there and only make the initial call to the database to first load the page. Just remember that this can impact scalability of the web site. It's a moot point if you have some nice hardware on a dedicated web server and only 10 concurrent users, but it might be more important if you have 1000 concurrent users and a limited amount of RAM.
Matthew Galbraith
August 1, 2003 at 9:06 am
Thanks for the advice, I really appreciate it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply