[thelist] SQL Update Query - Easy

[Joshua Olson]

> -----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