|
| |
Change table name in recordset by passing new name through hyperlink
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
 |
Change table name in recordset by passing new name thro... - 3/27/2005 9:49:44
Dear friends,
finally I have made my page as I would like to have it.
With your help I solved issue like how to page database results, transform rows into columbs, etc.
You helped me a lot and I would like to thank you for it.
See the result: koishop.
Now only one thing I need to solve, but I am not sure if it can be done.
I would like to change table information in the recordset definition by passing information through clicking a hyperlink. By doing so I can put these hyperlinks in a drop down menu which will be installed as the next step.
Can this be done and if yes how?
I would like to change "FROM koi_active" in e.g. "FROM 100" or "FROM 1000" etc. in the recordset definition below.
set rs = conn.Execute("SELECT * FROM koi_active")
Thanks in advance for your help.
Regards
Geraldo
|
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/27/2005 15:38:59
quote:
yourpage.asp?t=100
Then, use :
set rs = conn.Execute("SELECT * FROM "&left(request.querystring("t"),4))
This results in an error messag:
Microsoft JET Database Engine error '80040e14'
Syntax error in FROM clause.
/koishop/test/koi_lst_paging.asp, line 19
Yes the queries have named like: 100, 1000 etc.
However I forgot to mention that the default table must remain: koi_active which should be replaced by 100, 1000 etc.
See the code below where this replace should take place twice:
<%@LANGUAGE=VBScript%>
<%
' Define variables
dim recordsonpage, requestrecords, offset, allrecords, hiddenrecords, showrecords, lastrecord, recordcounter, pagelist, pagelistcounter
' DB connection
dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("../../../database/aenc_vijvers.mdb") & ";" & _
"Persist Security Info=True"
Conn.Open(sConnection)
' records per page
recordsonpage = 12
' count all records
allrecords = 0
set rs = conn.Execute("SELECT * FROM koi_active")
do until rs.EOF
allrecords = allrecords + 1
rs.movenext
loop
' if offset is zero then the first page will be loaded
offset = request.querystring("offset")
if offset = 0 OR offset = "" then
requestrecords = 0
else
requestrecords = requestrecords + offset
end if
' opens database
set rs = conn.Execute("SELECT * FROM koi_active ORDER BY koi_ID")
' reads first records (offset) without showing them (can't find another solution!)
hiddenrecords = requestrecords
do until hiddenrecords = 0 OR rs.EOF
hiddenrecords = hiddenrecords - 1
rs.movenext
if rs.EOF then
lastrecord = 1
end if
loop
%>
Thanks in advance for your help
Geraldo
< Message edited by Geraldo -- 3/27/2005 15:45:00 >
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/27/2005 16:59:36
Spooky,
On opening the page both queries should look like ("SELECT * FROM koi_active"), but this query should change according to what hyperlink is clicked.
E.g. when hyperlink (according your example) yourpage.asp?t=100 is clicked this should read ("SELECT * FROM 100").
So the default state should change from "koi_active" to "100"or "1000" etc. based upon which hyperlink is clicked.
Regards
Geraldo
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/27/2005 17:09:24
Spooky it will be on the same page.
I would like to put in in the drop down menu that I will install somewhat later.
Thanks
Geraldo
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/28/2005 4:01:47
Spooky,
It works perfect for table "100", but it does not work for 250, 500, 1000, 2500 and 5000.
Please check this for yourself: koishop_rev.
The other tables do also exist, but why does it not work for these?
Thanks again for the help.
Regards
Geraldo
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/28/2005 6:22:40
Spooky,
see the code below (it's the full page).
<%@LANGUAGE=VBScript%>
<%
' Define variables
dim recordsonpage, requestrecords, offset, allrecords, hiddenrecords, showrecords, lastrecord, recordcounter, pagelist, pagelistcounter
' DB connection
dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("../../../database/aenc_vijvers.mdb") & ";" & _
"Persist Security Info=True"
Conn.Open(sConnection)
' records per page
recordsonpage = 12
' count all records
allrecords = 0
Tablename = left(request.querystring("t"),4)
If Tablename&""="" then Tablename = "koi_active"
set rs = conn.Execute("SELECT * FROM "&Tablename )
do until rs.EOF
allrecords = allrecords + 1
rs.movenext
loop
' if offset is zero then the first page will be loaded
offset = request.querystring("offset")
if offset = 0 OR offset = "" then
requestrecords = 0
else
requestrecords = requestrecords + offset
end if
' opens database
Tablename = left(request.querystring("t"),4)
If Tablename&""="" then Tablename = "koi_active"
set rs = conn.Execute("SELECT * FROM "&Tablename )
' reads first records (offset) without showing them (can't find another solution!)
hiddenrecords = requestrecords
do until hiddenrecords = 0 OR rs.EOF
hiddenrecords = hiddenrecords - 1
rs.movenext
if rs.EOF then
lastrecord = 1
end if
loop
%>
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<META content="index,follow" name="robots">
<META content="koi" name=keywords>
<META content="A&C vijvers en tuinen, de specialisten in vijver- en tuinaanleg, eigen selectie koi uit Japan." name=Description>
<title>Koishop A & C Vijvers en Tuinen</title>
<SCRIPT LANGUAGE="JavaScript">
<!--
/**
* Opens the viewing window with parameters
*/
function launch(url) {
remote = open(url, "", "width=520, height=480, toolbar=0, scrollbars=0, location=0, status=0, menubar=0, resizable=0");
}
// -->
</SCRIPT>
</head>
<body bgcolor="#00000">
<div align="center">
<center>
<div align="center">
<center>
<div align="center">
<center>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="800" id="AutoNumber1">
<tr>
<td width="175" bgcolor="#000080" height="175" align="center">
<img border="0" src="../../images/aenc-logo.gif"></td>
<td width="600" bgcolor="#000080" height="175">
<div align="right">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="600" id="AutoNumber8" bgcolor="#FFFF99" height="153" bordercolor="#FFFF99">
<tr>
<td width="100%">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber9">
<tr>
<td width="204">
<p align="center"><font size="2" face="Arial"><u>
Contactinformatie</u><br>
<br>
</font><font color="#FFFFFF" size="2" face="Arial">
<a href="../../index.htm">A & C Vijvers & Tuinen</a></font><font size="2" face="Arial"> <br>
Zuivelweg 2B<br>
5809 EA Leunen/Venray<br>
tel. 0478 - 58 56 02 <br>
fax 0478 - 58 04 54<br>
gsm 0655-168 068</font></td>
<td width="143" align="right">
<img border="0" src="../../reisverslag/images/dag2/Beni_Kikokuryu_63_cm_Aoki.jpg" width="72" height="153"><img border="0" src="../../reisverslag/images/dag2/Beni_Kikokuryu_47_cm_Aoki.jpg" width="71" height="153"></td>
<td width="253">
<img border="0" src="../../images/tuin.jpg" width="253" height="153"></td>
</tr>
</table>
</td>
</tr>
</table>
</div>
</td>
<td width="25" bgcolor="#000080" rowspan="4" height="1150"> </td>
</tr>
<tr>
<td width="175" bgcolor="#000080" height="975" rowspan="3" valign="top">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber3">
<tr>
<td width="100%" height="25">
<div align="center">
<center>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="153" id="AutoNumber7" bgcolor="#FFFF99" height="4" bordercolor="#FFFF99">
<tr>
<td width="100%" valign="top" height="4">
<p align="center"><b>
<font size="2" face="Arial" color="#000080">Switch to English</font></b></td>
</tr>
</table>
</center>
</div>
</td>
</tr>
</table>
<div align="center">
<center>
<table border="1" cellpadding="0" cellspacing="1" style="border-collapse: collapse" width="153" id="AutoNumber4" bgcolor="#FFFF99" bordercolor="#FFFF99">
<tr>
<td width="100%" align="center">
<FONT color=#660000 face="Arial" size="2">
<b>Japanreis<br>
(</b><a href="../../reisverslag/pages/japanreis_1.htm">reisverslag</a><b>)<br>
</b><br>
A&C is vanaf 19 maart tot 31 maart weer naar Japan vertrokken om
voor haar klanten weer een nieuwe
collectie hoogwaardige Koi samen te stellen,<br>
<a href="../../pages/dutch/n_news.htm">lees verder...</a></FONT></td>
</tr>
</table>
</center>
</div>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber5" height="10">
<tr>
<td width="100%" height="10">
<p align="center"></td>
</tr>
</table>
<div align="center">
<center>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="153" id="AutoNumber6" bgcolor="#FFFF99" bordercolor="#FFFF99">
<tr>
<td width="100%">
<p align="center">
<FONT color=#660000 face="Arial"
size=2><b>Wereld Klasse Koi<br>
bij A & C Vijvers en Tuinen in Leunen</b><br>
<br>
Op dit moment bevindt<br>
zich bij A&C de beste mannelijke koi ter wereld, <font size="2">
<a href="../../pages/dutch/n_news_2.htm">lees verder...</a></font></FONT></td>
</tr>
</table>
</center>
</div>
</td>
<td width="600" bgcolor="#000000" height="25">
<p align="center"><b><font color="#FFFF99" size="2" face="Arial">
<a href="koi_lst_paging.asp?t=100"><font color="#FFFFFF">100</font></a></font><font size="2" face="Arial" color="#FFFFFF">
</font><font color="#FFFF99" size="2" face="Arial">
<a href="koi_lst_paging.asp?t=250"><font color="#FFFFFF">250</font></a><font size="2" face="Arial" color="#FFFFFF">
</font><a href="koi_lst_paging.asp?t=500"><font color="#FFFFFF">500</font></a><font size="2" face="Arial" color="#FFFFFF">
</font><a href="koi_lst_paging.asp?t=1000"><font color="#FFFFFF">1000</font></a><font size="2" face="Arial" color="#FFFFFF">
</font><a href="koi_lst_paging.asp?t=2500"><font color="#FFFFFF">2500</font></a><font size="2" face="Arial" color="#FFFFFF">
</font><a href="koi_lst_paging.asp?t=5000"><font color="#FFFFFF">5000</font></a></font></b></td>
</tr>
<tr>
<td width="600" bgcolor="#FFFFFF" height="900" valign="top">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="600" id="AutoNumber1">
<%
' prints records in the table
showrecords = recordsonpage
recordcounter = requestrecords
do until showrecords = 0 OR rs.EOF
recordcounter = recordcounter + 1
%>
<%If x = 0 then %>
<tr>
<%end if%>
<%x=x+1%>
<td width="100%" valign="top">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="150" id="AutoNumber2" bgcolor="#FFFFFF">
<tr>
<td width="100%" align="center" height="2"> </td>
</tr>
<tr>
<td width="100%" align="center"><font face="Arial" size="2"><b>Koi ID: <%= rs("koi_ID")%></b></font></td>
</tr>
<tr>
<td width="100%" align="center"><a href="javascript:launch('koi_popup.asp?koi_ID=<%= rs("koi_ID")%>')"><img border="0" src=<%= rs("koi_thumbnail")%></a></td>
</tr>
<tr>
<td width="100%" align="center"><b><font face="Arial" size="2">afm. <%= rs("koi_size")%> cm</font></b></td>
</tr>
<tr>
<td width="100%" align="center"><font face="Arial" size="2"><b><%= rs("koi_type")%></b></font> </td>
</tr>
<tr>
<td width="100%" align="center"><b>
<font face="Arial" color="#FF0000" size="2">Prijs: </font>
<font color="#FF0000" size="2">€</font><font face="Arial" color="#FF0000" size="2">
<%
If uCase(rs("koi_sold")) <> "SOLD" then
response.write rs("koi_price")
else
response.write rs("koi_sold")
End if
%>
</font></b></td>
</tr>
<tr>
<td width="100%" align="center" height="2"> </td>
</tr>
</table>
<% If x < 4 then %>
</td>
<%Else
x = 0%>
</td></tr>
<%end if%>
<%
showrecords = showrecords - 1
rs.movenext
if rs.EOF then
lastrecord = 1
end if
loop
%>
</table>
</td>
</tr>
<tr>
<td width="600" bgcolor="#000080" height="50">
<table cellspacing="0" cellpadding="4" border="0" width="600" style="border-collapse: collapse">
<tr>
<td align="center" width="50%"><% if requestrecords <> 0 then %><p align="right"><font face="Arial"><b><a href="koi_lst_paging.asp?offset=<% = requestrecords - recordsonpage %>">
<font size="2" color="#FFFF00">Vorige Pagina</font></a><font size="2" color="#FFFFFF"><% else %>Prev Page<% end if %></font></b></font></td>
<td align="center" width="50%"><% if lastrecord <> 1 then %><b><font color="#FFFFFF" size="2" face="Arial">
</font></b>
<p align="left"><font face="Arial"><b> <a href="koi_lst_paging.asp?offset=<% = requestrecords + recordsonpage %>">
<font size="2" color="#FFFF00">Volgende Pagina</font></a><font size="2" color="#FFFFFF"><% else %>Next Page<% end if %></font></b></font></td>
</tr>
<tr>
<td colspan="2" align="center"><font face="Arial"><b>
<font size="2" color="#FFFFFF">Pagina Lijst:</font><font size="2" color="#FFFF00">
<%
pagelist = 0
pagelistcounter = 0
do until pagelist > allrecords
pagelistcounter = pagelistcounter + 1
%> </font><font size="2">
<a href="koi_lst_paging.asp?offset=<% = pagelist %>"><font color="#FFFF00"><% = pagelistcounter %></font></a></font><font size="2" color="#FFFF00">
<%
pagelist = pagelist + recordsonpage
loop
%> </font></b></font>
</td>
</tr>
</table>
</td>
</tr>
</table>
</center>
</div>
<p>
<div align="center">
<center>
<p></p>
</center>
</div>
<%
' Closes connection
rs.close
Conn.close
%></body></html>
There a lot of html-table definition etc. but the start code is at the top and other parts of the code you can find at several places in the page code;-)
Thanks again for the quick response.
Regards
Geraldo
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/28/2005 16:04:13
Spooky,
this results on the page in the text: SELECT * FROM 100 (250, 500 etc.)
So it seams that it is not written in the sql statement.
Thanks
Geraldo
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/29/2005 0:29:32
Spooky,
I humoured you by changing the table namen in ka_100, ka_250 etc.
The resulting page is showing now: SELECT * FROM ka_1 (ka_2, ka_5 etc.) .
Check this page - koishop_test
So it seams that the string is just written to the page and not into the query string.
Hope this helps
Regards
Geraldo
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/29/2005 11:20:19
Hi Spooky,
Tablename = left(request.querystring("t"),4)
If Tablename&""="" then Tablename = "koi_active"
If Tablename <> "koi_active" then
response.write "SELECT * FROM "&Tablename
response.end
end if set rs = conn.Execute("SELECT * FROM "&Tablename )
do until rs.EOF
I took away the "blue" code and the result was that the page still did not load.
But I saw that only the first 4 characters of the string where posted into the query string. So I became adventures and changed the 4 in the topline (see above) into 8 and HOPLA it worked.
1000 Thanks for your help.
Regards
Geraldo
So the next thing I need to solve is if the result on the page is zero records the text "momentary there is no koi of that price category available" should be printed on the page.
A tip how to solve that is wishfull for me, at other hand I should first try to solve it by myself;-)
| |
|
|
|
Geraldo
Posts: 57
From: Eindhoven
Status: offline
|
RE: Change table name in recordset by passing new name ... - 3/29/2005 15:24:47
Spooky,
I really need to say "your the man", unless you are from the opposite sex;-)
This last tip you gave me worked flawless, you are looking at a very happy person.
See koishop test and try 5000.
I also had a try to send the information by using a pull down menu and also that worked.
I need to do some detailing in the menu and implement it on the final page. (I am using DHTML-menu builder for that purpose).
Further with the help from this forum I am starting a liitle bit to understand asp, but I am hoping that my provider will offer the FP way of doing things.
I told him that the rootweb needs to be an application web in order to enable me to use the FP database connecting and all its advantages (at least for me). He responded to me that he will move to an W2003 server within 3 weeks and have will enable me request.
So thanks again
Regards Geraldo
PS I could post the final code for others to look at it for ideas, if this something what is normally done (please some information on this).
< Message edited by Geraldo -- 3/29/2005 15:54:47 >
| |
|
 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 
k¥ 