SQL RecordSet problem

[freavis]

I am trying to run a query that return the output to a message box. However not being real familiar with recordsets I'm not sure how to return the info to the message box. I'm assuming that I will need to use some type of array. Please help.
Script:

Dim db, rundate, count

db = "Eriematerials"

rundate = InputBox("Enter Statements run date." & vbcrlf & vbcrlf _
& "Format mm/dd/yyyy ex. 01/01/2004&quot

'Create Connection
set con = CreateObject("ADODB.Connection&quot
strCon = "driver={SQL SERVER};server=WVAPP001;uid=me;pwd=uwish;database=" & db & ""
con.Open strCon

'Create Command
set doit = CreateObject("ADODB.Command&quot
set doit.ActiveConnection = con

sql = "select COUNT(kt1.keyvaluechar) as Total, kt1.keyvaluechar as Branch_ID " & _
"from {oj hsi.itemdata left outer join hsi.keyxitem101 kxi0 On " & _
"(hsi.itemdata.itemnum = kxi0.itemnum) left outer join hsi.keytable101 kt0 on " & _
"(kxi0.keywordnum = kt0.keywordnum) left outer join hsi.keyxitem106 kxi1 on " & _
"(hsi.itemdata.itemnum = kxi1.itemnum) left outer join hsi.keytable106 kt1 on " & _
"(kxi1.keywordnum = kt1.keywordnum)} " & _
"where hsi.itemdata.itemdate between {ts '" & rundate & " 00:00:00'} " & _
"and {ts '" & rundate & " 23:59:59'} and hsi.itemdata.status + 0 = 0 " & _
"and (hsi.itemdata.itemtypenum = 118) " & _
"Group by kt1.keyvaluechar "

set rs = CreateObject("ADODB.Recordset&quot

rs.Open sql, con

????????
????????

set con = nothing
set doit = nothing

wscript.quit


Output will look something like this
Total Branch_ID
234 EA
435 ES
908 EP

 

[PHV]

Something like this ?
msg="Total" & vbTab & "Branch_ID" & vbCrLf
rs.Open sql, con
While Not rs.EOF
msg=msg & rs.Fields("Total&quot & vbTab & rs.Fields("Branch_ID&quot & vbCrLf
Wend
rs.Close
Set rs =Nothing
WScript.Echo msg

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[freavis]

This was very helpful. There was one small problem though. I had to add rs.MoveNext to the loop. Went into an infinite loop the first time I ran it.
Thank you very much for your help.

 

[PHV]

Sorry for the typo but glad you solved your issue yourself.