[thelist] SQL Update Query - Easy
Joshua Olson
joshua at waetech.com
Wed Jan 26 15:43:47 CST 2005
> -----Original Message-----
> From: Rob Smith
> Sent: Wednesday, January 26, 2005 4:26 PM
> on Received.asp:
> Videos = request.QueryString("Video")
>
> Video = split(trim(Videos),",")
>
> counter = 0
>
> set RS = Server.CreateObject("ADODB.Recordset")
> RS.ActiveConnection = MM_web_request_STRING
> RS.CursorType = 0
> RS.CursorLocation = 2
> RS.LockType = 3
>
> while counter < uBound(Video)+1
> RS.Source = "Update tblVideos SET CheckedIn = 1 WHERE
> Video = '" &
> Video(counter) & "'"
> RS.Open()
> counter = counter + 1
> wend
>
> This will update the first record even when there are about
> 10 "Video's"
> checked off of the original form... Even when I do a quick
> response.write
> <query string> I see 10 Updates wiz across the screen. Yet
> only one, 1, uno,
> een gets updated. The others are lost.
Strange things happen when your code is more complex than need be, or when
you use the wrong tool for the job. Try this instead:
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open MM_web_request_STRING ' I'm assuming that this is the connect
string
while counter < uBound(Video)+1
sql = "Update tblVideos SET CheckedIn = 1 WHERE Video = '" &
Video(counter) & "'"
oConn.Execute sql
counter = counter + 1
wend
You could, of course, make this more efficient by considering the id's in
blocks of 50 or 100 and using an IN clause in the update query.
<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168
More information about the thelist
mailing list