Quick help - ASP SQL Querying?

[MagSafe]

Hey, could someone give me a bit of help?

Not really sure on including variables within SQL queries yet and have put the following into my code:

sql_archived_news = "SELECT SectionID, SectionShortDesc, SectionDate FROM tblNews WHERE SectionMain=1 And "& Month("SectionDate")=archivednewsmonth &" And "& Year("SectionDate")=2006 &" ORDER BY SectionDate DESC;"

archivednewsmonth is just a variable with a month in it (for example "January")

It's not working and not to sure where i'm going wrong? I've never really used variables or the Month("SectionDate") function in an SQL query so far so would just like to get it right so i know in the future

 

[Mr^B]

Try this:

sql_archived_news = "SELECT SectionID, SectionShortDesc, SectionDate FROM tblNews WHERE SectionMain=1 And Month("SectionDate")=" & archivednewsmonth & " And Year("SectionDate")=2006 ORDER BY SectionDate DESC;"

I've left 2006 in as a literal in the query, but that would be easy to replace with an "archivednewsyear" variable thus:

[...]And Year("SectionDate")=" & archivednewsyear & " ORDER BY[...]

Oh, and the SQL "Month" function returns a month number not a string - so you would have to pass it 1, rather than "January" for it to work.

 

[MagSafe]

Thanks for that,

I tried your replacement sql query but it still doesn't work, just shows an error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/test/Website/IndexIncludes/archivednewsmonth.asp, line 11, column 113
sql_archived_news = "SELECT SectionID, SectionShortDesc, SectionDate FROM tblNews WHERE SectionMain=1 And Month("SectionDate")=" & archivednewsmonth & " And Year("SectionDate")=2006 ORDER BY SectionDate DESC;"
----------------------------------------------------------------------------------------------------------------^

I changed the values sent to 1,2,3 etc. instead of the actual months

 

[Mr^B]

Thanks for that,

I tried your replacement sql query but it still doesn't work, just shows an error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/test/Website/IndexIncludes/archivednewsmonth.asp, line 11, column 113
sql_archived_news = "SELECT SectionID, SectionShortDesc, SectionDate FROM tblNews WHERE SectionMain=1 And Month("SectionDate")=" & archivednewsmonth & " And Year("SectionDate")=2006 ORDER BY SectionDate DESC;"
----------------------------------------------------------------------------------------------------------------^

I changed the values sent to 1,2,3 etc. instead of the actual months

Replace the double-quotes in the SQL with single ones.

Month('SectionDate')
Year('Sectiondate')

 

[MagSafe]

<%
[b]Line 10[/b]   set rsArchivedNews = Server.CreateObject("ADODB.Recordset")
[b]Line 11[/b]   sql_archived_news = "SELECT SectionID, SectionShortDesc, SectionDate FROM tblNews WHERE SectionMain=1 And Month('SectionDate')=" & archivednewsmonth & " And Year('SectionDate')=2006 ORDER BY SectionDate DESC;"
[b]Line 12[/b]   rsArchivedNews.Open sql_archived_news, conn, 3, 3
[b]Line 13[/b]   nLCount = rsArchivedNews.RecordCount
%>

Hmm, it doesn't give an error anymore on the sql statement, but says there is an error on line 12 , not sure if I should be changing anything there, or if my code just isn't working ... the variable is passing to the page fine, I just can't see why the query won't work properly.

 

[PsychoDuck]

Dont think you need either double quotes or single quotes, try this:-

sql_archived_news = "SELECT SectionID, SectionShortDesc, SectionDate FROM tblNews WHERE SectionMain=1 And Month(SectionDate)=" & archivednewsmonth & " And Year(SectionDate)=2006 ORDER BY SectionDate DESC;"

 

[toString]

Good way to solve these is to do a

response.write sql_archived_news

just before it gets to the line it fails on. Then you can read the actual SQL code and correct your errors from there.

 

[MagSafe]

Working perfectly now, thanks for all the help

Have taken a note of that for future use

Thanks again,

Steven.