p2p.wrox.com: Archives - access

access_asp thread: Getting Information from the db using links on a dynamic page

Message #1 by "Kevin Johnson" <kevinjohnsong@h...> on Tue, 12 Mar 2002 14:40:30

It would be great if sombody would be able to help me with this as i am 

really stuck.

I have a search page that retrieves data from my database and this 

generates a dynamic page with all the information searched for. But i need 

to put links on the page for each individual piece of data to get more 

information from the database, any ideas?





This is the ASP code behind my search page:



<%@ Language=VBScript %>

<% Response.Buffer = True %>





<HTML>

<HEAD>

<TITLE>Search Job Categories</TITLE>

</HEAD>

<BODY>

<% 



	Dim objRS

        

Dim jobcat

	jobcat = Request.Form("select")



	Set MyConn=Server.CreateObject("ADODB.Connection")

	Set objRS = Server.CreateObject("ADODB.Recordset")



	MyConn.Open "testdatabase"





	SQL = "SELECT Job_ID, Job_Category, Position_Available, Job_Type, 

Job_Location, SubmitDate FROM Jobs " & _

  	"WHERE Job_Category LIKE '" & jobcat & "'"

	



	Set objRS = MyConn.Execute(SQL)

	

	If objRS.BOF and objRS.EOF THEN

	Response.Redirect "http://Student-

iis/star_recruitment/failedsearch.htm"

	



	ELSE

	objRS.MoveFirst

%>

<p>&nbsp;</p>

<table width="764" border="1" cellspacing="5" cellpadding="5" 

align="center" bordercolor="#FFFFFF">

  <tr> 

    <td width="88" valign="top" height="50" bordercolor="#FFFFFF"> 

      <div align="center"><font color="#990066"><b>Job 

Category</b></font></div>

    </td>

    <td width="37" valign="top" bordercolor="#FFFFFF"> 

      <div align="center"><img src="file://///Student-

iis/star_recruitment/Star_3.jpg" width="24" height="22"></div>

    </td>

    <td width="89" valign="top" bordercolor="#FFFFFF"> 

      <div align="center"><font color="#990066"><b>Position 

Available</b></font></div>

    </td>

    <td width="38" valign="top" bordercolor="#FFFFFF"><img 

src="file://///Student-iis/star_recruitment/Star_3.jpg" width="24" 

height="22"></td>

    <td width="88" valign="top"> 

      <div align="center"><font color="#990066"><b>Job 

Location</b></font></div>

    </td>

    <td width="38" valign="top" bordercolor="#FFFFFF"><img 

src="file://///Student-iis/star_recruitment/Star_3.jpg" width="24" 

height="22"></td>

    <td width="88" valign="top"> 

      <div align="center"><b><font color="#990066">Job 

Type</font></b></div>

    </td>

    <td width="50" valign="top" bordercolor="#FFFFFF"><img 

src="file://///Student-iis/star_recruitment/Star_3.jpg" width="24" 

height="22"></td>

    <td width="88" valign="top"> 

      <div align="center"><b><font color="#990066">Date 

Submitted</font></b></div>

    </td>

  </tr>

  <%

	Do While Not objRS.EOF



  %>

  <tr> 

    <td height="30" valign="top" bordercolor="#FFFFFF" width="88"><%=objRS

("Job_Category")%> 

    </td>

    <td width="37"></td>

    <td valign="top" width="89" bordercolor="#FFFFFF"><%=objRS

("Position_Available")%> 

    </td>

    <td width="38"></td>

    <td valign="top" width="88"><%=objRS("Job_Location")%> </td>

    <td width="38"></td>

    <td valign="top" width="88"><%=objRS("Job_Type")%> </td>

    <td width="50"></td>

    <td valign="top" width="88"><%=objRS("SubmitDate")%> </td>

  </tr>

  <%

			

	objRS.MoveNext

	Loop

	

%>

</table>

<%

	END IF

	MyConn.Close

	Set MyConn = Nothing

	Set objRS = Nothing

%>

</BODY>

</HTML>

Message #2 by "Ken Schaefer" <ken@a...> on Wed, 13 Mar 2002 12:37:55 +1100

Kevin:



<a href="details.asp?ID=<%=objRS("ID")%>">More Information</a>



?



Also, why do you write out the following:

: <HTML>

: <HEAD>

: <TITLE>Search Job Categories</TITLE>

: </HEAD>

: <BODY>

when you might be doing a Response.Redirect to another page shortly after?

Open your recordset *first*, test to see if there are any records. If there

are no records then Response Redirect.

If there are records, then start to write out the HTML



Lastly, if you do a Set, then do a Set = Nothing

If you .Open something, then you .Close it.



Since Response.Redirect stops processing of your page you need to do:



Set objRS = Nothing

objConn.Close

Set objConn = Nothing



before you Response.Redirect()



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Kevin Johnson" <kevinjohnsong@h...>

Subject: [access_asp] Getting Information from the db using links on a

dynamic page





: It would be great if sombody would be able to help me with this as i am

: really stuck.

: I have a search page that retrieves data from my database and this

: generates a dynamic page with all the information searched for. But i need

: to put links on the page for each individual piece of data to get more

: information from the database, any ideas?

:

:

: This is the ASP code behind my search page:

:

: <%@ Language=VBScript %>

: <% Response.Buffer = True %>

:

:

: <HTML>

: <HEAD>

: <TITLE>Search Job Categories</TITLE>

: </HEAD>

: <BODY>

: <%

:

: Dim objRS

:

: Dim jobcat

: jobcat = Request.Form("select")

:

: Set MyConn=Server.CreateObject("ADODB.Connection")

: Set objRS = Server.CreateObject("ADODB.Recordset")

:

: MyConn.Open "testdatabase"

:

:

: SQL = "SELECT Job_ID, Job_Category, Position_Available, Job_Type,

: Job_Location, SubmitDate FROM Jobs " & _

:   "WHERE Job_Category LIKE '" & jobcat & "'"

:

:

: Set objRS = MyConn.Execute(SQL)

:

: If objRS.BOF and objRS.EOF THEN

: Response.Redirect "http://Student-

: iis/star_recruitment/failedsearch.htm"

:

:

: ELSE

: objRS.MoveFirst

: %>

: <p>&nbsp;</p>

: <table width="764" border="1" cellspacing="5" cellpadding="5"

: align="center" bordercolor="#FFFFFF">

:   <tr>

:     <td width="88" valign="top" height="50" bordercolor="#FFFFFF">

:       <div align="center"><font color="#990066"><b>Job

: Category</b></font></div>

:     </td>

:     <td width="37" valign="top" bordercolor="#FFFFFF">

:       <div align="center"><img src="file://///Student-

: iis/star_recruitment/Star_3.jpg" width="24" height="22"></div>

:     </td>

:     <td width="89" valign="top" bordercolor="#FFFFFF">

:       <div align="center"><font color="#990066"><b>Position

: Available</b></font></div>

:     </td>

:     <td width="38" valign="top" bordercolor="#FFFFFF"><img

: src="file://///Student-iis/star_recruitment/Star_3.jpg" width="24"

: height="22"></td>

:     <td width="88" valign="top">

:       <div align="center"><font color="#990066"><b>Job

: Location</b></font></div>

:     </td>

:     <td width="38" valign="top" bordercolor="#FFFFFF"><img

: src="file://///Student-iis/star_recruitment/Star_3.jpg" width="24"

: height="22"></td>

:     <td width="88" valign="top">

:       <div align="center"><b><font color="#990066">Job

: Type</font></b></div>

:     </td>

:     <td width="50" valign="top" bordercolor="#FFFFFF"><img

: src="file://///Student-iis/star_recruitment/Star_3.jpg" width="24"

: height="22"></td>

:     <td width="88" valign="top">

:       <div align="center"><b><font color="#990066">Date

: Submitted</font></b></div>

:     </td>

:   </tr>

:   <%

: Do While Not objRS.EOF

:

:   %>

:   <tr>

:     <td height="30" valign="top" bordercolor="#FFFFFF" width="88"><%=objRS

: ("Job_Category")%>

:     </td>

:     <td width="37"></td>

:     <td valign="top" width="89" bordercolor="#FFFFFF"><%=objRS

: ("Position_Available")%>

:     </td>

:     <td width="38"></td>

:     <td valign="top" width="88"><%=objRS("Job_Location")%> </td>

:     <td width="38"></td>

:     <td valign="top" width="88"><%=objRS("Job_Type")%> </td>

:     <td width="50"></td>

:     <td valign="top" width="88"><%=objRS("SubmitDate")%> </td>

:   </tr>

:   <%

:

: objRS.MoveNext

: Loop

:

: %>

: </table>

: <%

: END IF

: MyConn.Close

: Set MyConn = Nothing

: Set objRS = Nothing

: %>

: </BODY>

: </HTML>



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Return to Index

P2P Archives