asked on

How to Loop to have an array result?

Hi, Experts, how to achieve this result from the loop of record set, arDataArray = Array("100 Banana", "200 Apple", "300 Orange") . After looping I want the result to be like this, Array("100 Banana", "200 Apple", "300 Orange") 100, 200 and 300 are my Procode, Banana, Apple, and Orange are my Proname

   Set rs = cn.Execute("Select Procode, Proname from ActualCount")
      with rs
         If rs.BOF = True Then 'No record
            'Do nothing
         else
            do until rs.EOF
               arDataArray
            rs.MoveNext          
            loop   
         end if
      end with

Open in new window

Manuel Marienne-Duchêne

Set rs = cn.Execute("Select Procode, Proname from ActualCount")
      with rs
         If rs.BOF = True Then 'No record
            'Do nothing
         else
            do until rs.EOF
               stringforarray = stringforarray & rs("Procode") & " " & rs("proname") & ","
            rs.MoveNext          
            loop
             arDataArray = split(left(stringforarray,len(stringforarray )-1),",") 
         end if
      end with

Open in new window

aikimark

Let SQL do the heavy lifting:

   Set rs = cn.Execute("Select Procode & " " & Proname As ArraySource From ActualCount", adOpenDynamic)
   rs.movelast
   redim arDataArray(0 to rs.recordcount -1)
   rs.movefirst
   lngPosn = 0
      with rs
         If rs.BOF = True Then 'No record
            'Do nothing
         else
            do until rs.EOF
               arDataArray(lngPosn) = rs.fields("ArraySource")
               lngPosn = lngPosn + 1
               rs.MoveNext
            loop   
         end if
      end with

Open in new window

Whing Dela Cruz

ASKER

Hi Experts, thank you so much for your codes but I can't try right now, my server computer got big trouble needs to reformat. I will give you soon. Thanks a lot!

Whing Dela Cruz

ASKER

Hi, Manuel Marrienne, I tried your code but it didn't work. I've got an empty string when I view this code.

arDataArray = split(left(stringforarray,len(stringforarray )-1),",")

Open in new window

Whing Dela Cruz

ASKER

Hi, Aikimark, I also tried your code and still didn't work. I've got an error applying with this

Set rs = cn.Execute("Select Procode & " " & Proname As ArraySource From ActualCount", adOpenDynamic)

Open in new window

ASKER CERTIFIED SOLUTION

Manuel Marienne-Duchêne

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

You have to double up interior quote characters

Set rs = cn.Execute("Select Procode & "" "" & Proname As ArraySource From ActualCount", adOpenDynamic)

Open in new window

Whing Dela Cruz

ASKER

Hi, Manuel Marrienne, sorry but still got the same result. I remove the space but the result was the same...

Manuel Marienne-Duchêne

Before

arDataArray = split(left(stringforarray,len(stringforarray)-1),",")

Open in new window

try response.write stringforarray
if it's empty, the SQL request return nothing

Whing Dela Cruz

ASKER

Hi sir, this the result

357 Allopurinol 300mg Tab ,358 Ambroxol 30mg Tab ,359 Acetylcysteine 600mg Tab

Manuel Marienne-Duchêne

For me it's working
i test

   
stringforArray = "357 Allopurinol 300mg Tab ,358 Ambroxol 30mg Tab ,359 Acetylcysteine 600mg Tab,"

    arDataArray = split(left(stringforarray,len(stringforarray)-1),",")

    for each chaine in arDataArray
        response.write chaine & "<br />"
    next

Open in new window

and the result is
357 Allopurinol 300mg Tab
358 Ambroxol 30mg Tab
359 Acetylcysteine 600mg Tab

I've an array

Whing Dela Cruz

ASKER

I've found some wrong codes in my own project but Manuel Marienne-Duchêne codes is working very well. Thanks a lot to you, sir. More power!