Solved

VB to Java JSP conversion question!!

Posted on 2004-04-28
57
462 Views
Last Modified: 2010-04-01
Hey guys
this question is related to this thread:
http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20940232.html
i will post all the relevant code again so that you'll can earn extra points.

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

Author Comment

by:Gar04
ID: 10939229
this post was from Kuldeepchaturvedi:

"currently where these functions are running????"
0
 

Author Comment

by:Gar04
ID: 10939237
from me

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

Author Comment

by:Gar04
ID: 10939264

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

form me
"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"

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

Author Comment

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

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

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

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

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

    GetNextCID = NextID

GetNextCID_Exit:
    Exit Function

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

End Function

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

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

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

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

GetLastCID_Exit:
    Exit Function

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

End Function
0
 

Author Comment

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

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

 
End Sub
0
 

Author Comment

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

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

On Error GoTo Err_Sav_book_Click

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

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

Err_Sav_book_Click:
    MsgBox Err.Description
    Resume Exit_Sav_book_Click
End Sub
0
 

Author Comment

by:Gar04
ID: 10939375
I guess this cannot be done with javascript because it is only client side and cannot look up db for duplicates
and the like.
Is there callable statements or something to that effect that can manage this??
gaz
0
 

Author Comment

by:Gar04
ID: 10944101
Anybody got any idea about this at all??
pls
Gaz
0
 

Author Comment

by:Gar04
ID: 10945309
nobody got an idea???
Gaz
0
 

Author Comment

by:Gar04
ID: 10958856
Can someone please help
Gaz
0
 

Author Comment

by:Gar04
ID: 10965520
Hi
if possible can someone give me some advice or just let me know if you don't want to help so that i
can close this thread!!
regards
Gaz
0
 

Author Comment

by:Gar04
ID: 10968881
come on guys
throw me bone!!!
gaz
0
 

Author Comment

by:Gar04
ID: 10974030
isn't anyone willing to lend a hand ?????
Gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10975456
Hi Gaz, I need some time to digest this. :c)

Javier
0
 

Author Comment

by:Gar04
ID: 10977623
hey no problem
if you have any questions please just ask
i guess that i need to have database connections, like Access snapshots to do the necessary comparisons.
I know how it should work but i don't really know how to implement it correctly and i need some expert advice.
I am grateful for any help, javier.
Appreciatively.
Gaz
0
 

Author Comment

by:Gar04
ID: 10980030
Hey Jarasa,
this is some code from another thread for the CustomerID generation i pretty much understand how it works
but not sure how to incorporate it with JSP page and database integration:

File VBToJava.java:

import java.text.DecimalFormat;

import javax.swing.JOptionPane;

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

Expert Comment

by:jarasa
ID: 10986743
Hi Gaz here you hava a short Aprox. it shouldent be too hard to do the rest, if you need more help in certain things/steps do not hesitate to ask.

Take care

Javier

P.S.: I have not much time sorry.

<%
String rSQL = null;
String cSQL = "SELECT TOP 1 * FROM SiteBookings ORDER BY BookDate DESC";
String sSQL = "SELECT * FROM CampaignInformation ORDER BY CampaignRef DESC";
String pSQL = null;
Statement ca = null;
Statement rs = null;

String connectionURL ="jdbc:mysql://localhost:3306/petshopwebsite";
String driver= "com.mysql.jdbc.Driver";
String password = "";
String username ="root";

String mySiteID = request.getParameter("SiteID")==null?"",request.getParameter("SiteID");
String myCustomerID = request.getParameter("myCustomerID")==null?"",request.getParameter("myCustomerID");
String myCycleID = request.getParameter("myCycleID")==null?"",request.getParameter("myCycleID");

// I believe that this should be copntrolled by JavaScript before request but just in case
If (request.getParameter(CompanyName)==null || request.getParameter(CompanyAddress) || request.getParameter(City) || request.getParameter(Country) || request.getParameter(PostalCode) || request.getParameter(PhoneNumber))
      out.println("Please ensure that at least the CompanyName upto and including a phone Number is entered in the CustomerInformation Fields!");
else If (request.getParameter(ProductName) || request.getParameter(ProductCategory) || request.getParameter(Price))
      out.println("Please ensure that all Product Information fields are completed!");

// Retrieves a recordset of the last value saved and compares product information get last record

try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection c = DriverManager.getConnection(connectionURL,username, password);
      Statement st = c.createStatement();
      ResultSet rs = null;
      rs = st.executeQuery(cSQL);

      // tests the current record to previous records to ensure that campaign references comply
      if (rs.next()){
                If (!rs.getString("SiteID").equals(mySiteID) And !rs.getString("CustomerID").equals(myCustomerID) And !rs.getString("CycleID").equals(myCycleID)) {
                  // I have no Idea on how to solve this Gaz
                  // [CampaignRef] = GetLastCID(Forms![NewBookForm]![CycleID], Forms![NewBookForm]![BookYear])
                  // [CustomerID] = Forms![NewBookForm]![CustomerID]
                  // Me("BookDate") = Now()
                }
      } else {
            out.println("No records on DB")
      }
      
      // get recordset of Campaign Information
      rs = null;
      rs = st.executeQuery(sSQL);

      // checks conditions of recordset and allows insert
      if (rs.next()){
      
            rs = null;
            // Same here
            // If Me.CampaignRef <> DLookup("CampaignRef", "CampaignInformation") Then
            // sSQL = "INSERT INTO CampaignInformation(CampaignRef, ProductName, ProductCategory, Price)";
            // What Char is Chr(34)??
            // sSQL = 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) & ");";
            rs = st.executeUpdate(sSQL);
            
      } else {
            rs = null;
            // Same here
            // rSQL = "INSERT INTO CampaignInformation(CampaignRef, ProductName, ProductCategory, Price)"
            // What Char is Chr(34)??
            // 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) & ");"
            rs = st.executeUpdate(sSQL);
      }

}
 
// I guess you can end this up with this steps, right??  
          
%>
0
 

Author Comment

by:Gar04
ID: 10990267
Hey thanks,
i will give it my best shot
just wondering if i can several prepared statement from the same database connection(i guess that it would make things easier
) if not should i just used seperate try and catch blocks???:

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

Expert Comment

by:jarasa
ID: 10993659
You can do that Gaz, but always set to NULL the PS before you redo it.

Javier
0
 

Author Comment

by:Gar04
ID: 10998335
ok javier,(i know that you are busy, sorry for the bother)
i have to admit that i am very confused by all of this right now,
i will show you what i have attempted (i know it is probably wrong) and i will fill
in the rest with pseudo code.
any more help would be great.. Can you tell me if my next move is to try and do the id and ref generation and then the checks in a try and catch with if statements??? if these are met
i can run all the: z.executeUpdate();
u.executeUpdate();
t.executeUpdate();
am i on the right track?????????????
Gaz
<%@ 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";

// i hope that i can use just one connection
try
{                     // this just pulls out the data that the user wishes to book by using the BookingNo retrieved from
                      // the Search result page
  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();
// here i am attempting to insert the newly entered customer and campaign data in the SiteBookings table
// the problem is that i have to have the CustomerID and the CampaignRef generated first and this process requires
// a. generating the codes and checking them against values in the database
// i will have to run the tests here, what i would like is to run all these generation bits and tests just once and enter
// the data if all the conditions are met.  
  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);
  %>
<!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="left" valign="bottom" bgcolor="#FFFFFF"> <p>&nbsp;</p>
      <p>&nbsp;</p>
      <form name="form1" method="post" action="">
        <table width="100%" border="1" cellpadding="0" cellspacing="0" bordercolor="#3399CC">
          <tr>
            <td width="7%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Booking
                No.</font></div></td>
            <td width="8%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Site
                ID</font></div></td>
            <td colspan="2" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Site
                Location </font></div></td>
            <td width="4%" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="1">Cycle
                ID </font></div></td>
            <td width="34%" 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="2"><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>
          <tr>
            <td height="3"> <div align="center"></div></td>
            <td><div align="center"></div></td>
            <td colspan="2"><div align="center"></div></td>
            <td><div align="center"></div></td>
            <td><div align="center"></div></td>
          </tr>
          <tr>
            <td height="2" colspan="6" bgcolor="#336699"><div align="center"><font color="#FFFFFF" size="2">Please
                enter the relevant Information Below</font></div></td>
          </tr>
          <tr>
            <td height="5" colspan="2"><div align="center"><font size="2">Customer
                Name</font></div></td>
            <td width="27%"><input name="Compname" type="text" size="25"></td>
            <td width="20%"><div align="center"><font size="2">Company Address</font></div></td>
            <td colspan="2"><input name="Compadddress" type="text" size="35"></td>
          </tr>
          <tr>
            <td height="25" colspan="2"><div align="center"><font size="2">City</font></div></td>
            <td><input name="Cities" type="text" size="25"></td>
            <td><div align="center"><font size="2">Country</font></div></td>
            <td colspan="2"><input name="Countries" type="text" size="35"></td>
            <td> blah blah blah!!!!!!!!!!!!!</td>
          </tr>
          <tr>
            <td height="18" colspan="2">&nbsp;</td>
            <td colspan="4">&nbsp;</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></td>
  </tr>
</table>
</body>
</html>
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11003306
Well Gaz as I see it you're doing it quite well, I believe that you can use the same Connection also if you want you can use the same PreparedStatement and also the same Resultset, as far as you don't get lost on the use of them and you clean the PS and RS first.

On your code I've seen two things:

First you have the start <HTML> .....  part inside the for so it will get printed as many time as BookingsNo's you have but you have the .......</HTML> out side the for so the HTML code you're generating is gonna be wrong.

The second thing is that you're not executing the INSERTS You should do z.executeQuery(), u.executeQuery(), and t.executeQuery()

For the rest I gues it should work.

Javier
0
 

Author Comment

by:Gar04
ID: 11006721
Ok Jarasa,
Would you believe that i thought i was wrong and tried to implement it all with a bean instead. It is reassuring to know that i am on the right track with the JSP. You are right about the inserts, i haven't entered them yet, as i was only going to enter them if the conditions i propose to code are met. You know, whether the CustomerID and the CampaignRef are duplicate or not and when they are generated.
Gaz
0
 

Author Comment

by:Gar04
ID: 11010182
hey Jarasa,
can i do this:  
if(!cs.getString("SiteID").equals(rs.getString("SiteID"))..........) {

instead of this:
If (!cs.getString("SiteID").equals(SiteID) And !rs.getString("CustomerID").equals(CustomerID) And !rs.getString("CycleID").equals(myCycleID)) {
???????????
Gaz
:)              
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11012463
Well of course.

Anyway Gaz it should be this way with && instead of And that was a mistake of mine.

If (!rs.getString("SiteID").equals(mySiteID) && !rs.getString("CustomerID").equals(myCustomerID) && !rs.getString("CycleID").equals(myCycleID)) {

:c)

Javier
0
 

Author Comment

by:Gar04
ID: 11015938
Hey Javier,
i forgot to ask you if
this: if(!cs.getString("SiteID").equals(rs.getString("SiteID"))..........) {
means 'if cs.getString("SiteID")' is equal to or not equal to ??
i have a suspicion that it mean 'not equal to' but i am not sure?
can you set me straight.
Gaz
0
 

Author Comment

by:Gar04
ID: 11017339
Hey Javier,
i am a bit lost, as you'll probably guess from this code
any help would be excellent
this is what i have so far:

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<%! ///////////////// this is an attempted method to generate a new CustomerID
//////////////////////// it badly needs a bit of work. I guess that it really doesn't need the checks now
/////////////////////// because these can be done in the JSP code and then the method can be called right?
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();
           // }        
%>

0
 
LVL 6

Expert Comment

by:jarasa
ID: 11029132
! ---> Not Equal

Let me check the code, I'll tell you something.

Javier

0
 

Author Comment

by:Gar04
ID: 11035358
hey thanks,
sorry i haven't got bacl to you sooner, but i have been away.
here's the code:

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

<%!
   private void companyNameAfterUpdate(String companyName) {
          DecimalFormat df = new DecimalFormat("00");
          String sTemp = this.companyName.trim();
          String sCustomerID = "";
          // replace double spaces with single spaces
          sTemp = sTemp.replaceAll("  ", " ");
          // if shorter than 5 chars pad with zeros
          if (sTemp.length() < 5) {
               sTemp += "00000".substring(sTemp.length());
              //sTemp += "     ".substring(sTemp.length());
          }
          // split into an array
          String []a = sTemp.split(" ");
          // builds the CustomerID using case statements
          switch (a.length - 1) {
               case 0: sCustomerID = sTemp; break;
               case 1: sCustomerID = a[0].substring(0, 3) + a[1].substring(0, 2);
               case 2: sCustomerID = a[0].substring(0, 2) + a[1].substring(0, 2) + a[2].substring(0, 1);
               case 3: sCustomerID = a[0].substring(0, 2) + a[1].substring(0, 1) + a[2].substring(0, 1) + a[3].substring(0, 1);
               case 4: sCustomerID = 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='" +
               sCustomerID = sCustomerID.substring(0, 3) + df.format(i) + "'";
          } while( numberOfRecordsMatching("CustomerID", "CustomerInformation", sWhere) == 0);
         
          // converts to uppercase
          sCustomerID = sCustomerID.toUpperCase();
         
          //assigns value to CustomerID
          this.custId = sCustomerID;
     }
     

 %>
<% 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();
             // }         
%>
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Expert Comment

by:jarasa
ID: 11038072
Hehe I said let me check the code you posted gaz, you didn't need to post it again.

:c)

I'm a bit busy nowdays, so I can't answer you too fast, hope you understand.

Javier

0
 
LVL 6

Expert Comment

by:jarasa
ID: 11042314
Hey Gaz, I'm not sure if this is what you want, I see not much on the code but that you hava too many opened Branchets.
Javier

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<%! ///////////////// this is an attempted method to generate a new CustomerID
//////////////////////// it badly needs a bit of work. I guess that it really doesn't need the checks now
/////////////////////// because these can be done in the JSP code and then the method can be called right?
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();
                  }
            }        
      }
}
%>

0
 

Author Comment

by:Gar04
ID: 11106917
hey javier,
sorry that i haven't replied sooner, i just haven't had a chance cos i had to travel back to ireland from the USA and things have been a bit hectic, i will check your code this evening(i am in a internet cafe) and will get back on it as soon as i get home.
cheers
Gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11107241
Don't worry gaz.

had a good time in Ireland? or was too chilly?

Javier
0
 

Author Comment

by:Gar04
ID: 11329418
hey javier,
sorry about the huge delay in my responses
i have just arrived back in the USA yesterday and am attempting to get back into the swing of this and get this thing working.

currently i am having problems with two lines in the above code when i attempt to compile this program, have you any ideas???
it concerns the lines with the dot(.) operator!!

--------------------Configuration: JDK version 1.3.1_11 <Default>--------------------
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.java:24: cannot resolve symbol
symbol  : method replaceAll  (java.lang.String,java.lang.String)
location: class java.lang.String
          sTemp = sTemp.replaceAll(" "," ");
                       ^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.java:32: cannot resolve symbol
symbol  : method split  (java.lang.String)
location: class java.lang.String
          String []a = sTemp.split(" ");
                            ^
2 errors

Process completed.

any help would be greatly appreciated,

p.s. i hope that you are well and enjoying the summer :)

Gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11332342
Hi Gaz, and welcome back.

Well the fact is that neither the replaceAll method nor the split one exists on Sting class, you can use:

replace(char oldChar, char newChar)

But remember that a char must be inside singles quotes 'c'

Javier
0
 

Author Comment

by:Gar04
ID: 11335022
Hey Javier
thanks for the reply
you are right i cannot find either 'split' or 'replaceAll' in the java API that i have available
I thought that they belonged to the string class in the java lang but they do not
my IDE is a bit funny in that when i look for an explanation for the class it comes up with nonsense instead of
the good stuff.
I used the replace(char oldChar, char newChar) method that you suggested and it worked for the replaceAll()
however the spliting into array method split is still a problem do you know the method that i need to do this??
kind regards
garreth
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11335077
What is exactly the popouse of that function?
0
 

Author Comment

by:Gar04
ID: 11335557
i think that it creates an array called 'a' out of sTemp so that it can be used in the switch statement in order to create the customerid code that will be five letters long!!!
i hope that this makes sense, i guess that there is an actual method in the java api that does this correctly.
i also think that i got the code elsewhere, which would explain why it isn't in the java API

thanks for the help always
gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11335721
But you have a delimiter??
0
 

Author Comment

by:Gar04
ID: 11350721
Yeah, i guess that i have a delimiter?? doesn't that change arrays to strings???
can you explain better the significance of this cos i am not sure what you mean??

regards
gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11357646
I think this will work to fix your needs.

     StringTokenizer st = new StringTokenizer("White|Blue|Yellow|Red|Green","|");
     String a[] = new Array (st.countTokens());
     for (x=0;x<=a.length;x++) {
         a[x]=st.nextToken();
     }


Heven't tested tho, if you have problems, just tell me, OK?

Javier
0
 

Author Comment

by:Gar04
ID: 11374072
hey javier
are you suggesting that i use the string tokenizer class to accomplish the same thing as the code below should???
also can you explain the significance of the colours ("White|Blue|Yellow|Red|Green","|"); ???

i appreciate the help

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

Author Comment

by:Gar04
ID: 11374539
hey javier
these are the errors that i get in the compiler
i know that i am not using your code correctly but that is mostly because i don't fully understand it just yet
can you explain its use to me a bit

thanks
gaz

--------------------Configuration: JDK version 1.3.1_11 <Default>--------------------
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.java:33: cannot resolve symbol
symbol  : class Array  
location: class VBToJava
     String a[] = new Array (st.countTokens());
                      ^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.java:34: cannot resolve symbol
symbol  : variable x  
location: class VBToJava
     for (x=0;x<=a.length;x++) {
          ^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.java:34: cannot resolve symbol
symbol  : variable x  
location: class VBToJava
     for (x=0;x<=a.length;x++) {
              ^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.java:34: cannot resolve symbol
symbol  : variable x  
location: class VBToJava
     for (x=0;x<=a.length;x++) {
                          ^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.java:35: cannot resolve symbol
symbol  : variable x  
location: class VBToJava
         a[x]=st.nextToken();
           ^
5 errors

Process completed.

0
 
LVL 6

Accepted Solution

by:
jarasa earned 500 total points
ID: 11376943
Hi Gaz.

Well it was just a test so you could see how it should work, but I didn't test it so is normal that it has errors. try this:

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.util.*" %>

<%
try {

     StringTokenizer st = new StringTokenizer("White|Blue|Yellow|Red|Green","|");
     String a[] = new String [st.countTokens()];
     int x=0;
     for (x=0;x<a.length;x++) {
         a[x]=st.nextToken();
%>
<P style="{color:<%=a[x]%>; background-color:Black;}" ><%=a[x]%>
<%        
     }
} catch (NoSuchElementException e) {
      out.println(e.getMessage());
}
%>

This one works.

The Gree, Blue... etc are just colors Gaz!!

Javier
0
 

Author Comment

by:Gar04
ID: 11385202
i will give what you have suggested a shot, javier,
even though i am not that sure how it works just yet.
cheers
gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11385921
You said you have a delimiter, that split a delimited string into an array String.
:c)

Javier
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11385944
Anyway my friend you should strart thinking on closing this question, is getting too big. and none but me is reading your needs so I'm the only one helping you.

Javier

0
 

Author Comment

by:Gar04
ID: 11388938
hey Javier,
below is what i said about the 'delimiter'....i.e. that i am not sure what a delimiter is!
according to a book i looked at, a delimeter is: "Tokens are seperated from one another by delimiters, typically white space characters such as blank, tab, newline and carraige return".

from before:
"Yeah, i guess that i have a delimiter?? doesn't that change arrays to strings???
can you explain better the significance of this cos i am not sure what you mean??"

before, i was attempting to build the CustomerID using case statements, it works in visual basic, i just thought that i could port it to java and it would work too. I am sorry if i am pissing you off with this question.
i got a bit confused by your string tokenizer post, that is why i asked for an explanation.

Gaz
0
 

Author Comment

by:Gar04
ID: 11407133
Hey javier,
it turns out that i am using JDK 1.3 and the split method was only introduced for JDK 1.4 so that is why it doesn't work.
If you could explain how the string tokenizer can be used in this instance i would greatly appreciate it mate.
Gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11413772
Hey Gaz.

Did you tryed the example I post?

I believe it is clear. Any way the tokenizer does a kind of split.

but you have to got a delimiter in between what you want to split.

Javier

P.S.: You're not pissing me off with this question Gaz is just that I believe that if more experts get involved it will be better for you. and this question is too old so I don't think any other expert will see it and read all..... etc.

0
 

Author Comment

by:Gar04
ID: 11417432
I understand javier,
i am sorry for stretching this question so long but i was back in Ireland and then back in the USA and time got the better of me.
i guess that white space would be the best delimiter or -
however, that would mean that i have to put a delimeter in between the CustomerID tags in the Database where they are stored right??
Gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11423460
You don't have to put a delimiter on DB Gaz, you can use Space if you want but you have to be sure that the thing you are putting in there does not have any space.

That's why normally people uses pipes ("|") as delimiters is not usual to find that on any application data.

Javier
0
 

Author Comment

by:Gar04
ID: 11430977
hey Javier,
i tried what you posted and played with it a good bit but with little success

i have been messing with this too but i can't seem to get it to print to the screen so that i can see if it is correct or not
can you help???
gaz

import java.util.*;
//import java.text.DecimalFormat;
//import java.awt.*;
//import java.awt.event.*;
import javax.swing.*;


public class CusIdentity
{
     //DecimalFormat df = new DecimalFormat("00");
     
    String compName = "Joe Bloggs Advertising";
    String sCustId;
    //public static void main(String[] args) {
     //CusIdentity ci = new CusIdentity();
     //ci.companyNameAfterUpdate();
     
//}
  private void companyNameAfterUpdate()
{
   StringTokenizer strtok = new StringTokenizer(compName, " ");
   StringBuffer strbuf = new StringBuffer();
   while (strtok.hasMoreTokens());
   {
      strbuf.append(strtok.nextToken().charAt(0));
   }
   sCustId = strbuf.toString().toUpperCase();

}
}
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11433220
I don't get what you're trying to do here Gaz, you want to take just the first Char of each Word??
Javier
0
 

Author Comment

by:Gar04
ID: 11434762
yeah, that is essentially what i have been trying to do all along,
if the user types in "Joe Bloggs Advertising Company" then i would like the code to look like something like this:
"JBACO" or at least similar to that.
this is essentially what the original code did with the split method.
what am i doing wrong??????????
Gaz
0
 
LVL 6

Expert Comment

by:jarasa
ID: 11435690
Basically nothing just take the semicolom from the while statement and will work

while (strtok.hasMoreTokens());

while (strtok.hasMoreTokens())

You see??

But the compiler had to told you that!!!


Javier
0
 

Author Comment

by:Gar04
ID: 11459448
hey Javier,
I had already noticed the semi colon but thank you anyway and thank you for all the support,
I was just trying to get this code to print the results to the screen to see if it was working correctly
but it wasn't working for me. You know using System.out.println(). I guess that all needs to be static??
Gaz
0
 

Author Comment

by:Gar04
ID: 11460153
Hey javier,
this piece of code seems to work fine except for the fact that it doesn't ensure that the customer id code is
more that 5 characters long e.g. the name below "Brentwood Advertising" would return "BA"
instead i need it to return something like "BREAD". Does this make sense. The code has to be 5 chars long
I was hoping that there was a way to choose random letters from the name to concatenate to the code if it was not long enough.
how can this be done?
gaz

import java.util.*;
import javax.swing.*;

public class CusIdentity {

     static String coName = "Brentwood Advertising ";

     public static void main(String[] args) {
          System.out.println(getCoId2(coName));
       
     }
 public static String getCoId2(String coName) {
          StringTokenizer strtok = new StringTokenizer(coName);
          StringBuffer strbuf = new StringBuffer();
          while (strtok.hasMoreTokens()) {
               strbuf.append(strtok.nextToken().charAt(0));
          }
          return strbuf.toString().toUpperCase();
     }
}
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Owning a franchise can be the dream of a lifetime. It provides a chance for economic growth. You can be as successful as you want.  To make your franchise successful, you need to market it successfully. Here are six of the best marketing strategies …
Hyena v12.2 is now available for downloading and is available in English, French, German and Spanish versions.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now