getRows() and copying arrays. 1

[bitrot]

I want to copy an individual array out of what is returned from getRows with my recordset.

How do you copy to a single array from a multidimensional array ? I have tried stuff like.

tempArray = multiDimensionalArray(0)
'0 being where the array I want is stored

and I've also tried

REDIM PRESERVE multiDimensionalArray(0,UBOUND(multiDimensionalArray,2))

But no such luck... any other ideas ?

The multidimensional array is being created by the getRows method, if I could just return a single array from that, that would also work.

 

[ChrisHirst]

objRS.GetRows(NumOfRows,Start)

default for start is the current record

so
objRS.MoveFirst
objRS.GetRows(1)
will get the first record

objRS.MoveLast
objRS.GetRows(1)
will get the last record

objRS.Move(n,1)
objRS.GetRows(1)
will get the n^th record








Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[bitrot]

I am trying to use getRows so I can close the recordset quickly, I dont need to iterate through the whole thing, I'm actually trying to avoid that at first. I just want a copy of the one columns data stored in a regular old array, not a multi dimensional one.

 

[ChrisHirst]

GetRows sizes the array to suit the RS returned. so if you just want one column, then just put that one column into the recordset. It will then only be a one dimension array.

strSQL = "SELECT columnname FROM table ;"
objRS.open strSQL,objConn, etc 
ArrData = objRS.GetRows()
objRS.close 
' etc

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[bitrot]

FUNCTION setManufacturers()

	Query = "SELECT DISTINCT manufacturerNameSTR AS Category " &_
			"FROM manufacturers WHERE (manufacturerNameSTR IS NOT NULL) " &_
			"AND (manufacturerNameSTR <> 'Gift Certificate')"
			
	SET RS = SERVER.CREATEOBJECT("ADODB.RecordSet")
	SET DB = SERVER.CREATEOBJECT("ADODB.Connection")
	DB.Open dbLocation
	SET RS = DB.execute(Query)

	IF NOT rs.eof THEN
		manufacturerArray = rs.getRows()
	END IF
	
	RS.close
	SET RS = nothing
	DB.close
	SET DB = nothing
	
	IF IsArray(manufacturerArray) THEN
		
		REDIM manProdCountArray(UBOUND(manufacturerArray, 2))
		
		FOR iter = LBOUND(manufacturerArray,2) TO UBOUND(manufacturerArray,2)
			'response.write(iter & "<br>")
			SET mfgDB = SERVER.CREATEOBJECT("ADODB.Connection")
			SET mfgRS = SERVER.CREATEOBJECT("ADODB.RecordSet")
			mfgDB.Open dbLocation
			
			Query = "SELECT Count(productIdINT) AS productCount " &_
					"FROM products, manufacturers " &_
					"WHERE (manufacturers.manufacturerNameSTR='" & manufacturerArray(0,iter) & "') " &_
					"AND (manufacturers.manufacturerNameINT = products.manufacturerNameINT)"
					
			SET mfgRS = mfgDB.execute(Query)
			
			IF NOT mfgRS.eof THEN
				manProdCountArray(iter) = mfgRS("productCount")
			END IF
			
		NEXT
		
		mfgRS.close
		SET mfgRS = nothing
		mfgDB.close
		SET mfgRS = nothing
		
		Application("manufacturerArray") = manufacturerArray
		Application("manProdCountArray") = manProdCountArray
	END IF
	
END FUNCTION

RESPONSE.WRITE("manufacturerArray " & UBOUND(Application("manufacturerArray")) & "<BR>")
RESPONSE.WRITE("manProdCountArray " & UBOUND(Application("manProdCountArray")) & "<BR>")

returns:

manufacturerArray 0
manProdCountArray 83

the manufacturer array should be filled with the strings from the database.

 

[DreXor]

Chris, with the method you suggested i still think it comes back as a 2 dim array, just 0 on one of the ubound values

DreX
aKa - Robert

 

[DreXor]

this might help:
please note been ages since i've mussed about with getrows and 2 dim arrays you might have to poke at this a few times to get it to work - sorru dont have faculties here to test it..

Function ColumnOutGetrows(OriginArray,Column) ' originarray being the return from getrows, column being the integer valueof the column you want.
  Dim OutputArray()
  For PosMark=0 to ubound(OriginArray,1) ' this might need to be 2 instead of 1, i cant remember what each points to X or Y coords
      ReDim Preserve OutputArray(PosMark)
      OutputArray(PosMark) = OriginArray(Column,PosMark) ' dunno if these two need to be flipflopped ( see above )
  Next
  ColumnOutGetRows = OutPutArray
End Function

DreX
aKa - Robert

 

[Tarwn]

Ouch, it looks like right now your doing a lot of extra loops. I mean even if you only have ten records in your first recordset, thats 11 connections objects, 11 recordset objects (that you don't need), 11 calls to the database...wow.

To answer your previous post, your getting 0 back for the first array because your storing a two-dimensional array that's UBounds are 0,n. Since you didn't tell the UBound function which dimension you wanted the upper bound on (in your print statement) it went and got the upper bound for the first dimension. If you change your statement to:

RESPONSE.WRITE("manufacturerArray " & UBOUND(Application("manufacturerArray")[highlight],2[/highlight]) & "<BR>")

you should be set.

I know it wasn't originally your question, but I think we can find a better chain of logic to get the results you need.

First of all, if you are going to be receiving a recordset back from a ConnectionObject.Execute you do no need to Server.CreateObject that Recordset ahead of time. The connection objectis going to be creating it's own recordset internally then replacing whatever your recordset variable is pointing at (in this case a brand new recordset) with the populated one.

Also, I'm not sure why you have the loop in the middle at all?

A simpler solution would be a single sql statement that returns both the manufacturer andthe product count all at once, cutting down on that count + 1 calls to the database:

Query = "SELECT DISTINCT manufacturerNameSTR, COUNT(productIdINT) " & _
        "FROM manufacturers INNER JOIN products ON manufacturers.manufacturerNameInt = products.manufacturerNameINT " & _
        "WHERE (manufacturerNameSTR IS NOT NULL) " &_
            "AND (manufacturerNameSTR <> 'Gift Certificate') " & _
        "GROUP BY manufacturerNameSTR"

Now we would receive a recordset that gives us the manufacturer name and product count (personally I would also order it by manufacturer name so I would hav a pretty alphabetical list, but thats just me).

Now if you still wanted this to be in two arrays you could simply call GetRows twice, which will still be amazingly cheaper then the count + 1 connections, etc

Query = "SELECT DISTINCT manufacturerNameSTR, COUNT(productIdINT) as manProductCount" & _
        "FROM manufacturers INNER JOIN products ON manufacturers.manufacturerNameInt = products.manufacturerNameINT " & _
        "WHERE (manufacturerNameSTR IS NOT NULL) " &_
            "AND (manufacturerNameSTR <> 'Gift Certificate') " & _
        "GROUP BY manufacturerNameSTR"

Dim db_conn, rs_manufs
'Create and open the connection
Set db_conn = Server.Createobject("ADODB.Connection")
db_conn.Open dbLocation

'Execute the query and grab the returned recordset
Set rs_manufs = db_conn.Execute(Query)

'store this as two arrays in Application variables
Application("ManufacturerName") = rs_manufs.GetRows(-1,1,"manufacturerNameINT")
Application("ManufacturerProdCount") = rs_manufs.GetRows(-1,1,"manProductCount")

Set rs_manufs = Nothing
db_conn.Close
Set db_conn = Nothing

If you don't like ave 0, n arrays and just want a single dimension array it would be fairly easy to assign those two arrays to temporary variable and create a couple new arrays base on their UBounds, here is an example function:

'accepts an array to flatten and a numeric field to flatten (first index)
Function FlattenRSArray(arrOriginal,fieldToFlatten)
   Dim arr_new, ctr
   ReDim arr_new(UBound(arrOriginal,2)
   For ctr = 0 to UBound(arr_new)
      arr_new(ctr) = arrOriginal(fieldToFlatten,ctr)
   Next
   FlattenRSArray = arr_new
End Function

So if we were to add that to the code I had above, we would now have:

Query = "SELECT DISTINCT manufacturerNameSTR, COUNT(productIdINT) as manProductCount" & _
        "FROM manufacturers INNER JOIN products ON manufacturers.manufacturerNameInt = products.manufacturerNameINT " & _
        "WHERE (manufacturerNameSTR IS NOT NULL) " &_
            "AND (manufacturerNameSTR <> 'Gift Certificate') " & _
        "GROUP BY manufacturerNameSTR"

Dim db_conn, rs_manufs
'Create and open the connection
Set db_conn = Server.Createobject("ADODB.Connection")
db_conn.Open dbLocation

'Execute the query and grab the returned recordset
Set rs_manufs = db_conn.Execute(Query)

'get the data into an array and break it into two separate 1-dimensional arrays
Dim arr_temp
Set arr_temp = rs_manufs.GetRows(-1,1,"manufacturerNameINT,ManufacturerProdCount")

Set rs_manufs = Nothing
db_conn.Close
Set db_conn = Nothing

'store data as two arrays in Application variables
Application("ManufacturerName") = FlattenRSArray(arr_temp,0)
Application("ManufacturerProdCount") = FlattenRSArray(arr_temp,1)

Hopefully that will help increase your efficiency by at least a single order of magnitude, possibly more depending on how many records there were in the manufacturers table.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[DreXor]

throws tarwn a bone... good job again as usual man

DreX
aKa - Robert

 

[ChrisHirst]

you're correct drex it does return an array
array(0,recordcount)

bad dog chris.

should check facts first. but hey! it's never stopped me before.

but T (vbScript ) saves the day






Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[bitrot]

Thanks, for your help on the efficiency bit. Works Great A+

 

[Tarwn]

Glad to be of assistance, hope it helps in the future as well,

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.