|
 |
asp_databases thread: Conditional SQL statement to Access 2000 DB
Message #1 by "Bill Sisemore" <bsisemore@m...> on Thu, 21 Sep 2000 16:35:48 -0400
|
|
Hi All,
I am trying to write a conditional SQL statement to an Access 2000 DB
where I can sort through and write to a table, the results of a query
where I look for priority support issues based on status. For example,
if there is a critical issue that is open, I want it to just appear when
I go to the page this code resides within. The code is as follows noting
that I am receiving the first results of the "If" subroutine.
Any help would be greatly appreciated.
Thanks in Advance,
Bill Sisemore
<%
Dim strpriority, strstat
strpriority = strpriority & "critical"
strstat = strstat & "Open"
Set objGconn = Server.CreateObject("ADODB.Connection")
strConn = "globaladmin"
objGconn.Open strConn
Set objGrs = Server.CreateObject("ADODB.Recordset")
sSQL2 = "SELECT * FROM helpdesk WHERE status='" & strstat & priority='" &
strpriority "' ORDER BY index ASC"
Set objGrs = objGconn.Execute(sSQL2)
If objGrs.EOF Then
Response.Write _
"<FONT Size=3 COLOR=BLUE>" & _
"There are no Critical Open Issues at this time." & _
"</FONT>"
Else
Response.Write _
"<TABLE BORDER=""1"" WIDTH=85% CELLSPACING=""1"">" & _
"<TR>" & _
"<ALIGN=LEFT><FONT Size=1 COLOR=BLUE> Ticket </FONT><FONT Size=1
COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Priority </FONT><FONT
Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Status
</FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE>
eMail Address </FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1
COLOR=BLUE> Issue </FONT></LEFT>" & _
"</TR>"
Do While Not objGrs.EOF
Response.Write _
"<TR>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("index")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("priority")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("status")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("email")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("issue")
& "</FONT>" & "</TD>" & _
"</TR>"
objGrs.MoveNext
Loop
objGrs.Close
Response.Write "</TABLE>"
Set objGrs = Nothing
End If
objGconn.Close
Set objGconn = Nothing
%>
Message #2 by Imar Spaanjaars <Imar@S...> on Thu, 21 Sep 2000 23:17:36 +0200
|
|
The priority should be IN the SQL statement, like this:
sSQL2 = "SELECT * FROM helpdesk WHERE status='" & strstat & "' AND
priority='" &
strpriority & "' ORDER BY index ASC"
Imar
At 10:25 PM 9/21/2000 -0700, you wrote:
>Hi All,
>
>I am trying to write a conditional SQL statement to an Access 2000 DB
>where I can sort through and write to a table, the results of a query
>where I look for priority support issues based on status. For example,
>if there is a critical issue that is open, I want it to just appear when
>I go to the page this code resides within. The code is as follows noting
>that I am receiving the first results of the "If" subroutine.
>
>Any help would be greatly appreciated.
>
>Thanks in Advance,
>
>Bill Sisemore
>
><%
>
>Dim strpriority, strstat
>
>strpriority = strpriority & "critical"
>strstat = strstat & "Open"
>
>Set objGconn = Server.CreateObject("ADODB.Connection")
>strConn = "globaladmin"
>objGconn.Open strConn
>Set objGrs = Server.CreateObject("ADODB.Recordset")
>
>sSQL2 = "SELECT * FROM helpdesk WHERE status='" & strstat & priority='" &
>strpriority "' ORDER BY index ASC"
>
>Set objGrs = objGconn.Execute(sSQL2)
>
>If objGrs.EOF Then
> Response.Write _
> "<FONT Size=3 COLOR=BLUE>" & _
> "There are no Critical Open Issues at this time." & _
> "</FONT>"
> Else
> Response.Write _
> "<TABLE BORDER=""1"" WIDTH=85% CELLSPACING=""1"">" & _
> "<TR>" & _
> "<ALIGN=LEFT><FONT Size=1 COLOR=BLUE> Ticket </FONT><FONT Size=1
>COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Priority </FONT><FONT
>Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Status
></FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE>
>eMail Address </FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1
>COLOR=BLUE> Issue </FONT></LEFT>" & _
> "</TR>"
>Do While Not objGrs.EOF
>Response.Write _
> "<TR>" & _
> " <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("index")
>& "</FONT>" & "</TD>" & _
> " <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("priority")
>& "</FONT>" & "</TD>" & _
> " <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("status")
>& "</FONT>" & "</TD>" & _
> " <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("email")
>& "</FONT>" & "</TD>" & _
> " <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("issue")
>& "</FONT>" & "</TD>" & _
> "</TR>"
>objGrs.MoveNext
>Loop
>objGrs.Close
>Response.Write "</TABLE>"
>Set objGrs = Nothing
>End If
>
>objGconn.Close
>Set objGconn = Nothing
>
>%>
>
>
>
>
Message #3 by "Jason A. Greenfeld" <jgreenfeld@r...> on Thu, 21 Sep 2000 22:13:56 -0400
|
|
Hey Bill, how are you? I am brand new to the list, so I am not sure if I am
responding correctly. I think you are not getting the results that you want
because you SQL statement is slightly incorrect.
Try
sSQL2 = "SELECT * FROM helpdesk WHERE status='" & strstat & "' AND
priority='" &
strpriority & "' ORDER BY index ASC"
string variables are usually in the '" & string & "' format
One trick that I use when my SQL statements don't come out correct is to
display the SQL string before I execute it.
Ex: Response.Write sSQL2
If the statement looks up to par, then you should be all set to execute.
One other thing, in this SQL statement, I notice you use ASC in the ORDER BY
clause. I am pretty sure that ascending order is the default sort order. I
have actually never used ASC in my sort orders, only DESC(for descending
order).
Happy coding
Jason Greenfeld
Rubicon Technologies, Inc.
http://www.rubicontechnologies.com
-----Original Message----
From: Bill Sisemore [mailto:bsisemore@m...]
Sent: Friday, September 22, 2000 1:25 AM
To: ASP Databases
Subject: [asp_databases] Conditional SQL statement to Access 2000 DB
Hi All,
I am trying to write a conditional SQL statement to an Access 2000 DB
where I can sort through and write to a table, the results of a query
where I look for priority support issues based on status. For example,
if there is a critical issue that is open, I want it to just appear when
I go to the page this code resides within. The code is as follows noting
that I am receiving the first results of the "If" subroutine.
Any help would be greatly appreciated.
Thanks in Advance,
Bill Sisemore
<%
Dim strpriority, strstat
strpriority = strpriority & "critical"
strstat = strstat & "Open"
Set objGconn = Server.CreateObject("ADODB.Connection")
strConn = "globaladmin"
objGconn.Open strConn
Set objGrs = Server.CreateObject("ADODB.Recordset")
sSQL2 = "SELECT * FROM helpdesk WHERE status='" & strstat & priority='" &
strpriority "' ORDER BY index ASC"
Set objGrs = objGconn.Execute(sSQL2)
If objGrs.EOF Then
Response.Write _
"<FONT Size=3 COLOR=BLUE>" & _
"There are no Critical Open Issues at this time." & _
"</FONT>"
Else
Response.Write _
"<TABLE BORDER=""1"" WIDTH=85% CELLSPACING=""1"">" & _
"<TR>" & _
"<ALIGN=LEFT><FONT Size=1 COLOR=BLUE> Ticket </FONT><FONT Size=1
COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Priority </FONT><FONT
Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE> Status
</FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1 COLOR=BLUE>
eMail Address </FONT><FONT Size=1 COLOR=BLACK><b>|</b></FONT><FONT Size=1
COLOR=BLUE> Issue </FONT></LEFT>" & _
"</TR>"
Do While Not objGrs.EOF
Response.Write _
"<TR>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("index")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("priority")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("status")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("email")
& "</FONT>" & "</TD>" & _
" <TD>" & "<FONT SIZE=1 COLOR=#FF0000>" & objGrs("issue")
& "</FONT>" & "</TD>" & _
"</TR>"
objGrs.MoveNext
Loop
objGrs.Close
Response.Write "</TABLE>"
Set objGrs = Nothing
End If
objGconn.Close
Set objGconn = Nothing
%>
|
|
|
View Cart
