Vista - How to combine/return multiple values from a sql query

Hi guys.
I've been stuck on this for a while as a newb, so please forgive me in
advance.
Any help would be very appreciated.

I wrote a little script to query a sql server database.
Everything works except the fact that more than one recordset can be
returned, and each record can have more than one "Standard". How can
I get the multiple values of this field for each record and string
them into one element/field (this is for transmitting to FileNet with
XML code).

Set objConn = CreateObject("ADODB.Connection")
strConnection = "Driver={SQL
Server};SERVER=test;Database=mydb;UID=AppUID;PWD=pwd"

objConn.Open strConnection

Set objRS=CreateObject("ADODB.Recordset")

strSQLQuery = "SELECT a.TestNumber, a.MTE, a.Cust_MTE, a.Manufacturer,
a.Model, a.Serial, " & _
"a.Description, a.CustomerName, a.CustomerType, a.Technician,
a.Inspector, a.CalDate, a.DueDate, " & _
"a.AsFoundCode, a.AsReturnCode, a.PO_Number, a.CompletedDate,
a.CustomerID, a.Vendor, " & _
"a.QCApprovedBy, a.QCApprovedDate, b.CalProcedure, b.Rev_No,
c.STAND_ID " & _
"FROM LIMS_Cal_StandardsUsed AS c RIGHT JOIN (LIMS_Assets_CalProcsDef
AS b " & _
"INNER JOIN LIMS_CalRecord AS a ON b.MTE_PUI = a.MTE_PUI) ON c.TNPUI =
a.PUI " & _
"WHERE (((a.TestNumber)='" & strPDFNumber & "'))"

objRS.Open strSQLQuery, objConn

if objRS.EOF then
Dim fso1, tf
Set fso1 = CreateObject("Scripting.FileSystemObject")
Set tf = fso1.OpenTextFile("\\chaclsgfp1\apps\MudCats\ErrorLog.txt",
ForAppending, True)
tf.WriteLine ("Error finding record in database: " & strPDFNumber & "
" & strDate)
tf.close
Err.Clear
end if

'Take the names from the recordset
strTestNumber = objRS(0)
strMTE = objRS(1)
strCustMTE = objRS(2)
strSerial = objRS(5)
strCustType = objRS(8)
strCalDate = objRS(11)
strDueDate = objRS(12)
strInstruction = objRS(21)
strStandard = objRS(23)

if strStandard = "" Then
strStandard = "NONE"
end if

XML Portion - Each record returned from the query above can have more
than one Standard.

'idmDocMvCustom2 - Reference
with DocElem.appendChild(Request.createElement("property"))
.setAttribute "label", "Reference (M)"
.appendChild(Request.createElement("value")).text = strCalDate
.appendChild(Request.createElement("value")).text = strStatus
.appendChild(Request.createElement("value")).text = strInstruction
.appendChild(Request.createElement("value")).text = strStandard
.appendChild(Request.createElement("value")).text = strTestNumber
.appendChild(Request.createElement("value")).text = strCustMTE
.appendChild(Request.createElement("value")).text = strSerial
.appendChild(Request.createElement("value")).text = strCustType
.appendChild(Request.createElement("value")).text = "Calibrated By:
" & strInspector
end with

"Holly" <excyauseme@xxxxxx> wrote in message
news:57d44437-55b0-415f-8909-33c2602d9190@xxxxxxYou code has one recordset object, objRS. You open it with a query that
joins several tables, but you get one recordset, with possibly many rows.
However, you only read the first row. You need to loop through all of the
rows. I don't know how you want to combine the results from all rows, but in
the example below I concatenate values into one string for each field:
========
' Read each row of the recordset
Do Until objRS.EOF
' Read values from this row.
strTestNumber = strTestNumber & "," & objRS(0)
strMTE = strMTE & "," & objRS(1)
strCustMTE = strCustMTE & "," & objRS(2)
strSerial = strSerial & "," & objRS(5)
strCustType = strCustType & "," & objRS(8)
strCalDate = strCalDate & "," & objRS(11)
strDueDate = strDueDate & "," & objRS(12)
strInstruction = strInstruction & "," & objRS(21)
strStandard = strStandard & "," & objRS(23)
' Move to the next row.
objRS.MoveNext
Loop

' Clean up.
objRS.Close
========
Actually, I prefer referring to fields by name rather than some index
number. For example:
==========

' Read each row of the recordset
Do Until objRS.EOF
' Read values from this row.
strTestNumber = strTestNumber & "," & objRS.Fields("TestNumber").Value
strMTE = strMTE & "," & objRS.Fields("MTE").Value
strCustMTE = strCustMTE & "," & objRS.Fields("Cust_MTE").Value
strSerial = strSerial & "," & objRS.Fields("Serial").Value
strCustType = strCustType & "," & objRS.Fields("CustomerType").Value
strCalDate = strCalDate & "," & objRS.Fields("CalDate").Value
strDueDate = strDueDate & "," & objRS.Fields("DueDate").Value
strInstruction = strInstruction & "," &
objRS.Fields("CalProcedure").Value
strStandard = strStandard & "," & objRS.Fields("STAND_ID").Value

' Move to the next row.
objRS.MoveNext
Loop

' Clean up.
objRS.Close
=========
You could add code to remove (or prevent) the leading comma in each string.
You get the idea.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--