General Question regarding JSP resultSet use

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

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

>> If no one tells you before
:-P

thanks guys
i will the last question up and running before i delve into this
btw
thanks ever so much
b back soon
Gaz

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

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

Storing the data in the session saves the need for passing it around in the request, and also requerying the database.

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

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");
%>

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

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

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

Making a new db request for every request is probably the easiest to implement.

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>
<% } %>"

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

ok
well the primary key is a field called BookingsNo
how exactly would i implement this way?
gaz

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");

i will give it a go and get back to you guys
again thanks for the help
gaz

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.

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.

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?

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

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

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

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.

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

Sorry about this.

Gaz Please delete this Question https://www.experts-exchange.com/questions/20940750/For-The-Attention-of-Jarasa.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

>>>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....:-))

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.

javier are you sure???
you did help a lot

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

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

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

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?

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

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

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

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.

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

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

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

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

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

>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

>>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. :-)

I see you don't like much JavaScript, do you kenneth?
:c)
Javier

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

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

You guys worked on the week end as well....!!!!!!!
Good commitment..

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)

thats why I like to depend less and less on the javascript....

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

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

ok kennethxu
i will get right on it
and get back to you
when i hit bother
gaz

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();
  %>

looks good to me...
Have you tried to run it??

put
<form name=Bookform Action="BookingPage.jsp" method="post">

before <table>

and

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

i try to copy and paste the stack trace out of notepad but
it won't work for me
puzzled
gaz

any ideas on that?

hey i am sorry
i can't for the life of me cut and paste the stack
gaz

hey there seems to be a problem with my notepad
what do you suggest instead??
gaz

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

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

should it maybe be:
request.getParameterValues("BookingNo");

what is calling this jsp?

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

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>

try:

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

but it is definitely
"BookingsNo"
well alright then

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

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

the parameter name has a lower case b

changing the line i mentioned would have no affect on the query, what line did u chasnge?

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.

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

BTW that is the code for the third.jsp page i.e. BookingPage.jsp
gaz
:)

the line i suggested changing is still the same??

i changed it back before i posted it
gaz

Any particular reason :)

>     if( rs != null ) try { rs.close(); } catch( Throwable t ) { t.printStackTrace(); }

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

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

> 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

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

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)

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.  

so what happened to the nullpointerexception and blank page?

what type is SiteBookings in database?

ok it was the sql
there was an error
SiteBookings.BookingsNo should have been there

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

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

what do you mean by multiple forms
kuldeep??

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

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

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

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

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

kay
:)
gaz

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

currently where these functions are running????

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

yeah you should be able to port them to Jsp... its gonna be the java equivelent of your methods...

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

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

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

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

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

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

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

Have you had any joy??
Gaz
:)

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

ok
Kuldeep
thank you
Gaz

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

https://www.experts-exchange.com/questions/20970677/VB-to-Java-JSP-conversion-question.html

Thanks Gaz for the points :)

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

Javier

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?
https://www.experts-exchange.com/questions/20970677/VB-to-Java-JSP-conversion-question.html
much appreciated
Gaz