[thelist] stuck in a loop (classic asp/mssql)

[Ken Schaefer]

Why are you opening two connections?

Surely:
	comm.ActiveConnection = connstr1
should be:
	Set comm.ActiveConnection = oConn
?


Cheers
Ken

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Brian Cummiskey
: Sent: Tuesday, 23 August 2005 4:18 AM
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] stuck in a loop (classic asp/mssql)
: 
: Ian Anderson wrote:
: 
: > Or perhaps simpler to use the step option in the for loop?
: 
: 
: > for i = 0 to Ubound(aryValues) step 3
: >    strSQL = "update " & aryValues(i+2) & ".dbo.Database." &
: > aryValues(i+1) &" set status = 'U' where rowid = '" & aryValues(i) & "'"
: "
: >    ...execute the sql...
: > next
: 
: Perfect!  I tweaked this a little bit and i no have a working update
: script, cross-server :)
: 
: <%
: aryValues = Split(request.form("recordid"), ",")
: 
: for i = 0 to Ubound(aryValues) step 3
: 
: 	if aryValues(i+2) = "MBC" then
: 
: 
: 		server.ScriptTimeout = 9999
: 		set oConn = Server.CreateObject("ADODB.Connection")
: 		set comm = Server.CreateObject("ADODB.Command")
: 		set rst = Server.CreateObject("ADODB.Recordset")
: 		comm.CommandTimeout = 9999
: 		comm.CommandText = "sp_Multi_DNC_process"
: 		comm.CommandType = adCmdStoredProc
: 		'---- connstr1 = mbc
: 		oConn.Open connstr1
: 		comm.ActiveConnection = connstr1
: 
: 		comm.Parameters("@rowid") = aryValues(i)
: 		comm.Parameters("@jobid") = aryValues(i+1)
: 		comm.Execute
: 
: 		rowsupdated = comm.Parameters("@rowsupdated")
: 		rowsdeleted = comm.Parameters("@rowsdeleted")
: 		set comm = Nothing
: 
: 
: 		response.write "<p>rowid " & aryValues(i) & " for " &
: aryValues(i+1) &
: " has been updated</p>"
: 
: 	end if
: 
: 
: 	'----- and repeat the if for each server, replacing with connstr2
: and
: so forth down the line
: Next
: 
: 
: 
: and my mirrored SP looks like this:
: 
: 
: 
: SET QUOTED_IDENTIFIER ON
: GO
: SET ANSI_NULLS ON
: GO
: 
: 
: 
: 
: 
: ALTER    procedure sp_Multi_DNC_process
: (
: @rowid varchar(50),
: @jobid varchar(25),
: @rowsupdated int output,
: @rowsdeleted int output
: )
: as
: begin
: 
: declare @query varchar(4000)
: 
: set @rowsupdated = 0
: set @rowsdeleted = 0
: 
: 	SET @query = 'update ' + @jobid + ' set status = ''U'', resultcode =
: ''05'', ' +
: 	'resultother = ''DO NOT CALL'' where rowid =''' + @rowid + ''' '
: 	exec(@query)
: 	set @rowsupdated = @rowsupdated + @@rowcount
: 
: 
: 	set @query = 'delete from ' + @jobid + '_callrecordschedule where
: rowid
: =''' + @rowid + ''' '
: 	exec(@query)
: 	set @rowsdeleted = @rowsdeleted + @@rowcount
: 
: end
: 
: 
: GO
: SET QUOTED_IDENTIFIER OFF
: GO
: SET ANSI_NULLS ON
: GO
: 
: 
: 
: 
: 
: Thanks guys for all the ideas and suggestions.
: 
: 
: 
: --
: 
: * * Please support the community that supports you.  * *
: http://evolt.org/help_support_evolt/
: 
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !