|
| |
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
 |
I've put this off long enough - 4/2/2004 16:48:30
Time to dive into the world of arrays. I have a query that selects one field from 10 particular records in my db. I would like to assign each returned value to a variable, but have no idea how this is done. I read up a little on arrays, but it was not related to database results. Any thoughts/lessons anyone can share with me?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
|
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/2/2004 17:58:34
Not sure that's what I am looking for. Here's the fully scoop. I have the following SQL:
SELECT GoalValue From Goals
There are only 10 records in the db, so I should be getting 10 values. I need to display each value in different places throughout the page so was hoping that the values would be pulled out in ID order and I could assign each value to a variable so it will be available whereever I may need it elsewhere. Kind of sounded like an array type thing to me, but I may be off base on that. I essentially want to do something like MyValue=RS(value1), MyValue2=RS(value2), etc.
Does that make any more sense?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/2/2004 19:55:14
Exactly. Do you have a minute or two to explain how it works?
For example, what is this all about? RecordFound=-1
Second, what is the significance of the first 0 in this: Response.Write arrRecord(0, 0)
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Spooky
Posts: 26723
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: I've put this off long enough - 4/2/2004 20:10:01
RecordFound is a true / false case if there is no records.
If its possible there are none, then youll get an array error when you go to display the code.
You can use RecordFound to provide if / else code
If RecordFound then
'display the records
else
' raise an error
End if
arrRecord(0, 0) means basically this - its a 0 based array, so records start at 0.
The first column in the first record is displayed by (0,0)
The first column in the 2nd record is displayed by (0,1)
The 'column' is the value from the database :
SELECT GoalValue From Goals
You only have one 'column', so (1,1) would error
If you used :
SELECT GoalValue, FutureGoal From Goals
then you would have your 2nd 'column' of information and (1,1) would show the 2nd column value from the second record.
(Again, its a 0 based array, so '1' actually means '2' in normal terms
_____________________________
If you arent part of the solution, then there is good money to be made prolonging the problem
Sp ky
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/2/2004 20:37:20
Thank you.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/14/2004 21:21:16
Can I ask one more question on this issue? In the above example, I knew I was getting 10 records. How would I write the array values to my page if I don't know how many values I am going to get? I know it involves some sort of For x = type of stuff, but really have no clue how to do it. In my example, I am doing a SELECT DISTINCT on a field in my db. I do not know how many records I am going to get, but I want to write the results to a dropdown.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/14/2004 22:34:28
After I posted, I played around a little and came up with this. Although it seems to work, please tell me anything and everything I have done wrong as far a performance issues, etc. You will see I am pulling one full record and then also getting a second recordset that is all distinct values from one field.
<%
Dim conntemp, myDSN, myRS, mySQL, MyRecord, myRS2, mySQL2
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/Team304/fpdb/Autos.mdb")
conntemp.open myDSN
mySQL = "SELECT * FROM Results WHERE ID=4"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 3, 3
MyType=myRS("Type")
MyMake=myRS("Make")
MyModel=myRS("Model")
myRS.Close
Set myRS = nothing
mySQL2 = "SELECT DISTINCT [Type] FROM Results"
Set myRS2 = Server.CreateObject("ADODB.Recordset")
myRS2.Open mySQL2, conntemp, 3, 3
MyArray=myRS2.getrows()
myRS2.Close
Set myRS2 = nothing
conntemp.Close
Set conntemp = nothing
%>
<select name="MyType" size=1>
<option>-Choose One-</option>
<%
For Each i in MyArray
Response.Write "<option>"& i &"</option><BR />"
Next
%>
</select>
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/15/2004 14:22:53
So, is this part OK?
<%
For Each i in MyArray
Response.Write "<option>"& i &"</option><BR />"
Next
%>
Would there be a way write some code with the above that will allow the select box to show as selected the value that corresponds to that of the record pulled with: mySQL = "SELECT * FROM Results WHERE ID=4"
I tried something like:
<%
For Each i in MyArray
Response.Write "<option"
If i = MyType Then Response.write ("selected")
Response.write ">"& i & "</option><BR />"
Next
%>
What that did is exclude the value from the dropdown instead of showing it as selected. For example, if the Type of the record in question was "SUV", then SUV didn't appear as an option in the select box. Also, it errored out until I deleted the "End If".
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/15/2004 15:13:04
Believe it or not I actually understand most of the above after a quick read on the UBound function. However, can you explain a couple of things? First, not sure what the "2" is for in For iRowLoop = 0 to UBound(MyArray, 2).
Second, I don't understand the "0" in MyArray(0, iRowLoop)
Finally, from a few posts up,
less expensive cursor ???
Let's be honest here, I just saw "myRS.Open mySQL, conntemp, 3, 3" I have no clue what each of those 3s represent.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: I've put this off long enough - 4/15/2004 15:26:37
quote:
Second, I don't understand the "0" in MyArray(0, iRowLoop)
The 0 is identifying the first element of the "iRowLoop'th" row. Remember arrays are 0-based. It is very similar in referencing spreadshhet cells (at least in a 2D array):
myArray(columnNumber, rowNumber)
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: I've put this off long enough - 4/15/2004 15:28:44
quote:
less expensive cursor ???
Generally you want to try and use only "read-ahead" and "read-only" operations unless you absolutely need the other types of conns. Fewer requirements = fewer server resources.
At least I think that's what Spooky was referrring to.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: I've put this off long enough - 4/15/2004 18:51:49
quote:
You're using a 2-D array; columns and rows.
<Throbbing headache>
I am? I am only requesting all distinct values from a single column. Doesn't that make it one-dimensional? What in the world would a 3 or 4 dimensional array look like? What would be a real world example?
</throbbing headache>
< Message edited by betheball -- 4/15/2004 16:52:25 >
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
.