Recordset+Excel+Graph and array+ASP 1

[SH4F33]

Hello everybody. The question Im going to ask is not purely ASP, but the problem is on an ASP page. Let me resume the situation, I have an Excel spreadsheet. I need to display the values from the Excel document to an ASP page. This part is working. Now from these values, I need to make a line graph. I did the graph using Office Web Component. The graph is fine, but the problem is that the graph displays a limited number of records. I dont have the number in my head right now, since I'm at home. Well, lets say the graph displays a max of 10 records. 10 records are enough for me to do the graph. But, the 10 records start from the 1st record and end at the 10th record. The Excel document contains 883 (or 889) records.

When starting doing the page, Ive decided to display a record after each 30 records on the graph. Now to get these records out of 883 records, I've selected a record after each 30 records and put them in an array. In details, Ive selected record 30, 60, 90, 120... until end of recordset. But the method Ive used to get a record after each 30 records is very primitive.

The excel document has 3 columns.
Date|Col1|Col2

Here's the code:

'**********************
'START COUNTING RECORDS
'**********************
Dim Count

If Not objRS.EOF Then

	objRS.MoveFirst

	Count = 1

	Do While Not objRS.EOF

		objRS.MoveNext

		Count = Count + 1

	Loop

	objRS.MoveFirst

End If
'**********************
'END COUNTING RECORDS
'**********************

'**********************
'START POPULATING ARRAY
'**********************
Dim iCount
Dim arrayMax
Dim arrValues()
Dim limitCounter
Dim indexCounter
Dim strDate
Dim startDate

iCount = Int(Count)

arrayMax = int(iCount/30)

REDIM arrValues(2,int(arrayMax))

limitCounter = 1

indexCounter = 0

ObjRS.MoveFirst

Do While Not ObjRS.Eof

	If limitCounter = arrayMax + 1 Then

'		If objRS("Date") = "" Then

'			arrValues(0,indexCounter) = " "

'		Else

		arrValues(0,indexCounter) = Cstr(objRS("Date"))

'		End If

		arrValues(1,indexCounter) = objRS("Col1")

		arrValues(2,indexCounter) = objRS("Col2")

		limitCounter = 1

		indexCounter = indexCounter + 1

		if indexCounter = arrayMax + 1 Then

			exit Do

		end If

	End If

	limitCounter = limitCounter + 1

	ObjRS.MoveNext

Loop

'Putting last record in last item in array
objRS.MoveLast
arrValues(0,indexCounter-1) = objRS("Date")
arrValues(1,indexCounter-1) = objRS("Col1")
arrValues(2,indexCounter-1) = objRS("Col2")
endDate = objRS("Date")

'*********************
'END POPULATING ARRAY
'*********************

I got the number of records and kept it in variable iCount, then Ive divided the number of records by 30, and kept the result in variable arrayMax. This means that I'll put a record in the array after each arrayMax'th record. In the loop, Ive started to populate the array after each arrayMax'th record.

1. Im not satisfied with this method. I dont know if it is the right method to do what I wanted.

2. Is there a way to select a record after each Nth record using SQL query?

3. If I do a loop in the recordset and response.write the 3 columns, all records in field Col1 and Col2 disply fine. But for the field date, only dates having the format dd.mm.yyyy got displayed. I cant the format of each date. From what Ive noticed, the date should have the dd.mm.yyyy in the cell, and also it should have the format dd.mm.yyyy in the formula bar, if not, the the value will be null in the recordset. And the Date field in a record is null, the graph wont take this record. On the graph, the date is on the X-axis.

Thanks for any input.

 

[Tarwn]

3) The magic of reading Excel using SQL statements causes some issues. Basically the driver determines what type each column is when you do the query. Then as it pulls the data out it purposefully ignores any values that are not of the type/format for the column. No attempt to cast occurs, you just get a null value. There are several cases where this is a real pain (DTS packages for instance) at correcting, but generally the best bet is to select the whole column and change the cell format for the whole column in one go.

2) Yes, though it is much easier if you have a row number available. In fact I would suggest going ahead and adding a row number column to the sheet, as it will make your life much easier. With a row number you could use the MOD() function to determine which rows are evenly divisible by 30 (though you may need to toss in some casts also).
The other method I was thinking of likely wouldn't work with Excel and requires a column to order on:

SELECT TOP 1 * 
FROM (SELECT TOP 30 * FROM Whatever ORDER BY MyField)
ORDER BY MyField DESC

By changing the 30 to a 60, 90, etc in your code (or having a counter and incrementing it until the recordset returned is empty) then you could grab only the one record you need for each of these.

1) A cleaner (and faster) way to get an array from a recordset is to use the .GetRows method of the recordset. This removes the need to pre-declare the size of your array (and thus the need to loop and get the record count) and is more efficient (7-11 times usually) than looping through the recordset manually.

http://www.w3schools.com/ado/met_rs_getrows.asp

Hope this helps,
-T

Best MS KB Ever:

http://support.microsoft.com/kb/555375

 

[SH4F33]

Thanks for the replay and sorry for the latre response. Tomorrow at work , I'll try to add a new column in the excel file and work with MOD(). And thanks for the GetRows link.
I'll let you know about the progress.