Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

coolskater49 (TechnicalUser) (OP)
29 Nov 04 10:19
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 -

Quote:

<%
....
....
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.


Quote:

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
Helpful Member!  Lothario (Programmer)
29 Nov 04 10:57
With many fields try using something like this instead of the If else structure you are using:

Sample code...Taken from previous threads

CODE

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 (TechnicalUser) (OP)
29 Nov 04 11:50
Thanks for your reply, i have tried your suggestion and i am now using the following code, but it still does not work.

Quote:

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 (Programmer)
29 Nov 04 11:56
Try this:

CODE

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 (TechnicalUser) (OP)
29 Nov 04 12:11
All i get when i submit the data from the previous form is

Quote:

The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.
Lothario (Programmer)
29 Nov 04 12:23
Post your complete code...

-L
coolskater49 (TechnicalUser) (OP)
29 Nov 04 12:39
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)-

CODE

<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 -

CODE

<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 (Programmer)
29 Nov 04 12:45
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 (TechnicalUser) (OP)
29 Nov 04 13:03
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 -

CODE

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 -

CODE

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 (Programmer)
29 Nov 04 13:09
Do a Response.Write sql before this line:

Set objRecordset = .Execute(sql)


-L

coolskater49 (TechnicalUser) (OP)
29 Nov 04 13:17
ahhhh....got it now!
The problem was with the

CODE

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

CODE

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 (Programmer)
29 Nov 04 13:18
I am glad that you got it working...

Thanks for the star

-L
Genimuse (Programmer)
29 Nov 04 14:24
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 (Programmer)
29 Nov 04 14:43
Genimuse,

I already suggested that

thanks

-L
Genimuse (Programmer)
29 Nov 04 17:53
Ah, sorry, missed it.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close