help with dynamic variable and getrows function

[jefferyajones41]

Im trying to get a list of data from a database and assign it to a variable to display as 1 line, all im getting is the last row in the recordset.
<code>
''''''Dimension object variables
dim objConn
dim strConn
dim rs
dim path
dim sqlStr
dim adCmdText

''''''Set this value to nothing to start, otherwise Sample Text will be in our dropdown list
value = ""

''''''Database Connection
strConn="Bakers13-Sysdata"
Set objConn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
objConn.Open strConn

''''''SQL Select Statement
pnum=left(""&ProductNum, 4)
sqlStr = " Select NUTDATA.SHRT_DESC From NUTDATA INNER JOIN crossref ON NUTDATA.ITEM = crossref.CROSSITEM WHERE (((crossref.[item])='4987') AND ((crossref.[typeofinv])='I'))"
'sqlStr ="SELECT crossref.[item], NUTDATA.SHRT_DESC, NUTDATA.ALLERGEN FROM NUTDATA INNER JOIN crossref ON NUTDATA.ITEM = crossref.CROSSITEM WHERE (((crossref.[item])='4987') AND ((crossref.[typeofinv])='I'))"
'sqlStr =" SELECT ITEM, CARBOHYDRT, SIZEGRAM FROM NUTDATA WHERE ITEM='"&pnum& " ' "

''''''Execute
rs.Open sqlStr, objConn', adCmdText

''''''Go to the first record, read the cells until its reaches the end of the set
'if not rs.eof then
'rs.movefirst
'do while not rs.EOF

'value = "INGREDIENTS: " & rs.fields("SHRT_DESC")& vbcr

'rs.MoveNext
'loop
'else
'end if

' This code makes only 3 calls to database, no matter how many rows!
If Not RS.EOF Then arrRS = RS.GetRows()
Set RS = Nothing
If IsArray(arrRS) Then
For i = LBound(arrRS, 2) To UBound(arrRS, 2)
ING = arrRS(0, i)
'ING = arrRS(1, i)
'Response.Write("<p>" & Name & " (" & ID & ")</p>" & vbCrLf)
Value=ING
Next
Erase arrRS
End If

''''''Close the connection
'rs.Close
'Set rs = Nothing

''''''Remove the carriage return from the end of the value
value = Left(Value, Len(Value)-1)
value = RTrim(Value)
</code>

 

[Geates]

I'm confused by the following code

If IsArray(arrRS) Then
    For i = LBound(arrRS, 2) To UBound(arrRS, 2)
        ING = arrRS(0, i)
        'ING = arrRS(1, i)
        'Response.Write("<p>" & Name & " (" & ID & ")</p>" & vbCrLf)
Value=ING
    Next
    Erase arrRS
End If

it looks like you are looping from the lbound of the second dimension to the ubound of the second dimension yet trying to access the first element of all dimensions. Don't you instead mean

        ING = arrRS([red]i, 2[/red])

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[jefferyajones41]

the code im using was a snippit from another website and am trying to use it here, what im trying to do is get a list and populate the variable with that list, I made the change you showed and I get only 1 Item listed in my Variable.

 

[Geates]

then [tt]msgbox ubound(arrRS,2)[/tt] to see if there are more than one record. If not, then or SQL query is wrong

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[jefferyajones41]

the msgbox returns 11

 

[jefferyajones41]

whe i do
msgbox arrRs(0,i) i get a box for each of the records im expecting, so how do i get that data in to a single variable?

 

[guitarzan]

   [b][COLOR=blue]ING = ""[/color][/b]
   For i = LBound(arrRS, 2) To UBound(arrRS, 2)
      ING = [b][COLOR=blue]ING & [/color][/b]arrRS(0, i)
    Next