Change table name in recordset by passing new name through hyperlink (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Geraldo -> Change table name in recordset by passing new name through hyperlink (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



Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/27/2005 14:24:00)

The table is named "100", "1000"?

You could use a link such as :

yourpage.asp?t=100

Then, use :

set rs = conn.Execute("SELECT * FROM "&left(request.querystring("t"),4))



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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



Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/27/2005 15:56:17)

Sorry - Im confused
One query looks like :

set rs = conn.Execute("SELECT * FROM koi_active")

What should the new query look like?



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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



Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/27/2005 17:02:52)

Will the link to the "100" table exist on the same page as the results, or is it a different page linking to the results page above?



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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



Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/27/2005 17:24:27)

If its on the same page, then you need to check the state of that value,
On first visit, the code I gave you would be null (as there was no querystring)

Tablename = left(request.querystring("t"),4)
If Tablename&""="" then Tablename = "koi_active"

set rs = conn.Execute("SELECT * FROM "&Tablename )



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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




Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/28/2005 4:09:12)

Whats the full code you ended up using?



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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




Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/28/2005 14:10:15)

What happens if you do this? :

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

If you select "250" for example, is the select statement correct? (and they shouldnt really be numeric - its not a good naming method)



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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



Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/28/2005 16:53:00)

Can you humour me and change one tablename to "onehundred"?
I think youll find it will accept a text table name



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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



Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/29/2005 0:48:16)

You can remove that code above in blue that we added ;-)



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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;-)



Spooky -> RE: Change table name in recordset by passing new name through hyperlink (3/29/2005 14:58:42)

Looking quickly at your code, if "allrecords = 0" then, no records have been found.
Using that code would enable you to write something to your page.

eg :

If allrecords=0 then
response.write "No records"
Else
' show records
End if



Geraldo -> RE: Change table name in recordset by passing new name through hyperlink (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).



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1083984