Classic ASP extract data from a SQL Database and put it into array

Question

A simple table (Table1) with two fields (numer of monthly Apples and number of monthy Oranges for two seasons) in SQL Server 2000. I need to extract the values of those fields using Classic ASP and populate two arrays. Constraints: those arrays were defined already by an out-of-the-shelve app that I need not to touch too much because I am a beginner in Classic ASP and I might do something wrong.

Definition of those arrays are as follows:

Dim m_arrApples, m_arrOranges

I have succeded to connect to database, extract data and then display it in a browser (format figures). Yet, when I need to transfer it in an array (in order to be taken over by that out-of-the-shelve app and process it (displaying in a graph format) I encounter an error. Here it is my code:

'Open connection with SQL Server
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;server=" & NameServer & ";database=" & DataBaseName & ";uid=" & NameUser & ";pwd=" & DataBasePassword & ";" 

'Open table and all records for further processing 
dim strSQL
strSQL = "select * from " & Table1
Set rst = conn.Execute(strSQL)

'Going through the records in the table and try to get values of each field and allocate them to the array
If rst.BOF And rst.EOF Then
    Response.Write "No data"
Else

i=0

Do While (Not rst.EOF)
i=i+1

'The line below confirms that the data extracted from database is displayed in the browser
'in its primary form (not graph), but that means I did it correctly up to this point 
Response.Write rst("Apples") & "/" & rst("Oranges") & "<br>"

m_arrApples(i)= rst("Apples")         ' THE ERROR IS AT THIS LINE. HERE THE SCRIPT IS STOPPED
m_arrOranges(i)= rst("Oranges")

rst.MoveNext
Loop
End If

The error is:

Microsoft VBScript runtime error '800a000d' 
Type mismatch

Any hint will be highly appreciated.

When working with dynamic arrays you need to use ReDim Preserve to define the new size, if you already know the size use a fixed array (if you know you'll have 10 values use Dim m_arrApples(9)). — user692942
– user692942, Commented Jan 13, 2017 at 14:57
Yes. it worked. Thanks! Where do I credit your answer? Not used stackoverflow for long time ... — Gab2021
– Gab2021, Commented Jan 13, 2017 at 15:09
It's not so much that you're using the wrong method to resize the arrays, it's more that you haven't defined any arrays, dynamic, fixed, or otherwise. Lankymart's answer relies on the fact that Redim will actually convert a non-array variable into an array, but your code would be more understandable if you dimmed your arrays as, well, arrays: Dim m_arrApples(), m_arrOranges(). — Martha
– Martha, Commented Jan 13, 2017 at 19:39
@Martha same old argument, the difference is minor and as long as you ReDim inconsequential. However there is still one compelling reason to use Dim yourvar() and that is IsArray() will always be True where as Dim yourvar is just a variable so it will return False - see Dim output() versus Dim output — user692942
– user692942, Commented Jan 13, 2017 at 20:51

Community · Accepted Answer · 2017-05-23 10:29:36Z

2

When working with dynamic arrays you need to use

ReDim Preserve m_arrApples(i + 1)

to increase the size of the Array, if you already know the size use a Fixed Array (if you know you'll have 10 values use Dim m_arrApples(9)).

Useful Links

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Jan 13, 2017 at 15:19

user692942

16.4k8 gold badges85 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian T · Accepted Answer · 2017-01-14 19:32:20Z

0

Try using the Getrows() method of the Recordset

'Create a Recordset
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM Table1", objConn

'now read the Recordset into a 2nd array
Dim aTable1Values
aTable1Values = objRS.GetRows()

You can then loop through the 2 dimensional array thus :

Dim iRowLoop, iColLoop
For iRowLoop = 0 to UBound(aTable1Values, 2)
  For iColLoop = 0 to UBound(aTable1Values, 1)
    Response.Write(aTable1Values(iColLoop, iRowLoop) & "<br>")
  Next 'iColLoop

  Response.Write("<p>")
Next 'iRowLoop

more information here https://web.archive.org/web/20210619191820/https://www.4guysfromrolla.com/aspfaqs/ShowFAQ.asp?FAQID=161

edited Jan 14, 2017 at 19:32

answered Jan 14, 2017 at 13:30

Brian T

863 bronze badges

4 Comments

user692942 Over a year ago

That doesn't get you two single dimension arrays, it get's you one multiple dimension array, you still have to break it down into two arrays - "I need to extract the values of those fields using Classic ASP and populate two arrays. Constraints: those arrays were defined already". Also OP has already commented before you posted. GetRows() is great I use it all the time but no benefit in this scenario.

Brian T Over a year ago

It does however give you a size for the array to help with the redim.

Martha Over a year ago

@BrianT: um, you do know about objRS.RecordCount, don't you?

Brian T Over a year ago

@Martha - granted, you could use that but you'd have one too many wouldn't you? ;-)

Collectives™ on Stack Overflow

Classic ASP extract data from a SQL Database and put it into array

2 Answers 2

Useful Links

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Useful Links

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related