CODEWITHSUNDEEP
sqlasp-classic
Ian Williams
Ian Williams

Reputation: 55

Classic ASP SELECT * WHERE x AND x

I was wondering how to properly code the strSQL line below to return correct results.

SELECT * FROM Reviews WHERE Month=x AND Year=x

I will hardcode in the month and year.

I am running an asp page using an access db. I tried the string above (with the month and year hardcoded) but it did not work.

The server log page is down so I cant check it that way and even though my browser is set to display the full error message, all I see is 500 - Internal Server Error.

I have checked the Reviews table for records matching the month and year I coded and there are quite a few for March 2012 so it should display several results.

The month and year fields are separate fields in the database manually entered by the user.

<%
Set adoCon = Server.CreateObject("ADODB.connection")' initialize connection
Set ors = Server.CreateObject("ADODB.RecordSet")' initialize record set

adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database.mdb")

Const COLUMN_COUNT = 1
strSQL = "SELECT * FROM Reviews WHERE Month= 'March' AND Year= '2012'"

coll_width=100/COLUMN_COUNT

column = 0 ' initialize counter

ors.open strSQL,adoCon,1,1  'open record set

Do While Not ors.EOF
If column = 0 Then Response.Write "<TR>" 

%>
<td width="<%=coll_width%>%" valign="top" ><table width="100%" border="0">
<tr    > 
<td height="100%">
<div class="extra_container post reviews">
<figure> <img src="images/<% = oRs.Fields("Image") %>" width="200" alt=""> </figure>
<div>
<div class="date">
<% = oRs.Fields("ReleaseDate") %>
</div>
<div class="title">
<% = oRs.Fields("Artist") %> - <% = oRs.Fields("Album") %>
</div>
<% = oRs.Fields("Teaser") %>
<br>
<a href="cdreviewsmain.asp?pl=<% = oRs.Fields("ID") %>" class="button">more</a> </div>
</div>
</td>
</tr>
</table>
</td>
<% column = column + 1 
If column = COLUMN_COUNT Then 
Response.Write "</TR>" & vbNewLine 
column = 0 ' start over! 
End If 
oRS.MoveNext 
Loop %>
<% ors.Close 'Close the database connection
'SET ors = Nothing 'Clean up after yourself
%>
</table>

I have a couple of other problems but I will get them sorted one at a time!

Thanks for any help you may be able to give,

Ian

Upvotes: 0

Views: 3720

Answers (2)

John
John

Reputation: 1327

As far as I remember you have to use a hash when using dates in access like:

“SELECT * FROM tableName WHERE ([Year] = #” & varDate & “#”

Upvotes: 0

D.B.
D.B.

Reputation: 1782

MONTH and YEAR are reserved words in SQL... try escaping them with [ ]

SELECT * FROM Reviews WHERE [Month]=x AND [Year]=x

If 'x' is numeric, then just put the numbers, if they are strings, then put in quotes. in Access use double quotes and single quotes in SQL Server.

BTW, If you got MS Access installed, you can use the Queries/View tab to build a criteria and then look at the corresponding SQL code to get hints.

Upvotes: 2

Related Questions