Fog Creek Software
Discussion Board




Simple ASP Question

* How do you do a multiple Insert or muliple Update in classic ASP and MS Access?

Here's my database table:

tblWidgets:
Widgets_ID (number field)
Widgets_Description (memo field)

* My current coding (Updating example):

strSQL = "UPDATE tblWidgets SET Widgets_Description = '" & strDescription & "' "
strSQL = strSQL & "WHERE Widgets_ID = " & strID

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn

* Does the SQL have to be wrapped up in a loop to iterate through all the changes to Widgets_Description sent from the previous page? If so, how? If not, please send me in the right direction.

Thanks.

Chi Lambda
Sunday, September 14, 2003

what you want to do with objConn.Execute qry

where qry is UPDATE statement per ItemID...

And do an execute for each item.

you can also do a objRS.Open on a SELECT where ItemID in (list of unique items)

and then while not .EOF movenext and match the rs("ItemID") to the item id in your 2d array of properties.. once you get the array offset assign the values like this

rs(Description) = arrProperties(intItemID)(0)

Enjoy :D

Li-fan Chen
Sunday, September 14, 2003

If you want to reduce round trips and want to assume one transaction anyway you could wrap multiple updates (dynamically written to point to each ItemID) in a single transaction and pass it to objConn.Execute(qry)

Li-fan Chen
Sunday, September 14, 2003

Li-fan:

Thanks for the help but I cannot get the batch Insert to work. My looping structure must be incorrect. For example:

pageONE.asp
3 instances of TEXTAREA form boxes for inserting 3 new Widgets Descriptions.

pageTWO.asp
Process the previous forms' content

What happens is that I get a comma-deliniated result in Widgets_Description with the 3 descriptions repeated in 3 new fields -- instead of each new description on its own line.

Any suggestions? (This must be so simple but I just can't get it.)

Thanks

Chi Lambda
Monday, September 15, 2003

Probably because you named the textarea input tags the same thing.

Li-fan Chen
Monday, September 15, 2003

<TEXTAREA Name"comments"></TEXTAREA>
<TEXTAREA Name"comments"></TEXTAREA>
<TEXTAREA Name"comments"></TEXTAREA>

Arrives at your ASP as value,value,value when you try to evaluate Request("comments")

Li-fan Chen
Monday, September 15, 2003

Li-fan:

Yup. Brilliant. Thanks for the help!

Chi Lambda
Monday, September 15, 2003

*  Recent Topics

*  Fog Creek Home