ASP - IF statement help 1

[coolskater49]

I have a a series of ASP web pages that connect to an Oracle database and retrieve data / perform queries.

On one page the user enters the query data, then clicks submit, and the ASP queries the DB and displays the results. Currently i have this set-up working for when the user is only presented with one box to enter a query value into.
This is the code i am using and it works -

<%
....
....
If BRANCHS_ID<>"" Then
Response.Write("The Branch ID requested is: " & BRANCHS_ID & " <br />")
Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1;Password=*****;User ID=******;Data Source=stora;Persist Security Info=True"
.Open
sql=(SQL STATEMENT)
Set objRecordset = .Execute(sql) %>

I now want to give the user three boxes to enter query data into, where the user may enter into any 1, 2 or ALL of the boxes.
I am attempting to do this using an IF statement like this -

IF box1 is null AND box2 is null THEN
execute this sql statement
ELSE IF box3 is null AND box2 is null THEN
execute this sql statement
ELSE IF box3 is null AND box1 is null THEN
execute this sql statement
....
....etc so in the end i will have 7 different sql statements capable of execution.

This is my actual code below, and it doesnt work.

Could anyone please help?

Cheers, sorry for the long post.

If AGE="" AND YR="" Then
Response.Write("The Category requested is: " & CAT & "<br />")
Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1;Password=******;User ID=*****;Data Source=stora;Persist Security Info=True"
.Open
sql(SQL STATEMENT)
Set objRecordset = .Execute(sql)

Else If CAT="" AND YR="" Then
Response.Write("The Age Restriction requested is: " & AGE & "<br />")
Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1;Password=******;User ID=******;Data Source=stora;Persist Security Info=True"
.Open
sql=(SQL STATEMENT)
Set objRecordset = .Execute(sql)

Else If CAT="" AND AGE="" Then
Response.Write("The Year requested is: " & YR & "<br />")
Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1;Password=******;User ID=******;Data Source=stora;Persist Security Info=True"
.Open
sql=(SQL STATEMENT)
Set objRecordset = .Execute(sql)
....
....
End If

 

[Lothario]

With many fields try using something like this instead of the If else structure you are using:

Sample code...Taken from previous threads

serEngineer = request.Form("engineer")
serCallNo = request.Form("jobid")
serDate = request.Form("mdate")
serCustomer = request.Form("customer")
serLocation = request.Form("location")
serJobtype = request.form("jobtypeID")
serSql="SELECT * FROM AllocatedJobs WHERE"
if(serEngineer<>"") then
serSql=serSql&" EngineerIDs ='"&serEngineer&"' AND"
end if
if(serCallNo<>"") then
serSql=serSql&" JobID ='"&serCallNo&"' AND"
end if
if(serDate<>"") then
serSql=serSql&" [Date] ='"&serDate&"' AND"
end if
if(serCustomer<>"") then
serSql=serSql&" customerID ='"&serCustomer&"' AND"
end if
if(serLocation<>"") then
serSql=serSql&" Location LIKE'%"&serLocation&"%' AND"
end if
if(serJobtype<>"") then
serSql=serSql&" jobtype ='"&serJobtype&"' AND"
end if
If right(sSql,6)="where" then
serSql=left(serSql, len(serSql) - 5)  'No criteria
Else
serSql=left(serSql,len(serSql)-3) 'remove dangling AND
End if
serSql=serSql&" ORDER BY [Date];"

-L

 

[coolskater49]

Thanks for your reply, i have tried your suggestion and i am now using the following code, but it still does not work.

Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1;Password=****;User ID=*****;Data Source=stora;Persist Security Info=True"
.Open
sql=("select * from DVD_CATALOG where")

if (CAT<>"") then
sql=sql & " category = '" & CAT & "' AND"
end if
if (AGE<>"") then
sql=sql & " age_restriction = '" & AGE & "' AND"
end if
if (YR<>"") then
sql=sql & " year = " & YR & " AND"
end if
sql=sql & " catalog_no is not null"

Set objRecordset = .Execute(sql)
%>

Any other suggestions would be much appreciated,
Thanks for the help.

 

[Lothario]

Try this:

Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection 
.ConnectionString = "Provider=MSDAORA.1;Password=****;User ID=*****;Data Source=stora;Persist Security Info=True"
.Open 
sql="select * from DVD_CATALOG where catalog_no is not null"

if (CAT<>"") then
sql=sql & " category = '" & CAT & "' AND"
end if

if (AGE<>"") then
sql=sql & " age_restriction = '" & AGE & "' AND"
end if

if (YR<>"") then
sql=sql & " year = " & YR & " AND"
end if

If right(sql,4)="AND" then
sql=left(sql,len(sql)-3) 
End if

Set objRecordset = .Execute(sql)

Also let me know what error u r getting...

-L

 

[coolskater49]

All i get when i submit the data from the previous form is

The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

 

[Lothario]

Post your complete code...

-L

 

[coolskater49]

Here is the form page where the user enters data which is passed onto the next page (this page works, as i have tried it)-

<HTML><HEAD> <TITLE>The Picture House</TITLE>
<style type="text/css">
<!--
.style1 {font-size: 11px}
-->
</style></HEAD>

<BODY bgcolor=#FFFFCC>

<CENTER><H1>The Picture House<BR></H1><h2>Managing Director</H2></CENTER>
<P>Query 'The Picture House LTD' DVD Catalogue</p><BR>
Back to <A HREF="LINK">Main Menu</A><BR><BR>

<table>
<tr>
<form action="Cataloguequery2.asp" method="post">
<td>Query by Category:</td>
<td><input type="text" name="CAT" size="20"></td>
</tr>
<tr><td><span class="style1">And/Or</SPAN></td></tr>
<tr>
<td>Query by Age Restriction:</td>
<td><input type="text" name="AGE" size="20"></td>
</tr>
<tr><td><span class="style1">And/Or</SPAN></td></tr>
<tr>
<td>Query by Year:</td>
<td><input type="text" name="YR" size="20"></td>
<td><input type="submit" value="Submit"></td>
</tr>
</form>
</tr>
</table>

</BODY>
</HTML>

This is the code on the page that queries the DB and displays the results -

<HTML><HEAD> <TITLE>The Picture House</TITLE></HEAD>
<BODY bgcolor=#FFFFCC>

<CENTER><H1>The Picture House<BR></H1><h2>Managing Director</H2></CENTER>
<P>Results for query 'The Picture House LTD' by DVD_Catalogue<BR>
Back to <A HREF="LINK">Main Menu</A><BR>
Back to <A HREF="LINK">Query Menu</A><BR><BR>

<%
dim CAT
dim AGE
dim YR
Dim objConnection
Dim objRecordset
CAT=Request.Form("CAT")
AGE=Request.Form("AGE")
YR=Request.Form("YR")

Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection 
.ConnectionString = "Provider=MSDAORA.1;Password=****;User ID=****;Data Source=stora;Persist Security Info=True"
.Open 
sql="select * from DVD_CATALOG where"

if (CAT<>"") then
sql=sql & " category = '" & CAT & "' AND"
end if

if (AGE<>"") then
sql=sql & " age_restriction = '" & AGE & "' AND"
end if

if (YR<>"") then
sql=sql & " year = " & YR & " AND"
end if

If right(sql,4)="AND" then
sql=left(sql,len(sql)-3) 
End if

Set objRecordset = .Execute(sql)
%>

<table border="2" cellspacing="1" id="AutoNumber1" align="center" cellpadding="2" bordercolorlight="#808080">
<tr>
<td><B>CATALOG_NO</B></td>
<td><B>TITLE</B></td>
<td><B>CATEGORY</B></td>
<td><B>DAILY_RENTAL_PRICE</B></td>
<td><B>PRICE_TO_BUY</B></td>
<td><B>AGE_RESTRICTION</B></td>
<td><B>YEAR</B></td>
</tr> 
<% Do while (Not objRecordset.eof)%>
<tr>
<td><%=objRecordset.Fields("CATALOG_NO")%>&nbsp;</td>
<td><%=objRecordset.Fields("TITLE")%>&nbsp;</td>
<td><%=objRecordset.Fields("CATEGORY")%>&nbsp;</td>
<td><%=objRecordset.Fields("DAILY_RENTAL_PRICE")%>&nbsp;</td>
<td><%=objRecordset.Fields("PRICE_TO_BUY")%>&nbsp;</td>
<td><%=objRecordset.Fields("AGE_RESTRICTION")%>&nbsp;</td>
<td><%=objRecordset.Fields("YEAR")%>&nbsp;</td>
</tr> 
<% objRecordset.MoveNext 
Loop 
End With%>
</table>

<% Set objConnection = Nothing 
Set objRecordset = Nothing 
End If
%>

</BODY>
</HTML>

I know its not exactly great code.
Thanks for the help.

 

[Lothario]

I dont see anything wrong with the code...

is your second file named Cataloguequery2.asp

and also go to Internet Explorer options and uncheck "Show friendly Http error messages " box to see the exact error message.

-L

 

[coolskater49]

Yup, i keep checking the file names to make sure ive got it correct and it is called that. The error message i get is -

Microsoft VBScript compilation error '800a0400' 

Expected statement 

/student/bq219030/DWDS ASP/Cataloguequery2.asp, line 71 

End If
^

Im not sure if this END IF is needed or not, so i deleted, and tested it again and the HTML displayed (i.e background color, and bits of text) but where the ASP should display the DB table i get the following error -

Microsoft OLE DB Provider for Oracle error '80040e14' 

ORA-00921: unexpected end of SQL command 

/student/bq219030/DWDS ASP/Cataloguequery2.asp, line 41

Hope thats not too confusing.

 

[Lothario]

Do a Response.Write sql before this line:

Set objRecordset = .Execute(sql)


-L

 

[coolskater49]

ahhhh....got it now!
The problem was with the

If right(sql,4)="AND" then
sql=left(sql,len(sql)-3) 
End if

For some reason it was not deleting the AND off the end of the SQL statement.
So i just deleted that code and replaced it with

sql=sql & " catalog_no is not null"

Which is an argument that will not affect the outcome of the query as catalog_no is the primary key, and so will always be not null.

Thanks a lot for your help!!
Much Appreciated.

 

[Lothario]

I am glad that you got it working...

Thanks for the star

-L

 

[Genimuse]

I also suggest that in IE you choose Tools->Internet Options, choose the Advanced tab, and uncheck "Show friendly HTTP error messages".

You'll get a lot more information about your errors, including a line number.

 

[Lothario]

Genimuse,

I already suggested that

thanks

-L

 

[Genimuse]

Ah, sorry, missed it.