3

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.

Gab2021
  • 69
  • 1
  • 11
  • When working with dynamic arrays you need to use [`ReDim Preserve`](https://msdn.microsoft.com/en-us/library/c850dt17(v=vs.84).aspx) 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 Jan 13 '17 at 14:57
  • 1
    Yes. it worked. Thanks! Where do I credit your answer? Not used stackoverflow for long time ... – Gab2021 Jan 13 '17 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 Jan 13 '17 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](http://stackoverflow.com/a/26059851/692942) – user692942 Jan 13 '17 at 20:51
  • @Gab2020 that was a comment, I've [added an answer](http://stackoverflow.com/a/41637851/692942) now. – user692942 Jan 15 '17 at 16:45

2 Answers2

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

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
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

Brian T
  • 98
  • 3
  • 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](http://stackoverflow.com/questions/41636801/classic-asp-extract-data-from-a-sql-database-and-put-it-into-array#comment70474905_41636801) before you posted. `GetRows()` is great I use it all the time but no benefit in this scenario. – user692942 Jan 14 '17 at 16:16
  • It does however give you a size for the array to help with the redim. – Brian T Jan 15 '17 at 17:37
  • 1
    @BrianT: um, you do know about `objRS.RecordCount`, don't you? – Martha Jan 16 '17 at 21:52
  • @Martha - granted, you could use that but you'd have one too many wouldn't you? ;-) – Brian T Jan 17 '17 at 22:29