Help with how to return a multifield from sql query, & concatenate

[HolleeC]

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" or STAND_ID. 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).

Here is a snip of the 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

'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

 

[PScottC]

How is the data for that field stored in the database? Are you actually getting an array back and not a string? Are you getting a string with embedded line feeds?

PSC

Governments and corporations need people like you and me. We are samurai. The keyboard cowboys. And all those other people out there who have no idea what's going on are the cattle. Mooo! --Mr. The Plague, from the movie "Hackers

 

[HarleyQuinn]

I take it you mean that you have more than one row returned?

If that's the case you can loop the Recordset until it reached the End of File (rs.EOF = True), picking out what you need as you go along.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[HolleeC]

Hi guys. Harley, that was my problem - and PScott, I didn't know my strings from my arrays! The records (in access) returned from this query could be one line or up to 3 or 4 lines. The only difference for each record is the STAND_ID number. So I did the EOF thing (or a Do Until EOF and Loop) like Harley suggested. Then
I get each of the standard numbers and just concatenate them in one field. So I have something primitive like:

'Open the recordset
objRS.Open strSQLQuery, objConn

Do Until objRS.EOF or loopCount > 4
loopCount = loopCount + 1

strStandard = objRS(23)
strStand = strStandard & ", " & strStand
objRS.MoveNext

loop

So I think it works! Oh glorious day!
Thanks you guys!

 

[HarleyQuinn]

Glad to help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com