[thelist] SQL Update Query - Easy

Ken Schaefer Ken at adOpenStatic.com
Wed Jan 26 21:18:32 CST 2005



: -----Original Message-----
: From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On
: Behalf Of Joshua Olson
: Sent: Thursday, 27 January 2005 8:44 AM
: To: thelist at lists.evolt.org
: Subject: RE: [thelist] SQL Update Query - Easy
: 
: > -----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.



Don't forget validation code! SQL Injection anyone? :-)

Cheers
Ken


More information about the thelist mailing list