Solved

Array from ADO recordset, then display in a mesaage box

Posted on 2004-08-19
11
186 Views
Last Modified: 2010-04-17
Exactly what the title says....
This is the code I have, but I don't know ow to display the array in a mesage box....

   Set adoMRS2 = New ADODB.Recordset
   With adoMRS2
                  strSql2 = "Select Product_ID from tracking_file Where Product_ID <> '" & strDTID & "' AND field_1 ='" & strSS & "'"

             .Open strSql2, adoMCN2, , , 1
            End With
If Not adoMRS2.EOF Then
arr1 = adoMRS2.GetRows

MSGBOX "These are the results: " & arr1

I would prefer to have each record on a anew line, so I suppose I need to insert &Chr(13) betwen each.
0
Comment
Question by:lexo
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 6

Expert Comment

by:bkthompson2112
ID: 11846344
Hi lexo,

You'll need to loop through arr1 and add each field of each record to a string.
after each record add a vbCrLf.

then use that string in the msgbox.

Be careful, according to msdn: "The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used."
see here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctMsgBox.asp

maybe a better way to do this would be to loop throught arr1 and use debug.print to send the results to the immediate window.

bkt
0
 
LVL 8

Expert Comment

by:bramsquad
ID: 11846351
'do everything to get your array here

        Dim arr1() As String

        Dim i As Integer, total As String
        For i = 0 To arr1.Length - 1
            total += arr1(i) + vbCrLf
        Next

msgbox( "These results are:" + vbcrlf + vbcrlf + total )

~b

0
 
LVL 8

Expert Comment

by:bramsquad
ID: 11846356
just to note, vbcrlf is the visual basic constant for a carriage return, i know you mentioned Chr(13)
0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

Author Comment

by:lexo
ID: 11846399
it errors at .Length.  I am using a very toned down version of Vb in a macro. I don't think it sees that as a function.
0
 
LVL 8

Expert Comment

by:bramsquad
ID: 11846714
im using .net, prolly why.....try this

For i = 0 To UBound(arr1) - 1

~b
0
 
LVL 3

Expert Comment

by:Stimphy
ID: 11857664
Try this...

Join(Arr1,vbCrLf)


Regaurds,
Dave
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11860230
The GetRows method is used to copy records from a Recordset object into a variant that is a two-dimensional array. The variant array is automatically dimensioned (sized) to fit the requested number of columns and rows. To allow backwards compatibility with earlier versions of ADO, the columns are placed in the first dimension of the array and the rows are placed in the second dimension.

Dim arr1 as Variant
Dim i as Long
Dim j As Long

 Set adoMRS2 = New ADODB.Recordset
   With adoMRS2
               strSql2 = "Select Product_ID from tracking_file Where Product_ID <> '" & strDTID & "' AND field_1 ='" & strSS & "'"

             .Open strSql2, adoMCN2, , , 1
            End With
If Not adoMRS2.EOF Then
arr1 = adoMRS2.GetRows

For i = LBound(arr1, 1) to UBound(arr1, 1)
    sLine = ""
    For j LBound(arr1, 2) to UBound(arr1, 2)
        sLine = sLine & arr1(j, i) & ", "
    Next j
    sLine = Left(sLine, Len(sLine) -2)
    Debug.Print sLine
    MsgBox "Line " & i & " " & sLine
Next i

0
 
LVL 3

Expert Comment

by:Stimphy
ID: 11863845
I think what you are doing now will probably be your best alternative.

Regaurds,
Dave
0
 

Author Comment

by:lexo
ID: 11874894
Ok I got it to work, but didn't use the code provided to me here.  Thank you for your time!!

arr1 is a string.

If Not adoMRS2.EOF Then
Do Until adoMRS2.EOF = True
   arr1=  arr1 & adoMRS2("Loan_ID") & vbCrLf
   adoMRS2.MoveNext
Loop
MsgBox "Results:  " & vbCrLf & arr1
End If
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 12097181
PAQed, with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
wordcount challenge 11 121
Not needed 13 112
Programatically extract date from website 8 77
Cygwin - GNU GPL License 1 24
Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question