PDA

View Full Version : response.redirect with data from a database

colnixon
06-05-2009, 10:57 AM
Hi,

I have a form on my home page which asks the user to input a location. Once submitted the details are passed on to a page called redirect1.asp where results are filtered based on the field "location"

Only 1 result will ever be returned for each search, I need to read in data from this record for a field called "url" and then do a:

response.direct url

the "url" field contains file names so the appropriate page can be loaded for the appropriate location.

example: if customer searches for "location" = luton then data in "url" field for that record = lutonchiropractors.htm
so the customer would be redirected to www.mydomain.co.uk/lutonchiropractors.htm

I use frontpage database results wizards and an example of my code can been seen below without any attempt at a reditect, any help will be much appreitiated.

Many Thanks

<body>

<table width="100%" border="0" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<thead>
<tr>
<td><b>ID</b></td>
<td><b>Location</b></td>
<td><b>url</b></td>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Location,url" s-columntypes="3,202,202" s-dataconnection="connection1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="ID" s-menuvalue="ID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="ID,Location,url" s-criteria s-order s-sql="SELECT * FROM Pages WHERE (url = '::url::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="url=" s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region.</font></td></tr>" startspan b-WasTableFormat="TRUE" --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Pages WHERE (url = '::url::')"
fp_sDefault="url="
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="connection1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan --><tr>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Location,url" s-column="ID" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>ID<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"ID")%><!--webbot bot="DatabaseResultColumn" endspan --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Location,url" s-column="Location" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>Location<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"Location")%><!--webbot bot="DatabaseResultColumn" endspan --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Location,url" s-column="url" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>url<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"url")%><!--webbot bot="DatabaseResultColumn" endspan --></td>
</tr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan --></tbody>
</table>

</body>
Old Pedant
06-05-2009, 08:56 PM
No idea how much idiotic FP junk will interfere with this, but it's really trivial if you avoid FP.

You just need to do *EXACTLY* what you stated:

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "...your connection string..."

loc = Trim(Request("Location"))
SQL = "SELECT url FROM Pages WHERE location LIKE '%" & Replace(loc,"'","''") & "%'"
Set RS = conn.Execute(SQL)
If RS.EOF Then
Response.Write "Location not found"
url = ""
Else
url = RS("url")
End If
RS.Close
Conn.Close
If url <> "" Then Response.Redirect url
%>


I can't see any reason for all that FP webbot stuff for this problem, since you do NOT want to actually show anything on the page.