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

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"

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"

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

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.

'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

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

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

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

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

// 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 + "'";

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

// 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")
}

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

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

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

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

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

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;

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

//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 + "'";

//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 + "'";

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;

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

//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 + "'";

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

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

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

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

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

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.

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

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.

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

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

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

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

Featured Post

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

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 …