Gar04
asked on
VB to Java JSP conversion question!!
Hey guys
this question is related to this thread:
https://www.experts-exchange.com/questions/20940232/General-Question-regarding-JSP-resultSet-use.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
"
this question is related to this thread:
https://www.experts-exchange.com/questions/20940232/General-Question-regarding-JSP-resultSet-use.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
"
ASKER
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"
"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"
ASKER
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"
ASKER
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![NewBookF
and this [CampaignRef] = GetNextCID(Forms![NewBookF
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].[Campaign
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].[Campaign
GetLastCID = LastId
GetLastCID_Exit:
Exit Function
GetLastCID_Err:
Select Case Err
Case Else
MsgBox Err.Description
Resume GetLastCID_Exit
Resume
End Select
End Function
the function is called on the booking form by this
[CampaignRef] = GetLastCID(Forms![NewBookF
and this [CampaignRef] = GetNextCID(Forms![NewBookF
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].[Campaign
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].[Campaign
GetLastCID = LastId
GetLastCID_Exit:
Exit Function
GetLastCID_Err:
Select Case Err
Case Else
MsgBox Err.Description
Resume GetLastCID_Exit
Resume
End Select
End Function
ASKER
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
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
ASKER
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)
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("S
'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![NewBookF
[CustomerID] = Forms![NewBookForm]![Custo
Me("BookDate") = Now()
'get recordset of Campaign Information
Set ca = CurrentDb.OpenRecordset("S
'checks conditions of recordset and allows insert
If (ca.EOF And ca.BOF) Then
rSQL = "INSERT INTO CampaignInformation(Campai
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(Campai
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![NewBookF
[CustomerID] = Forms![NewBookForm]![Custo
Me("BookDate") = Now()
'get recordset of Campaign Information
Set ca = CurrentDb.OpenRecordset("S
'checks conditions of recordset and allows insert
If (ca.EOF And ca.BOF) Then
rSQL = "INSERT INTO CampaignInformation(Campai
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(Campai
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
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)
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("S
'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![NewBookF
[CustomerID] = Forms![NewBookForm]![Custo
Me("BookDate") = Now()
'get recordset of Campaign Information
Set ca = CurrentDb.OpenRecordset("S
'checks conditions of recordset and allows insert
If (ca.EOF And ca.BOF) Then
rSQL = "INSERT INTO CampaignInformation(Campai
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(Campai
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![NewBookF
[CustomerID] = Forms![NewBookForm]
Hi Gaz, I need some time to digest this. :c)
Javier
Javier
ASKER
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
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
ASKER
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().l
JOptionPane.showMessageDia
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
//>>>>>>>>>>>>>>>>>>>>>>>>
String sWhere;
int i = 1;
sWhere = "CustomerID='" + sCompanyID + "'";
do {
sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
} while( numberOfRecordsMatching("C
// converts to uppercase
sCompanyID = sCompanyID.toUpperCase();
//assigns value to CustomerID
this.customerID = sCompanyID;
a = null; // not needed
}
private int numberOfRecordsMatching(St
return 0; // to be implemented!!!
}
}
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().l
JOptionPane.showMessageDia
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
//>>>>>>>>>>>>>>>>>>>>>>>>
String sWhere;
int i = 1;
sWhere = "CustomerID='" + sCompanyID + "'";
do {
sWhere = "CustomerID='" + sCompanyID.substring(0, 3) + df.format(i) + "'";
} while( numberOfRecordsMatching("C
// converts to uppercase
sCompanyID = sCompanyID.toUpperCase();
//assigns value to CustomerID
this.customerID = sCompanyID;
a = null; // not needed
}
private int numberOfRecordsMatching(St
return 0; // to be implemented!!!
}
}
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:3
String driver= "com.mysql.jdbc.Driver";
String password = "";
String username ="root";
String mySiteID = request.getParameter("Site
String myCustomerID = request.getParameter("myCu
String myCycleID = request.getParameter("myCy
// I believe that this should be copntrolled by JavaScript before request but just in case
If (request.getParameter(Comp
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(Prod
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.j
Connection c = DriverManager.getConnectio
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").e
// I have no Idea on how to solve this Gaz
// [CampaignRef] = GetLastCID(Forms![NewBookF
// [CustomerID] = Forms![NewBookForm]![Custo
// 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(Campai
// 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(Campai
// 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??
%>
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:3
String driver= "com.mysql.jdbc.Driver";
String password = "";
String username ="root";
String mySiteID = request.getParameter("Site
String myCustomerID = request.getParameter("myCu
String myCycleID = request.getParameter("myCy
// I believe that this should be copntrolled by JavaScript before request but just in case
If (request.getParameter(Comp
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(Prod
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.j
Connection c = DriverManager.getConnectio
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").e
// I have no Idea on how to solve this Gaz
// [CampaignRef] = GetLastCID(Forms![NewBookF
// [CustomerID] = Forms![NewBookForm]![Custo
// 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(Campai
// 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(Campai
// 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??
%>
ASKER
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.od
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try
{
conn = DriverManager.getConnectio
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
PreparedStatement z = conn.preparedCall("INSERT INTO SiteBookings" +
"(CustomerID, SiteBookings.CampaignRef, SiteBookings.Price)" + "VALUES(?,?,?)" + "WHERE SiteBookings.BookingsNo = ?");
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.od
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try
{
conn = DriverManager.getConnectio
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
PreparedStatement z = conn.preparedCall("INSERT INTO SiteBookings" +
"(CustomerID, SiteBookings.CampaignRef, SiteBookings.Price)" + "VALUES(?,?,?)" + "WHERE SiteBookings.BookingsNo = ?");
You can do that Gaz, but always set to NULL the PS before you redo it.
Javier
Javier
ASKER
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.od
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.getConnectio
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
"(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("INSE
"(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("INS
"(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"><!--DWLa
</tr>
<tr>
<td height="21" align="left" valign="bottom" bgcolor="#FFFFFF"><!--DWLa
</tr>
<tr>
<td rowspan="2" align="left" valign="bottom" bgcolor="#FFFFFF"> <p> </p>
<p> </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.getSt
<td><div align="center"><%=rs.getSt
<td colspan="2"><div align="center"><%=rs.getSt
<td><div align="center"><%=rs.getSt
<td><div align="center"><%=rs.getSt
</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><input name="Cities" type="text" size="25"></td>
<td><div align="center"><font size="2">Country</font></d
<td colspan="2"><input name="Countries" type="text" size="35"></td>
<td> blah blah blah!!!!!!!!!!!!!</td>
</tr>
<tr>
<td height="18" colspan="2"> </td>
<td colspan="4"> </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>
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.od
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.getConnectio
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
"(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("INSE
"(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("INS
"(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"><!--DWLa
</tr>
<tr>
<td height="21" align="left" valign="bottom" bgcolor="#FFFFFF"><!--DWLa
</tr>
<tr>
<td rowspan="2" align="left" valign="bottom" bgcolor="#FFFFFF"> <p> </p>
<p> </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.getSt
<td><div align="center"><%=rs.getSt
<td colspan="2"><div align="center"><%=rs.getSt
<td><div align="center"><%=rs.getSt
<td><div align="center"><%=rs.getSt
</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><input name="Cities" type="text" size="25"></td>
<td><div align="center"><font size="2">Country</font></d
<td colspan="2"><input name="Countries" type="text" size="35"></td>
<td> blah blah blah!!!!!!!!!!!!!</td>
</tr>
<tr>
<td height="18" colspan="2"> </td>
<td colspan="4"> </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>
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
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
ASKER
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
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
ASKER
hey Jarasa,
can i do this:
if(!cs.getString("SiteID")
instead of this:
If (!cs.getString("SiteID").e
???????????
Gaz
:)
can i do this:
if(!cs.getString("SiteID")
instead of this:
If (!cs.getString("SiteID").e
???????????
Gaz
:)
Well of course.
Anyway Gaz it should be this way with && instead of And that was a mistake of mine.
If (!rs.getString("SiteID").e
:c)
Javier
Anyway Gaz it should be this way with && instead of And that was a mistake of mine.
If (!rs.getString("SiteID").e
:c)
Javier
ASKER
Hey Javier,
i forgot to ask you if
this: if(!cs.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
i forgot to ask you if
this: if(!cs.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
ASKER
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().l
//JOptionPane.showMessageD
//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("C
// converts to uppercase
sCompanyID = sCompanyID.toUpperCase();
//assigns value to CustomerID
this.custId = sCompanyID;
// a = null; // not needed
}
private int numberOfRecordsMatching(St
return 0; // to be implemented!!!
}
}
%>
<% Class.forName("sun.jdbc.od
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.getConnectio
Statement st = conn.createStatement();
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
"(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("INSE
"(CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress)" + "VALUES(?,?,?,?,?,?,?,?,?)
u.setString(1,custId); u.setString(2,compName); u.setString(3,compAddress)
u.setString(5,countriEs); u.setString(6,postCode); u.setString(7,phoNe); u.setString(8,faxNum); u.setString(9,emailAddress
PreparedStatement t = conn.prepareStatement("INS
"(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("CompanyNam
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").e
campRef = !cs.getString("CampaignRef
}
}else{
//campRef = method(args) get new cid method
}
ss = st.executeQuery(sSQL);
if (ss.next()){
// not equal to =if (!ss.getString("CampaignRe
// execute this query = t.executeQuery();
// }
%>
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().l
//JOptionPane.showMessageD
//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("C
// converts to uppercase
sCompanyID = sCompanyID.toUpperCase();
//assigns value to CustomerID
this.custId = sCompanyID;
// a = null; // not needed
}
private int numberOfRecordsMatching(St
return 0; // to be implemented!!!
}
}
%>
<% Class.forName("sun.jdbc.od
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.getConnectio
Statement st = conn.createStatement();
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
"(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("INSE
"(CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress)" + "VALUES(?,?,?,?,?,?,?,?,?)
u.setString(1,custId); u.setString(2,compName); u.setString(3,compAddress)
u.setString(5,countriEs); u.setString(6,postCode); u.setString(7,phoNe); u.setString(8,faxNum); u.setString(9,emailAddress
PreparedStatement t = conn.prepareStatement("INS
"(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("CompanyNam
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").e
campRef = !cs.getString("CampaignRef
}
}else{
//campRef = method(args) get new cid method
}
ss = st.executeQuery(sSQL);
if (ss.next()){
// not equal to =if (!ss.getString("CampaignRe
// execute this query = t.executeQuery();
// }
%>
! ---> Not Equal
Let me check the code, I'll tell you something.
Javier
Let me check the code, I'll tell you something.
Javier
ASKER
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.DecimalF
<%!
private void companyNameAfterUpdate(Str
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.le
//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("C
// converts to uppercase
sCustomerID = sCustomerID.toUpperCase();
//assigns value to CustomerID
this.custId = sCustomerID;
}
%>
<% Class.forName("sun.jdbc.od
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.getConnectio
Statement st = conn.createStatement();
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
"(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("INSE
"(CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress)" + "VALUES(?,?,?,?,?,?,?,?,?)
u.setString(1,custId); u.setString(2,compName); u.setString(3,compAddress)
u.setString(5,countriEs); u.setString(6,postCode); u.setString(7,phoNe); u.setString(8,faxNum); u.setString(9,emailAddress
PreparedStatement t = conn.prepareStatement("INS
"(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("CompanyNam
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").e
campRef = !cs.getString("CampaignRef
}
}else{
//campRef = method(args) get new cid method
}
ss = st.executeQuery(sSQL);
if (ss.next()){
// not equal to =if (!ss.getString("CampaignRe
// execute this query = t.executeQuery();
// }
%>
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.DecimalF
<%!
private void companyNameAfterUpdate(Str
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.le
//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("C
// converts to uppercase
sCustomerID = sCustomerID.toUpperCase();
//assigns value to CustomerID
this.custId = sCustomerID;
}
%>
<% Class.forName("sun.jdbc.od
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.getConnectio
Statement st = conn.createStatement();
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
"(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("INSE
"(CustomerID, CompanyName, CompanyAddress, City, Country, PostalCode, PhoneNumber, FaxNumber, EmailAddress)" + "VALUES(?,?,?,?,?,?,?,?,?)
u.setString(1,custId); u.setString(2,compName); u.setString(3,compAddress)
u.setString(5,countriEs); u.setString(6,postCode); u.setString(7,phoNe); u.setString(8,faxNum); u.setString(9,emailAddress
PreparedStatement t = conn.prepareStatement("INS
"(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("CompanyNam
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").e
campRef = !cs.getString("CampaignRef
}
}else{
//campRef = method(args) get new cid method
}
ss = st.executeQuery(sSQL);
if (ss.next()){
// not equal to =if (!ss.getString("CampaignRe
// execute this query = t.executeQuery();
// }
%>
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
:c)
I'm a bit busy nowdays, so I can't answer you too fast, hope you understand.
Javier
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().l
//JOptionPane.showMessageD
//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("C
// converts to uppercase
sCompanyID = sCompanyID.toUpperCase();
//assigns value to CustomerID
this.custId = sCompanyID;
// a = null; // not needed
}
private int numberOfRecordsMatching(St
return 0; // to be implemented!!!
}
}
%>
<%
Class.forName("sun.jdbc.od
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.getConnectio
Statement st = conn.createStatement();
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
z.setString(1,custId);
z.setString(2,campRef);
z.setString(3,pricEs);
z.setInt(4,bookno);
PreparedStatement u = conn.pepareStatement("INSE
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("INS
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("CompanyNam
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").e
campRef = !cs.getString("CampaignRef
}
}else{
//campRef = method(args) get new cid method
}
ss = st.executeQuery(sSQL);
if (ss.next()){
// not equal to =
if (!ss.getString("CampaignRe
// execute this query =
t.executeQuery();
}
}
}
}
%>
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().l
//JOptionPane.showMessageD
//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("C
// converts to uppercase
sCompanyID = sCompanyID.toUpperCase();
//assigns value to CustomerID
this.custId = sCompanyID;
// a = null; // not needed
}
private int numberOfRecordsMatching(St
return 0; // to be implemented!!!
}
}
%>
<%
Class.forName("sun.jdbc.od
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.getConnectio
Statement st = conn.createStatement();
PreparedStatement p = conn.prepareCall("SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocati
String booknos[] = request.getParameterValues
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("INS
z.setString(1,custId);
z.setString(2,campRef);
z.setString(3,pricEs);
z.setInt(4,bookno);
PreparedStatement u = conn.pepareStatement("INSE
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("INS
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("CompanyNam
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").e
campRef = !cs.getString("CampaignRef
}
}else{
//campRef = method(args) get new cid method
}
ss = st.executeQuery(sSQL);
if (ss.next()){
// not equal to =
if (!ss.getString("CampaignRe
// execute this query =
t.executeQuery();
}
}
}
}
%>
ASKER
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
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
Don't worry gaz.
had a good time in Ireland? or was too chilly?
Javier
had a good time in Ireland? or was too chilly?
Javier
ASKER
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!!
--------------------Config
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : method replaceAll (java.lang.String,java.lan
location: class java.lang.String
sTemp = sTemp.replaceAll(" "," ");
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
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
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!!
--------------------Config
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : method replaceAll (java.lang.String,java.lan
location: class java.lang.String
sTemp = sTemp.replaceAll(" "," ");
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
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
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
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
ASKER
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
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
What is exactly the popouse of that function?
ASKER
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
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
But you have a delimiter??
ASKER
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
can you explain better the significance of this cos i am not sure what you mean??
regards
gaz
I think this will work to fix your needs.
StringTokenizer st = new StringTokenizer("White|Blu
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
StringTokenizer st = new StringTokenizer("White|Blu
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
ASKER
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|Gr
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);
}
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|Gr
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);
}
ASKER
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
--------------------Config
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : class Array
location: class VBToJava
String a[] = new Array (st.countTokens());
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
for (x=0;x<=a.length;x++) {
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
for (x=0;x<=a.length;x++) {
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
for (x=0;x<=a.length;x++) {
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
a[x]=st.nextToken();
^
5 errors
Process completed.
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
--------------------Config
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : class Array
location: class VBToJava
String a[] = new Array (st.countTokens());
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
for (x=0;x<=a.length;x++) {
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
for (x=0;x<=a.length;x++) {
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
for (x=0;x<=a.length;x++) {
^
C:\Documents and Settings\Amanda Blanton\Desktop\VBToJava.j
symbol : variable x
location: class VBToJava
a[x]=st.nextToken();
^
5 errors
Process completed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i will give what you have suggested a shot, javier,
even though i am not that sure how it works just yet.
cheers
gaz
even though i am not that sure how it works just yet.
cheers
gaz
You said you have a delimiter, that split a delimited string into an array String.
:c)
Javier
:c)
Javier
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
Javier
ASKER
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
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
ASKER
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
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
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.
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.
ASKER
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
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
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
That's why normally people uses pipes ("|") as delimiters is not usual to find that on any application data.
Javier
ASKER
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.companyNameAfterUpdat
//}
private void companyNameAfterUpdate()
{
StringTokenizer strtok = new StringTokenizer(compName, " ");
StringBuffer strbuf = new StringBuffer();
while (strtok.hasMoreTokens());
{
strbuf.append(strtok.nextT
}
sCustId = strbuf.toString().toUpperC
}
}
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.companyNameAfterUpdat
//}
private void companyNameAfterUpdate()
{
StringTokenizer strtok = new StringTokenizer(compName, " ");
StringBuffer strbuf = new StringBuffer();
while (strtok.hasMoreTokens());
{
strbuf.append(strtok.nextT
}
sCustId = strbuf.toString().toUpperC
}
}
I don't get what you're trying to do here Gaz, you want to take just the first Char of each Word??
Javier
Javier
ASKER
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
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
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
while (strtok.hasMoreTokens());
while (strtok.hasMoreTokens())
You see??
But the compiler had to told you that!!!
Javier
ASKER
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
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
ASKER
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(getCoId
}
public static String getCoId2(String coName) {
StringTokenizer strtok = new StringTokenizer(coName);
StringBuffer strbuf = new StringBuffer();
while (strtok.hasMoreTokens()) {
strbuf.append(strtok.nextT
}
return strbuf.toString().toUpperC
}
}
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(getCoId
}
public static String getCoId2(String coName) {
StringTokenizer strtok = new StringTokenizer(coName);
StringBuffer strbuf = new StringBuffer();
while (strtok.hasMoreTokens()) {
strbuf.append(strtok.nextT
}
return strbuf.toString().toUpperC
}
}
ASKER
"currently where these functions are running????"