Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts

    My Session only shows one Record Set

    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.

    Code:
    <% 
      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>
    Kind regards,
    Mike Hughes

  • #2
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts

    I stil need some help. Please

    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
    Kind regards,
    Mike Hughes

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try this:
    Code:
    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

  • #4
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks dude - but still no joy.
    Kind regards,
    Mike Hughes

  • #5
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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:

    Code:
    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.

  • #6
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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
    %>
    Kind regards,
    Mike Hughes


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •