Solved

General Question regarding JSP resultSet use

Posted on 2004-04-01
134
1,779 Views
Last Modified: 2012-06-27
hey
is it possible that when you write to a DB and query it and get results back
and display them in a table that you can have a button or something that then takes those results and sends them to
another page like a form page for booking

here is what i mean
  |searchpage| -------> |searchresultspage|------------------------------> |bookingpage|                                    
has a form for                runs a jsp callablestmt query                            shows the data from the searchresultpage
searching                       on DB and shows results in a table                    in a form as well as addtional fileds to be
                                    has a booknow! button on each row                   filled out, when submit is hit the  additional  
                                    when the user hits the button                            data is stored in the DB
                                     forwards the data to another page with a
                                     form
if this is possible then how cani implement it
i already have the search page and the results page(however it is not displaying results as yet, need to fix it)
the pages are in JSP, the Database is MS Access.

cheers
Gaz
0
Comment
Question by:Gar04
  • 63
  • 23
  • 22
  • +3
134 Comments
 
LVL 6

Expert Comment

by:jarasa
ID: 10734052
Hi Gaz.
Yes is possible, I'll show you how to do it once we get done with the last question. If no one tells you before.

:c)

Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10734090
you can either use a link or form in earch row of the searchresultpage to pass the data:

<% while( rs.next() ) { %>
  <tr>
  <td>you normal table cell data</td>
  ......
  <td>
    <form action="bookingpage">
      <input type=hidden name="field1" value='<%=rs.getString("field1");%>'>
      <input type=hidden name="field2" .....
      ......
      <input type=submit ....>
    </form>
   <td>
  </tr>
<% } %>
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10734093
>> If no one tells you before
:-P
0
 

Author Comment

by:Gar04
ID: 10734257
thanks guys
i will the last question up and running before i delve into this
btw
thanks ever so much
b back soon
Gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10734276
thats quite a possible stuff to do....
depends how you want it implement it.....

1. the way Kenneth is suggesting, create a form or create a URL with all the values to be passed to the second ( or third page) and take it on from there...

It will increase the size of your HTML and will be sending more data between the browser and server.

2. Send only the primary key of the selected record to the third page and then query the database again to get the row back..
will reduce the size of the HTML and browser to server data transfer.

Will increase the load between seb server and data base.. ( work for me whenever my users are on dail up network and my database and web server are sitting real close to each other....

0
 
LVL 6

Expert Comment

by:jarasa
ID: 10734413
>:-P
I knew that "someone" will do it.

Anyway I agree with Kuldeepchaturvedi and I believe is the best way but maybe for Gaz as he is starting on this Kenneth option will be better.

Anyway Tomorrow I'll tell you a nice way with OUTLOOK :c)

Javier
0
 
LVL 92

Expert Comment

by:objects
ID: 10736203
Storing the data in the session saves the need for passing it around in the request, and also requerying the database.
0
 

Author Comment

by:Gar04
ID: 10736266
hey guys
objects :
>>>>>>>>>Storing the data in the session saves the need for passing it around in the request, and also requerying the database.
 does this mean that from the
moment my user logs in i shoud give him/her session scope rather than request scope
and how exactly does that work(briefly) ??

Gaz

0
 
LVL 92

Expert Comment

by:objects
ID: 10736299
No, a session is created for you automatically which you can use to store session wide data.
eg. to store something in session use:

<%
   session.put("data", mydata);
%>

Then to access that data from a subsequent request (same sessiuon) use:

<%
   SomeClass mydata = (SomeClass) session.get("data");
%>
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10736330
Only thig is that you won't be able to store the resultset directly ( which is a interface not a object), so you will have to convert that data .. may be in a vector or in a arraylist..
and then use it in the session and in the table display....
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 10736724
You can always just load the search results into a javaBean and then get them back at any page you want.
If you want to know how to do this let me know.
Ghost
0
 

Author Comment

by:Gar04
ID: 10736906
which do you think would be the easiest way to implement this given my superior lack of knowledge?
 
you can let me know how to do it if you like
all help is appreciated greatly

:0)
gaz
0
 
LVL 92

Expert Comment

by:objects
ID: 10736922
Making a new db request for every request is probably the easiest to implement.
0
 

Author Comment

by:Gar04
ID: 10737025
so objects you think that Kennethxu's way is probably the easiest
so essentially the form is hidden and collects the data then posts it to the bookingpage
but it requires more calls to the db
how exactly is it done??
ideally i would like a button at the end of each record that sends the data to a form on the bookingpage
however, a question would be ...what if the user wanted to book more than one site at a time
how would i account for that??????????


"you can either use a link or form in earch row of the searchresultpage to pass the data:

<% while( rs.next() ) { %>
  <tr>
  <td>you normal table cell data</td>
  ......
  <td>
    <form action="bookingpage">
      <input type=hidden name="field1" value='<%=rs.getString("field1");%>'>
      <input type=hidden name="field2" .....
      ......
      <input type=submit ....>
    </form>
   <td>
  </tr>
<% } %>"
0
 
LVL 92

Expert Comment

by:objects
ID: 10737038
> so objects you think that Kennethxu's way is probably the easiest

No, what Kuldeepchaturvedi suggested.
"2. Send only the primary key of the selected record to the third page and then query the database again to get the row back.."
0
 

Author Comment

by:Gar04
ID: 10737111
ok
well the primary key is a field called BookingsNo
how exactly would i implement this way?
gaz
0
 
LVL 92

Expert Comment

by:objects
ID: 10737165
in your link you would pass the required key:
<a href="nextpage.jsp?id=<%= idvalue %>">

or pass it as a hidden field if a form

then on your next page you would extract that id and make query to db

Object id = resest.getParameter("id");
0
 

Author Comment

by:Gar04
ID: 10737229
i will give it a go and get back to you guys
again thanks for the help
gaz
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10737333
There is a number of ways to pass information down to next jsp page.
it really depend on
1) how much data you are passing down?
2) are those sensitive data?

In most of situation, as I said above, you can use link for hidden field to achieve your goal. Session or singleton (or application scope) can be used for storing relatively large amount of data or cache database result for performace reason. But I guess you don't want to dig into that yet, because they are a little more complicated.

I don't see my suggestion earlier conflicts with Kuldeepchaturvedi's comment. no matter how you got to pass down some data, you cannot do without it, a link or a form.

In conclusion,
1. if you have a few data items (say less then 5) to pass down, just use the hidden fields, that's all you need and you don't need to bother database connection/query again. which actually take time.
2. if you have a lot of data items to pass, then pass the id. but in the next page, you'll have to again open a database connection to query for other data items by id.
0
 
LVL 92

Expert Comment

by:objects
ID: 10737354
Yes but the simplest approach would be to just pass the key data, and make a new db call for each request  rather than fiddling around passing all the required data around in the request.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10737586
I cannot see where the simplest come from. coding? processing?
you still cannot avoid passing data.
in addition, database connection and database query can be otherwise avoid.
is this the simplest?
0
 
LVL 92

Expert Comment

by:objects
ID: 10737606
> you still cannot avoid passing data.

no, but you can certainly minimise it.
Its a lot simpler to implement passing a single field, than it is to implement passing a complete result set.
Also removes the coupling between pages making maintenance easier.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10737971
>> Its a lot simpler to implement passing a single field, than it is to implement passing a complete result set.
a lot? assuming as I said for 5 column resulstset, that's just maximal 10 additional line of html code.
comparing make the database connection code plus end up again you need to read every field from the another new resultset. which one is more simplier?

I'm not here saying that redo requery in next page has no advantage at all. in fact I use it a lot myself. but since we are talking about simplier, it is obviously NO.
0
 
LVL 92

Expert Comment

by:objects
ID: 10738019
> comparing make the database connection code plus end up again you need to read every field from the
> another new resultset. which one is more simplier?

reading from result set is simpler, the code already exists so theres no extra development work involved.

0
 
LVL 6

Expert Comment

by:jarasa
ID: 10739110
This looks gorgeous you don't need any button just click on the row that gets illuminated and will call next JSP I think that as Kenneth says in this case is not to hard to send all the data from the row through the request and is faster to process the next page than passing the PK and requering the DB or Storing the rs on the session and finding it there.

The PK -> DB Solution is good but why should you call and call the DB connections to DB are expensive.
The Session Storing one, well I use session to store a lot of things but The Server calls me by night and say "JAVIER YOU'RE KILLING ME"

second.jsp
<TABLE>
<% while( rs.next() ) { %>
  <tr bgColor='#EEEFF3' onMouseOver="this.bgColor='#B2C4F0';" onMouseOut="this.bgColor='#EEEFF3';" onclick="document.location.href='yourBookingJsp.jsp?FIELD1=<%=rs.getString("field1")%>&FIELD2=<%=rs.getString("field2")%>&FIELDN=<%=rs.getString("fieldN")%>&'">
  <td><%=rs.getString("field1")%>'><td>
  <td><%=rs.getString("field2")%>'><td>
  ........
  <td><%=rs.getString("fieldN")%>'><td>
  </tr>
<% } %>
</TABLE>

third.jsp
<BR>
This is what you booked:
<BR>
FIELD 1: <%= request.getParameter("FIELD1")==null?" - ":request.getParameter("FIELD1") %>
<BR>
FIELD 2: <%= request.getParameter("FIELD2")==null?" - ":request.getParameter("FIELD2") %>
<BR>
FIELD N: <%= request.getParameter("FIELDN")==null?" - ":request.getParameter("FIELDN") %>
<BR>

Javier

P.S.: Gaz what ever you do here, Please this time Split the points. It maked me feel stupid on the last one, not becose of the points but becose whoever that sees the answer will think that all of the rest coments are useless and that's not true.
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10739140
onclick="document.location.href='third.jsp?FIELD1=<%=rs.getString("field1")%>&FIELD2=<%=rs.getString("field2")%>&FIELDN=<%=rs.getString("fieldN")%>';">

Sorry about this.
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10739218
Gaz Please delete this Question http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20940750.html
I don't want the points and you may need them for other question, I'm not going to post any coment on it so just delete it.

Javier

0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10740512
>>>I cannot see where the simplest come from. coding? processing?
I think its simplest from the coding point of view... since he has made is so far, means he knows how to connect to the database..
he can practically cut and paste the DB code from his jsp and put it in the third one..

Second this if at all he decides to changes some thing in the database... i.e. add one more field.. he will not have to touch second.jsp only changes will be on the third.jsp.

The form idea is good but having one form earch row will be making that page DOM heavy as lead.
and whenever I try to build a query string... my string operations kills me ( I am so poor keeping a track of where to put a + sign, a quote; or double qoute....:-))
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10740712
I always agree that passing key is a good and sometimes a must approach. especially when data are sensitive and you don't want user to tap into it by send you fake data through url or changed form.

But if we talk about the simplest, have a look at what Javier have to post.
0
 

Author Comment

by:Gar04
ID: 10741132
javier are you sure???
you did help a lot
0
 

Author Comment

by:Gar04
ID: 10741254
sorry javier
i am not up to speed with the ediquette that you guys practice i EE
i will be more careful next time
it is just that you all help a lot and i feel that it is necessary to reward you all
gaz
0
 

Author Comment

by:Gar04
ID: 10741408
what should i do guys???
you have given me a lot of options

a question about the logic of this  ???

1st of all i have my Searchpage.jsp then i have the Resultpage.jsp (that you guys helped me with last)

with javiers suggestion, on the Resultpage(i think that this is the second.jsp you are referring to right????) i highlight the rows that i want to book then onClick they are sent to the third.jsp
where presumably i would have a form to fill out customer details and a submit button that inserts the lot into the database ....   correct???

gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10741587
yeah pretty much so..... above code that javier has given actually creates a query string to send the data over... you might want to create a form if your number of fields are more..
0
 

Author Comment

by:Gar04
ID: 10741740
yeah the number of field are more

i.e. the row containing the result data to be booked will be sent to the third.jsp page and on that page i would like the data to be displayed in a form that also has additional fields for the customer(person making the booking )
to enter their data i.e. name, address etc...

another question i have regarding this form would be that i would like to use a javabean to validate the customer entry
i have one from a prior form that i can reuse
i am just concerned that it may conflict with the data that is being read in from javiers secondpage?????

one more thing, on javiers page is it possible that a user can send more than one row to the bookpage
i.e. say for instance that the user wanted to book more than one site??

have you any ideas on this?
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10741977
well as far as bean goes you should be able to use it... I wd put a intermediate page to set all the properties from the second page to bean and then use the bean in the third page.... intermediate page should be forwarding to the third one...

now if you want to use multiple booking options than I think my approach of using the primary key passing is the best bet you have.....


you will have to create a check box against every row... clicking that checkbox will set the value of checkbox as the primary key of the row....

upon submittal to the third page..
will be do a request.getParameterValues("checkbox"); which will return the array of all the selected row keys...
then we can call the database to get all the data and display them back to the user....


0
 

Author Comment

by:Gar04
ID: 10742708
right that sounds fairly complicated
but i think that i understand how it works
perhaps i should forget about the bean for the tie being til i get the booking thing sorted out

this is what the returned row on the Resultpage looks like:

BookingNo|  SiteLocation       |  SiteType | Prismatic | Illuminated | CycleID | CycleStart | CycleEnd |       Book          |
2034           Somewhere           48 Sheet        0                0               15        07/07         21/07        CheckBox object

so i should put a form checkbox object on each row that is returned then the user checks the box and
on submit the checkbox value (if yes), along with the corresponding row data is sent to the third page
and formated in a form that has addition field for customer information right,

so when the checkbox is checked it then becomes the primary key for the record/row

can you expalin how to do this exactly

i will send you all the info you need

btw thanks a lot
Gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10743817
Gaz if you want to do it with a check box give me a second to look for my code, About the points or the questions, is just what I told you if you split your awards in the question some one that has your problem will look all the answers you slipt and not just one, is not for the points I'm here to help, and to know that my coments helped you is enough reward.

:c)

Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10743935
if multiple rows of data need to be passed down, and since you have so many columns. then you probably have to go with passing keys.
1. have entire table in the form
2. in each row include a check box <input type=checkbox name="bookingNo" value='<%=rs.getString("BookingNo")%>'>
3. include a submit button in the form.
0
 

Author Comment

by:Gar04
ID: 10743989
sorry kennethxu

you mean just put the table  in form tags like:
<form>

<table>
all the results
</table>

</form>

ok, then i will build what is necessary and get back
asap
btw
thanks for the continued help
Gaz
0
 
LVL 6

Assisted Solution

by:jarasa
jarasa earned 100 total points
ID: 10743996
Is something like this:

second.jsp
<TABLE>
<% while( rs.next() ) { %>
  <tr bgColor='#EEEFF3' onMouseOver="this.bgColor='#B2C4F0';" onMouseOut="this.bgColor='#EEEFF3';" onclick="checkIT(1)">
  <td><input type=checkbox name=check value='<%=rs.getString("PrimaryKey")%>'></td>
  <td><%=rs.getString("field1")%>'><td>
  <td><%=rs.getString("field2")%>'><td>
  ........
  <td><%=rs.getString("fieldN")%>'><td>
  </tr>
<% } %>
</TABLE>
<Input type=Button Name=submit value="Book It" OnClick="assign();">
<FORM name=form1 Action="third.jsp">
<Input type=Hidden name=List value="">
</FORM>
Script.js
function checkIT(nFila) {
      
      var objetos = document.all.check.length;

                // If there are more than 1 row
      if(objetos != null) {
            if (document.all.check[nFila-1].checked)
                  document.all.check[nFila-1].checked=false;
            else
                  document.all.check[nFila-1].checked=true;
                // There is only 1 row
      } else {
            if (document.all.check.checked)
                  document.all.check.checked=false;
            else
                  document.all.check.checked=true;
      }
      
}

function assign() {
      var objetos = document.all.check.length;
      var x;

      document.all.list.value = '';

      if(objetos != null) {
            for(x = 0; x < objetos; x++) {
                  if(document.all.check[x].checked) {
                        if(document.all.list.value != '')
                              document.all.list.value = document.all.list.value + '#';
      
                        document.all.list.value = document.all.list.value + document.all.check[x].value;
                  }
            }
      } else {
            if(document.all.check.checked)
                  document.all.list.value = document.all.check.value;
      }

      if(document.all.list.value != '') {
            document.forms[0].submit();
      }
}

Take a while with it and if you have questions ask them

Javier


0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10744950
>> now if you want to use multiple booking options than I think my approach of using the primary key passing is the best bet you have.....
Kuldeepchaturvedi, agreed!

Javier, you must be javascript expert! what javascript editor/debugger do you use?
btw, but wouldn't be easier if we put all checkboxes in the form?
0
 

Author Comment

by:Gar04
ID: 10745028
ok
thanks guys, javier
i will take a little while with this as
i need to figure out how it works too
regards
gaz
0
 

Author Comment

by:Gar04
ID: 10752414
hey javier et al
seem to have a reasonably good idea how your code works
i guess that in the third.jsp i need to connect to the database
and run the  query again to display the necessary data using the primary key as the key

<%@ 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 NewTestQ(?)}");>>>>>>>>>the only thing is that i have to create a new query  that only takes the booings number as a parameter right???????
  int bookno=Integer.parseInt( rs.getString("BookngsNo"));     >>>>>>>>>> this is what the primary key is !!!
  cs.setInt(1,bookno);
   
  ResultSet rs = cs.executeQuery();
  %>

am i on the right track???

furthermore i will display the data in a table again with the necessary extra fileds for customer data and then also insert the data into the database, i.e. make the booking!!!

can you tell me if this is right or wrong and perhaps give some more pointers??
thanks a lot
gaz
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10752559
yes, you are on the right track.

>> int bookno=Integer.parseInt( rs.getString("BookngsNo"));     >>>>>>>>>> this is what the primary key is !!!
no, because you get the key from previous page, you need to use request.getParameter(). and since you would allow user to select multiple bookings. that involves passing multiple selected keys to third.jsp. so you'll need request.getParameterValues();

String booknos[] = request.getParameterValues( "bookno" );
for( int ii=0; ii<booknos.length; ii++ ) {
    int bookno = Integer.parseInt( booknos[ii] );
    cs.setInt( 1, bookno );
    rs = cs.executeQuery();
    if( rs.next() ) {
      //display the detail
    }
}
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10752683
>Javier, you must be javascript expert! what javascript editor/debugger do you use?
>btw, but wouldn't be easier if we put all checkboxes in the form?

Kenneth
To tell you the truth, I use only UltraEdit wich is a text editor, and in this case you really don't need to put the check boxes on the form becose you use the hidden field to pass the values on the PK.

Gaz
On third.jsp you have to divide all the PK in order to be able to process all, maybe is not needed to make a calleablestatement and will work with a normal query with ca where clause like WHERE bookno IN (all the PK comma separated).

Is sunday and I have no time to explain it better, tomorrow I'll go further

;c)

Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10753118
>>in this case you really don't need to put the check boxes on the form
I was trying to say, we can eliminate the javascript and hidden form by simply putting the checkbox in the form. :-)
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10753408
I see you don't like much JavaScript, do you kenneth?
:c)
Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10753649
haha, not really, javascript helped me a lot. I sincerely admire your skill in javascript. I just get used to stick on the rule of "if same functionality can be achieved without javascript, do without it".
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10756096
Well Kenneth, I guess is a nice rule. But I'm used to make my code for the Intranet and as we have IE as corporate browser, it helps us a lot on doing all the things we want, I believe that on the Internet is harder since you must care on what browser the client has etc.

Gaz. One thing I forgot to tell you is that you should put this on the <Input> so it will work properly if the user clicks directly on the Checkbox:

<input type=checkbox name=check value='<%=rs.getString("PrimaryKey")%>' onclick="checkIT(1)">

also notice that you shold make a counter in order to be able to change the checkIT(counter) number.

Javier
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10756858
You guys worked on the week end as well....!!!!!!!
Good commitment..

0
 

Author Comment

by:Gar04
ID: 10759730
hey guys
just a question
if the browser doesn't have javascript enabled
then i guess that Javiers way wouldn't work..right???
then what??
gaz
;0)
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10759763
thats why I like to depend less and less on the javascript....

Having the checkboxs in the form wd still work though...
0
 

Author Comment

by:Gar04
ID: 10759804
and that would avoid the javascript right??
if that is the case then for my situation i think that it is very necessary to avoid javascript
as perhaps some users won't have it enabled therefore rendering the service useless!!
gaz
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 100 total points
ID: 10759823
actually, as I stated,it is easier to do without javascript:

<FORM name=form1 Action="third.jsp" method="post">
<TABLE>
<% while( rs.next() ) { %>
  <tr bgColor='#EEEFF3' onMouseOver="this.bgColor='#B2C4F0';" onMouseOut="this.bgColor='#EEEFF3';" onclick="checkIT(1)">
  <td><input type=checkbox name=bookno value='<%=rs.getString("PrimaryKey")%>'></td>
  <td><%=rs.getString("field1")%>'><td>
  <td><%=rs.getString("field2")%>'><td>
  ........
  <td><%=rs.getString("fieldN")%>'><td>
  </tr>
<% } %>
<Input type=Button Name=submit value="Book It">
</FORM>
0
 

Author Comment

by:Gar04
ID: 10759922
ok kennethxu
i will get right on it
and get back to you
when i hit bother
gaz
0
 

Author Comment

by:Gar04
ID: 10760439
ok is this correct so far???
on the SearchRes.jsp (i.e. the second.jsp) i have:

<form name=Bookform Action="BookingPage.jsp" method="post">
        <% while (rs.next())
        { %>
        <tr bgcolor='#EEEFF3' onMouseOver="this.bgColor='#B2C4F0';" onMouseOut="this.bgColor='#EEEFF3';">
          <td height="20"> <div align="center"><font color="#000000" size="1"><%=rs.getString("BookingsNo")%></font>
              </div></td>
          <td><div align="center"><font color="#000000" size="1"><%=rs.getString("SiteLocation")%></font></div></td>
          <td><div align="center"><font color="#000000" size="1"><%=rs.getString("SiteType")%></font></div></td>
          <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("Prismatic")%></font></div></td>
          <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("Illuminated")%></font></div></td>
          <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleID")%></font></div></td>
          <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleStart")%></font></div></td>
          <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleEnd")%></font></div></td>
          <td><div align="center"><font color="#000000" size="1"><input type=checkbox name=BookingsNo value='<%=rs.getString("BookngsNo")%>'>
          </font></div></td>
        </tr>
            <Input type=Button Name=submit value="BookIt">
            </form>

and on the BookingPage.jsp (i.e. the third.jsp page) i have:

<%@ 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 NewTestQ(?)}");
  String booknos[] = request.getParameterValues( "BookingsNo" );
      for( int ii=0; ii<booknos.length; ii++ ) {
    int bookno = Integer.parseInt( booknos[ii] );
     
  cs.setInt(1,bookno);
   
  ResultSet rs = cs.executeQuery();
  %>
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10760461
looks good to me...
Have you tried to run it??
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10760479
put
<form name=Bookform Action="BookingPage.jsp" method="post">

before <table>

and

put
          <Input type=Button Name=submit value="BookIt">
          </form>
after </table>
0
 

Author Comment

by:Gar04
ID: 10760486
Not yet cos essentially, i want to have the data returned display in a form rather than in a table
but i don't know how to do this correctly so that all the data can be displayed right cos there may be more than one row??
gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10760595
Gaz I still think that you don't need the calleableStatement here. Just do a simple query, it will be faster.

Javier

P.S.: Anyone that wants to get anything interesting on the Internet will have JavaScript enabled but......

:cP

0
 

Author Comment

by:Gar04
ID: 10827266
hey guys sorry that it has been so long since i got back to you on this thread but
i have been otherwise distracted
i apologise for any inconvenience

right now i have implemented the third.jsp page i.e. Bookingpage.jsp
and i used a resultset query instead of a callable statement like you suggested jarasa.

however, i am having a problem with the second.jsp page i.e. SearchRes.jsp and therefore i can't
test the third.jsp page to see if it works

the problem is in the checkbox form control in the while statement, i get an error message:
<input type=checkbox name=BookingsNo value='Exception: java.sql.SQLException: No data found

would it be because i call it twice in the while statement below
<%=rs.getString("BookingsNo")%>

<% while (rs.next())
        { %>
          <tr bgcolor='#EEEFF3' onMouseOver="this.bgColor='#B2C4F0';" onMouseOut="this.bgColor='#EEEFF3';">
            <td height="24"> <div align="center"> </div>
              <div align="center"><%=rs.getString("BookingsNo")%></div></td>
            <td height="24"><div align="center"><font color="#000000" size="1"><%=rs.getString("SiteLocation")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString("SiteType")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("Prismatic")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("Illuminated")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleID")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleStart")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleEnd")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1">
                <input type=checkbox name=BookingsNo value='<%=rs.getString("BookingsNo")%>'>
                </font></div></td>
          </tr>
          <%}%>
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10827321
you sure your query is returning a cloumn as BookingsNo?? because it does really hurt to get the same column value twice..
0
 

Author Comment

by:Gar04
ID: 10827584
sorry about last post got it sorted out
i was calling the BookingsNo twice and it didn't like it much

ok, my new problem is with the third.jsp page = BookingPage.jsp

this is the code as well as the error messages:

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillBoardCompany");
  rs = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteBookings.CycleID, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.CompanyName, SiteBookings.CompanyAddress, SiteBookings.City, SiteBookings.Country, SiteBookings.PostalCode, SiteBookings.PhoneNumber, SiteBookings.FaxNumber, SiteBookings.EmailAddress, SiteBookings.ProductName, SiteBookings.ProductCategory FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings = ?");
  String booknos[] = request.getParameterValues("BookingsNo");
      for( int ii=0; ii<booknos.length; ii++ ) {
    int bookno = Integer.parseInt( booknos[ii] );
     
  rs.setInt(1,bookno);
   
  ResultSet rs = rs.executeQuery();
  %>
<form blah blah>
<table>
 <% while (rs.next())
        { %>
          <tr>
            <td><div align="center"><%=rs.getString("BookingsNo")%></div></td>
            <td><div align="center"><%=rs.getString("SiteID")%></div></td>
            <td><div align="center"><%=rs.getString("SiteLocation")%></div></td>
            <td><div align="center"><%=rs.getString("CycleID")%></div></td>
            <td><div align="center"><%=rs.getString("BookYear")%></div></td>
          </tr>
              <%}%>
          <% }
    catch (Exception e)
    {
     out.print("Exception: " + e);
    }
    finally
    {
     if( rs != null ) try { rs.close(); } catch( Throwable t ) { t.printStackTrace(); }
     if( conn != null ) try { conn.close(); } catch( Throwable t ) { t.printStackTrace(); }
    }
%>
</table>
</form>



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: 180 in the jsp file: /BookingPage.jsp

Generated servlet error:
    [javac] Compiling 1 source file

C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:240: 'catch' without 'try'
    catch (Exception e)
    ^



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

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:31: 'try' without 'catch' or 'finally'
    try {
    ^
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:281: '}' expected
}
 ^
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:50: incompatible types
found   : java.sql.CallableStatement
required: java.sql.ResultSet
  rs = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteBookings.CycleID, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.CompanyName, SiteBookings.CompanyAddress, SiteBookings.City, SiteBookings.Country, SiteBookings.PostalCode, SiteBookings.PhoneNumber, SiteBookings.FaxNumber, SiteBookings.EmailAddress, SiteBookings.ProductName, SiteBookings.ProductCategory FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings = ?");
                       ^



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

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:55: cannot resolve symbol
symbol  : method setInt  (int,int)
location: interface java.sql.ResultSet
  rs.setInt(1,bookno);
    ^



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

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:57: rs is already defined in _jspService(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
  ResultSet rs = rs.executeQuery();
            ^



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

Generated servlet error:
C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:57: cannot resolve symbol
symbol  : method executeQuery  ()
location: interface java.sql.ResultSet
  ResultSet rs = rs.executeQuery();
                   ^
7 errors


any help would be great
gaz
0
 

Author Comment

by:Gar04
ID: 10827617
hey Kuldeep
yeah that is what i thought too but it wouldn't work
it works now though when i only call it once
it really doesn't matter much though, the last post is more pressing a problem
cheers
gaz
0
 

Author Comment

by:Gar04
ID: 10827671
hey
if you notice i have rather more fields being called in the sql query than i am displaying in the table
i want to create form objects fro these fields instead of a table
would that be causing a problem
also it seems to think that there isn't a catch to go witht the try statement but i think that there is!
gaz
:)
0
 
LVL 19

Assisted Solution

by:Kuldeepchaturvedi
Kuldeepchaturvedi earned 200 total points
ID: 10827704
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillBoardCompany");
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteBookings.CycleID, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.CompanyName, SiteBookings.CompanyAddress, SiteBookings.City, SiteBookings.Country, SiteBookings.PostalCode, SiteBookings.PhoneNumber, SiteBookings.FaxNumber, SiteBookings.EmailAddress, SiteBookings.ProductName, SiteBookings.ProductCategory FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings = ?"); //CON.PRPEARE CALL RETURNS A PREPARED STATEMENT AND NOT A RESULT SET!! CHANGE RS TO BE P
  String booknos[] = request.getParameterValues("BookingsNo");
     for( int ii=0; ii<booknos.length; ii++ ) { //FOR LOOP STARTS...
    int bookno = Integer.parseInt( booknos[ii] );
     
  p.setInt(1,bookno);
   
  ResultSet rs = p.executeQuery();
  %>
<form blah blah>
<table>
 <% while (rs.next())
        { %>
          <tr>
            <td><div align="center"><%=rs.getString("BookingsNo")%></div></td>
            <td><div align="center"><%=rs.getString("SiteID")%></div></td>
            <td><div align="center"><%=rs.getString("SiteLocation")%></div></td>
            <td><div align="center"><%=rs.getString("CycleID")%></div></td>
            <td><div align="center"><%=rs.getString("BookYear")%></div></td>
          </tr>
            <%}%> //FOR LOOP DOES NOT ENDS...  SO ADD ONE MORE CLOSING } HERE
          <% }
    catch (Exception e)
    {
     out.print("Exception: " + e);
    }
    finally
    {
     if( rs != null ) try { rs.close(); } catch( Throwable t ) { t.printStackTrace(); }
     if( conn != null ) try { conn.close(); } catch( Throwable t ) { t.printStackTrace(); }
    }
%>
</table>
</form>


Try this one..!!
0
 

Author Comment

by:Gar04
ID: 10827919
hey its better
but it doesn't seem to like:

exception

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

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

Generated servlet error:
    [javac] Compiling 1 source file

C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:57: rs is already defined in _jspService(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
  ResultSet rs = p.executeQuery();
            ^
1 error
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 19

Assisted Solution

by:Kuldeepchaturvedi
Kuldeepchaturvedi earned 200 total points
ID: 10827946
ResultSet rs = p.executeQuery();
should
rs=p.executeQuery();

0
 

Author Comment

by:Gar04
ID: 10828001
cheers
i have a lot to learn
however, now i get a null pointer exception
Exception: java.lang.NullPointerException
not sure what the means
but i bet you do!
gaz
:)
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10828025
First lets change

catch (Exception e)
    {
     out.print("Exception: " + e);
     e.printStackTrace();
    }


this will print a bunch of stuff on the tomcat console on the server side...

which will give us more information out this null pointer as where its being thrown....

and then we can find out why its happening....
0
 

Author Comment

by:Gar04
ID: 10828086
ok, did what you suggested and it just prints the same thing
i have the database open at the minute, but surely that shouldn't to any harm cos it
is already running the prior query successfully??
gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10828122
try
catch (Exception e)
    {
     out.print("Exception: " + e);
     e.printStackTrace(out);
    }
0
 

Author Comment

by:Gar04
ID: 10828123
would it have anything to do with the sql and the fact that i am only displaying half of the fields looked for in the query so far?
gaz
0
 

Author Comment

by:Gar04
ID: 10828144
it don't like it much

C:\Program Files\Apache Group\Tomcat 4.1\work\Standalone\localhost\NewBBWeb\BookingPage_jsp.java:245: cannot resolve symbol
symbol  : method printStackTrace  (javax.servlet.jsp.JspWriter)
location: class java.lang.Exception
     e.printStackTrace(out);
      ^
1 error


0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10828162
Hmm.......
actually the previous code is printing the stackTrace at your server console ( i.e. tomcat window and not the explorer)..
so either you have to look at the tomcat window or its logs in the log folder to find whats its printing...
I thought you can direct the stackTrace to go to the client.. that why I tried my last comment...
revert it back and try finding what its printing at tomcat console ( server side not the browser) and tell me what it is...
0
 

Author Comment

by:Gar04
ID: 10828284
right this was in the log file

apparently there is an error at line 181 in the jsp file which is :

 <% while (rs.next())
        { %>
          <tr>
            <td><div align="center"><%=rs.getString("BookingsNo")%></div></td>
            <td><div align="center"><%=rs.getString("SiteID")%></div></td>
            <td><div align="center"><%=rs.getString("SiteLocation")%></div></td>
            <td><div align="center"><%=rs.getString("CycleID")%></div></td>
            <td><div align="center"><%=rs.getString("BookYear")%></div></td>
          </tr>
              <%}%>       ......................................................................line 181 the closing bracket for the for loop??
                                                                                                                           guess that it is in the wrong place
              <%}%>
          <% }
    catch (Exception e)


0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10828309
now there is a catch back again.....:-)
line 181 is of compiled jsp i.e. the java class... so line 181 in jsp and the one that system is telling us not gonna be the same one....:-)
show me the stacktrace, let see if we can find something out from it...
0
 

Author Comment

by:Gar04
ID: 10828348
i try to copy and paste the stack trace out of notepad but
it won't work for me
puzzled
gaz
0
 

Author Comment

by:Gar04
ID: 10828354
any ideas on that?
0
 

Author Comment

by:Gar04
ID: 10828497
hey i am sorry
i can't for the life of me cut and paste the stack
gaz
0
 

Author Comment

by:Gar04
ID: 10828590
hey there seems to be a problem with my notepad
what do you suggest instead??
gaz
0
 
LVL 92

Expert Comment

by:objects
ID: 10828649
>   for( int ii=0; ii<booknos.length; ii++ ) { //FOR LOOP STARTS...

try changing that to:

  for( int ii=0; booknos!=null && ii<booknos.length; ii++ ) { //FOR LOOP STARTS...
0
 

Author Comment

by:Gar04
ID: 10828733
hey did the above and now i get a blank white screen
can i email you the log file as there is something up with my notepad and i can't paste the log stack here
0
 
LVL 92

Assisted Solution

by:objects
objects earned 100 total points
ID: 10828837
Looks like request.getParameterValues("BookingsNo"); is returning null.
Check that BookingsNo request parameter is being passed to page.
0
 
LVL 92

Expert Comment

by:objects
ID: 10828849
should it maybe be:
request.getParameterValues("BookingNo");

what is calling this jsp?

0
 

Author Comment

by:Gar04
ID: 10828873
well this is the check box on the second.jsp page that is supposed to send the BookingsNo to the
getparameter on the third.jsp

<input type=checkbox name=bookingsNo value='<%=rs.getString("BookingsNo")%>'>

earlier, i had a problem on the second page whereby i had the :

<td><div align="center"><font color="#000000" size="1"><%=rs.getString("BookingsNo")%></font></div></td>
and
<input type=checkbox name=bookingsNo value='<%=rs.getString("BookingsNo")%>'>
 and i kept getting an error
so i took out the first call
in other words i was calling it twice and it didn't like it
but i don't think that that shoud be a problem
could this be related??
gaz
0
 

Author Comment

by:Gar04
ID: 10828889
this is the second.jsp page that calls BookingPage.jsp



<%@ 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(?, ?, ?)}");
 
  String siteName = request.getParameter("SiteName");
  if( siteName == null ) siteName = "";
  int selCyc=Integer.parseInt( request.getParameter("SelectCyc"));
  int year=Integer.parseInt( request.getParameter("SelectYear"));
 
      cs.setString(1, siteName );
    cs.setInt(2, selCyc );
    cs.setInt(3, year);
   
  ResultSet rs = cs.executeQuery();
  %>
      
<form name=Bookform Action="BookingPage.jsp" method="post">
        <table width="95%" height="46" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#006699">
          <tr bordercolor="#0033CC">
            <td width="68" height="20" align="center" valign="middle"> <div align="center"></div>
              <strong><font color="#000000" size="1">Booking No.&nbsp;</font></strong></td>
            <td width="198" align="center" valign="middle"><font color="#000000" size="1"><strong>Site
              Location</strong></font></td>
            <td width="107" height="20" align="center" valign="middle"> <div align="center"><font color="#000000" size="1"><strong>Site
                Format</strong></font></div></td>
            <td width="68" align="center" valign="middle"> <font color="#000000" size="1">&nbsp;<strong>Prismatic</strong></font></td>
            <td width="62" align="center" valign="middle" bgcolor="#FFFFFF"> <div align="center"><font color="#000000" size="1"><strong>Illuminated</strong></font></div></td>
            <td width="60" align="center" valign="middle"> <div align="center"><font color="#000000" size="1"><strong>CycleID</strong></font></div></td>
            <td width="109" align="center" valign="middle"> <div align="center"><font color="#000000" size="1"><strong>CycleStart</strong></font></div>
              <div align="center"></div>
              <div align="center"></div></td>
            <td width="121" align="center" valign="middle"> <div align="center"><font color="#000000" size="1"><strong>CycleEnd</strong></font></div></td>
            <td width="64" align="center" valign="middle"> <div align="center"><font color="#000000" size="1"></font></div>
              <div align="center"><font size="1"><strong>Book</strong></font></div></td>
          </tr>
          <% while (rs.next())
        { %>
          <tr bgcolor='#EEEFF3' onMouseOver="this.bgColor='#B2C4F0';" onMouseOut="this.bgColor='#EEEFF3';">
            <td height="24"> <div align="center"> </div>
              <div align="center"></div></td>
            <td height="24"><div align="center"><font color="#000000" size="1"><%=rs.getString("SiteLocation")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString("SiteType")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("Prismatic")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("Illuminated")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleID")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleStart")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1"><%=rs.getString ("CycleEnd")%></font></div></td>
            <td><div align="center"><font color="#000000" size="1">
                <input type=checkbox name=bookingsNo value='<%=rs.getString("BookingsNo")%>'>
                </font></div></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>
          <Input type=submit Name=Submit value="BookIt">
        </form>
0
 
LVL 92

Expert Comment

by:objects
ID: 10828890
try:

String booknos[] = request.getParameterValues("bookingsNo");
0
 

Author Comment

by:Gar04
ID: 10828910
but it is definitely
"BookingsNo"
well alright then
0
 

Author Comment

by:Gar04
ID: 10828927

this is what i get for that but i was kinda expecting that cos it is BookingsNo
Exception: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
any more ideas
btw thanks for the help
gaz
0
 
LVL 92

Expert Comment

by:objects
ID: 10828936
> <input type=checkbox name=bookingsNo value='<%=rs.getString("BookingsNo")%>'>

the parameter name has a lower case b
0
 
LVL 92

Expert Comment

by:objects
ID: 10828940
changing the line i mentioned would have no affect on the query, what line did u chasnge?
0
 
LVL 92

Expert Comment

by:objects
ID: 10828953
actually it may have by the fact that previously it wasn't even getting to the query.
in which case we are making progress :)

post your current code.
0
 

Author Comment

by:Gar04
ID: 10828965
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillBoardCompany");
  PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteBookings.CycleID, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.CompanyName, SiteBookings.CompanyAddress, SiteBookings.City, SiteBookings.Country, SiteBookings.PostalCode, SiteBookings.PhoneNumber, SiteBookings.FaxNumber, SiteBookings.EmailAddress, SiteBookings.ProductName, SiteBookings.ProductCategory FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings = ?");
  String booknos[] = request.getParameterValues("BookingsNo");
  for( int ii=0; booknos!=null && ii<booknos.length; ii++ ) {
    int bookno = Integer.parseInt( booknos[ii] );
     
  p.setInt(1,bookno);
   
  rs = p.executeQuery();
  %>

<form name="form1" method="post" action="">
        <table width="100%" border="1" cellpadding="0" cellspacing="0" bordercolor="#3399CC">
          <tr>
            <td width="13%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Booking
                No.</font></div></td>
            <td width="10%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Site
                ID</font></div></td>
            <td width="54%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Site
                Location </font></div></td>
            <td width="10%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Cycle
                ID </font></div></td>
            <td width="13%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Book
                Year </font></div></td>
          </tr>
              <% while (rs.next())
        { %>
          <tr>
            <td><div align="center"><%=rs.getString("BookingsNo")%></div></td>
            <td><div align="center"><%=rs.getString("SiteID")%></div></td>
            <td><div align="center"><%=rs.getString("SiteLocation")%></div></td>
            <td><div align="center"><%=rs.getString("CycleID")%></div></td>
            <td><div align="center"><%=rs.getString("BookYear")%></div></td>
          </tr>
              <%}%>
              <%}%>
          <% }
    catch (Exception e)
    {
       out.print("Exception: " + e);
     e.printStackTrace();
    }
    finally
    {
     if( rs != null ) try { rs.close(); } catch( Throwable t ) { t.printStackTrace(); }
     if( conn != null ) try { conn.close(); } catch( Throwable t ) { t.printStackTrace(); }
    }
%>
        </table>
      </form>
0
 

Author Comment

by:Gar04
ID: 10828987
BTW that is the code for the third.jsp page i.e. BookingPage.jsp
gaz
:)
0
 
LVL 92

Expert Comment

by:objects
ID: 10828994
the line i suggested changing is still the same??
0
 

Author Comment

by:Gar04
ID: 10828999
i changed it back before i posted it
gaz
0
 
LVL 92

Expert Comment

by:objects
ID: 10829011
Any particular reason :)
0
 
LVL 92

Expert Comment

by:objects
ID: 10829024
>     if( rs != null ) try { rs.close(); } catch( Throwable t ) { t.printStackTrace(); }

closing the result set should also be done inside the loop.
0
 

Author Comment

by:Gar04
ID: 10829033
because it is definitely BookingsNo with an Uppercase B
BookingPage.jsp
 String booknos[] = request.getParameterValues("BookingsNo");
  for( int ii=0; booknos!=null && ii<booknos.length; ii++ ) {
    int bookno = Integer.parseInt( booknos[ii] );

this is the SearchRes.jsp i.e. the second.jsp
 <input type=checkbox name=BookingsNo value='<%=rs.getString("BookingsNo")%>'>

it is BookingsNo in the Access DB
 that is the only reason
what would changing it to lower case accomplish??
you have me a little confused
0
 
LVL 92

Expert Comment

by:objects
ID: 10829064
> this is the SearchRes.jsp i.e. the second.jsp
> <input type=checkbox name=BookingsNo value='<%=rs.getString("BookingsNo")%>'>

thats different to what you posted above
 <input type=checkbox name=bookingsNo value='<%=rs.getString("BookingsNo")%>'>

> it is BookingsNo in the Access DB
> that is the only reason

thats irrelevant, what u are referring to is the request parameter name


0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10829087
whoa.... 26 comments in the time it takes me to reach home from office....:-)

Object's point is
if you have
<input type=checkbox name=bookingsNo value='<%=rs.getString("BookingsNo")%>'>
then

String booknos[] = request.getParameterValues("bookingsNo");
or if you have

<input type=checkbox name=BookingsNo value='<%=rs.getString("BookingsNo")%>'>
then

String booknos[] = request.getParameterValues("BookingsNo");
bottom line is that the name of checkbox and the getParameterValues should be reffering to exactly the same name..

Do one more thing for me..

for( int ii=0; booknos!=null && ii<booknos.length; ii++ ) {
    int bookno = Integer.parseInt( booknos[ii] );
 out.println(bookno);    // just a crude debug method..
  p.setInt(1,bookno);


just to see if its getting the numbers already...
0
 

Author Comment

by:Gar04
ID: 10829171
sorry i had posted the earlier post, objects after i had fooled around with the changes but i had changed it back after
sorry about the confusion
but i know that it has to refer to the name
sorry again about the confusion
i sometimes lose track of changes i make when i fiddle with this stuff

Kuldeep, i will test your debug
too
gaz:0)

0
 

Author Comment

by:Gar04
ID: 10829199

this is what i get
2782 is the BookingsNo of the site that i tried to book
would it possibly have something to do with the sql
i have, perhaps it is wrong, cos it should really only look for one parameter not two

2782 Exception: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.  
0
 
LVL 92

Expert Comment

by:objects
ID: 10829220
so what happened to the nullpointerexception and blank page?
0
 
LVL 92

Expert Comment

by:objects
ID: 10829271
what type is SiteBookings in database?
0
 

Author Comment

by:Gar04
ID: 10829291
ok it was the sql
there was an error
SiteBookings.BookingsNo should have been there
0
 

Author Comment

by:Gar04
ID: 10829358
Is is possible to have the rest of the sql statement Fields that are not
displayed in the table, display in form objects like dropdowns, text fields
by the way they are blank in the db, then i would like to have all these Inserted into the db
thus completing the booking
the fileds are CustomerInformation stuff or should i do this another way
btw sorry for the confusion, i know that i am a pain in the rear
i do appreciate the help though
Gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10829616
you shd be able to do that......

just create a form after the table and put the input fields/drop downs...

once they select all the values... you can run a insert command back to the database on the submit...
one question though...

how you gonna do the multiple forms????
0
 

Author Comment

by:Gar04
ID: 10829743
what do you mean by multiple forms
kuldeep??
0
 

Author Comment

by:Gar04
ID: 10830124
i guess you mean multiple bookings by more than one user
right??
gaz
p.s. i don't know...do i ever know??
0
 

Author Comment

by:Gar04
ID: 10830145
I guess that i could
discard all the additional customer related fields from the above sql
and just create a form and insert the data to those fields that match the
BookingsNo field data from the rs table data
would that work better????????
gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10836582
>>what do you mean by multiple forms
As we said that on the second page user can select multiple bookings.. so now if we want him to fill up details for all the bookings then we need more than one occurance of the same field so that they can fill in the values for all the bookings that they are doing....

I think we can do the same thing that we did with the checkbox on the second page.. and then insert all the values to the database...
0
 

Author Comment

by:Gar04
ID: 10838644
ok
i understand
the funny thing is at the moment
the search page only allows the user to select one site
from a dropdown list
i know that the second and third jsp pages can, for the most part, cater for more than one booking
but i would need to change
the first.jsp page.
is there a way i can allow the dropdown list to allow the user to select more than one site at a time?
gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10838787
Well I think lets finish of one booking success fully...:-)
once thats done then we will worry abt the multiples....
0
 

Author Comment

by:Gar04
ID: 10838894
kay
:)
gaz
0
 

Author Comment

by:Gar04
ID: 10932966
Hello
There are complications when i try to save data (insert data to the database).
In the Access database, when i save a new booking, a few important things happen:
First, there are two functions that run, 1. a function runs that generates a campaign reference code
and 2. a function runs that generates a customer identification code. Both of these are based on the data that is entered in the form fields
is there a way i can do these in jsp as well????????
Gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10932992
currently where these functions are running????
0
 

Author Comment

by:Gar04
ID: 10933063
they run from the submit/save button on a bookings form in the Access database!
i just wondered if these can be migrated to the web app, i looked for examples
but can't seem to find any
gaz
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10933097
yeah you should be able to port them to Jsp... its gonna be the java equivelent of your methods...
0
 

Author Comment

by:Gar04
ID: 10933141
right,
so i guess they would be on save button.
i can show you the code in VB and perhaps you can help me convert it to java...if possible
gaz
0
 

Author Comment

by:Gar04
ID: 10933180
essentially, if the user is an existing user, i wouldn't need to have the CustomerID function run
i could just have them choose there company name from a dropdown list or something,
however, if the user is new then the CustomerID function would need to run!
The CampaignRef campaign would definitely have to run
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10933206
show us the code and lets see what we can do on it,
it looks like its gonna be a server side code so we will not be putting it on the save button ( it can only invoke java script functions) but we will embed it in Java code
0
 

Author Comment

by:Gar04
ID: 10933326
This is the code for the generation of a CampaignRef (there are two functions for this but they are very similar)
the function is called on the booking form by this
[CampaignRef] = GetLastCID(Forms![NewBookForm]![CycleID], Forms![NewBookForm]![BookYear])
and this [CampaignRef] = GetNextCID(Forms![NewBookForm]![CycleID], Forms![NewBookForm]![BookYear])
depending on an if statement
the code ends up looking like this A2004-0001,   A= Cycle Number e.g. 1, 2004 is the year and 0001 represents the first product of the campaign.
i will send the CustomerID function next

'This is a public function that generates a new Campaign Reference Code
'for each new Advertisement Campaign Booking Sites

' Two parameters are read in
Public Function GetNextCID(iCycle, BkYr As Integer) As String
   
    'Declaration of variable types
    Dim tmpID As String, Bkyear As String
    Dim LastId As Variant, LastSeq As Integer
    Dim NextSeq As Integer, NextID As String

    On Error GoTo GetNextCID_Err
   
    Bkyear = CStr(BkYr & "-")
    tmpID = Chr(iCycle + 64)
    tmpID = tmpID & Right(Bkyear, 5)
    LastId = DMax("CampaignRef", "SiteBookings", "([SiteBookings].[CampaignRef] like '" & tmpID & "*')")
    If IsNull(LastId) Then
      LastSeq = 0
    Else
      LastSeq = Val(Right(LastId, 4))
    End If

    NextSeq = LastSeq + 1
    NextID = tmpID & Right("0000" & NextSeq, 4)

    GetNextCID = NextID

GetNextCID_Exit:
    Exit Function

GetNextCID_Err:
    Select Case Err
    Case Else
      MsgBox Err.Description
      Resume GetNextCID_Exit
      Resume
    End Select

End Function

'This is a public function to retrieve the last Campaign Reference stored
'when certain criteria are met

Public Function GetLastCID(iCycle, BkYr As Integer) As String

    Dim tmpID As String, Bkyear As String
    Dim LastId As Variant

    On Error GoTo GetLastCID_Err
   
    Bkyear = CStr(BkYr & "-")
    tmpID = Chr(iCycle + 64)
    tmpID = tmpID & Right(Bkyear, 5)
    LastId = DMax("CampaignRef", "SiteBookings", "([SiteBookings].[CampaignRef] like '" & tmpID & "*')")
   
    GetLastCID = LastId

GetLastCID_Exit:
    Exit Function

GetLastCID_Err:
    Select Case Err
    Case Else
      MsgBox Err.Description
      Resume GetLastCID_Exit
      Resume
    End Select

End Function
0
 

Author Comment

by:Gar04
ID: 10933388
Hey Kuldeep
for the CustomerID i think that javascript might be ok
essentially, what happens on the form is AfterUpdate action, when the user enters a name
and moves on, the function takes that name and generates a code five letters long and capitalised
from that name.

Private Sub CompanyName_AfterUpdate()
'pulls data pertainng to Customer from row source
Me.CustomerID = Me.CompanyName.Column(1)
Me.CompanyAddress = Me.CompanyName.Column(2)
Me.City = Me.CompanyName.Column(3)
Me.Country = Me.CompanyName.Column(4)
Me.PostalCode = Me.CompanyName.Column(5)
Me.PhoneNumber = Me.CompanyName.Column(6)
Me.FaxNumber = Me.CompanyName.Column(7)
Me.EmailAddress = Me.CompanyName.Column(8)
 
  'Generates a CustomerID for new Customer
  ' Validate we have a new value to work with
  If Nz(Me.CompanyName, "") = "" Then
    MsgBox "You must enter a company name", vbExclamation
    Me.CompanyName.SetFocus
    Exit Sub
  End If
 
  Dim a
  Dim sTemp As String
  Dim sCompanyID As String
  sTemp = Trim(Me.CompanyName)
 
  ' replace double spaces with single spaces
  Do
    sTemp = Replace(sTemp, "  ", " ")
  Loop Until InStr(1, sTemp, "  ", vbTextCompare) = 0
 
  ' if shorter than 5 chars pad with zeros
  Do
    sTemp = sTemp & "0"
  Loop Until Len(sTemp) >= 5
 
  ' split into an array
  a = Split(sTemp, " ")
 
  ' builds the CustomerID using case statements
  Select Case UBound(a)
  Case 0
    sCompanyID = sTemp
  Case 1
    sCompanyID = Left(a(0), 3) & Left(a(1), 2)
  Case 2
    sCompanyID = Left(a(0), 2) & Left(a(1), 2) & Left(a(2), 1)
  Case 3
    sCompanyID = Left(a(0), 2) & Left(a(1), 1) & Left(a(2), 1) & Left(a(3), 1)
  Case Is >= 4
    sCompanyID = Left(a(0), 1) & Left(a(1), 1) & Left(a(2), 1) & Left(a(3), 1) & Left(a(4), 1)
  End Select
 
 
  ' Do loop test for existence of sCompanyID
  Dim sWhere As String
  Dim i As Integer
  sWhere = "CustomerID='" & sCompanyID & "'"
  i = 1
   
  Do
    sWhere = "CustomerID='" & Left(sCompanyID, 3) & Format(i, "00") & "'"
  Loop Until DCount("CustomerID", "CustomerInformation", sWhere) = 0
 
  ' converts to uppercase
  sCompanyID = UCase(sCompanyID)
 
  'assigns value to CustomerID
  Me.CustomerID = sCompanyID
  Set a = Nothing

 
End Sub
0
 

Author Comment

by:Gar04
ID: 10933416
I will also show you the code for the Save button in Access that triggers these
function so that you can see the condition statements and what have you that need to comply so that these
functions can work!!

'Saves Bookings, checks data through recordsets and comparisons
Private Sub SaveBook_Click()

On Error GoTo Err_Sav_book_Click

Dim rSQL As String
Dim cSQL As String
Dim sSQL As String
Dim pSQL As String
Dim ca As DAO.Recordset
Dim rs As DAO.Recordset

If (IsNull(Me.CompanyName) Or IsNull(Me.CompanyAddress) Or IsNull(Me.City) Or IsNull(Me.Country) Or IsNull(Me.PostalCode) Or IsNull(Me.PhoneNumber)) Then
MsgBox "Please ensure that at least the CompanyName upto and including a phone Number is entered in the CustomerInformation Fields!"
Exit Sub
ElseIf (IsNull(Me.ProductName) Or IsNull(Me.ProductCategory) Or IsNull(Me.Price)) Then
MsgBox "Please ensure that all Product Information fields are completed!"
Exit Sub
Else
'Retrieves a recordset of the last value saved and compares product information
'get last record
Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM SiteBookings ORDER BY BookDate DESC", dbOpenSnapshot)
'tests the current record to previous records to ensure that campaign references comply
    If (rs("SiteID") <> Me("SiteID")) And (rs("CustomerID") = Me("CustomerID")) And (rs("CycleID") = Me("CycleID")) Then
     [CampaignRef] = GetLastCID(Forms![NewBookForm]![CycleID], Forms![NewBookForm]![BookYear])
     [CustomerID] = Forms![NewBookForm]![CustomerID]
     Me("BookDate") = Now()
      'get recordset of Campaign Information
    Set ca = CurrentDb.OpenRecordset("SELECT * FROM CampaignInformation ORDER BY CampaignRef DESC", dbOpenSnapshot)
    'checks conditions of recordset and allows insert
    If (ca.EOF And ca.BOF) Then
        rSQL = "INSERT INTO CampaignInformation(CampaignRef, ProductName, ProductCategory, Price)"
        rSQL = rSQL & "Values (" & Chr(34) & Me.CampaignRef & Chr(34) & ", " & Chr(34) & Me.ProductName & Chr(34) & ", " & Chr(34) & Me.ProductCategory & Chr(34) & "," & Chr(34) & Me.Price & Chr(34) & ");"
    CurrentDb.Execute rSQL
    ElseIf Me.CampaignRef <> DLookup("CampaignRef", "CampaignInformation") Then
      rSQL = "INSERT INTO CampaignInformation(CampaignRef, ProductName, ProductCategory, Price)"
        rSQL = rSQL & "Values (" & Chr(34) & Me.CampaignRef & Chr(34) & ", " & Chr(34) & Me.ProductName & Chr(34) & ", " & Chr(34) & Me.ProductCategory & Chr(34) & "," & Chr(34) & Me.Price & Chr(34) & ");"
    CurrentDb.Execute rSQL
    End If
        sSQL = "INSERT INTO CustomerInformation( CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress) Values ( "
        sSQL = sSQL & Chr(34) & Me.CustomerID & Chr(34) & ", " & Chr(34) & Me.CompanyName & Chr(34) & ", " & Chr(34) & Me.CompanyAddress & Chr(34) & " , " & Chr(34) & Me.City & Chr(34) & " , " & Chr(34) & Me.Country & Chr(34) & " ," & Chr(34) & Me.PostalCode & Chr(34) & "," & Chr(34) & Me.PhoneNumber & Chr(34) & "," & Chr(34) & Me.FaxNumber & Chr(34) & " , " & Chr(34) & Me.EmailAddress & Chr(34) & ");"
        CurrentDb.Execute sSQL
   DoCmd.RunCommand acCmdSaveRecord
   Else
   [CampaignRef] = GetNextCID(Forms![NewBookForm]![CycleID], Forms![NewBookForm]![BookYear])
   [CustomerID] = Forms![NewBookForm]![CustomerID]
   Me("BookDate") = Now()
    'get recordset of Campaign Information
    Set ca = CurrentDb.OpenRecordset("SELECT * FROM CampaignInformation ORDER BY CampaignRef DESC", dbOpenSnapshot)
     'checks conditions of recordset and allows insert
     If (ca.EOF And ca.BOF) Then
        rSQL = "INSERT INTO CampaignInformation(CampaignRef, ProductName, ProductCategory, Price)"
        rSQL = rSQL & "Values (" & Chr(34) & Me.CampaignRef & Chr(34) & ", " & Chr(34) & Me.ProductName & Chr(34) & ", " & Chr(34) & Me.ProductCategory & Chr(34) & "," & Chr(34) & Me.Price & Chr(34) & ");"
      CurrentDb.Execute rSQL
      ElseIf Me.CampaignRef <> DLookup("CampaignRef", "CampaignInformation") Then
      rSQL = "INSERT INTO CampaignInformation(CampaignRef, ProductName, ProductCategory, Price)"
        rSQL = rSQL & "Values (" & Chr(34) & Me.CampaignRef & Chr(34) & ", " & Chr(34) & Me.ProductName & Chr(34) & ", " & Chr(34) & Me.ProductCategory & Chr(34) & "," & Chr(34) & Me.Price & Chr(34) & ");"
      CurrentDb.Execute rSQL
      End If
   sSQL = "INSERT INTO CustomerInformation( CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress) Values ( "
        sSQL = sSQL & Chr(34) & Me.CustomerID & Chr(34) & ", " & Chr(34) & Me.CompanyName & Chr(34) & ", " & Chr(34) & Me.CompanyAddress & Chr(34) & " , " & Chr(34) & Me.City & Chr(34) & " , " & Chr(34) & Me.Country & Chr(34) & " ," & Chr(34) & Me.PostalCode & Chr(34) & "," & Chr(34) & Me.PhoneNumber & Chr(34) & "," & Chr(34) & Me.FaxNumber & Chr(34) & " , " & Chr(34) & Me.EmailAddress & Chr(34) & ");"
        CurrentDb.Execute sSQL
   DoCmd.RunCommand acCmdSaveRecord
   End If
   End If
Exit_Sav_book_Click:
    Exit Sub

Err_Sav_book_Click:
    MsgBox Err.Description
    Resume Exit_Sav_book_Click
End Sub
0
 

Author Comment

by:Gar04
ID: 10933455
Hey
guys, i have to go for a little while, if you have any questions
please ask me.
Alternatively, if you guys want me to open these questions in seperate threads
so that you can earn more points, i will.
Gaz
0
 

Author Comment

by:Gar04
ID: 10938826
Have you had any joy??
Gaz
:)
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10939025
Hey I am getting busy some really important office work for next couple of days..
So it may be this week end before I can look in to it..:-(
by that time if somebody cracks them, fine otherwise we will see what we can do
0
 

Author Comment

by:Gar04
ID: 10939154
ok
Kuldeep
thank you
Gaz
0
 

Author Comment

by:Gar04
ID: 10939311
Hey kuldeep i have opened this question in another thread
so that you guys can earn other points not related to this
Gaz

http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20970677.html
0
 
LVL 92

Expert Comment

by:objects
ID: 10945564
Thanks Gaz for the points :)
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10946784
:c) Thanks Gaz, and sorry I couldn't get back to this, but I'm too busy these last weeks.

Javier

0
 

Author Comment

by:Gar04
ID: 10959148
Hey
no problem guys, you deserve more points than are allowed unfortunately.
if you get a second, can you have a look at this thread and give me your advice, opinions...whatever?
http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20970677.html
much appreciated
Gaz
 
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now