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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display records for 1 month

    How can I display records from my DB only for 1 month!

    Example:
    When you insert a review to a certain record, I would like that record with most reviews for this month (i.e. August) would be displayed.
    Reviews from July, etc.. will be in archive.


    My rs:
    Code:
    <%
    Dim caliber
    Dim caliber_numRows
    
    Set caliber = Server.CreateObject("ADODB.Recordset")
    caliber.ActiveConnection = MM_search_STRING
    caliber.Source = "SELECT * FROM [Submit_load Poizvedba] ORDER BY date DESC"
    caliber.CursorType = 0
    caliber.CursorLocation = 2
    caliber.LockType = 1
    caliber.Open()
    
    caliber_numRows = 0
    %>
    Thanks,
    Last edited by urko; 09-02-2005 at 02:25 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Use the DatePart function

    btw date is a reserved word and you should not name a column with a reserved word

    Code:
    <%
    Dim caliber
    Dim caliber_numRows
    
    Set caliber = Server.CreateObject("ADODB.Recordset")
    caliber.ActiveConnection = MM_search_STRING
    caliber.Source = "SELECT * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
    caliber.CursorType = 0
    caliber.CursorLocation = 2
    caliber.LockType = 1
    caliber.Open()
    
    caliber_numRows = 0
    %>

  • #3
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    datediff may also work for you
    Code:
    caliber.Source = "SELECT * FROM [Submit_load Poizvedba] WHERE DateDiff("m",[Submit_load Poizvedba].[date], date()) = 0 ORDER BY date DESC"
    Last edited by NancyJ; 09-02-2005 at 04:41 PM.

  • #4
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks concerning the date warning! ( I will fix that ).

    The first one worked, but I get all records displayed which were entered in Septemeber.

    What I would like is that I would get all reviews from 1 record only, and possibly for previous month ( in this case August ).

    By 1 record only is to show to users which record from the DB had most reviews in previous month and all those reviews from that 1 record would be visible!

    Just to give u an idea. The name of that page would be: Item of the month!
    So that item had most reviews and is now shown specialy on one page with reviews only from a previous month!
    Is this possible


    With the second solution I get EOF and BOF error.

    Thanks all for your help,

    I really appreciate

  • #5
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    apologies - use getdate() rather than date() in the sql statement.

    to give you any more info on what you want to do I'd need to see your data structure.

  • #6
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by NancyJ
    apologies - use getdate() rather than date() in the sql statement.

    to give you any more info on what you want to do I'd need to see your data structure.
    At the moment I only have rs and filed items like Name, date, and description on that page.

    I have made relationships in Access DB to the table Items so that users could write reviews(this works without problems) .
    But because I don't know much of asp coding I have problems that I can't solve.
    So I really appreciate your help on this one.

  • #7
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    To only get 1 record you need to change your sql select statement. Try using the TOP keyword to get the record.

    Code:
    <%
    Dim caliber
    Dim caliber_numRows
    
    Set caliber = Server.CreateObject("ADODB.Recordset")
    caliber.ActiveConnection = MM_search_STRING
    caliber.Source = "SELECT TOP 1 * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
    caliber.CursorType = 0
    caliber.CursorLocation = 2
    caliber.LockType = 1
    caliber.Open()
    
    caliber_numRows = 0
    %>
    Are the reviews in the same table? or are they in a different table? To get the reviews you will need to use a JOIN if in a different table and you could use a sub query if they are in the same table.
    Last edited by miranda; 09-02-2005 at 06:36 PM.

  • #8
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by miranda
    To only get 1 record you need to change your sql select statement. Try using the TOP keyword to get the record.

    Are the reviews in the same table? or are they in a different table? To get the reviews you will need to use a JOIN if in a different table and you could use a sub query if they are in the same table.
    I have to Db tables
    1. Items
    2. Reviews

    In relationships I have connected these two tables so that users can write reviews for a different type of product. Now this works ok in browser as I have tested and have no problem with this.

  • #9
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    I'm at home now so I dont have anything to test this on but off the top of my head
    Code:
    caliber.Source = "SELECT top 1 count(distinct reviews.itemID) as numreviews, reviews.*  FROM reviews WHERE DateDiff("m",[Submit_load Poizvedba].[date], date()) = 0 ORDER BY numreviews DESC"
    or something like that... sorry if its not 100% accurate, I dont have an SQL synax checker at home

  • #10
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    here is how to get the results from the reviews table This is assuming that [Submit_load Poizvedba] is the table that holds the items. Just replace the name of the table and the name of the field.

    Code:
    "SELECT * FROM the_table_with_reviews WHERE the_field_name = (SELECT TOP 1 * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date())) ORDER BY date DESC"

  • #11
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by miranda
    here is how to get the results from the reviews table This is assuming that [Submit_load Poizvedba] is the table that holds the items. Just replace the name of the table and the name of the field.

    Code:
    "SELECT * FROM the_table_with_reviews WHERE the_field_name = (SELECT TOP 1 * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date())) ORDER BY date DESC"
    Submit_load Poizvedba is a Query that I'm using to display reviews.

    Submit_load is a table in DB where records are described.

    So I need that reviews from Sumbit_load Poizvedba would be visible for previous month ( ie August ), but only reviews from a record which had most reviews!
    example:
    If I get 10 reviews for record #2 and 12 reviews for record # 5 I would like that only reviews from record #5 would be shown!

    So that means that from September 1st to September 30th this page would show only records from August ( ID Record and all reviews for this record ) and then on October 1st it will shown record with most reviews in September.

    Hope you know what I mean

  • #12
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    To get the previous months reviews use this DatePart("m",DateAdd("m",-1,Date())). This is a function within a function. The inner function gets the date of 1 month previous to today(August 04, 2005, or 8/4/2005 as m/d/yyyy) the outer function says to just get the value of the month for this (August = 8). So this month using DatePart("m",DateAdd("m",-1,Date())) will return 8 and next month it will return 9 and so on

    You will need to designate a unique field to key off of. For example say that items table has a column with data type autonumber that is used as a primary key to signify a unique record. Your reviews table then have a column with data type number that holds this value, so that you know which item the review is for. (obviously this doesnt have to be numeric values, you could have set it up so that these are data type text. The point is you will have some field used as a unique identifier in the items table that is then listed in the rviews table to show which record they relate to) In this case you want to grab that value of that column as the TOP.

    uniqueIdentifier = the column in the query that designates which record in the items table the reviews correpond to. All you need to do is change that to whatever you named this column.


    Code:
    <%
    Dim caliber
    Dim caliber_numRows
    
    Set caliber = Server.CreateObject("ADODB.Recordset")
    caliber.ActiveConnection = MM_search_STRING
    caliber.Source = "SELECT TOP 1 [Submit_load Poizvedba].[uniqueIdentifier], [Submit_load Poizvedba].* FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",DateAdd("m",-1,Date())) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
    caliber.CursorType = 0
    caliber.CursorLocation = 2
    caliber.LockType = 1
    caliber.Open()
    
    caliber_numRows = 0
    %>

  • #13
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by miranda
    To get the previous months reviews use this DatePart("m",DateAdd("m",-1,Date())). This is a function within a function. The inner function gets the date of 1 month previous to today(August 04, 2005, or 8/4/2005 as m/d/yyyy) the outer function says to just get the value of the month for this (August = 8). So this month using DatePart("m",DateAdd("m",-1,Date())) will return 8 and next month it will return 9 and so on

    You will need to designate a unique field to key off of. For example say that items table has a column with data type autonumber that is used as a primary key to signify a unique record. Your reviews table then have a column with data type number that holds this value, so that you know which item the review is for. (obviously this doesnt have to be numeric values, you could have set it up so that these are data type text. The point is you will have some field used as a unique identifier in the items table that is then listed in the rviews table to show which record they relate to) In this case you want to grab that value of that column as the TOP.

    uniqueIdentifier = the column in the query that designates which record in the items table the reviews correpond to. All you need to do is change that to whatever you named this column.


    Code:
    <%
    Dim caliber
    Dim caliber_numRows
    
    Set caliber = Server.CreateObject("ADODB.Recordset")
    caliber.ActiveConnection = MM_search_STRING
    caliber.Source = "SELECT TOP 1 [Submit_load Poizvedba].[uniqueIdentifier], [Submit_load Poizvedba].* FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",DateAdd("m",-1,Date())) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
    caliber.CursorType = 0
    caliber.CursorLocation = 2
    caliber.LockType = 1
    caliber.Open()
    
    caliber_numRows = 0
    %>
    Ok this works partly as it shown a record with 3 reviews, but I have 1 record that has 5 reviews and that one didn't show. but please don't be mad as I would like something different.
    Unique key is "ID" and so this part is ok and works like u decsribed.

    What I would like is to dispaly headings of each revirew for most reviewed record, so hat users can click on a specific review.
    So what i made is a repeated region to display all reviews(title only) ( not Records name ) and made a link on title to read review.

    So users can browse or see all reviews click on a desired review title and popup opens with that review.

    Code:
    <%If (Repeat1__numRows Mod 2) Then%>
           <tr style="background-color:#f4f4f4">
      <%Else%>
           <tr style="background-color:#ffffff">
      <%End If%>
    			      <td width="20%"><%= DoDateTime((caliber.Fields.Item("date").Value), 1, 4105) %></td>
    			      <td width="20%"><a href="#" onClick="MyWindow=window.open('http://myserver/myfolder/loads_display.asp?load_id=<%=(caliber.Fields.Item("Load_id").Value)%>','MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=no,width=500,height=620'); return false;"><%=(caliber.Fields.Item("Title").Value)%></a></td>
    			      <td width="20%"><%=(caliber.Fields.Item("bullet").Value)%></td>
    			      <td width="20%"><%=(caliber.Fields.Item("powder").Value)%></td>
    		        </tr>
                  </table>
                  <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      caliber.MoveNext()
    Wend
    %>

  • #14
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    so what part do you need help with?

  • #15
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by miranda
    so what part do you need help with?
    The last RS that you send me is ok, only thing is, that I want all reviews to be shown not just 1, and also that all reviews are from record ID which had most reviews in previous month. All other reviews from different Record ID should not be shown in that page.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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