Classic ASP Discussion

Print Page | Close Window

SQL query

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=4373
Printed Date: 30 March 2026 at 6:37am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL query

Posted By: jrockfl
Subject: SQL query
Date Posted: 20 July 2003 at 8:45am

What is the syntax so I can add AND status = open to this query? strSQL = "SELECT * FROM tbltickets WHERE userid=" & lngRecordNo

Also, how can I alternate table row colors with this code?

<%
'Loop through the recordset
Do While Not rsTickets.EOF
%>
        <tr bgcolor="#D8D8D8">
          <td><%=(rsTickets("ticketid"))%></td>
          <td><%=(rsTickets("ticstat"))%></td>
          <td><%=(rsTickets("ticpri"))%></td>
          <td><%=(rsTickets("ticsub"))%></td>
          <td><%=(rsTickets("opendate"))%></td>
        </tr>
<%
'Move to the next record in the recordset
rsTickets.MoveNext
Loop
%>

Replies:

Posted By: michael
Date Posted: 20 July 2003 at 9:50am

<%
strSQL = "Select * from tblTickets where userid=" & lngRecordNo & " AND status = 'open'"
........
'Loop through the recordset
colcounter = 1
Do While Not rsTickets.EOF
if (colcounter MOD 2) then
    tblbgcol = "bgcolor=""#D8D8D8"""
else
    tblbgcol = "bgcolor=""#D0D0D0"""
end if

%>
        <tr <%=tblbgcol%>
          <td><%=(rsTickets("ticketid"))%></td>
          <td><%=(rsTickets("ticstat"))%></td>
          <td><%=(rsTickets("ticpri"))%></td>
          <td><%=(rsTickets("ticsub"))%></td>
          <td><%=(rsTickets("opendate"))%></td>
        </tr>
<%
colcounter = colcounter + 1
'Move to the next record in the recordset
rsTickets.MoveNext
Loop
%>

Quick and dirty. You should consider to put everything in vbscript and not mix it. Use response.write for your table generation.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: jrockfl
Date Posted: 20 July 2003 at 12:59pm

Thank you! That would perfectly. I will go back an use response.write. Do you typically use response.write as much as possible for the html?

Posted By: MorningZ
Date Posted: 20 July 2003 at 4:06pm

i do it a little different (less lines of code), not that it really matters

<%
strSQL = "Select * from tblTickets where userid=" & lngRecordNo & " AND status = 'open'"
........
rowColor = "#D8D8D8"
'Loop through the recordset
Do While Not rsTickets.EOF
%>
        <tr bgcolor="<%= rowColor %>">
          <td><%=(rsTickets("ticketid"))%></td>
          <td><%=(rsTickets("ticstat"))%></td>
          <td><%=(rsTickets("ticpri"))%></td>
          <td><%=(rsTickets("ticsub"))%></td>
          <td><%=(rsTickets("opendate"))%></td>
        </tr>
<%
if rowColor = "#D8D8D8" then rowColor = "#FFFFFF" else rowColor = "#D8D8D8"
'Move to the next record in the recordset
rsTickets.MoveNext
Loop
%>

-------------
Contribute to the working anarchy we fondly call the Internet

Posted By: jrockfl
Date Posted: 20 July 2003 at 4:21pm

That way works too...thank you!

Posted By: jrockfl
Date Posted: 20 July 2003 at 4:29pm

How would I use Response.Write? I keep getting errors when I do it

Posted By: zaboss
Date Posted: 20 July 2003 at 11:57pm

Response.Write("<TR>")
Response.Write("<TD Width=""10%"" BGCOLOR=""" & rowColor & """><Font Face=""Georgia, Times New Roman, Times, serif"" Color="""">" & RSTickets("ticketID") & "</Font></TD>")

.....

Response.Write("<TD Width=""10%"" BGCOLOR=""" & rowColor & """><Font Face=""Georgia, Times New Roman, Times, serif"" Color="""">" & RSTickets("ticketOpenDate") & "</Font></TD>")
Response.Write("</TR>")
And right after the end of the loop
Response.Write("</Table>")

-------------
Cristian Banu
http://www.soft4web.ro - Soft 4 web

Posted By: zaboss
Date Posted: 21 July 2003 at 12:00am

As a rule, in Response.Write statements, the HTML brackets should be between double quotes "<HTML TAG>" and values of the HTML in double double quotes like in width = ""10%"".

-------------
Cristian Banu
http://www.soft4web.ro - Soft 4 web

Posted By: jrockfl
Date Posted: 21 July 2003 at 5:21am

Thank you! I was way off.