Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jsmith8858
Dr. Cross Join
7423 Posts |
 Posted - 2003-03-28 : 11:21:41
|
| My first question after 600+ posts !!! (but I have learned alot from others). OK, suppose I have an ASP app that is basically like a timesheet. I have a whole bunch of <input> tags on the page, lined up in a row/column grid, and named basically in the form of "RrrCcc", for row rr (01-??) and column cc (01-04). Typical # of rows might be 20. I also have some hidden <input> tags for each row: "Drr" = 1 if that row rr has been changed, 0 if it hasn't "Irr" = the primary key (LineID) of that line in the database. Thus, in the database, I have a table like this (call it Lines) : LineID, C01, C02, C03, C04. Of course, in the database the columns have actual names (like "project", "hours", "comment", etc) and there's more fields (i.e., UserID) but you get the idea. So, page is loaded with the grid and the user goes through and updates all of the fields in their screen, and behind the scenes I use Javascript to update the "Drr" input box as lines get dirty. Then, they click "SAVE", which passes the entire form (all input boxes) to another ASP script which will update the database. QUESTION (finally): What is the best way to do this? My best take is for the ASP page to make a long comma-delimited string like: LineID, Col1, Col2, Col3, Col4, LineID, Col1, Col2, ...etc ... for only lines that have changed and pass that to a stored procedure which parses it and updates the database. if the string ends up too long (>7999 chars), break it up and call the proc multiple times. Is this the best way to do this? I am concerned about the # of elements on the form in my HTML page I can pass to an ASP page (is there a limit? I am creating too many?) I want to keep things a generic as possible (i.e., not having the browsers need to download ActiveX objects) but allow the users to update many rows at the same time without re-loading the page constantly or hitting the database constantly. Any advice? Is there a better/more accepted way of doing this sort of thing? - Jeff- Jeff |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-28 : 11:35:20
|
I did one of these a while back, but don't remember what I did with it.Basically, the way I would do it is use the count property (from memory) to loop through a row at a time. I'll try to explain this.. if you were to name multiple form items the same name, you can loop through them in asp. If you were just to response.write say, UserID, you will get a csv string of all the UserIDs. But, you can also do something like :For i = 0 to Request.Form("UserID").Count thisUserID = Request.Form("UserID")(i)Nextand... these will come out in the right order. So, if you named the LineID and the UserID fields the same for each row, the index of that field will match the index of the UserID for the same row.Some almost psuedocode (assuming your lineid fields are called lineid etc)For i = 0 to Request.Form("LineID").Count if Request.Form("Drr")(i) = 1 then call update proc for Request.Form("LineID")(i) and Request.Form("UserID")(i) etc etc end ifNext The syntax maybe a little off here, but the theory works  (It's late in Sydney).I hope that makes some sense....Damian |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-28 : 11:45:14
|
| Thanks, Damian -- getting the data together from the form object is no problem (I love SQl but I've always been a programmer first, so for me that stuff is easy), I guess the question is more what is the best way to update the database. What you propose definitely makes sense and is very similiar to what I've written.However, in your example, it looks like you recommend calling a stored proc over and over for each row ... will there be performance problems or too much communication between the db server and the web server by doing this? That is why I thought of concatenating a string together and calling a stored proc just once and letting the proc parse the string and do an update on all rows at once in the database.And the other half is: Will there be performance problems passing this large number of elements in a form to an ASP script? (obviously I am not using a querystring() for this; that'd be 1 long URL!)Thanks for your help!- Jeff |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-28 : 20:34:44
|
Ahh sorry, I missed the question bit  I did say it was late...First up, that many form elements should be fine.Regarding multiple stored proc calls, we it will never be *the fastest* asp page, but shouldn't be too much of a problem. Basically, you have to parse and do multiple updates somewhere so it doesn't make sense to concatenate all the data in asp then re-parse it in your proc. With connection pooling it shouldn't be too much overhead to call the proc multiple times. You also don't have to worry about overrunning any character limits. Plus, you said you would normally have a maximum of 20 rows, in most cases people won't be updating all of them at once.I'd give it a go and see how it handles, you might be surprised.Damian |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-28 : 23:35:00
|
| Thanks Merkin ! Good points; I'll have to try both and see what happens! I hadn't considered connection pooling and all that, plus the fact that I'd be putting the data all into 1 string, and then just taking it all back out again just to save some round trips that will probably be all cached together anyway.I'll let you know what I end up doing ...- Jeff |
|
|
|
|
|
|
|
|
ASP question