Loop Through Recordset with VBScript

[TheStriker]

Hello,

I am trying to loop through a recordset using ADO but when I run the following code, I get the error:

'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another'

Here is the code that generates this error"

dim SQL
dim rsLoopSvrs
dim conn

SQL = "SELECT * From dbo.tbl_xp_schedule"
set rsLoopSvrs = CreateObject("ADODB.Recordset")
set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB; Data Source=MySource; Initial Catalog=MyDatabase; User ID=MyUserID; Password=MyPassword;"
conn.Execute(SQL)

if trgsvr <> "" then
do while (NOT conn.EOF)
If trgsvr = rsLoopSvrs.Fields.Item("target_server").Value And rsLoopSvrs.Fields.Item("comp_date").Value <> "" then 
	msgbox "The server you have specified has already been scheduled for an XP Image Download.  Please enter another server", vbExclamation+vbOKOnly,"Server Already Scheduled"  
exit sub
end if	
conn.MoveNext
Loop
end if

It points to the line where my do...while loop starts which makes me think my loop is not constructed properly. I don't want to use a server object via (conn = Server.CreateObject("")) because I want to use msgboxes. I don't do a lot of loops so I am out of practice. I'm thinking the problem may be in my connection string. Any help on the best way to acheive this would be most appreciated.

By the way, trgsvr is a variable that references a form element (textbox).

Thanks
TheStriker

 

[tsuji]

Hello TheStriker,

Try instead this?

SQL = "SELECT * From dbo.tbl_xp_schedule"
[red]'set rsLoopSvrs = CreateObject("ADODB.Recordset")[/red]    'commented out
set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB; Data Source=MySource; Initial Catalog=MyDatabase; User ID=MyUserID; Password=MyPassword;"
[blue]set rsLoopSvrs=[/blue]conn.Execute(SQL)

regards - tsuji

 

[tsuji]

Further notes:

Change the rest correspondingly?

if trgsvr <> "" then
do while (NOT [blue]rsLoopSvrs[/blue].EOF)
If trgsvr = rsLoopSvrs.Fields.Item("target_server").Value And rsLoopSvrs.Fields.Item("comp_date").Value <> "" then 
    msgbox "The server you have specified has already been scheduled for an XP Image Download.  Please enter another server", vbExclamation+vbOKOnly,"Server Already Scheduled"  
exit sub
end if    
[blue]rsLoopSvrs[/blue].MoveNext
Loop
end if

- tsuji