GetRows() issue

[TheCandyman]

I am trying to get recordset info to move to getrows because of the increase in speed, but i seem to be having some issues with it. the record set pulls 80 records, which i can loop through just fine. When i through the info into an array i think i'm messing something up there.

	SQL="SELECT reg_id, booth_assigned FROM tbl_boothinfo WHERE booth_assigned IS NOT NULL"
	rs.open SQL,Conn,0,2,1
	If not rs.eof Then
		BoothArray = rs.GetRows()
		BoothRecLast = UBound(BoothArray)
		response.write "Booths:" &BoothRecLast& "<br>"
		    For i=0 to BoothRecLast  
    			response.write " " &booth& "-" &BoothArray(0,i)& " " &BoothArray(1,i)& "<br>"
  			Next
  	End If

Output

Booths:1
-16
-17 231

It shows it only has 2 rows, hence the Booths 1, What am i doing wrong with this getrows??

 

[travisbrown]

You need to specify the dimension with UBOUND. GetRows returns an array of fields and rows, where as you are treating it like a one dimensional array.

Try BoothRecLast = UBound(BoothArray,2)

The 2 specifies that the UBOUND is the number of rows returned, not the number of fields. Note your query returns two fields, so the UBOUND stops at 2 records.

 

[TheCandyman]

That solved one issue, my output is now correct as follows

-16
-17 231
-18 801, 803, 900, 901, 902, 903
-19 1025
-21 707, 806
-22 1119, 1121, 1218, 1220

In the second column, you see i have numbers, some single, others with several numbers in there. i would like to break each number into a seperate row in the array. I'm fairly new to arrays, so i don't know a good way to do it. Thoughts?

 

[emozley]

I would use the split function - this will turn a string into a one dimensional array:

http://www.w3schools.com/vbscript/func_split.asp

The syntax would be something along the lines of...

StringSplit=Split(BoothArray(0,i),",")

...although I may have got that wrong - the idea is to split up the string and the "," means every time you hit a "," you split it again.

 

[travisbrown]

Ah. first thing I'll tell you is you should have a normalized database structure. One table for the registration ids and another child table for each booth assigned per registration. Data is easier to manage and performance is better.

Nonetheless, what you want to do is split and loop the second field. It is really an array in itself.

Try something like this. Haven't tested, but you should get the idea.

  SQL="SELECT reg_id, booth_assigned FROM tbl_boothinfo WHERE booth_assigned IS NOT NULL"
    rs.open SQL,Conn,0,2,1
    IF NOT rs.EOF AND NOT rs.BOF Then
        BoothArray = rs.GetRows()
			IF isArray(BoothArray) THEN
       			BoothRecLast = UBound(BoothArray,2)
        		response.write "Booths: " & BoothRecLast & "<br/>"
            	For i=0 to BoothRecLast  
					CALL SplitBooth(BoothArray(0,i),BoothArray(1,i))
            	Next
			END IF
	End If


FUNCTION SplitBooth(RegNumber,BoothString)
	arr = SPLIT(str,",")
	IF isArray(arr) THEN
		FOR x = 0 TO UBOUND(arr)
			response.write RegNumber & SPACE(1) & arr(x) & "<br />"
		NEXT
	END IF
END FUNCTION

 

[TheCandyman]

travisbrown, that seems to work just great. instead of writing it out how would i store that new info??

FUNCTION SplitBooth(RegNumber,BoothString)
    arr = SPLIT(BoothString,",")
    IF isArray(arr) THEN
        FOR x = 0 TO UBOUND(arr)
            BoothArray(0,x)=RegNumber
            BoothArray(1,x)=arr(x)
        NEXT
    END IF
END FUNCTION

 

[travisbrown]

Store it where? In a new array?

What's your end game with this data?

 

[TheCandyman]

I'm doing a dynamic table, if a booth number is taken it shades out the box. Was going to wipe out the "arr" and just use the BoothArray since "arr" only seems to have 1 column.

I want to create the array when the page loads, then make calls to this function to determine if the cell is shaded.

Function ChangeImage(booth)'------------------------------------------
    ChangeImage = "images/Spacer.gif"
    For i=0 to BoothRecLast
      	If booth = BoothArray(1,i) Then 
      		ChangeImage = "images/Sold.png"
      		Exit For	
      	End If	
  	Next
End Function

But i'm still working on getting that part to work correctly. Make sense?

 

[TheCandyman]

You ever had a function return this:

ChangeImage(132) 'Call to Function

Function: 82 'Returns


I make a call to a function, even if the function is blank and it returns that, what is it?? I have never seen that before

 

[travisbrown]

Probably just need a simple conditional. Where are you getting your complete booth ids from?

 

[TheCandyman]

This is the html call

<td background="images/Floor_Layout_04.gif"><img src="<% ChangeImage(132) %>" width="39" height="41" alt=""></td>

This is the Function

Function ChangeImage(booth)'------------------------------------------
  	TempImage = "images/Spacer.gif"
   ' For i=0 to UBound(BoothArray,2)
   '   	If booth = BoothArray(1,i) Then 
   '   		TempImage = "images/Sold.png"
   '   		Exit For	
   '   	End If	
   ' Next
  	ChangeImage = TempImage
End Function

I did comment out part of it, and it still returns this:

<td background="images/Floor_Layout_04.gif"><img src="Function: 82" width="39" height="41" alt=""></td>

 

[travisbrown]

You are manually naming the booths in your html? If so, call your function like this. Again, not tested.

<td background="images/Floor_Layout_04.gif"><img src="<% = BoothCheck(39) %>" width="39" height="41" alt=""></td>
<%

FUNCTION BoothCheck(BoothID)
	SQL="SELECT reg_id, booth_assigned FROM tbl_boothinfo WHERE booth_assigned IS NOT NULL"
    rs.open SQL,Conn,0,2,1
    IF NOT rs.EOF AND NOT rs.BOF Then
        BoothArray = rs.GetRows()
            IF isArray(BoothArray) THEN
                   BoothRecLast = UBound(BoothArray,2)
                response.write "Booths: " & BoothRecLast & "<br/>"
                For i=0 to BoothRecLast  
					arr = SPLIT(BoothArray(1,i),",")
					IF isArray(arr) THEN
						FOR x = 0 TO UBOUND(arr)
							IF BoothID = arr(x) THEN 
								BoothCheck = "<img src=""images/Sold.png"" width=""39"" height=""41"" alt="" Title=""" & RegNumber &""" />"
							ELSE 
								BoothCheck = "<img src=""images/someotherimage.png"" width=""39"" height=""41"" alt="" Title=""Available"" />"
							END IF
						NEXT
					END IF
                Next
            END IF
    End If
END FUNCTION

Note you are not calling functions properly. If the function is simply passing back a variable the same name as the function (as above, treat it like a response object (response.write FunctionName() or = FunctionName())

IF the Function is returning inner responses or not a reponse object, use Call Functionname()

Might be good to read up on Subs and Functions. It will help keep your code organized and easy to manage.

 

[travisbrown]

Did this help?

 

[TheCandyman]

I bookmarked this page, got moved to another project so this one went on hold until next week. I havn't tried it, but your code looks like that i need. Thanks for you help!!!!!