...

View Full Version : My Session only shows one Record Set



hughesmi
04-26-2006, 09:32 PM
Can some one help with this. The below scrpt is only showing the first record in the database. I think the realastionships on the dadabase is ok.



<%
If Session("PersonID") = "" Then
Response.Redirect "Login.asp"
End if
%>
<link rel="stylesheet" type="text/css" href="style.css">
<%


Dim adoNewsCon 'Database Connection Variable
Dim rsNewsConfiguration 'Holds the configuartion recordset
Dim strAdoNewsConfig 'Holds the Database driver and the path and name of the database
Dim strNewsSQL 'Holds the SQL query for the database
Dim intPreviewNewsItems 'Number of files shown on each page
Dim blnNewsLCode 'News page code set to true
Dim strNewsBgColour 'Holds the background colour of the News Administrator
Dim strNewsTextColour 'Holds the text colour of the News Administrator
Dim strNewsTextType 'Holds the font type of the News Administrator
Dim intNewsTextSize 'Holds the font size of the News Administrator
Dim intNewsSmallTextSize 'Holds the size of small fonts
Dim strNewsLinkColour 'Holds the link colour of the News Administrator
Dim strNewsTableColour 'Holds the table colour
Dim strNewsTableBorderColour 'Holds the table border colour
Dim strNewsTableTitleColour 'Holds the table title colour
Dim strNewsVisitedLinkColour 'Holds the visited link colour of the News Administrator
Dim strNewsActiveLinkColour 'Holds the active link colour of the News Administrator





'Create database connection

'Create a connection odject
Set adoNewsCon = Server.CreateObject("ADODB.Connection")

'------------- If you are having problems with the script then try using a diffrent driver or DSN by editing the lines below --------------

'Database connection info and driver
strAdoNewsConfig = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../private/news.mdb")

'---------------------------------------------------------------------------------------------------------------------------------------------

'Set an active connection to the Connection object
adoNewsCon.Open strAdoNewsConfig

'Read in the configuration for the script
'Intialise the ADO recordset object
Set rsNewsConfiguration = Server.CreateObject("ADODB.Recordset")

'Initialise the SQL variable with an SQL statement to get the configuration details from the database
strNewsSQL = "SELECT tblConfiguration.* From tblConfiguration;"

'Query the database
rsNewsConfiguration.Open strNewsSQL, strAdoNewsConfig

'If there is config deatils in the recordset then read them in
If NOT rsNewsConfiguration.EOF Then

'Read in the configuration details from the recordset
strNewsTextColour = rsNewsConfiguration("text_colour")
strNewsTextType = rsNewsConfiguration("text_type")
intNewsTextSize = CInt(rsNewsConfiguration("text_size"))
intNewsSmallTextSize = CInt(rsNewsConfiguration("small_text_size"))
strNewsTableColour = rsNewsConfiguration("table_colour")
strNewsTableBorderColour = rsNewsConfiguration("table_border_colour")
strNewsTableTitleColour = rsNewsConfiguration("table_title_colour")
strNewsLinkColour = rsNewsConfiguration("links_colour")
strNewsVisitedLinkColour = rsNewsConfiguration("visited_links_colour")
strNewsActiveLinkColour = rsNewsConfiguration("active_links_colour")
blnNewsLCode = CBool(rsNewsConfiguration("Code"))
intPreviewNewsItems = rsNewsConfiguration("No_of_preview_items")
End If

'Reset server object
rsNewsConfiguration.Close
Set rsNewsConfiguration = Nothing

%>

</style>

<body topmargin="1" background="" style="background-attachment: fixed" leftmargin="1">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="166" height="338">
<tr>
<td width="212" height="338" valign="top">
<div align="left" style="width: 247px; height: 127px">
<table width="178" border="0" cellspacing="0" cellpadding="0" bgcolor="<% = strNewsTableBorderColour %>" style="border-collapse: collapse" bordercolor="#111111">
<tr>
<td width="221">
<div align="center">
<center>
<table width="281" border="0" cellspacing="0" cellpadding="3" bgcolor="<% = strNewsTableColour %>" style="border-collapse: collapse; " bordercolor="#111111">
<tr>
<td width="275">
<%
Dim rsNews 'Database recordset holding the news items
Dim intNewsItems 'Loop counter for displaying the news items

'Create recorset object
Set rsNews = Server.CreateObject("ADODB.Recordset")

rsNews.Filter = "News_ID = " & Session("PersonID")

'Initalise the strNewsSQL variable with an SQL statement to query the database

'strNewsSQL = "SELECT TOP " & intPreviewNewsItems & " tblNews.* FROM tblNews ORDER BY News_Date DESC;"

strNewsSQL = "SELECT tblNews.News_ID, tblNews.PersonID AS tblNews_PersonID, tblNews.News_title, tblNews.Short_news, tblNews.News_item, tblNews.News_Date, tblNews.Comments, Person.PersonID AS Person_PersonID, Person.FamilyName, Person.GivenName, Person.LastLogin FROM Person INNER JOIN tblNews ON Person.PersonID = tblNews.PersonID;"


'strNewsSQL = "SELECT * FROM tblNews ORDER BY News_Date DESC;"


'Query the database
rsNews.Open strNewsSQL, adoNewsCon

'If there are no news item to display then display a message seying so
If rsNews.EOF Then Response.Write("<span class=""text"">Sorry, There is no News Items to display</span>")

'Loop round to display each of the news items
For intNewsItems = 1 to intPreviewNewsItems

'Iv there are no records then exit for loop

If rsNews.EOF Then Exit For

%>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><a href="news/news_item.asp?NewsID=<% = rsNews("News_ID") %>" target="MainFrame"><% = rsNews("News_title") %></a>
<% = FormatDateTime(rsNews("News_Date"), vbShortDate) %>
<br>
<b>
<% = rsNews("Short_news") %></b>&nbsp
(<a href="news/news_item.asp?NewsID=<% = rsNews("News_ID") %>">M</a><a target="MainFrame" href="news/news_item.asp?NewsID=<% = rsNews("News_ID") %>">ore
info</a>)</td>
</tr>
</table>
<p>
<%
'Move to the next record in the recordset
rsNews.MoveNext
Next

'Reset server objects
rsNews.Close
Set rsNews = Nothing
Set strAdoNewsConfig = Nothing
Set adoNewsCon = Nothing
%><br>
<br>
&nbsp;<a target="MainFrame" href="news/default.asp">Site
News and Archive </a>
<br>
</td>
</tr>
</table>
</center>
</div>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</p>
</div>

hughesmi
04-29-2006, 02:36 PM
Hi everyone. I still require help with this.

I'm trying to retreve all records under the user. I make the caller register and then they can log in with a en email addres and password and the they can add their on news items. The session stay's open until they log out. I just can't get the records to loop.

I know it has some think to do with this line - but i canny fugure it out?



'Query the database
rsNews.Open strNewsSQL, adoNewsCon

'If there are no news item to display then display a message seying so
If rsNews.EOF Then Response.Write("<span class=""text"">Sorry, There is no News Items to display</span>")

'Loop round to display each of the news items
For intNewsItems = 1 to intPreviewNewsItems

'Iv there are no records then exit for loop

If rsNews.EOF Then Exit For

mehere
05-03-2006, 06:58 PM
try this:


If rsNews.EOF and rsNews.BOF Then
Response.Write("<span class=""text"">Sorry, There is no News Items to display</span>")
Else
Do while not rsNews.EOF
%>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><a href="news/news_item.asp?NewsID=<% = rsNews("News_ID") %>" target="MainFrame"><% = rsNews("News_title") %></a>
<% = FormatDateTime(rsNews("News_Date"), vbShortDate) %>
<br>
<b>
<% = rsNews("Short_news") %></b>&nbsp
(<a href="news/news_item.asp?NewsID=<% = rsNews("News_ID") %>">M</a><a target="MainFrame" href="news/news_item.asp?NewsID=<% = rsNews("News_ID") %>">ore
info</a>)</td>
</tr>
</table>
<p>
<%
'Move to the next record in the recordset
rsNews.MoveNext
Loop

hughesmi
05-03-2006, 07:26 PM
Thanks dude - but still no joy.

Spudhead
05-05-2006, 11:35 AM
I've got a feeling it's your SQL. And that filtering line.

I really wouldn't bother with recordset filtering. Just use WHERE statements. Firstly, because recordsets are expensive objects to keep in memory and you really don't want to be working with them any more than you absolutely need to. Secondly, because it makes it much easier to debug if you can just paste a SQL statement into your database query window and see at a glance that it's giving you the results you expect.

So, assuming you haven't already done it, try this:



SELECT
tblNews.News_ID,
tblNews.PersonID AS tblNews_PersonID,
tblNews.News_title,
tblNews.Short_news,
tblNews.News_item,
tblNews.News_Date,
tblNews.Comments,
Person.PersonID AS Person_PersonID,
Person.FamilyName,
Person.GivenName,
Person.LastLogin
FROM Person
INNER JOIN tblNews ON tblNews.PersonID = Person.PersonID
WHERE Person.PersonID = 3


(obviously, replace "3" with a Person ID that you KNOW exists and has News items)

Just paste that query into Access and see what you get back. If everything is there as it should be, put it into your code. If, after that, you're still only getting one record displayed - well, at least you now know it's just a code issue.

hughesmi
05-05-2006, 06:57 PM
Thanks. The last post got it working. However I may need to post back when updating a user record.

Working code.


<!--#include file="clssfd.asp"-->
<%
If Session("PersonID") = "" Then
Response.Redirect "Login.asp"
End if
%>
<%
Dim rsItems
Set rsItems = Server.CreateObject("ADODB.Recordset")
'rsItems.Filter = "News_ID = " & Session("PersonID")
'strSQL = "SELECT * FROM Person"
strSQL = "SELECT tblNews.News_ID, tblNews.PersonID AS tblNews_PersonID, tblNews.News_title, tblNews.Short_news, tblNews.News_item, tblNews.News_Date, tblNews.Comments, Person.PersonID AS Person_PersonID, Person.FamilyName, Person.GivenName, Person.LastLogin FROM Person INNER JOIN tblNews ON Person.PersonID = tblNews.PersonID WHERE Person.PersonID =" & Session("PersonID")

rsItems.Open strSQL, objConn



If Not rsItems.EOF Then ' current user has news items

%>
<TABLE BORDER="1" CELLSPACING="3" CELLPADDING="3">
<TR>
<TH>News Title</TH>
<TH>x</TH>
<TH>x</TH>
</TR>
<% Do While Not rsItems.EOF %>
<TR ALIGN=CENTER>
<TD><%=rsItems("News_title") %></TD>
<TD></TD>
<TD></TD>
<%
rsItems.MoveNext
Loop
%>
</TABLE>
<%
Else ' current user has no items
Response.Write "<CENTER><H2>No items to display</H2></CENTER>"
End If
rsItems.close
%>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum