...

View Full Version : changing rs.field to records



gilgalbiblewhee
06-21-2007, 03:31 AM
I have:

<select id="sel1" name="sel1" onchange="" size="20">
<%do until rs.EOF%>

<%
i = 0
for each x in rs.Fields
if i = 0 then
i = 1
else
%>
<option href="showverse.asp?<%Response.Write(x.value)%>">
<%
'OPTION EXPLICIT
dim txt,a
If IsNull(x.value) then
response.write("")
else
txt= x.value
a=Split(txt, "&")
'opening the bible table
'Opening Database connection
Set Conn2 = Server.CreateObject("ADODB.Connection")
Conn2.Open DSNName

set RS2 = Server.CreateObject("ADODB.recordset")

'This SQL statement creates a list of books
SQL2 = "Select * from bible"

sql2 = sql2 & " where " & a(0) & " AND " & a(1)
rs2.Open sql2,conn2, 1
response.Write(rs2("book_title") & " " & rs2("chapter") )
'response.Write(sql2)

rs2.close
conn2.close
'response.write(a(0) & " ")
'response.write(a(1))
end if
%>
</option>
<%
' process the rest of the loop except the one ignored above
end if
next
rs.MoveNext%>

<%loop
rs.close
conn.close%>
</select>

At first I had a table where rs.Fields was suitable but I want to change to select dropdowns. I'm trying to think how I'm going to be able to make the script populate the 22 dropdowns (according to the 22 fields) instead of populating record-by-record.

Daemonspyre
06-21-2007, 01:36 PM
Just so I am clear on what you want to do (please correct me if I am interpreting what you want wrong):

1) You wish to have 22 SELECT fields on your form, 1 for every book in the bible.

2) Of those 22 SELECT fields, you want each to hold the chapters for that particular book.

3) When you select a particular chapter, it posts to another page [or section ] which displays the verses in that chapter.

Am I on the right track?

If so, then it's just a matter of altering your loop so that there are 2 loops. First loop builds the 22 SELECTs. In each SELECT, there is a second loop which is basically your code above (with a couple of tweaks).

If I am wrong, please clarify a little more.

In either case, I can help you write that if you would like.

HTH!

gilgalbiblewhee
06-21-2007, 04:15 PM
Well yes. It has to do with the bible but the 22 select fields is another table but in the same database. This table has URL extensions to the bible table, whatever comes after ".asp?" . Ok here's what I had laid out in table format:
http://i.domaindlx.com/wheelofgod/search/cat/list.asp
( If that doesn't work I have one set in the root: http://i.domaindlx.com/wheelofgod/list.asp )

But here's what I have so far in Select field:
http://i.domaindlx.com/wheelofgod/search/cat/biblewheeldata1.asp
The script I posted previously is where I'm stuck. I think I have to use the

Dim RowData
RowData = rs.GetRows()


But How? Does it replace "for each x in rs.Fields" or what?

Daemonspyre
06-21-2007, 05:18 PM
Unfortunately, I can't view the page. I am getting a



General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x15f0 Thread 0x178c DBC 0x8f7e48c Jet'.


error.

However, to answer your question about the rs.GetRows():

Here's some example code to help solve this:



<%
rs.open "SELECT Book FROM tblBible;", conn
arrBooks = rs.GetRows() 'GetRows puts your Recordset into a 2-dimensional array
iRowNumber = ubound(arrBooks,2) 'Set the UpperBound (UBound)
'
rs.close
'
for iCounter = 0 to iRowNumber 'Here is the FOR EACH x IN RS.FIELDS
response.write("SOME HTML CODE" & arrBooks(0,iCounter))
next
%>



So, as you can see commented above, rs.GetRows() takes your recordset data and stores it into an array.

Then you basically use your array values (like you do with your split) and use them as arrBooks(Field_in_Row,Row_In_Database).

gilgalbiblewhee
06-21-2007, 05:29 PM
Ok I'll look at it when I get back. It's giving that error ( temporary volatile... ) sometimes it works sometimes it doesn't but check the one placed in the root folder. That should work.

Daemonspyre
06-21-2007, 05:33 PM
FYI - all the links you provided return the same error.

gilgalbiblewhee
06-21-2007, 07:52 PM
Are you sure this one gives error? Because it works for me:
http://i.domaindlx.com/wheelofgod/list.asp

Also try:
http://n.1asphost.com/wheelofgod/list.asp

Daemonspyre
06-21-2007, 08:00 PM
Ah, working now, but boy is it slow...

Will have more for you when I have the opportunity to look into it.

Daemonspyre
06-21-2007, 08:42 PM
OK... I can't do too much to help you here since the table is daunting, and without at least a CREATE TABLE statement (or 1 row of example data) from your table, it's hard to do exactly what you want.

However, here's a starting point.

Notes:

1) I don't know your table structure so I used some default SQL statements. You will need to do some playing with it, but you will catch onto it quickly.

2) You can't do OPTION HREF, but you can do OPTION VALUE ONCLICK. See the code. =)

I have made comments throughout that should help you in doing what you want to do.

Let me know if you have any other questions!



<% rs.open "SELECT ID FROM tblBibleBook1", conn 'Get All Books
arrBooks = rs.GetRows() 'Create Array for all books
iRowMax = ubound(arrBooks,2) 'Set UBOUND LIMIT
rs.close
'
for x = 0 to iRowMax 'Loop through values
response.write("<select id=""" & arrBooks(0,x) & """ size=""1"" name=""" & arrBooks(0,x) & """>" & vbCrLf) 'this creates the spX selects
'
rs.open "SELECT * FROM tblBibleBooks2 WHERE BibleBook1 = " & arrBooks(0,x), conn 'Get all the chapters based on books ID
arrChapters = rs.GetRows() 'Create Array for all chapters
iChapMax = ubound(arrChapters,2) 'Set UBOUND Limit
rs.close
'
for y = 0 to iChapMax 'Loop through values
response.write(" <option value=""" & arrChapters(0,y) & """ onclick=""document.location.href='showVerse.asp?" & arrChapters(2,y) & """>" & arrChapters(1,y) & "</option>" & vbCrLf)
'
'Write out Option, Value=ID, Name=arrChapter(0,y) WHERE Name = Genesis 1
'arrChapters(2,y) = Book=1&Chapter=1...
'Based on your table, you may need to do
'Book= arrBooks(0,x) & Chapter= arrChapters(2,x)
'
next
next
'
Erase arrBooks 'Erase Array (cleanup)
Erase arrChapters 'Erase Array (cleanup)
%>

gilgalbiblewhee
06-21-2007, 11:22 PM
hmmm i don't think that's what I'm looking for.
I have:

<%
DSNName = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
DSNName = DSNName & Server.MapPath("../../kjv.mdb")
DSNName = DSNName & ";PWD=" & "mypass"


'Opening Database connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSNName

set RS = Server.CreateObject("ADODB.recordset")

'This SQL statement creates a list of spokes
SQL1 = "Select * from inssp" 'url extensions table

rs.Open sql1,conn, 1
%>
<%
i = 0
for each x in rs.Fields
if i = 0 then
i = 1
else
response.write("<span id='" & x.name &"'>" & x.name & "</span>")
' process the rest of the loop except the one ignored above
end if
next%>
<div id="divform">
<form name="frm" id="frm">
<select id="sel1" name="sel1" onchange="" size="20">
<%do until rs.EOF%>

<%

i = 0
for each x in rs.Fields
if i = 0 then
i = 1
else
%>
<option href="showverse.asp?<%Response.Write(x.value)%>">
<%
'OPTION EXPLICIT
dim txt,a
If IsNull(x.value) then
response.write("")
else
txt= x.value
a=Split(txt, "&")
'opening the bible table used to simply fetch the name of the book
'Opening Database connection
Set Conn2 = Server.CreateObject("ADODB.Connection")
Conn2.Open DSNName

set RS2 = Server.CreateObject("ADODB.recordset")

'This SQL statement creates a list of books
SQL2 = "Select * from bible"

sql2 = sql2 & " where " & a(0) & " AND " & a(1) 'for your info after splitting the url extension a(0) is "book=number" and a(1) is "chapter=number".
rs2.Open sql2,conn2, 1
response.Write(rs2("book_title") & " " & rs2("chapter") ) ' book_title is the name of the book like genesis, exodus and chapter is the chapter
'response.Write(sql2)

rs2.close
conn2.close
'response.write(a(0) & " ")
'response.write(a(1))
end if
%>
</option>
<%
' process the rest of the loop except the one ignored above
end if
next
rs.MoveNext%>

<%loop
rs.close
conn.close
conn = nothing
%>
except that the rs.Fields is not helpful in this (otherwise everything else runs smoothly). I need a populating by columns.

gilgalbiblewhee
06-22-2007, 05:33 AM
Ok let's use a simple example from w3schools.com and work on that:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("../../kjv.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from inssp", conn

'The first number indicates how many records to copy
'The second number indicates what recordnumber to start on
p=rs.GetRows(8,0)
rs.close
conn.close


'This example returns the value of the first
'column in the first two records
response.write(p(1,0))
response.write("<br />")
response.write(p(1,1))
response.write("<br />")
response.write(p(1,2))
response.write("<br />")
response.write(p(1,3))
response.write("<br />")
response.write(p(1,4))
response.write("<br />")
response.write(p(1,5))
response.write("<br />")
response.write(p(1,6))
response.write("<br />")
response.write(p(1,7))
response.write("<br />")

%>

How do you make this ubound? With a for loop?

Daemonspyre
06-22-2007, 01:47 PM
Ok let's use a simple example from w3schools.com and work on that:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("../../kjv.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from inssp", conn

'The first number indicates how many records to copy
'The second number indicates what recordnumber to start on
p=rs.GetRows(8,0)
rs.close
conn.close


'This example returns the value of the first
'column in the first two records
response.write(p(1,0))
response.write("<br />")
response.write(p(1,1))
response.write("<br />")
response.write(p(1,2))
response.write("<br />")
response.write(p(1,3))
response.write("<br />")
response.write(p(1,4))
response.write("<br />")
response.write(p(1,5))
response.write("<br />")
response.write(p(1,6))
response.write("<br />")
response.write(p(1,7))
response.write("<br />")

%>

How do you make this ubound? With a for loop?

OK, OK... So, I may have been slightly overzealous in what I was trying to show you.

In using your example above, with changes, I will show you how to do that with just 2 loops. Comments are in the code.



<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("../../kjv.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from inssp", conn
arrayR = rs.GetRows() 'This has been altered to use an ARRAY. Because we are not putting limits on it (i.e. (8,0), we have made it dynamic AND 2-dimensional
maxC = rs.Fields.Count 'This line creates variable maxC, sets it to max fields in database
maxR = UBOUND(arrayR,2) 'This line creates variable called maxP, defines it as the upper limit of our array.
rs.close
conn.close

For x=0 to maxR 'Loop through the ARRAY ROWS starting at 0 to the upper limit of our array
For y=0 to maxC-1 'Loop through the ARRAY COLUMNS starting at 0 to MAX COLUMNs minus 1 (since we are starting at 0)
response.write(arrayR(y,x) & "<br />") 'Here, instead of p(1,0), we want to use the ARRAY. Also, we are combining the 2 response.write lines together
Next
Next
'
response.flush 'Flush Response buffer to screen
'
Erase arrayR
Set rs = nothing
Set conn = nothing
%>


Now for the line-by-line... Explanation is BELOW CODE


set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("../../kjv.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from inssp", conn

This is your code -- no need for explanation:



arrayR = rs.GetRows()
maxC = rs.Fields.Count
maxR = UBOUND(arrayR,2)


Now, the fun part:

arrayR is the variable we are using for our 2-D ARRAY. 2D arrays are very similar to databases: you have a field (column) and a row. They can be as big as you need them to be. rs.GetRows() is an ADO function used to store ADO Recordsets in 2D arrays. Arrays are less work on the database server, and performance is better since they are in memory.

maxC is the variable used to Count the number of Fields in our recordset.

maxR is the variable used to set the Upper Limit (UBOUND) of our recordset. The "2" is the dimension of the limit (2-D).

I don't think that I need to say what rs.close and conn.close do.



For x=0 to maxR
For y=0 to maxC-1
response.write(arrayR(y,x) & "<br />")
Next
Next


Here are our 2 loops:

Loop 1 goes through all the RECORDS in the Array. You always want to start at 0 (since ASP is 0-based).

Loop 2 goes through all the FIELDS in the Array. The Second Loop does something a little different. Since maxC counted all the fields in our recordset, it started at 1, not 0. So, we have to put a (minus 1) on the maxC, otherwise we will get a "Subscript out of range" error.

It then writes out arrayR(0,0) [First record, first field]...arrayR(1,0) [Second record, first field]......arrayR(m,n), where m = max records and n = max fields in your database.




response.flush
'
Erase arrayR
Set rs = nothing
Set conn = nothing


This is just some clean-up work.

Response.Flush pushes the response buffer to the screen.

Erase arrayR tells ASP to erase all the data in arrayR and destroy the array.
Set ... = nothing destroys those Objects.

All are necessary as they release memory back to the server.

***************************************************


Does that help explain it a little better? If not, please do not hesitate to ask.

Since you posted your code, I will do some manipulation and get back to you on setting up array based loops for what you want.

gilgalbiblewhee
06-22-2007, 03:48 PM
But when I test it's giving field by field instead of record by record.

If one record has ( names of fields ) sp1, sp2, sp3, ...up to sp22. I want the search to be populating sp1 (all), then sp2 (all) ...

Notice in my example:

response.write(p(1,0))
response.write("<br />")
response.write(p(1,1))
response.write("<br />")
response.write(p(1,2))
response.write("<br />")
response.write(p(1,3))
response.write("<br />")
response.write(p(1,4))
response.write("<br />")
response.write(p(1,5))
response.write("<br />")
response.write(p(1,6))
response.write("<br />")
response.write(p(1,7))
response.write("<br />")

gilgalbiblewhee
06-22-2007, 04:15 PM
I decided to use my head a little and switched the x and y:

For y=0 to maxR 'Loop through the ARRAY ROWS starting at 0 to the upper limit of our array
For x=0 to maxC-1

gilgalbiblewhee
06-23-2007, 04:20 AM
For some reason the code is making an extra select field than the number of fields available in the database table and it's giving an error:


<span class='spokes' id='spoke23'>Spoke 23<br />
<select class='selspokes' id='sp23'> <font face="Arial" size=2>

<p>Microsoft VBScript runtime </font> <font face="Arial" size=2>error '800a0009'</font>
<p>
<font face="Arial" size=2>Subscript out of range: '23'</font>
<p>
<font face="Arial" size=2>/wheelofgod/search/cat/biblewheeldata1.asp</font><font face="Arial" size=2>, line 119</font>


<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("../../kjv.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from inssp", conn
arrayR = rs.GetRows() 'This has been altered to use an ARRAY. Because we are not putting limits on it (i.e. (8,0), we have made it dynamic AND 2-dimensional
maxC = rs.Fields.Count 'This line creates variable maxC, sets it to max fields in database
maxR = UBOUND(arrayR,2) 'This line creates variable called maxP, defines it as the upper limit of our array.
rs.close
conn.close

For y=1 to maxR 'Loop through the ARRAY ROWS starting at 0 to the upper limit of our array
Response.write("<span class='spokes' id='spoke" & y & "'>Spoke " & y & "<br />")
Response.write("<select class='selspokes' id='sp" & y & "'>")
For x=0 to maxC 'Loop through the ARRAY COLUMNS starting at 0 to MAX COLUMNs minus 1 (since we are starting at 0)


'OPTION EXPLICIT
dim txt,a
If IsNull(arrayR(y,x)) then
response.write("")
else
response.write("<option href='showverse.asp?" & arrayR(y,x) & "'>")
txt= arrayR(y,x) 'x.value
a=Split(txt, "&")
'response.write(a(0) & " ")
'response.write(a(1))

'opening the bible table
'Opening Database connection
set conn2=Server.CreateObject("ADODB.Connection")
conn2.Provider="Microsoft.Jet.OLEDB.4.0"
conn2.Open(Server.Mappath("../../kjv.mdb"))
set rs2 = Server.CreateObject("ADODB.recordset")


'This SQL statement creates a list of books
SQL2 = "Select * from bible"

sql2 = sql2 & " where " & a(0) & " AND " & a(1)
rs2.Open sql2,conn2, 1
response.Write(rs2("book_title") & " " & rs2("chapter") )
'response.Write(sql2)

rs2.close
conn2.close
response.Write("</option>") 'Here, instead of p(1,0), we want to use the ARRAY. Also, we are combining the 2 response.write lines together
end if



Next
Response.write("</select></span><br />")
Next

'
response.flush 'Flush Response buffer to screen
'
Erase arrayR
Set rs = nothing
Set conn = nothing
%>

Daemonspyre
06-25-2007, 01:15 PM
Change this line:


For x=0 to maxC

to


For x=0 to maxC-1

Remember my comment about ASP starting at 0, but rs.Fields.Count starting at 1?


Loop 2 goes through all the FIELDS in the Array. The Second Loop does something a little different. Since maxC counted all the fields in our recordset, it started at 1, not 0. So, we have to put a (minus 1) on the maxC, otherwise we will get a "Subscript out of range" error.

gilgalbiblewhee
06-25-2007, 07:10 PM
when I got the script from you the first time I tested it but it wasn't loading. So I switched the x and the y in:

For y=1 to maxR-1 'Loop through the ARRAY ROWS starting at 0 to the upper limit of our array

Response.write("<span class='spokes' id='spoke" & y & "'>Spoke " & y & "<br />")
Response.write("<select class='selspokes' id='sp" & y & "'>")
For x=0 to maxC 'Loop through the ARRAY COLUMNS starting at 0 to MAX COLUMNs minus 1 (since we are starting at 0)
And it worked except that the loop continues and gets stuck on an empty field halfway done as you see my previous posting.

Daemonspyre
06-25-2007, 07:33 PM
Ah... Here's the mistake:



For y=1 to maxR '<<<<< Don't do a maxR-1. That means that you want all but the last record in your database.

Response.write("<span class='spokes' id='spoke" & y & "'>Spoke " & y & "<br />")
Response.write("<select class='selspokes' id='sp" & y & "'>")
For x=0 to maxC-1 '<<<<< here's where you want the (-)1 all

gilgalbiblewhee
06-25-2007, 07:46 PM
I got:

<font face="Arial" size=2>

<p>Microsoft JET Database Engine</font> <font face="Arial" size=2>error '80004005'</font>
<p>
<font face="Arial" size=2>System resource exceeded.</font>
<p>
<font face="Arial" size=2>/wheelofgod/search/cat/biblewheeldata1.asp</font><font face="Arial" size=2>, line 145</font>

gilgalbiblewhee
06-25-2007, 07:49 PM
oh I didn't upload it but after uploding I got:

Microsoft JET Database Engine error '80004005'

System resource exceeded.

/wheelofgod/search/cat/biblewheeldata1.asp, line 108

Daemonspyre
06-25-2007, 07:53 PM
That's a connection error. Access can't handle the number of connections that are being opened.

Here's some more information on that...

http://www.thescripts.com/forum/thread52976.html

Here's one final question --

Would you be willing to zip and email/IM/PM your table(s) and that 1 page so that I can write (and test) what you are trying to accomplish? (Destruction of all db materials would be guaranteed upon thread closure.)

gilgalbiblewhee
06-25-2007, 07:55 PM
I tried on my other site and it gives the same error that it starts loading the scripting for the empty or non-existent field but gets stuck there. because the split function cannot work on the empty field.

Daemonspyre
06-25-2007, 08:05 PM
Simple solution --- do an ON ERROR RESUME NEXT. It won't fix the problem, but it will get your site up.

gilgalbiblewhee
06-25-2007, 08:24 PM
ok. Here's what I have so far:
http://i.domaindlx.com/wheelofgod/search/cat/biblewheeldata1.asp

Daemonspyre
06-25-2007, 08:38 PM
What's in sp23 (the 23rd record)? Anything?

What about 24, 25, 26, and 27?

If they are all empty (or, by your code, if the 'x.value' is empty), then the simplest solution is to change your SQL statement to include a WHERE statement.

SELECT * FROM inssp WHERE FIELD1* IS NOT NULL

(* - because I don't know your field names, whatever your field's name is should go here.)

This way, you are not pulling empty data and your ASP errors should magically disappear.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum