Hello all, There is a news page set up, with a ton of code on it. I think I have this right but I am not sure. I think this is the part that grabs the records from the SQL Database, if I am right how can you make this select only thoes records from the current year? It works fine for the month, but it returns all records from this september and last september.

<%
    if len(Request.QueryString("month")) > 0 then
        Session("whatmonth") = Request.QueryString("month")
    else
        Session("whatmonth") = DatePart("m", date)
    end if
    SQLStr="SELECT * FROM ORNews ORDER by Datestamp DESC"
    Set RS=Server.CreateObject("ADODB.Recordset")
    RS.Open SQLStr,Application("DBConn"),adOpenKeyset,adLockPe ssimistic,adCmdText
%>

Thanks

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

__________________
-----------------------------------------------------------
\"Don\'t follow someone who\'s not going anywhere\" John Mason

Hi,


SQL Server attaches a time-stamp id info to each record inserted into the tables. But that information is used for Internal purposes only. The Users of the DB Server cannot in any way retrieve such information.

In case of such requirement, the design team should identify the tables involved in such requirements and include a datetime column that takes GetDate () by default.

There is a concept called Bookmark in SQL Server that will allow you to bookmark a specific row in the result set and return to it at any point in time during the logic or process. This is a whole new concept from what you requested. See BOL for further help regarding bookmarking rows.

Thanks
Suresh

If there is a column called the_date:

SELECT * FROM ORNews WHERE the_date BETWEEN date_start AND date_end ORDER by the_date DESC

Thanks for you help guys. The colomn in the DB is called datestamp and it stores the date like this 9/15/2004. This is what I am using and it seems to be working so far.

    if len(Request.QueryString("month")) > 0 then
        Session("whatmonth") = Request.QueryString("month")
        Session("whatyear") = Request.QueryString("year")
    else
        Session("whatmonth") = DatePart("m", date)
        Session("whatyear") = DatePart("yyyy", date)
    end if

Then a for next looop to get the proper records

    isy = 0
    for x=1 to RS.recordcount
    if trim(Session("whatmonth")) = trim(Month(RS("datestamp"))) and trim(Session("Whatyear")) = trim(Year(RS("datestamp"))) then
    isy = isy + 1%>



-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Morpheus,

Than doing all these checks in the ASP code, you can easily do that at the backend and get only the rows that you need. Which gives you just the rows of the year passed as querystring. If you want the that for a particular month, then you got to check for year first and month next. Else you would get rows that match the month of any year.

This results in only the rows that you would look for, and straight away you can go displaying it, that doing any other checks as you do in your asp now.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks happygv, but I keep getting an error.

Incorrect syntax near '='

Can you check the syntax and see if it is right :)

Thanks

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Hi Morpheus,

I am sure there is nothing wrong with the syntax. I suspect your Request.QueryString("year") or Request.QueryString("month") returns nothing. That should have caused this error.

Can you do this in your ASP code and post here the result displayed on your browser.

'Not sure which of the following SQL statements you use...

'SQLStr="SELECT * FROM ORNews WHERE year(Datestamp)=" & Request.QueryString("year") &
" and month(Datestamp)=" & Request.QueryString("month") &
" ORDER by Datestamp DESC"
'OR
SQLStr="SELECT * FROM ORNews WHERE year(Datestamp)=" & Request.QueryString("year") & " ORDER by Datestamp DESC"
Response.write SQLStr
Response.End

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Sorry happygv but I can't seem to get past either SQL statements.
Line 1: Incorrect syntax near 'and' (for the first)

Line 1: Incorrect syntax near '=' (for the second)

I will keep playing around with it and see what I can get.

Thanks

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Hi Morpheus,

Then you add this on top of the page within ASP tags and see if you are getting the QS value.
Response.write "QS YEAR VALUE is = " & Request.QueryString("year")
Response.end

See if that displays value of year that is passed as querystring. I hope that doesn't display anything as value

So in both the cases the culprit is Request.QueryString("year"). This doesn't come with any value.

And so your sql statement is generated as
SELECT * FROM ORNews WHERE year(Datestamp)=
Which when executed is incomplete and gives you that error.

You can get past that actually, by not letting the asp page execute this line
RS.Open SQLStr...
By adding the following
Response.write SQLStr
Response.End

after the line that reads SQLStr="SELECT * FROM ...
and before the line that reads RS.Open SQLStr...

Response.End, doesn't let the script engine interpret the line after it. So that stops the page execution at that point. You would face that error you mentioned only when you execute a recordset.open method and only if the SQLStr contains any syntax error.

Once you are through, you can copy the resulting SQL statement and paste it in to you SQL query analyser and execute the same to see if that returns the result that you expect. But I am sure you are missing the querystring value in there, that is the cause for that error.

If possible post the entire URL here (along with querystring values if any) that comes when you face this error. That might give some clue.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection