JSP result page in dreamweaver MX using Access sql PROBLEM??

You made that Query in Access and copied to your JSP?
Can you post the code for the connection?
Javier

yes i did build the query in Access and then i tried to modify it for the
jsp, it does not give an error it just returns no data!!

here is the code
thanks

<%@ include file="Connections/BillboardCompany.jsp" %>
<%
String rsSearchRes__varSiteLocation = "M1 Motorway";
if (request.getParameter("SiteName") !=null) {rsSearchRes__varSiteLocation = (String)request.getParameter("SiteName");}
%>
<%
String rsSearchRes__varCycleID = "8";
if (request.getParameter("SelectCyc") !=null) {rsSearchRes__varCycleID = (String)request.getParameter("SelectCyc");}
%>
<%
String rsSearchRes__varSelectYear = "2004";
if (request.getParameter("SelectYear") !=null) {rsSearchRes__varSelectYear = (String)request.getParameter("SelectYear");}
%>
<%
Driver DriverrsSearchRes = (Driver)Class.forName(MM_BillboardCompany_DRIVER).newInstance();
Connection ConnrsSearchRes = DriverManager.getConnection(MM_BillboardCompany_STRING,MM_BillboardCompany_USERNAME,MM_BillboardCompany_PASSWORD);
PreparedStatement StatementrsSearchRes = ConnrsSearchRes.prepareStatement("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd, SiteBookings.Price, SiteBookings.BookYear  FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID  WHERE (((SiteInformation.SiteLocation) Like '" + rsSearchRes__varSiteLocation + "') AND ((SiteBookings.CycleID) Like '" + rsSearchRes__varCycleID + "') AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear) Like '" + rsSearchRes__varSelectYear + "') AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));");
ResultSet rsSearchRes = StatementrsSearchRes.executeQuery();
boolean rsSearchRes_isEmpty = !rsSearchRes.next();
boolean rsSearchRes_hasData = !rsSearchRes_isEmpty;
Object rsSearchRes_data;
int rsSearchRes_numRows = 0;
%>

any ideas jarasa?
gaz

Sorry I was on a meeting.

If the query works on access it should do on your JSP as it, is strange, I suppose that you have tried to make a easier query on the same statement?

Javier

I think that as you're not catching any exceptions that's the reaon you're not getting any errors, have you take a look at the Logs?

Javier

this is probably closer to what it should be
i just read that wildcards cannot be used in access with numbers so
this query works in Access but when i change CycleID value to the varCycleID
AND ((SiteBookings.CycleID)= varCycleID) for the JSP it doesn't work

i know that the syntax is wrong but i don't know how to rectify it???


SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd, SiteBookings.Price, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.ProductName, SiteBookings.ProductCategory, SiteBookings.CampaignRef, SiteInformation.Blocked, SiteBookings.BookDate
FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID
WHERE (((SiteInformation.SiteLocation) Like "*" & [varSiteLocation] & "*") AND ((SiteBookings.CycleID)= varCycleID) AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear) Like "*" & [varSelectYear ] & "*") AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));

 "If the query works on access it should do on your JSP as it, is strange, I suppose that you have tried to make a easier query on the same statement?"

sorry, i am not sure what you mean by this??
gaz

Well I ment if you try to put in that statemen just "SELECT * FROM SiteInformation" just in case it wont work and see if the problem is on the connection, as I told you you're not putting the statemen inside a try {} catch so is not giving you any Exception, but maybe you can find that Exception printed on the LOGS.

with the last post i get an error in dreamweaver that says that there are too few parameters
3 expected??
any further ideas??
gaz
p.s. i will check the log asap
and get back asap
cheers
gaz

Is SiteBookings.CycleID on your table NUMERIC??

can't find the exception on the tomcat log cos it doesn't display a file for this operation
i think that it must just be the sql and how to integrate it with the jsp
if as you say, the access sql should work in dreamweaver JSP then i think that the sql syntax is the problem
but u r the expert and i am just guessing
Gaz

yes SiteBookings.CycleID is a numeric data type

"i just read that wildcards cannot be used in access with numbers so
this query works in Access but when i change CycleID value to the varCycleID
AND ((SiteBookings.CycleID)= varCycleID) for the JSP it doesn't work"

however, i am not using the wildcard in the sql above
??

Try to execute the "SELECT * FROM SiteInformation" to see what happens.

I have to go, if you're in a hurry on this I'll try to connect from home and keep helping, is strange that no other Experts have got in yet.

Anyway Wich TOMCAT logs have you checked?

Javier

the work logs
for my application
i am new to all of this

doesn't  "SELECT * FROM SiteInformation" just get all the fields from site information
do you mean to just test the * wildcard
gaz

Yup but then you must make the varCycleID as an Integer not as string, try this:

<%
Integer rsSearchRes__varCycleID = 8;
if (request.getParameter("SelectCyc") !=null) {rsSearchRes__varCycleID = new Integer (request.getParameter("SelectCyc"));}
%>

>do you mean to just test the * wildcard

No I just mean to test that connection is working properly if you make that SQL it should return something, if it doesn't then you have a problem with the connection.

Javier

sorry,
the connection is fine
this is one of many recordsets that i have generated, they were much less complex
i'm sorry, i must have forgot to mention that it was returning "no data"
so the connection was working
that is why i think that it is the sql!
gaz

right i am going to as a stupid question
the number below is just the default number is a real search parameter is not parsed right???
that is how i set it up in dreamweaver
so essentially this : Integer rsSearchRes__varCycleID = 8; converts the SelectCyc string value to Integer for the
query to run???????? is that correct????
if so i need to keep an eye out for this in future!!

<%
String rsSearchRes__varCycleID = "10";
if (request.getParameter("SelectCyc")  !=null) {rsSearchRes__varCycleID = (String)request.getParameter("SelectCyc") ;}
%>

<%
Integer rsSearchRes__varCycleID = 8;
if (request.getParameter("SelectCyc") !=null) {rsSearchRes__varCycleID = new Integer (request.getParameter("SelectCyc"));}
%>

right i think that i have figured out the problem
the dropdown list that i use in the searchpage to send the CycleID value to the request.getParameter("SelectCyc") and the sql for the result page is a string value i need it to be a number for the database to recognise it
i am not sure how to fix this??
Gaz

Hi Gaz.

It does not matter how it is the value of the dropdown list, by ref all the parameter sent to the server are treated as Strings, so you have to convert it to Integer once you got it there, I forgot to tell you the you have to change one more thin on the query that is:

 AND ((SiteBookings.CycleID) Like '" + rsSearchRes__varCycleID.parseInt() + "')

You have to convert the Integer to Int so it won't crash on the query.

I'm now at home but I really can't help you much until tomorrow, I have to do things :c(

Take a while with this, ok?

>query to run???????? is that correct????
>if so i need to keep an eye out for this in future!!

Yes you must take a BIG EYE on that for the future.

:c))

Javier

cheers for the help so far javier
i tried what you said but it doesn't work yet

SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd, SiteBookings.Price, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.ProductName, SiteBookings.ProductCategory, SiteBookings.CampaignRef, SiteInformation.Blocked, SiteBookings.BookDate
FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID
WHERE (((SiteInformation.SiteLocation) Like "*" & [varSiteLocation] & "*")

AND ((SiteBookings.CycleID) Like '" + rsSearchRes__varCycleID.parseInt() + "') AND ((CDate([CycleStartDate] & [BookYear]))>Now())


AND ((SiteBookings.BookYear) Like "*" & [varSelectYear ] & "*") AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));

I'm thinking another way.
instead of making such a long query in jsp, would you consider to prepare you query just in MSAccess, and let your jsp get the result from Access Query object?
that way, you can easily test your query in MSAccess, a much better interface. then simple use it in jsp.

BTW
>> I told you you're not putting the statemen inside a try {} catch so is not giving you any Exception,
the exception will be through regardless of whether there is a try catch block.

As I have suggested earlier you'll find it a lot easier to locate sql problems (and avoid many others) if you use a PreparedStatement :)

hey kennethxu
how exactly can i implement what you suggested?

objects, cheers for the advice

i will take it on board and get back to you guys

gaz

>> how exactly can i implement what you suggested?
OK, put it that way. in MSAccess, you have Tables, Queries, Forms, Reports and etc. I'm sure you know how to use the Queries because I believe the query you posted above was initially generated from Access Queries.
My advice was instead of repeat the same query again in jsp page, why don't you simple let you jsp call the Query object that you have created in the MSAccess. If you need to pass in parameters, you can define parameter in your Query object as well.

let me know if you have further enquires.

> how exactly can i implement what you suggested?

You need to use a CallableStatement:

CallableStatement cs = con.prepareCall("{call MyQueury(?, ?)}");
cs.setObject(1, arg1);
cs.setObject(2, arg2);
ResultSet rs = cs.executeQuery();
...

>As I have suggested earlier you'll find it a lot easier to locate sql problems (and avoid many others) if you use a >PreparedStatement :)

When you did that Objects, in other question? Gaz is using preparedStatement here anyway, but not setting the Parameters tho.

Gaz I think Kenneth is right, I shoulded noticed that before, you can creat that Query on Access an call it the way Objects posted, that should work.

>the exception will be through regardless of whether there is a try catch block.
I know that the Exception wil be through, but wil it appear on the page or just in the Logs, I thought it was just in the logs, that's why I suggested to look there.

Javier

> Gaz is using preparedStatement here anyway, but not setting the Parameters tho.

true, but you don't really get all the benefit without using parameters.

Yup!!

:c)

>> CallableStatement cs = con.prepareCall("{call MyQueury(?, ?)}");
>> cs.setObject(1, arg1);
>> cs.setObject(2, arg2);
>> ResultSet rs = cs.executeQuery();
isnt' that basically the samething that I have suggested in another Gar04's Q?
https://www.experts-exchange.com/questions/20926152/Access-Parameter-querys-in-DMX-with-JSP.html

hey guys sorry i forgot about that thread all together cos i was working on something else, i will examine it more and see if i can figure it out in the mean time,  I have created a new query object in Access for this particular query and have called it SiteSearchQ    (by the way sorry about the long sentences but my return key is bust)                                                                                                                                                                        so i need to do this:  CallableStatement cs = con.prepareCall("SiteSearchQ(?, ?, ?)");>> not sure about the query call???
cs.setObject(1, arg1); >>>>>>>>>>>>>>>> is this where i put my variable name varSiteLocation = request.getParameter("SiteName")
cs.setObject(2, arg2); >>>>>>>>>>>>>>>> is this where i put my variable name varSelectCyc = request.getParameter("SelectCyc")                                  
cs.setObject(3, arg3); >>>>>>>>>>>>>>>> is this where i put my variable name varSelectYear= request.getParameter("SelectYear")                                               ResultSet rs = cs.executeQuery();                             I am not farmiliar with this CallableStatement thingy?? is it related to stored procedures???              if so how do i code it correctly and does it matter what way the Access SQL looks for Parameters like:        SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd, SiteBookings.Price
FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID
WHERE (((SiteInformation.SiteLocation) Like "*" & [Enter Location Name : ] & "*") AND ((SiteBookings.CycleID) Like "*" & [ Enter CycleID 1-26: ] & "*") AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear) Like "*" & [ Enter Year 2004 or 2005: ] & "*") AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));
                               thanks again       Gaz                                                    

is this in any way correct:                                                                                                                                            <%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillboardCompany");
  Callable Statement cs = conn.preparedCall("{SiteSearchQ(?, ?, ?)}");
  cs.setObject(1, request.getParameter("SiteName")); cs.setObject(2, request.getParameter("SelectCyc")); cs.setObject(3, request.getParameter("SelectYear"));
  ResultSet rs = cs.executeQuery();
  while (rs.next())
  {
    >>>>> not sure what to put here???
     
   
  }
}
%>

hey, i have just read that mySQL and Access do not support stored procedures??? this isn't related to stored procedures is it?? it is just a query object not supposed to be a stored procedure???   pls help gaz

I think it has nothing in common, an stored procedure is a program that is executed on the DB and give a result hehe the QueryObject too hehehehe, well, they have something in common!!, anyway I don't think that it matters.

while (rs.next())  {
>>>>> not sure what to put here???

     Here you get the result of the query, the Data you were expecting :c)
   
 }

Javier

is the code above correct, because i am not sure if it is
by the way thank you for replying javier

i don't know very much about Callable Statements and i cannot find a good example online
do you think that i am coding it right cos i cannot test this yet

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
conn = DriverManager.getConnection("jdbc:odbc:BillboardCompany");
Callable Statement cs = conn.preparedCall("{SiteSearchQ(?, ?, ?)}");
cs.setObject(1, request.getParameter("SiteName"));
cs.setObject(2, request.getParameter("SelectCyc"));
cs.setObject(3, request.getParameter("SelectYear"));
ResultSet rs = cs.executeQuery();
  while (rs.next())
  {
    >>>>> not sure what to put here???

     
   
  }
}
%>

while (rs.next())
  {
    >>>>> not sure what to put here???
>>>>>>>> how do i get the results from the query?
i.e. each column result from the query so that i can put them in a table???

  }
}

1. you are missing a call keyword.
2. there is no space bettween Callable and Statement.
   CallableStatement cs = conn.preparedCall("{Call SiteSearchQ(?, ?, ?)}");
3. you need to use setInt ro setString depend on the parameter type you defined in Access Query.
   cs.setString(1, request.getParameter("SiteName"));
   cs.setString(2, request.getParameter("SelectCyc"));
   cs.setString(3, request.getParameter("SelectYear"));
or maybe
  int year=Integer.parseInt( request.getParameter("SelectYear"));
  cs.setInt(3, year);

in the while( rs.next() ), do whatever you normally do for a resultset.

is this more correct??

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
conn = DriverManager.getConnection("jdbc:odbc:BillboardCompany");
CallableStatement cs = conn.preparedCall("{call SiteSearchQ(?, ?, ?)}");
cs.setString(1, request.getParameter("SiteName"));
cs.setString(2, request.getParameter("SelectCyc"));
cs.setString(3, request.getParameter("SelectYear")); >>>>i changed the datatypes in the db to text to make the whole thing easier>>>>>>>>>>
ResultSet rs = cs.executeQuery();
  while (rs.next())
  {
    here i just create a table and use a repeated region to show data
     
   
  }
}
%>

looks good to me :-)

just to make sure, when you run the query in msaccess. it prompt you for parameters in the order of sitename, selectcyc and selectyear, and you do get result after you supplyed it with correct value. is it right?

yes the query in access works

the only thing is that the names
SiteName
SelectCyc and SelectYear are not the names of the query parameters
they are the names of the three select form objects sent to the results jsp page from the site search jsp page
will cause problems???

>> will cause problems???
no! they go by position, like 1,2,3. that's why I asked you the order.

cool
u r a god amongst men!!!
i will get back to you as soon as i implement this
cheers again
gaz

i doubled checked something and realised that i had to change BookYear datatype in my DB table
back to a Number from Text
i changed the code like you said, below is it ok cos SelectYear is a string value initially???

<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillboardCompany");
  CallableStatement cs = conn.preparedCall("{Call SiteSearchQ(?, ?, ?)}");
  cs.setString(1, request.getParameter("SiteName"));
  cs.setString(2, request.getParameter("SelectCyc"));
  int year=Integer.parseInt( request.getParameter("SelectYear")); >>>>>>>>>>>>>>>>>>>>>>>>>>>>
  cs.setInt(3, year);>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  ResultSet rs = cs.executeQuery();
  while (rs.next())
  {
   

looks good!

BTW, you should can specify parameter order and type in msaccess query by going to design view, right click on titlebar and select parameters.

> CallableStatement cs = conn.preparedCall("{Call SiteSearchQ(?, ?, ?)}");

That should be:

CallableStatement cs = conn.prepareCall("{Call SiteSearchQ(?, ?, ?)}");

hey Kennethxu
is there any chance you can tell me what i am doing wrong
here, with the display of the data in a table
here is my code below and below that is the error messages it seems to be having problems with rs and cs??:
btw, thanks for the help


<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillboardCompany");
  CallableStatement cs = conn.prepareCall("{Call SiteSearchQ(?, ?, ?)}");
  cs.setString(1, request.getParameter("SiteName"));
  cs.setString(2, request.getParameter("SelectCyc"));
  int year=Integer.parseInt( request.getParameter("SelectYear"));
  cs.setInt(3, year);
  ResultSet rs = cs.executeQuery();
 }catch (Exception e) {out.print("Exception: " + e); }
%>

  <%   while (rs.next())
  {
    %>
      <table width="100%" height="63" border="1" cellpadding="0" cellspacing="0" bordercolor="#006699">
        <tr bordercolor="#0033CC">
          <td width="16%" height="20"> <div align="center"><font color="#000000" size="2">Site
              Location </font></div></td>
          <td width="10%"> <div align="center"><font color="#000000" size="2">Site
              Format</font></div></td>
          <td width="9%"><div align="center"><font color="#000000" size="2">Prismatic</font></div></td>
          <td width="10%"><div align="center"><font color="#000000" size="2">Illuminated</font></div></td>
          <td width="9%"><div align="center"><font color="#000000" size="2">CycleID</font></div></td>
          <td width="10%"><div align="center"><font color="#000000" size="2">CycleStart</font></div></td>
          <td width="9%"><div align="center"><font color="#000000" size="2">CycleEnd</font></div></td>
          <td colspan="2">&nbsp;</td>
          <td width="11%">&nbsp;</td>
        </tr>
        <tr bordercolor="#99CCFF">
          <td height="20"><% rs.getString("SiteLocation") %></td>     ?????????????? i get errors here because of the formatting
          <td><% rs.getString ("SiteType"); %></td>                                     can you expain how to out put the data correctly
                                                                                                                   as i can find no good examples!!!
          <td><% rs.getString ("Prismatic") %></td>
          <td><% rs.getString ("Illuminated") %></td>
          <td><% rs.getString("CycleID") %></td>
          <td><% rs.getString ("CycleStart") %></td>
          <td><% rs.getString ("CycleEnd") %></td>
          <td colspan="2">&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr bordercolor="#99CCFF">
          <td colspan="10">&nbsp;</td>
        </tr>
        <%}%>


ERROR
HTTP Status 500 -

--------------------------------------------------------------------------------

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 141 in the jsp file: /SearchRes.jsp

Generated servlet error:
    [javac] Compiling 1 source file

C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:211: not a statement
 rs.getString("SiteType") ; " "
                            ^



An error occurred at line: 141 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:211: ';' expected
 rs.getString("SiteType") ; " "
                               ^



An error occurred at line: 1 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:49: cs is already defined in _jspService(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
  CallableStatement cs = conn.prepareCall("{Call SiteSearchQ(?, ?, ?)}");
                    ^



An error occurred at line: 122 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:153: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
   while (rs.next())
          ^



An error occurred at line: 140 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:208: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
 rs.getString("SiteLocation") ;  
 ^



An error occurred at line: 141 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:211: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
 rs.getString("SiteType") ; " "
 ^



An error occurred at line: 142 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:214: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
 rs.getString ("Prismatic") ;  
 ^



An error occurred at line: 143 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:217: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
 rs.getString ("Illuminated") ;  
 ^



An error occurred at line: 144 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:220: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
 rs.getString ("CycleID") ;  
 ^



An error occurred at line: 145 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:223: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
 rs.getString ("CycleStart") ;  
 ^



An error occurred at line: 146 in the jsp file: /SearchRes.jsp

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\SearchRes_jsp.java:226: cannot resolve symbol
symbol  : variable rs  
location: class org.apache.jsp.SearchRes_jsp
 rs.getString ("CycleEnd") ;  
 ^
11 errors


      at org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:130)
      at org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:293)

1. you defined the cs twice, remove the word CallableStatment.
2. you define cs in the try block and use it outside of it.

hey thanks for the help
right now the browser returns the jsp page but it doesn't contain any data???

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
 <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillboardCompany");
  cs = conn.prepareCall("{Call SiteSearchQ(?, ?, ?)}");
  cs.setString(1, request.getParameter("SiteName"));
  cs.setString(2, request.getParameter("SelectCyc"));
  int year=Integer.parseInt( request.getParameter("SelectYear"));
  cs.setInt(3, year);
  ResultSet rs = cs.executeQuery();
  %>


      <% while (rs.next())
  { %>
        <tr bordercolor="#99CCFF">
          <td height="20"><% rs.getString("SiteLocation");%></td>
          <td width="60"><% rs.getString("SiteType"); %></td>
          <td width="18"><% rs.getString ("Prismatic");%></td>
          <td width="18"><% rs.getString ("Illuminated");%></td>
          <td width="18"><% rs.getString ("CycleID");%></td>
          <td width="18"><% rs.getString ("CycleStart");%></td>
          <td width="18"><% rs.getString ("CycleEnd");%></td>
          <td colspan="2">&nbsp;</td>
          <td width="1">&nbsp;</td>
        </tr>
            <%}%>
        <tr bordercolor="#99CCFF">
          <td colspan="10">&nbsp;</td>
        </tr>
     <% }
    catch (Exception e)
    {
     out.print("Exception: " + e);
    }
    finally
    {
     if( cs != null ) try { cs.close(); } catch( Throwable t ) { t.printStackTrace(); }
     if( conn != null ) try { conn.close(); } catch( Throwable t ) { t.printStackTrace(); }
    }
%>
 

i just thought of something
in the DB these are :


           <td width="18"><% rs.getString ("Prismatic");%></td>        is a boolean value in the DB
          <td width="18"><% rs.getString ("Illuminated");%></td>       is a boolean value in the db
          <td width="18"><% rs.getString ("CycleStart");%></td>        is converted to a date value in the db query SiteSearchQ
          <td width="18"><% rs.getString ("CycleEnd");%></td>         is converted to a date value in the db query SiteSearchQ

i guess this would cause bother??????

the boolean and date value returned will be converted to string when you use getString.

I'm a little worried about the parameters that you have passed in. is SelectCyc supposed to be a date?

also, I would try:

  cs.setString(1, "hardcode a name here that works in msaccss");
  cs.setString(2, "similar to above");
  cs.setInt(3, 2003);

right
so what do you think that the problem is??
could it have something to do with the lack of a repeated region?

cheers
Gaz

just to test if you get anything back with hardcode parameter value that you know works in msaccess.

can u post the actual html of the returned page.
the lats posting didn't actually include the <table> tag.
And one earlier had the <table> tag inside the loop.

this is the entire Page:


<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
 <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillboardCompany");
  cs = conn.prepareCall("{Call SiteSearchQ(?, ?, ?)}");
  cs.setString(1, request.getParameter("SiteName"));
  cs.setString(2, request.getParameter("SelectCyc"));
  int year=Integer.parseInt( request.getParameter("SelectYear"));
  cs.setInt(3, year);
  ResultSet rs = cs.executeQuery();
  %>
      
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SearchRes</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--


function mmLoadMenus() {
  if (window.mm_menu_1_0) return;
      window.mm_menu_1_0 = new Menu("root",181,16,"Times New Roman, Times, serif",10,"#000000","#FFFFFF","#FFFFFF","#000084","center","middle",1,0,500,-5,7,true,true,true,0,true,true);
  mm_menu_1_0.addMenuItem("48 Sheet Billboard Format");
  mm_menu_1_0.addMenuItem("6 Sheet Billboard Format");
  mm_menu_1_0.addMenuItem("Shopperlite");
  mm_menu_1_0.addMenuItem("Commuterlites");
   mm_menu_1_0.hideOnMouseOut=true;
   mm_menu_1_0.bgColor='#336666';
   mm_menu_1_0.menuBorder=1;
   mm_menu_1_0.menuLiteBgColor='#336666';
   mm_menu_1_0.menuBorderBgColor='#000000';
  window.mm_menu_0309133610_0 = new Menu("root",124,16,"Times New Roman, Times, serif",10,"#000000","#FFFFFF","#FFFFFF","#000084","center","middle",1,0,500,-5,7,true,true,true,0,true,true);
  mm_menu_0309133610_0.addMenuItem("Belfast&nbsp;City&nbsp;Area");
  mm_menu_0309133610_0.addMenuItem("Northern&nbsp;Ireland");
   mm_menu_0309133610_0.hideOnMouseOut=true;
   mm_menu_0309133610_0.bgColor='#336666';
   mm_menu_0309133610_0.menuBorder=1;
   mm_menu_0309133610_0.menuLiteBgColor='#336666';
   mm_menu_0309133610_0.menuBorderBgColor='#000000';

 
  mm_menu_0309133610_0.writeMenus();
} // mmLoadMenus()
<!--

function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}

//-->//-->
</script>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
<script language="JavaScript" src="mm_menu.js"></script>
</head>
<body>
<script language="JavaScript1.2">mmLoadMenus();</script>
<table width="90%" border="1" align="center" cellpadding="0" cellspacing="1" bgcolor="#FFFFFF">
  <!--DWLayoutTable-->
  <tr>
    <td width="91" height="123" rowspan="2" align="center" valign="bottom" bgcolor="#FFFFFF">
      <div align="left"></div>
      <div align="right"></div>
      <div align="right"><img src="graphic.gif" width="90" height="123"></div></td>
    <td width="774" height="42" align="left" valign="bottom" bgcolor="#FFFFFF"><img src="topnavbar/toplogout.gif" width="600" height="40"></td>
    <td width="3" height="42" align="left" valign="bottom" bgcolor="#FFFFFF"><!--DWLayoutEmptyCell-->&nbsp;</td>
  </tr>
  <tr>
    <td height="73" align="left" valign="bottom" bgcolor="#FFFFFF"><img src="banner.gif" width="600" height="80"></td>
    <td align="left" valign="bottom" bgcolor="#FFFFFF"><!--DWLayoutEmptyCell-->&nbsp;</td>
  </tr>
  <tr>
    <td width="91" align="center" valign="bottom" bgcolor="#FFFFFF"><table width="100%" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
      </table></td>
    <td colspan="2" align="center" valign="top">
      <table width="100%" height="63" border="1" cellpadding="0" cellspacing="0" bordercolor="#006699">
        <tr bordercolor="#0033CC">
          <td width="16%" height="20"> <div align="center"><font color="#000000" size="2">Site
              Location </font></div></td>
          <td width="10%"> <div align="center"><font color="#000000" size="2">Site
              Format</font></div></td>
          <td width="9%"><div align="center"><font color="#000000" size="2">Prismatic</font></div></td>
          <td width="10%"><div align="center"><font color="#000000" size="2">Illuminated</font></div></td>
          <td width="9%"><div align="center"><font color="#000000" size="2">CycleID</font></div></td>
          <td width="10%"><div align="center"><font color="#000000" size="2">CycleStart</font></div></td>
          <td width="9%"><div align="center"><font color="#000000" size="2">CycleEnd</font></div></td>
          <td colspan="2">&nbsp;</td>
          <td width="11%">&nbsp;</td>
        </tr>
            <% while (rs.next())
  { %>
        <tr bordercolor="#99CCFF">
          <td height="20"><font color="#000000" size="2"><% rs.getString("SiteLocation");%></font></td>
          <td width="60"><font color="#000000" size="2"><% rs.getString("SiteType"); %></font></td>
          <td width="18"><font color="#000000" size="2"><% rs.getString ("Prismatic");%></font></td>
          <td width="18"><font color="#000000" size="2"><% rs.getString ("Illuminated");%></font></td>
          <td width="18"><font color="#000000" size="2"><% rs.getString ("CycleID");%></font></td>
          <td width="18"><font color="#000000" size="2"><% rs.getString ("CycleStart");%></font></td>
          <td width="18"><font color="#000000" size="2"><% rs.getString ("CycleEnd");%></font></td>
          <td colspan="2">&nbsp;</td>
          <td width="1">&nbsp;</td>
        </tr>
            <%}%>
        <tr bordercolor="#99CCFF">
          <td colspan="10">&nbsp;</td>
        </tr>
     <% }
    catch (Exception e)
    {
     out.print("Exception: " + e);
    }
    finally
    {
     if( cs != null ) try { cs.close(); } catch( Throwable t ) { t.printStackTrace(); }
     if( conn != null ) try { conn.close(); } catch( Throwable t ) { t.printStackTrace(); }
    }
%>
 
                
      </table>
        </td>
  </tr>
  <tr>
    <td height="50" align="center" valign="bottom" bgcolor="#000066">&nbsp;</td>
    <td height="50" colspan="2" align="center" valign="top" bgcolor="#FFFFFF">&nbsp;</td>
  </tr>
</table>
</body>
</html>

gaz, you haven't answer my ealier question :-)

Comment from kennethxu
Date: 03/31/2004 09:45PM PST
 Your Comment  


I'm a little worried about the parameters that you have passed in. is SelectCyc supposed to be a date?

also, I would try:

  cs.setString(1, "hardcode a name here that works in msaccss");
  cs.setString(2, "similar to above");
  cs.setInt(3, 2003);
 

no kennethxu
SelectCyc is a string value in a dropdown list that i created in the searchpage form
i hard coded it
cs.setString(1, "M1 Motorway Belfast");
  cs.setString(2, "25");
  cs.setInt(3, 2004);
but it returned no values,
gaz

When you say the page is empty do you mean totally blank, or just no data returned from query?

if you run the query in msacces, and enter those 3 hardcode values. are they appear in the right order and is the query msaccess returning values.

just no data returned from query

i.e. the table columns are empty
 in the query the CycleStart and CycleEnd are generated by this function CDate([CycleStartDate] & [BookYear])

CycleStartDate is text and BookYear is a number, would this cause problems??? i am unsure of this
cos isn't cs just running the query and getting the results, access has no bother running the query!

gaz

>>>>>>>>>if you run the query in msacces, and enter those 3 hardcode values. are they appear in the right order and is the query msaccess returning values.

yes it sure is ken

this is what the three parameters look like in the query:

Like "*" & [Enter Location Name : ] & "*"
Like "*" & [Enter CycleID Numbers 1 to 26 : ] & "*"
[Enter the Year 2004 0r 2005: ]

no matter how, it is the msaccess that is running the query. to it is important that you test in msaccess first and make sure it returns records.

Like "*" & [Enter Location Name : ] & "*"                         = string
Like "*" & [Enter CycleID Numbers 1 to 26 : ] & "*"          = string
[Enter the Year 2004 0r 2005: ]                                      = number

that is strange. is there anyway you can put up your .mdb file on internet so I can download and test?

or if you cannot, then send it to kennethxu at hotmail.com
anybody else interested please put up email address

i will try right now and get the details to you asap
gaz

can u post the sql for the access query.
and email me your db

just the .mdb is enough for me. if it's too big, then send it to kenneth.xu at jpmorgan.com
I'll have to get off now and will have a look at it tomorrow.

sorry guys
i tried to put it up pn the web
but it wouldn't send to the server, it is 13.5 MB so thats the reason when i zipped it it is still 1.7 MB
too big for email, the web server won't take the upload for some reason, i don't know why it is suppose to have an upload capacity of 5 MB? i will keep trying though!
and last night EE server could not be found after your last post and wouldn't let me log in:

CycleID is a text property

here is the sql:

SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd
FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID
WHERE (((SiteInformation.SiteLocation) Like "*" & [Enter Location Name : ] & "*") AND ((SiteBookings.CycleID) Like "*" & [Enter CycleID Numbers 1 to 26 : ] & "*") AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear)=[Enter the Year 2004 0r 2005: ]) AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));

ok guys
my little 56K modem eventually uploaded the file to
www.geocities.com
login in as
username: evilpixie80
password: spawnblood2
 go to file manager and the b.zip file should be therein, it contains BillBoardCompany.mdb
it is just full of test data and really the bones of a full database

btw, i really appreciate your help
u r great

else you can go directly to

http://www.geocities.com/evilpixie80/
 and you should get the mdb file there

gaz :0)

Sorry guys Couldn't get back here until now.
As Kenneth says is a good idea to get the .mdb file. Gaz you can make a copy of it and delete the tables that won't be used for the question query and also some data so the result will be better, then it will be smaller and meybe you can send it by mail.

On my profile youhave my email if you want to send it over.

Javier

Gaz pls do not put your U/P here you never know who can see it and use it for?

Kenneth I don't know if you know how the admin can delete that post but I think it would be appropiated.

Javier

P.S.: I got the file I'll check it out.

He he I knew this... :c(

Wich Access version did you used Gaz??

I got Access97 and does not let me open it, I'll try at home I think I got Access 2000 there.

Javier

don't worry about the U/P i never use it
i just created it for this purposes
gaz
:)

Well Gaz is a matter that someone could use it to hold there some Illegal stuff and you'll get blamed.

I can't open your DB here but I'll try at home and let you know, ok?

Javier

sure i can close it down after this right??

The account?
Well just change the P.

it is the oddest thing
when the return page i.e. results page comes back it looks like
it has moved the width of the table columns which would indicate that perhaps it is returning data but that it can't be displayed???
could that happen??
gaz

gaz, when I tried enter "M1 Motorway Belfast", 25 and 2004 in the MSAccess query object, it returns no record too!
that means query you created in msaccess is not correct!

you should always test the query in msaccess first and make sure it works.

OK, I found that there is actually no record matches you search criteria. you query and code are all correct. try hard code this and you should get 2 records.

  cs.setString(1, "M1");
  cs.setString(2, "15");
  cs.setInt(3, 2004);

in addition, you should also check if user didn't put anything in the search field, so you need to give a default:

  String siteName = request.getParameter("SiteName");
  if( siteName == null ) siteName = "";
  String selectCyc= request.getParameter("SelectCyc");
  if( selectCyc== null ) selectCyc= "";
  int year= 2004;
  try{ year = Integer.parseInt( request.getParameter("SelectYear")); } catch(Exception e ){}

  cs.setString(1, siteName );
  cs.setString(2, selectCyc );
  cs.setInt(3, year);

i tried the new search in hardcode you suggested
however, the data is still not being displayed
funny though i think that the data is there i just can't see it
well, that is what i think, it just seems that the table is two rows wide
i have the font set to black and the background white
i don't understand
gaz
btw thanks for the help

Hi Gaz.
Can you post the code you wrote inside the while (rs.next() ){}
Javier

>>>>>>>>you should always test the query in msaccess first and make sure it works.

i did many many times
that data should be there in fact it is in the database these are the results from the query???
i promise

BookingsNo      SiteID      SiteLocation      SiteType      Prismatic      Illuminated      CycleID      CycleStart      CycleEnd
2079      1      M1 Motorway Belfast      48 sheet      No      No      25      03/12/2004      16/12/2004

here is the while loop code

 <% while (rs.next())
  { %>
        <tr bordercolor="#99CCFF">
          <td height="20" bgcolor="#FFFFFF"><font color="#000000" size="2">
            <% rs.getString("SiteLocation");%>
            </font></td>
          <td width="60" bgcolor="#FFFFFF"><font color="#000000" size="2">
            <% rs.getString("SiteType"); %>
            </font></td>
          <td width="18" bgcolor="#FFFFFF"><font color="#000000" size="2">
            <% rs.getString ("Prismatic");%>
            </font></td>
          <td width="18" bgcolor="#FFFFFF"><font color="#000000" size="2">
            <% rs.getString ("Illuminated");%>
            </font></td>
          <td width="18" bgcolor="#FFFFFF"><font color="#000000" size="2">
            <% rs.getString ("CycleID");%>
            </font></td>
          <td width="18" bgcolor="#FFFFFF"><font color="#000000" size="2">
            <% rs.getString ("CycleStart");%>
            </font></td>
          <td width="18" bgcolor="#FFFFFF"><font color="#000000" size="2">
            <% rs.getString ("CycleEnd");%>
            </font></td>
        </tr>
        <%}%>

OK you have to put <%= rs.getString ("CycleEnd")%> instead of <% rs.getString ("CycleEnd");%>

in all of them
Javier

This <% rs.getString ("CycleEnd");%> returns the value but does not print it to the outputStream.
This <%= rs.getString ("CycleEnd")%> does it or this <% out.println(rs.getString ("CycleEnd"));%> too.

is up to you how you preffer to do it.

;c)

but this is the JSP way <%= rs.getString ("CycleEnd")%>

Javier

ok sorry i didn't even notice that, staring too long and looking in the wrong places

it is working now but the format is a little wonky
thanks a lot guys i really appreciate
i will find a way to give you'll max points
btw i also had ; at the end of each one

wonderful catch, Javier!

>btw i also had ; at the end of each one
Well maybe it works but you don't need it

>wonderful catch, Javier!
Thanks, but I'm sure you've noticed too.

:c)

Javier

P.S.: Gaz if no one tells you about your new question I'll do it tomorrow OK?

i would be very much obliged
cheers