Solved

porting Access VB code to Java for a web app

Posted on 2004-05-01
42
478 Views
Last Modified: 2008-02-01
Hey Experts,
I have some access vb functions that i need to port to java code for a jsp web app
i understand java fairly well but i am not a proficient programmer and need some help/advice:

there are two functions(from a access database) one that generates a CustomerID and another that gnerates a CampaignRef
here is the code respectively:
Private Sub CompanyName_AfterUpdate()
 
  '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 >>>>>>>>>i guess i would need some server side testing here to check for an
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> customerid in the database
  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



this is the function for the CampaignRef:

'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

This is the little checks that i use to call these in the DB:
'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

any help would be very much appreciated regarding this thread
thank you for your time
regards
Gar04
0
Comment
Question by:Gar04
  • 21
  • 9
  • 9
  • +1
42 Comments
 
LVL 7

Expert Comment

by:maheshexp
ID: 10970138
first open up a database connection, then send ur SQL statements
http://java.sun.com/docs/books/tutorial/jdbc/
0
 
LVL 7

Expert Comment

by:maheshexp
ID: 10970148
0
 

Author Comment

by:Gar04
ID: 10977641
Hey, thanks for the help
maheshexp, i kinda know how to do database conections using java
i really needed help getting my head around the broader picture.
Thanks.
Gaz.
0
 
LVL 7

Expert Comment

by:maheshexp
ID: 10978508
it's not only a broader picture, also a confusing picture. if u can say what all these functions do, i can figure out how to solve it
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10978808
What does UBound(), Left(), Right(), DCount() etc. do respectively taking the arguments into account?
0
 

Author Comment

by:Gar04
ID: 10979064
I hope that this makes sense!!
i am not a very proficient programmer by any stretch of the imagination, this is essentially a patchwork job!

UBound() function : Returns a Long containing the largest available subscript for the indicated dimension of an array.
example:Dim Upper
Dim MyArray(1 To 10, 5 To 15, 10 To 20)    ' Declare array variables.
Dim AnyArray(10)
Upper = UBound(MyArray, 1)    ' Returns 10.
Upper = UBound(MyArray, 3)    ' Returns 20.
Upper = UBound(AnyArray)    ' Returns 10.

In relation to this data it ensures that the CustomerID is 5 letters long, as it picks the largest value available in the array.
this being the Array: a = Split(sTemp, " ")

Left() : Returns a Variant (String) containing a specified number of characters from the left side of a string.
Syntax: Left(string, length)
Dim AnyString, MyStr
AnyString = "Hello World"    ' Define string.
MyStr = Left(AnyString, 1)    ' Returns "H".
MyStr = Left(AnyString, 7)    ' Returns "Hello W".
MyStr = Left(AnyString, 20)    ' Returns "Hello World".

Right(): Returns a Variant (String) containing a specified number of characters from the right side of a string.
Syntax: Right(string, length)
example:Dim AnyString, MyStr
AnyString = "Hello World"    ' Define string.
MyStr = Right(AnyString, 1)    ' Returns "d".
MyStr = Right(AnyString, 6)    ' Returns " World".
MyStr = Right(AnyString, 20)    ' Returns "Hello World".

DCount() : You can use the DCount function to determine the number of records that are in a specified set of records (a domain).

Loop Until DCount("CustomerID", "CustomerInformation", sWhere) = 0
Syntax = DCount(expression, domain, criteria)
0
 
LVL 14

Accepted Solution

by:
Tommy Braas earned 250 total points
ID: 10979327
This should get you started! Keep in mind that Java is object oriented, not procedural or object dependent.

Here is an almost complete conversion of the CompanyName_AfterUpdate() sub routine. Enjoy!

File VBToJava.java:

import java.text.DecimalFormat;

import javax.swing.JOptionPane;

/*
* Created on May 3, 2004
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Generation - Code and Comments
*/
/**
* @author Tommy
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Generation - Code and Comments
*/
public class VBToJava {
      
      String companyName = null;
      String customerID = null;
      DecimalFormat df = new DecimalFormat("00");
      
      private void companyNameAfterUpdate() {
            
            // Generates a CustomerID for new Customer
            // Validate we have a new value to work with
            if (this.companyName.trim().length() == 0) {
                  JOptionPane.showMessageDialog(null, "You must enter a company name");
                  return;
            }
            
            String sTemp = this.companyName.trim();
            String sCompanyID = "";
            
            // replace double spaces with single spaces
            sTemp = sTemp.replaceAll("  ", " ");
            
            // if shorter than 5 chars pad with zeros
            if (sTemp.length() < 5) {
                  sTemp += "     ".substring(sTemp.length());
            }
            
            // split into an array
            String []a = sTemp.split(" ");
            
            // builds the CustomerID using case statements
            
            switch (a.length - 1) {
                  case 0: sCompanyID = sTemp; break;
                  case 1: sCompanyID = a[0].substring(0, 3) + a[1].substring(0, 2);
                  case 2: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
                  case 3: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
                  case 4: sCompanyID = a[0].substring(0, 1) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1) + a[4].substring(0, 1);
            }
            
            // Do loop test for existence of sCompanyID >>>>>>>>>i guess i would need some server side testing here to check for an
            //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> customerid in the database
            String sWhere;
            int i = 1;
            sWhere = "CustomerID='" + sCompanyID + "'";
            
            do {
                  sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
            } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);
            
            // converts to uppercase
            sCompanyID = sCompanyID.toUpperCase();
            
            //assigns value to CustomerID
            this.customerID = sCompanyID;
            
            a = null;  // not needed
      }
      
      private int numberOfRecordsMatching(String expr, String Domain, String query) {
            return 0; // to be implemented!!!
      }
}
0
 

Author Comment

by:Gar04
ID: 10979977
Cheers orange,
give me a while to get my head around what you did.
:)
Gaz
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10980035
Sure, not a problem!
0
 

Author Comment

by:Gar04
ID: 10980155
right, ok...i see that it pretty much works just as the VB, thats cool
this:

"Do
    sWhere = "CustomerID='" & Left(sCompanyID, 3) & Format(i, "00") & "'"
  Loop Until DCount("CustomerID", "CustomerInformation", sWhere) = 0"

becomes this:

"do {
               sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
          } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);"

essentially, this is where i need to pull an sql recordset of some kind out of the database and run a comparison right???
if so, i need to figure out how to do this!!
cheers
Gaz
         

0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10980302
>> essentially, this is where i need to pull an sql recordset of some kind out of the database and run a comparison right???
Yes in essence. Make sure you use a PreparedStatement as it is cached by the DB, or use a stored statement.
0
 

Author Comment

by:Gar04
ID: 10981378
I think that i can use a callable statement perhaps??, i need to look some stuff up for this though.
Thank you for the help, keep it comin'.
Gaz
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10981618
=-)
0
 
LVL 5

Expert Comment

by:rsalzmann
ID: 10983800
Hi Gar4,

I'm in the process of doing the very same thing.  If you will ask more specific questions about the parts of code you want to convert, I can help you get it figured out.  chances are I've had to figure out several times what you're running into.

-robb
0
 

Author Comment

by:Gar04
ID: 10986058
ok rob,
thanks for the help.
Gar
0
 

Author Comment

by:Gar04
ID: 10990301
Hey rob
I was wondering if you know whether it is possible to have several prepared statements used from the same
database connection on a jsp page?? btw this all related to the question above:

<%@ 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 FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings.BookingsNo = ?");
  PreparedStatement z = conn.preparedCall("INSERT INTO SiteBookings" +
  "(CustomerID, SiteBookings.CampaignRef, SiteBookings.Price)" + "VALUES(?,?,?)" + "WHERE SiteBookings.BookingsNo = ?");
0
 
LVL 5

Expert Comment

by:rsalzmann
ID: 10991823
Gar,
I would consider this arrangement risky, due to the threading nature of the servlet container.  if you are pooling your connections, using an extra connection or two in this arrangement to ensure thread safe execution should not be a noticeable hit on resources.  Just be sure to return the connections to the pool when you've close your resultset.  
robb

0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10991936
The bigger hit in terms of performance will come from the fact that you're using ODBC. I hope you're only using it for development! Furthermore, I would encapsulate the database specific stuff in a bean, and use that bean in the JSP page as opposed to writing that code directly into the JSP page. Additionally, why are you using prepareCall() when you're not calling a stored procedure? Use prepareStatement() instead!
0
 

Author Comment

by:Gar04
ID: 10995642
Sorry, i am using a prepared statement guys, i just cut and paste the code mid progress
and hadn't changed everything. I wanted to be sure that i could use more than one first.
I will finish wha i started and then i will ask you guys for some more pointers. Not sure about the bean
suggestion as i don't know enough about that approach.
Gaz
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10996144
A bean is merely, in its most basic form, a class with some properties and getters and setters for those properties. The naming of the setters and getters follows a certain (simple) pattern. E.g.
Property name: String name;
Setter name: void setName(String name)
Getter name: String getName()

Look into the jsp:usebean tag. Here's some docs: http://javaalmanac.com/egs/javax.servlet.jsp/usebean.jsp.html?l=rel
0
 
LVL 5

Expert Comment

by:rsalzmann
ID: 10997086
Gar,

if you're prototyping, JSP is a good approach for now.  You will want to get more familiar with using JavaBeans when you begin to implement your final solution.  

The main issue here between beans and JSP is maintainability and memory use.  Moving most of the logic in the JSP to beans(and subsequently business logic classes will improve the maintainability of your code, and streamline the footprint of your JSP(running as servlet).

Beans allow you to separate the various logical functions of the JSP into standard java classes.   You use the same java code in a bean as in the java portions of the JSP.  

Again, if your main goal for now is to achieve a working translation of the VB app in Java, then JSP is fine.  

regards
robb
0
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

 
LVL 5

Expert Comment

by:rsalzmann
ID: 10997150
gar,

Don't forget to add break; 's to the end of cases 1 - 4 here:
 -robb
 
         switch (a.length - 1) {
               case 0: sCompanyID = sTemp; break;
               case 1: sCompanyID = a[0].substring(0, 3) + a[1].substring(0, 2);
               case 2: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
               case 3: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
               case 4: sCompanyID = a[0].substring(0, 1) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1) + a[4].substring(0, 1);
0
 

Author Comment

by:Gar04
ID: 10998457
i think i understand how the bean works now, i had started an attempt to implement this in JSP code(below)
the logical flow of the data is as follows:
Sitesearch.jsp page // user selects data to run a data search ---> data is returned and displayed in the
SearchResults.jsp page// user then checks boxes beside each record(they select those they wish to book) and on submit ---> data is sent to BookingPage.jsp page, here the data is displayed again along with a form for the customer to enter
customer information and campaign information.
essentially, this is the page that you suggest using the bean, the problem i am concerned/ignorant about is how do i get
the bean to work with the data just sent to the page, I think that i can figure out how to get it working with the data to be entered though??????? how do you suggest i merge the two processes???
gaz

this is what i was working on thus far:
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
PreparedStatement p = null;
PreparedStatement z = null;
PreparedStatement u = null;
PreparedStatement t = null;
Statement ca = null;
ResultSet rs = null;
String qSQL = "SELECT * FROM CustomerInformation ORDER BY CustomerID DESC";
String cSQL = "SELECT TOP 1 * FROM SiteBookings ORDER BY BookDate DESC";
String sSQL = "SELECT * FROM CampaignInformation ORDER BY CampaignRef DESC";
try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillBoardCompany");
  PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteBookings.CycleID, SiteBookings.BookYear FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings.BookingsNo = ?");
    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();
  ca = st.executeQuery(
  PreparedStatement z = conn.prepareStatement("INSERT INTO SiteBookings" +
  "(CustomerID, CampaignRef, Price)" + "VALUES(?,?,?)" + "WHERE SiteBookings.BookingsNo = ?");
  z.setString(1,Custid);
  z.setString(2,Campref);
  z.setString(3,Prices);
  z.setInt(4,bookno);
  PreparedStatement u = conn.pepareStatement("INSERT INTO CustomerInformation" +
  "(CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress)" + "VALUES(?,?,?,?,?,?,?,?,?)" );
  u.setString(1,Custid);
  u.setString(2,Compname);
  u.setString(3,Compaddress);
  u.setString(4,Cities);
  u.setString(5,Countries);
  u.setString(6,Postcode);
  u.setString(7,Phone);
  u.setString(8,Fax);
  u.setString(9,Email);
  PreparedStatement t = conn.prepareStatement("INSERT INTO CampaignInformation" +
  "(CampaignRef, ProductName, ProductCategory, Price)" + "VALUES(?,?,?,?)");
  t.setString(1,Campref);
  t.setString(2,Prodname);
  t.setString(3,Prodcategory);
  t.setString(4,Prices);
  %>
0
 

Author Comment

by:Gar04
ID: 10998554
Hey
ok Robb
thanks for the advice, thus far.
The problem is that i am not a programmer, and just have a very broad understanding of this stuff.
I think that i can implement it with jsp maybe???!!!
i imagine that i will have problems with the structure.
this is where your expert advice will hopefully pull me out, provided i can understand you guys!
Gaz
0
 

Author Comment

by:Gar04
ID: 11002169
Hey Orange and Robb,
i took your advice on the bean and have attempted to implement it, below is what i have so far, any help
would be great as i am a bit lost now, especially, when trying to integrate your CustomerID VBtoJava method:

package com.ora.jsp.beans.userinfo;
//Various package imports
import java.io.*;
import java.util.*;
import java.sql.*;
import java.text.DecimalFormat;
import javax.swing.JOptionPane;
import com.ora.jsp.util.*;// this is a little program used to validate email address
//bean used to capture and validate user input for booking
//also, used to save data to the Access Db.
public class BookBean implements Serializable {
   
    // Bean Properties
    private String Booknum,CustomerID,compName,companyAdd;
    private String citiEs,countriEs,postCode,phoNe,FaxNum;
    private String emailAddress;
    private String prodName,prodCat,pricEs;
    private boolean isInitialized;
   
    // the set and get methods      
    public String getBookingsNo() {
        return bookNum;
    }

    public void setBookingsNo(String bookNum) {
        isInitialized = true;
        this.bookNum = bookNum;
    }
   
     public String getCustomerID() {
        return custId;
    }

    public void setCustomerID(String custId) {
        isInitialized = true;
        this.custId = custId;
    }

    public String getCompanyName() {
        return compName;
    }

    public void setCompanyName(String compName) {
        isInitialized = true;
        this.compName = compName;
    }

    public String getCompanyAddress() {
        return companyAdd;
    }

    public void setCompanyAddress(String companyAdd) {
        isInitialized = true;
        this.companyAdd = companyAdd;
    }

    public String getCity() {
        return citiEs;
    }

    public void setCity(String citiEs) {
        isInitialized = true;
        this.citiEs = citiEs;
    }

    public String getCountry() {
        return countriEs;
    }

    public void setCountry(String countriEs) {
        isInitialized = true;
        this.countriEs = countriEs;
    }
     
    public String getPostalCode() {
        return postCode;
    }

    public void setPostalCode(String postCode) {
        isInitialized = true;
        this.postcode = postCode;
    }

    public String getPhone() {
        return phoNe;
    }

    public void setPhone(String phoNe) {
        isInitialized = true;
        this.phoNe = phoNe;
    }
   
    public String getFax() {
        return faxNum;
    }  
     
    public void setFax(String faxNum) {
        isInitialized = true;
        this.faxNum = faxNum;
    }
   
     public String getEmailAddress() {
        return emailAddress;
    }

    public void setEmailAddress(String emailAddress) {
        isInitialized = true;
        this.emailAddress = emailAddress;
    }
   
    public String getProductName() {
        return prodName;
    }

    public void setProductName(String prodName) {
        isInitialized = true;
        this.prodName = prodName;
    }
   
    public String getProdCatgory() {
        return prodCat;
    }

    public void setProductCategory(String prodCat) {
        isInitialized = true;
        this.prodCat = prodCat;
    }
   
    public String getPrice() {
        return pricEs;
    }

    public void setPrice(String pricEs) {
        isInitialized = true;
        this.pricEs = pricEs;
    }//end get and set methods
   
   
    // attempt to generate CustomerID
    public class VBToJava {
     
     String companyName = null;
     String customerID = null;
     DecimalFormat df = new DecimalFormat("00");
     
     private void companyNameAfterUpdate() {
         
          // Generates a CustomerID for new Customer
          // Validate we have a new value to work with
          if (this.companyName.trim().length() == 0) {
               JOptionPane.showMessageDialog(null, "You must enter a company name");
               return;
          }
         
          String sTemp = this.companyName.trim();
          String sCompanyID = "";
         
          // replace double spaces with single spaces
          sTemp = sTemp.replaceAll("  ", " ");
         
          // if shorter than 5 chars pad with zeros
          if (sTemp.length() < 5) {
               sTemp += "     ".substring(sTemp.length());
          }
         
          // split into an array
          String []a = sTemp.split(" ");
         
          // builds the CustomerID using case statements
         
          switch (a.length - 1) {
               case 0: sCompanyID = sTemp; break;
               case 1: sCompanyID = a[0].substring(0, 3) + a[1].substring(0, 2);
               case 2: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
               case 3: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
               case 4: sCompanyID = a[0].substring(0, 1) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1) + a[4].substring(0, 1);
          }
         
          // Do loop test for existence of sCompanyID >>>>>>>>>i guess i would need some server side testing here to check for an
          //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> customerid in the database
          String sWhere;
          int i = 1;
          sWhere = "CustomerID='" + sCompanyID + "'";
         
          do {
               sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
          } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);
         
          // converts to uppercase
          sCompanyID = sCompanyID.toUpperCase();
         
          //assigns value to CustomerID
          this.customerID = sCompanyID;
         
          a = null;  // not needed
     }
     
     private int numberOfRecordsMatching(String expr, String Domain, String query) {
          return 0; // to be implemented!!!
     }
}

    //Method to insert bookings data in SiteBookings tbl.
    public void insertBookdata() throws ClassNotFoundException, SQLException  {
                
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  Connection conn = null;
                  PreparedStatement as = null;
                  
            try {
                   if (!isValid() ){
                 throw new SQLException( "invalid data - from BookBean.insertBookdata" );
                 }
                 else {
                  conn = DriverManager.getConnection( "jdbc:odbc:BillboardCompany");
                    as = conn.prepareStatement( "INSERT INTO SiteBookings" +
                    "(CustomerID, CampaignRef,Price)" + "values( ?,?,? )" );
                    as.setString( 1, custId );
                    as.setString( 2, campName );
                    as.setString( 3, pricEs);
                    
                    as.executeUpdate();
                   }
              
                  }
                  
              finally {
                                  if( as != null )
                                
                                  try {as.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                                
                                  if( conn!=null)
                                
                                  try {conn.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                             }
            }
    //Method to insert Customer Information
    public void insertCusdata() throws ClassNotFoundException, SQLException  {
                
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  Connection conn = null;
                  PreparedStatement cs = null;
                  
            try {
                   if (!isValid() ){
                 throw new SQLException( "invalid data - from BookBean.insertCusdata" );
                 }
                 else {
                  conn = DriverManager.getConnection( "jdbc:odbc:BillboardCompany");
                    cs = conn.prepareStatement( "INSERT INTO CustomerInformation" +
                    "(CustomerID, CompanyName, CompanyAddress, City, Country,PostCode,PhoneNumber,FaxNumber,EmailAddress)" + "values( ?, ?, ?, ?, ?, ?,?,?,? )" );
                    cs.setString( 1, custId );
                    cs.setString( 2, compName );
                    cs.setString( 3, compAddress );
                    cs.setString( 4, citiEs );
                    cs.setString( 5, countriEs );
                    cs.setString( 6, postCode );
                    cs.setString( 7, phoNe );
                    cs.setString( 8, faxNum );
                    cs.setString( 9, emailAddress );
                    
                    cs.executeUpdate();
              }
              
                   
                  }
                  
                  finally {
                                  if( cs != null )
                                
                                  try {cs.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                                
                                  if( conn!=null)
                                
                                  try {conn.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                               
                               }
            
    }
    //Method to insert Campaign Information
    public void insertCampdata() throws ClassNotFoundException, SQLException  {
                
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  Connection conn = null;
                  PreparedStatement bs = null;
                  
            try {
                   if (!isValid() ){
                 throw new SQLException( "invalid data - from BookBean.insertCampdata" );
                 }
                 else {
                  conn = DriverManager.getConnection( "jdbc:odbc:BillboardCompany");
                    bs = conn.prepareStatement( "INSERT INTO CampaignInformation" +
                    "(CampaignRef,ProductName, ProductCategory,Price)" + "values( ?, ?, ?, ? )" );
                    bs.setString( 1, campRef );
                    bs.setString( 2, prodName );
                    bs.setString( 3, prodCat );
                    bs.setString( 4, pricEs );
                    
                    
                    bs.executeUpdate();
              }
              
                   
                  }
                  
                  finally {
                                  if( bs != null )
                                
                                  try {bs.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                                
                                  if( conn!=null)
                                
                                  try {conn.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                                }
            }      


     
    //Validation Methods for the Booking Data
   public String getPropertyStatusMsg() {
               
               String msg ="Thank you for using the Look-See Biilboards bookings System!";
          if (!isInitialized ()) {
          msg ="Please enter values in all fields";
          }
          else if (!isValid()) {
          msg ="The Following data is missing or invalid: ";
          }
          return msg;
          
          }
    // Method for printing validation error details      
    public String[] getPropertyStatusDetails() {
    Vector details = new Vector();
    if (isInitialized() && !isValid()) {
          
          if (compName == null) {
                      details.addElement("a company name must be entered!");
                      }//end if
                
          if (compAddress == null) {
                      details.addElement("A company address must be entered!");
                      }//end if            
            
            if (citiEs == null) {
                      details.addElement("A city name or relevant data must be entered! ");
                      }// end if
                      
          if (countriEs == null) {
                      details.addElement("A country name or relevant data must be entered!");
                      }// end if       
          
          if (postCode == null) {
                      details.addElement("A postcode or relevant data must be entered");
                      }// end if
          
          if (phoNe == null) {
                      details.addElement("A phone number must be entered");
                      }//end if
                      
          if (emailAddress == null) {
                      details.addElement("An Email Address must be entered");
                      }// end if
                else if (!StringFormat.isValidEmailAddr(emailAddress))  {
                                  details.addElement("Invalid Email Format:"+emailAddress+",try this format:'bob@name.com'");
                            }// end if else
                            
          }// end if
          
          // set details into array
          String[] arr = new String[details.size()];
          details.copyInto(arr);
          return arr;
    }      
    //initialize fields
    public boolean isInitialized()  {
          return isInitialized;
          }//end isInitialized
          
   
    // returns true if values are valid
    public boolean isValid() {
       return compName != null &&
                  CompAddress != null && citiEs != null && countriEs != null &&
                  postCode != null && phoNe != null && faxNum != null &&
            StringFormat.isValidEmailAddr(emailAddress);
       }//end isValid

   
 }
0
 

Author Comment

by:Gar04
ID: 11002274
hey, my head is a bit of a muddle cos i have just tried to get it around javabeans.
i need help. If you guys are willin' to help out i would appreciate it a lot
i am trying to figure out how to integrate the   public class VBToJava {
class that Orange sent to me, but am not sure what to do next.

Any pointers would be great.
regards.
Gaz
0
 
LVL 5

Expert Comment

by:rsalzmann
ID: 11006810
First of all, remember that all Java code runs on the server.  Even the JSP code.  JSP's get compiled and run as a servlet by the application server.  This is why while learning/prototyping, you can get away with using all JSP's .  

I mention the fact that all java code runs on the server because you are trying to launch a dialog if the company name is blank.  If this dialog were to get launched, it would appear on the server, not in the client, and you would wait for the server to respond at the client, while the server waits for the dialog to be OK'd at the server.

Consider validating user input with JavaScript in the client before submitting the form.

Beans won't gain you that much right now, and will cost you more time to get something working, if a deadline is of concern.  

Finally, I'm happy to help, but I think you should read the EE FAQ, specifically this section under "Big Mistakes":
http://www.experts-exchange.com/Programming/Programming_Languages/Java/help.jsp#hi107

kind regards,
robb
0
 

Author Comment

by:Gar04
ID: 11009059
Hey Robb,
I read the EE FAQ like you suggested, I hope that you are referring to the asking of more than one question in a thread??
well, that is what i have understood you to mean. The problem is that it is all the same question. I am just uber confused as to the implementation. Moreover, the problem is that i can't validate with javascript because i don't know javascript. In fact, i really don't know that much java yet (just learning), but i am a bit stuck for time.
If it seems that i am asking multiple questions it is because i have been unable to solve the intitial question with the advice and direction provided by the experts thus far.
I have decided to go back to JSP.
It seems easier for now.
Have you any idea how i can integrate this java code with the JSP below:
// this is supposed to generate a customer id from the CompanyName entered by a user in the JSP form.
public class VBToJava {
     
     String compName = null;
     String custId = null;
     DecimalFormat df = new DecimalFormat("00");
     
     private void companyNameAfterUpdate() {
         
          // Generates a CustomerID for new Customer
          // Validate we have a new value to work with
          //if (this.companyName.trim().length() == 0) {
               //JOptionPane.showMessageDialog(null, "You must enter a company name");
               //return;
          //}
         
          String sTemp = this.compName.trim();
          String sCompanyID = "";
         
          // replace double spaces with single spaces
          sTemp = sTemp.replaceAll("  ", " ");
         
          // if shorter than 5 chars pad with zeros
          if (sTemp.length() < 5) {
               sTemp += "     ".substring(sTemp.length());
          }
         
          // split into an array
          String []a = sTemp.split(" ");
         
          // builds the CustomerID using case statements
         
          switch (a.length - 1) {
               case 0: sCompanyID = sTemp; break;
               case 1: sCompanyID = a[0].substring(0, 3) + a[1].substring(0, 2);
               case 2: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
               case 3: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
               case 4: sCompanyID = a[0].substring(0, 1) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1) + a[4].substring(0, 1);
          }
         
          //in this section of the code i need t be able to check that the newly generated Customer ID is unique
          // i still haven't fully figured out how to do this yet???
          String sWhere;
          int i = 1;
          sWhere = "CustomerID='" + sCompanyID + "'";
         
          do {
               sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
          } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);
         
          // converts to uppercase
          sCompanyID = sCompanyID.toUpperCase();
         
          //assigns value to CustomerID
          this.custId = sCompanyID;
         
          a = null;  // not needed
     }
      private int numberOfRecordsMatching(String expr, String Domain, String query) {
          return 0; // to be implemented!!!
     }
}

this is the main part of the JSP page so far:
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
PreparedStatement p = null;// pulls data from prior booking
PreparedStatement z = null;// inserts data into SiteBookings
PreparedStatement u = null;// inserts data into CustomerInformation
PreparedStatement t = null;// inserts data into CampaignInformation
String qSQL = "SELECT * FROM CustomerInformation ORDER BY CustomerID DESC";
String cSQL = "SELECT TOP 1 * FROM SiteBookings ORDER BY BookDate DESC";
String sSQL = "SELECT * FROM CampaignInformation ORDER BY CampaignRef DESC";
Statement ca = null;
String campRef; String custId;
ResultSet rs = null; ResultSet qs = null; ResultSet cs = null; ResultSet ss = null;

try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillBoardCompany");
  PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteBookings.CycleID, SiteBookings.BookYear FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings.BookingsNo = ?");
    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();
  PreparedStatement z = conn.prepareStatement("INSERT INTO SiteBookings" +
  "(CustomerID, CampaignRef, Price)" + "VALUES(?,?,?)" + "WHERE SiteBookings.BookingsNo = ?");
  z.setString(1,custId); z.setString(2,campRef); z.setString(3,pricEs); z.setInt(4,bookno);
  PreparedStatement u = conn.pepareStatement("INSERT INTO CustomerInformation" +
  "(CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress)" + "VALUES(?,?,?,?,?,?,?,?,?)" );
  u.setString(1,custId); u.setString(2,compName); u.setString(3,compAddress); u.setString(4,citiEs);
  u.setString(5,countriEs); u.setString(6,postCode); u.setString(7,phoNe); u.setString(8,faxNum); u.setString(9,emailAddress);
  PreparedStatement t = conn.prepareStatement("INSERT INTO CampaignInformation" +
  "(CampaignRef, ProductName, ProductCategory, Price)" + "VALUES(?,?,?,?)");
  t.setString(1,campRef); t.setString(2,prodName); t.setString(3,prodCat); t.setString(4,pricEs);
 
  %>
0
 
LVL 5

Expert Comment

by:rsalzmann
ID: 11011798
Yes, you hit the nail on the head :->  BTW, i know how frustrating it can be to translate from Access to Java.  I'm pretty good with PERL, so In my case I did as many mass translations as possible using search and replace regular expressions.  Just an idea if you have any background in PERL.

I'm glad you decided to continue with JSP for now.  Baby steps: Get it to compile, get it to run, get it to run without errors and erroneous output, freeze that code, then begin refactoring.  When you get good enough, you can begin to architect things better at the outset.  Sometimes its better to get something out there that works right away, and then go back and improve on it.  ok, enough preaching, let's see about that mass of code up there....

Put your class-wide variables and methods in a declaration section, it's the <%! ... %> section.  I think you can pretty much copy everything in VBtoJava into the declaration section and it will be ok.  Just watch your brackets as you copy and paste.   Then in the existing section, just call the methods and assign the variables as needed.  

Also, while developing, consider using single purpose JSP's to keep the code short and easier to read.  As each one passes unit testing, then begin to combine them as needed.  Smaller bites are easier to chew, with this translation project you're already sucking meatballs through a straw :P

~robb
0
 

Author Comment

by:Gar04
ID: 11017280
Hey Robb,
this is what i have up to now, i still feel a bit lost can you possibly examine and edit/give me pointers
as to what i need to do next.
one main question i have regards the VBtoJava class, i thought that methods that set or return a value
cannot have void in the declaration: private void companyNameAfterUpdate() {
this does. I figure that when i call this method i also need to be able to pass arguments to it
like: private void companyNameAfterUpdate(compName, custId) ???
As you may well have guessed, i am very confused now.
Any help would be appreciated.

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<%!
import java.text.DecimalFormat;
import javax.swing.JOptionPane;

public class VBToJava {
     
     String compName = null;
     String custId = null;
     DecimalFormat df = new DecimalFormat("00");
     
     private void companyNameAfterUpdate() {
         
          // Generates a CustomerID for new Customer
          // Validate we have a new value to work with
          //if (this.companyName.trim().length() == 0) {
               //JOptionPane.showMessageDialog(null, "You must enter a company name");
               //return;
          //}
         
          String sTemp = this.compName.trim();
          String sCompanyID = "";
         
          // replace double spaces with single spaces
          sTemp = sTemp.replaceAll("  ", " ");
         
          // if shorter than 5 chars pad with zeros
          if (sTemp.length() < 5) {
               sTemp += "     ".substring(sTemp.length());
          }
         
          // split into an array
          String []a = sTemp.split(" ");
         
          // builds the CustomerID using case statements
         
          switch (a.length - 1) {
               case 0: sCompanyID = sTemp; break;
               case 1: sCompanyID = a[0].substring(0, 3) + a[1].substring(0, 2);
               case 2: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
               case 3: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
               case 4: sCompanyID = a[0].substring(0, 1) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1) + a[4].substring(0, 1);
          }
         
          //in this section of the code i need t be able to check that the newly generated Customer ID is unique
          // i still haven't fully figured out how to do this yet???
          String sWhere;
          int i = 1;
          sWhere = "CustomerID='" + sCompanyID + "'";
         
          do {
               sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
          } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);
         
          // converts to uppercase
          sCompanyID = sCompanyID.toUpperCase();
         
          //assigns value to CustomerID
          this.custId = sCompanyID;
         
         // a = null;  // not needed
     }
      private int numberOfRecordsMatching(String expr, String Domain, String query) {
          return 0; // to be implemented!!!
     }
}
 %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = null;
PreparedStatement p = null;// pulls data from prior booking
PreparedStatement z = null;// inserts data into SiteBookings
PreparedStatement u = null;// inserts data into CustomerInformation
PreparedStatement t = null;// inserts data into CampaignInformation
String qSQL = "SELECT * FROM CustomerInformation ORDER BY CustomerID DESC";
String cSQL = "SELECT TOP 1 * FROM SiteBookings ORDER BY BookDate DESC";
String sSQL = "SELECT * FROM CampaignInformation ORDER BY CampaignRef DESC";
String campRef; String custId;
ResultSet rs = null; ResultSet qs = null; ResultSet cs = null; ResultSet ss = null;

try
{
  conn = DriverManager.getConnection("jdbc:odbc:BillBoardCompany");
  Statement st = conn.createStatement();
  PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteBookings.CycleID, SiteBookings.BookYear FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID WHERE SiteBookings.BookingsNo = ?");
    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();
  PreparedStatement z = conn.prepareStatement("INSERT INTO SiteBookings" +
  "(CustomerID, CampaignRef, Price)" + "VALUES(?,?,?)" + "WHERE SiteBookings.BookingsNo = ?");
  z.setString(1,custId); z.setString(2,campRef); z.setString(3,pricEs); z.setInt(4,bookno);
  PreparedStatement u = conn.pepareStatement("INSERT INTO CustomerInformation" +
  "(CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress)" + "VALUES(?,?,?,?,?,?,?,?,?)" );
  u.setString(1,custId); u.setString(2,compName); u.setString(3,compAddress); u.setString(4,citiEs);
  u.setString(5,countriEs); u.setString(6,postCode); u.setString(7,phoNe); u.setString(8,faxNum); u.setString(9,emailAddress);
  PreparedStatement t = conn.prepareStatement("INSERT INTO CampaignInformation" +
  "(CampaignRef, ProductName, ProductCategory, Price)" + "VALUES(?,?,?,?)");
  t.setString(1,campRef); t.setString(2,prodName); t.setString(3,prodCat); t.setString(4,pricEs);
  qs = st.executeQuery(qSQL);
   if (qs.next()){
              if (!qs.getString("CompanyName").equals(compName) && !qs.getString("CompanyName").equals(compAddress) && !qs.getString("PostalCode").equals(postCode) && !qs.getString("PhoneNumber").equals(phoNe)) {
               custId = !qs.getString("CustomerID");
              }
     } else {
          //custId = method(args) use little method to generate CustomerID and assign it to custId.
     }
       cs = st.executeQuery(cSQL);
       if (cs.next()){
                if (!cs.getString("SiteID").equals(rs.getString("SiteID")) && !cs.getString("CustomerID").equals(rs.getString("CustomerID")) && !cs.getString("CycleID").equals(rs.getString("CycleID"))) {
                 campRef = !cs.getString("CampaignRef");// i.e. get last cid method
                     }
                     }else{
                     //campRef = method(args) get new cid method
                     }
                    
            ss = st.executeQuery(sSQL);
            if (ss.next()){
            // not equal to =if (!ss.getString("CampaignRef").equals(campRef) && !ss.getString("ProductName").equals(prodName) && !ss.getString("ProductCategory").equal(prodCat)){
             // execute this query = t.executeQuery();
             // }         
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>BookingPage</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">



</head>
<body>

<table width="702" border="1" align="center" cellpadding="0" cellspacing="1" bgcolor="#FFFFFF">
  <!--DWLayoutTable-->
  <tr>
    <td width="91" height="123" rowspan="3" align="center" valign="bottom" bgcolor="#FFFFFF">
      <div align="left"></div>
      <div align="right"></div>
      <div align="right"></div></td>
    <td width="602" height="21" align="left" valign="bottom" bgcolor="#FFFFFF"><!--DWLayoutEmptyCell-->&nbsp;</td>
  </tr>
  <tr>
    <td height="21" align="left" valign="bottom" bgcolor="#FFFFFF"><!--DWLayoutEmptyCell-->&nbsp;</td>
  </tr>
  <tr>
    <td rowspan="2" align="center" valign="top" bgcolor="#FFFFFF">
      <form name="form1" method="post" action="">
        <table width="100%" border="1" cellpadding="0" cellspacing="0" bordercolor="#3399CC">
          <tr>
            <td width="30%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Booking
                No.</font></div></td>
            <td width="30%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Site
                ID</font></div></td>
            <td colspan="3" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Site
                Location </font></div></td>
            <td width="30%" colspan="2" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Cycle
                ID </font></div></td>
            <td width="30%" 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 colspan="3"><div align="center"><%=rs.getString("SiteLocation")%></div></td>
            <td colspan="2"><div align="center"><%=rs.getString("CycleID")%></div></td>
            <td><div align="center"><%=rs.getString("BookYear")%></div></td>
          </tr>
          <tr>
            <td height="3"> <div align="center"></div></td>
            <td><div align="center"></div></td>
            <td colspan="3"><div align="center"></div></td>
            <td colspan="2"><div align="center"></div></td>
            <td><div align="center"></div></td>
          </tr>
          <tr>
            <td height="21" colspan="8" bgcolor="#336699">
              <div align="center"><font color="#FFFFFF" size="2">Please enter
                the relevant Customer Information below.</font></div></td>
          </tr>
          <tr>
            <td height="4" colspan="8">
              <div align="center"></div>
              <div align="center"></div>
              <div align="center"></div>
              <div align="center"></div>
              <div align="center"> </div>
              <div align="center"></div>
              <div align="center"></div>
              <div align="center"></div></td>
          </tr>
          <%}%>
         
        </table>
       
        <table width="100%" height="156" border="0" cellpadding="0" cellspacing="0">
          <tr>
            <td width="19%" height="45"> <div align="center"><font size="2">Customer
                Name</font></div></td>
            <td> <div align="left">
                <input type="text" name="compName">
              </div></td>
            <td width="18%"><div align="center"><font size="2">Customer Address</font></div></td>
            <td> <div align="left">
                <input name="compAddress" type="text" size="30">
              </div></td>
          </tr>
          <tr>
            <td height="36"> <div align="center"><font size="2">City</font></div></td>
            <td> <div align="left">
                <input type="text" name="citiEs">
              </div></td>
            <td><div align="center"><font size="2">Country</font></div></td>
            <td> <input type="text" name="countriEs"> <div align="center"></div></td>
          </tr>
          <tr>
            <td height="18"> <div align="center"><font size="2">Postcode</font></div></td>
            <td> <div align="left">
                <input type="text" name="postCode">
              </div></td>
            <td><div align="center"><font size="2">Phone</font></div></td>
            <td> <input type="text" name="phoNe"></td>
          </tr>
          <tr>
            <td height="5"><div align="center"><font size="2">Fax</font></div></td>
            <td><input type="text" name="faxNum"></td>
            <td><div align="center"><font size="2">Email</font></div></td>
            <td><input type="text" name="emailAddress"></td>
          </tr>
          <tr bgcolor="#336699">
            <td height="2" colspan="4"> <div align="center"><font color="#FFFFFF" size="2">Please
                enter the relevant Campaign Information below.</font></div></td>
          </tr>
          <tr bgcolor="#FFFFFF">
            <td height="3" colspan="4">&nbsp;</td>
          </tr>
          <tr>
            <td height="9"><div align="center"><font size="2">Product Name</font></div></td>
            <td><input type="text" name="prodName"></td>
            <td><div align="center"><font size="2">Product Category</font></div></td>
            <td><input type="text" name="prodCat"></td>
          </tr>
        </table>
        <p>
          <input type="submit" name="Submit" value="Submit">
        </p>
        <p>&nbsp;</p>
      </form></td>
  </tr>
  <tr>
    <td width="91" height="343" align="center" valign="bottom" bgcolor="#FFFFFF"><!--DWLayoutEmptyCell-->&nbsp;</td>
  </tr>
  <tr>
    <td height="50" colspan="2" align="center" valign="bottom" bgcolor="#FFFFFF">&nbsp;</td>
  </tr>
</table>
</body>
</html>
 <%}%>
          <% }
    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(); }
    }
%>
 
0
 
LVL 5

Assisted Solution

by:rsalzmann
rsalzmann earned 250 total points
ID: 11017991
I've rewritten the top part of your jsp as follows, see also enclosed comments...

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<%@ page import="java.text.DecimalFormat"%>
<%!          
     private void companyNameAfterUpdate(String compName) {
          DecimalFormat df = new DecimalFormat("00");
          String sTemp = compName.trim();
          String sCompanyID = "";
         
          // replace double spaces with single spaces
          sTemp = sTemp.replaceAll("  ", " ");

          // if shorter than 5 chars pad with zeros
 
     // RS: if you wish to pad with zeros, it should be sTemp += "00000".substring(sTemp.length());
          if (sTemp.length() < 5) {
               sTemp += "     ".substring(sTemp.length());
          }
         
          // split into an array
          String []a = sTemp.split(" ");
         
          // builds the CustomerID using case statements
         
          switch (a.length - 1) {
               case 0: sCompanyID = sTemp; break;
               case 1: sCompanyID = a[0].substring(0, 3) + a[1].substring(0, 2);
               case 2: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
               case 3: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
               case 4: sCompanyID = a[0].substring(0, 1) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1) + a[4].substring(0, 1);
          }
         
          //in this section of the code i need t be able to check that the newly generated Customer ID is unique
          // i still haven't fully figured out how to do this yet???
         
 // RS: do this in a query, e.g. SELECT * FROM CUST_ID_TBL WHERE CUSTOMER_ID =
          // sCompanyID.substring(0, 3) + df.format(i)
          // then test for uniqueness by checking the resultSet:
          // boolean unique = rs.first();  <-returns false if there are no rows inthe resultset.
          // no need for  all the looping below this way.
          String sWhere;
          int i = 1;
          sWhere = "CustomerID='" + sCompanyID + "'";
         
          do {
               sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
          } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);
         
          // converts to uppercase
          sCompanyID = sCompanyID.toUpperCase();
         
          //assigns value to CustomerID
          this.custId = sCompanyID;
         
         // a = null;  // not needed
     }
      private int numberOfRecordsMatching(String expr, String Domain, String query) {
          return 0; // to be implemented!!!
     }
}
 %>
 
~ robb
0
 

Author Comment

by:Gar04
ID: 11018550
Cheers Robb,
I was actually in the process of doing what you just did, i had pulled the code into an ide to see if i could
get the method to run, however, i was getting a few errors relating to the .split()
i didn't understand this at all and was a bit stuck, thank you for lending a hand.

regarding the check for uniqueness of the CustomerID, i guess i really don't need it in this code
i can just use this outside of this method:
// the qSQL query is : String qSQL = "SELECT * FROM CustomerInformation ORDER BY CustomerID DESC";

qs = st.executeQuery(qSQL);
   if (qs.next()){
              if (!qs.getString("CompanyName").equals(compName) && !qs.getString("CompanyName").equals(compAddress) && !qs.getString("PostalCode").equals(postCode) && !qs.getString("PhoneNumber").equals(phoNe)) {
               
            custId = !qs.getString("CustomerID");// the already saved CustomerID
             
     }
     
} else {
             // call the method here to get the new CustomerID
          custId = companyNameAfterUpdate(String compName);
     }

is this correct, what do you think???
Gaz
 
0
 
LVL 5

Expert Comment

by:rsalzmann
ID: 11019420
Sounds like you are catching on fast Gaz!  Good-on-ya!  ~robb
0
 

Author Comment

by:Gar04
ID: 11019895
I hope that you're right mate,
i am slightly worried about the: <%@ page import="java.text.DecimalFormat"%>
if i don't need the loop anymore then i don't need this:  String sWhere;
          int i = 1;
          sWhere = "CustomerID='" + sCompanyID + "'";
         
          do {
               sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
          } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);
         
do i ????????? or am i wrong???

Gaz
0
 
LVL 5

Expert Comment

by:rsalzmann
ID: 11019909
I think you are right.  But if you take it out, and get an error immediately after(the page won't even load), then most likely you are still using it somewhere else in the code.  I made a quick scan of the code again and think it will be ok.
~robb
0
 

Author Comment

by:Gar04
ID: 11020334
ok cheers Robb
i will keep workin on it and when i hit a wall
i guess that i can ask
btw thanks for the help so far its been good
gaz
0
 

Author Comment

by:Gar04
ID: 11385171
hey
i am sos i haven't been back for a while with this one
i have had a lot of stuff going on
however, i have not yet resolved this yet and would like to
pls help more if you can and don't close this one down just yet

thanks
gaz
0
 

Author Comment

by:Gar04
ID: 11389263
hey orangehead 911 et al

orangehead 911 you wrote a line in the code below that you sent a long time back, that i just don't understand
and can't seem to get it to work
"  // split into an array
          String []a = sTemp.split(" "); "
i can't find 'split' in the java API and my compiler won't recognise it...... do you think that you could shed some light on this and perhaps let me know if the string tokenizer would work instead?????????????????????????????????????????????

Gaz
:)

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<%@ page import="java.text.DecimalFormat"%>
<%!          
     private void companyNameAfterUpdate(String compName) {
          DecimalFormat df = new DecimalFormat("00");
          String sTemp = compName.trim();
          String sCompanyID = "";
         
          // replace double spaces with single spaces
          sTemp = sTemp.replaceAll("  ", " ");

          // if shorter than 5 chars pad with zeros
 
     // RS: if you wish to pad with zeros, it should be sTemp += "00000".substring(sTemp.length());
          if (sTemp.length() < 5) {
               sTemp += "     ".substring(sTemp.length());
          }
         
          // split into an array
          String []a = sTemp.split(" ");
         
          // builds the CustomerID using case statements
         
          switch (a.length - 1) {
               case 0: sCompanyID = sTemp; break;
               case 1: sCompanyID = a[0].substring(0, 3) + a[1].substring(0, 2);
               case 2: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
               case 3: sCompanyID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
               case 4: sCompanyID = a[0].substring(0, 1) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1) + a[4].substring(0, 1);
          }
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 11393062
>> i can't find 'split' in the java API and my compiler won't recognise it...... do you think that you could shed some light on this and perhaps let me know if the string tokenizer would work instead?????????????????????????????????????????????
The split() method was added to the Java APIs in JDK 1.4. You can use a StringTokenizer instead.
0
 

Author Comment

by:Gar04
ID: 11403287
ok
cheers
so how exactly can i implement the string tokenizer with the code you
posted here?... or would i have to do it entirely differently??
gaz
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 11464824
=-)

Oh, the reason why I didn't get back to you regarding your last question was that I went on vacation.
0
 

Author Comment

by:Gar04
ID: 11466339
hope you had a good time!!!
:)
gaz
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to use external config file with Spring MVC 4 59
count7 challenge 12 69
countX 22 70
JList custom Cell Renderer refresh 15 39
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

707 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

15 Experts available now in Live!

Get 1:1 Help Now