Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post

    changing rs.field to records

    I have:
    Code:
    <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.

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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!
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    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/se...wheeldata1.asp
    The script I posted previously is where I'm stuck. I think I have to use the
    Code:
    Dim RowData
    RowData = rs.GetRows()
    But How? Does it replace "for each x in rs.Fields" or what?

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Unfortunately, I can't view the page. I am getting a

    Code:
    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:

    Code:
    <%
    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).
    Last edited by Daemonspyre; 06-21-2007 at 10:13 PM.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #5
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    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.

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    FYI - all the links you provided return the same error.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #7
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    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

  • #8
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Ah, working now, but boy is it slow...

    Will have more for you when I have the opportunity to look into it.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #9
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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!

    Code:
    <% 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)
    %>
    Last edited by Daemonspyre; 06-21-2007 at 09:44 PM. Reason: Cleaned up spacing issues when posted
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #10
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    hmmm i don't think that's what I'm looking for.
    I have:
    Code:
    <%
    		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.

  • #11
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    Ok let's use a simple example from w3schools.com and work on that:
    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
    
    '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?

  • #12
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Quote Originally Posted by gilgalbiblewhee View Post
    Ok let's use a simple example from w3schools.com and work on that:
    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
    
    '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.

    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

    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:

    Code:
    	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.

    Code:
    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.

    Code:
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #13
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    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:
    Code:
    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 />")

  • #14
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    I decided to use my head a little and switched the x and y:
    Code:
    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

  • #15
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    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>
    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 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
    						%>


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •