getting information from loops

[davesmith]

My problem - i am creating an RTF file through an ASP(i have done this by creating the setout in word, saving the file in rtf, copying this into my asp as a writefile(" blah blah&quot referencing to the bits i need to change with the data)
This works, but there is a bit where i have a table section (maximum of four lines) and i want to bring up to 4 records from a table through.

What i want to do - select all records (ie. last name is smith!!!
Goto first record
Set an incremental value (ie.record) = 1
declare name1, name2, name3, name4
put first person into name1 (ie. name&[record] = name)
if not last record increment (record + 1)
loop
so i will get in declared name1 - dave, name2 - bob etc.
Then reference these into my cells on the RTF file.
I know what i want to do but haven't got a clue how to do it (i'm an access programmer by nature)

If this is too messy/confusing, don't worry i have been struggling with it for days (it may be a simple fix, but i am losing hair, very quickly)

Thanks

Dave

 

[vasah20]

are you accessing a database, or an RTF file as your datastore?

if it's the Access DB, here's a quick solution:
If you need to know how to open a connection, see faq333-178

'this assumes con is an open connection

Dim rs, sql
set rs = server.createobject("adodb.recordset")
rs.open "SELECT * FROM tblName WHERE lastname = 'smith', con

while not rs.EOF
 'at this point you have all the information that you need,
 'so why store it into separate variables if the recordset
 'has the data?

 Response.write rs("firstname") 'print out the name value
wend
set rs = nothing

if I understand correctly, now you want to store it into the RTF file, which you've created in Word. You should be able to adapt your RTF file saving code to incorporate the RS, but if you can't post your RTF saving code and we'll see if we can adapt it.

good luck
leo

 

[Ovatvvon]

In the case that you need to scroll through a few different matches in the database (ex: firstname = john, but there's a john smith, john nelson, and john pearson, and you want to display them all...use the connection that vasah20 showed you, use a loop to scroll through all matches, example:


<table border=2>
Do while not rs.eof

Response.write(&quot;<tr><td>&quot; & rs(&quot;firstname&quot & &quot;</td></tr>&quot
rs.movenext
loop
</table>


Vasah20 is right though, you don't need to enter all the data into variables. All that is doing is adding a lot of unnecisary(sp?) work for you, and the server.

Hope this helps
-Ovatvvon

 

[davesmith]

basically, i have created a word document (ie. i don't have to learn all the different switches it does to create the page layout i require) i have then put general text in the cells i want to put my data into (ie. name1) and then i can reference all the information i need at the top, search for the general text (name1) and put the variable i have calculated above in the cell.

but with the second part of the rtf i need to reference several records in, but each field in each record needs a different variable (the rtf is not set out to take the info from each table and then move onto the next record, it takes a bit of info, adds it together then divides by a total of a record but then splits it into amounts dependant on the value into each record!!!! it was bad enough writing it in access!

so this is why i thought if i get all the raw data for the first user such as
name salary pension dob etc
and put these into variables such as name1, salary1, pen....., but doing this by having it setup as name & recordNo, for name1
Then when this is all collected, move onto the next record, and repeat.

The report is very complex and unfortunately has to be done through an rtf for our clients printing. I don't want to be refering to the tables each time i have a total this will take 4eva to download!!!

Any ideas, greatly appreciated!

Dave(TheHeatIsn'tHelpingMyMood)Smith

 

[vasah20]

If you need to do it in this way, I would suggest using an array. It seems that you know what the index of the array would be for each person (eg: dave gets name1, salary1, while bob gets name3, salary3, etc..), so you should use .GetRows to get the full recordset, and just specify the index that you need.

ex:
'again - assuming con is a connection
dim rs, con, sql, rsArr
sql = &quot;select name, salary, otherfield from tblName where name = 'smith'&quot;

set rs = server.createobject(&quot;adodb.recordset&quot
rs.open sql, con
if not rs.eof then 'make sure theres recordset data
rsArr = rs.GetRows
end if

'close and free the objects as early as you can,
'to increase performance
rs.close
con.close
set rs = nothing
set con = nothing


CONST name = 0 'notice these correspond
CONST salary = 1 'with their location in the sql
CONST otherfield = 2 'string, starting at 0

if isarray(rsArr)
'the array is 0 based, so
'to get bob's data, someindex = 1, where 1 would
'be the 2nd row returned. for dave: someindex = 0

Response.write rsArr(name,someindex)
else
Response.write &quot;no data returned&quot;
end if

not only does this give you just the raw data, there is also a significant increase in speed.

hope this helps
leo