It gives the proper output when i run it 7 times. But it also gives the ColumnNotFound error at line 152 all 7 times. When i run it for the 8th time it gave an error of Incorrect syntax near 's' at line 133 which is "statement.executeUpdate(movieValue);" . When i checked the database i found that it had made 8 entries in the customer table and only 7 entries in cust_music and cust_movie tables. Please help.
try
{
int counter = 0;
String name = request.getParameter("custName");
String address = request.getParameter("address");
String phoneStr = request.getParameter("phone").trim();
int phone = new Integer(phoneStr).intValue();
String email = request.getParameter("emailid");
String movie[] = request.getParameterValues("movies");
String music[] = request.getParameterValues("music");
String payment = request.getParameter("payment");
String cardNo = "";
int totalCost = 0;
if(payment.equals("Card"))
{
String cardNo1 = request.getParameter("cardNo1");
String cardNo2 = request.getParameter("cardNo2");
String cardNo3 = request.getParameter("cardNo3");
String cardNo4 = request.getParameter("cardNo4");
cardNo = cardNo1+cardNo2+cardNo3+cardNo4;
}
else
cardNo = "NIL";
con = DriverManager.getConnection("jdbc:odbc:project");
statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
String customer = "IF NOT EXISTS (SELECT *FROM DBO.SYSOBJECTS WHERE NAME = 'CUSTOMER') CREATE TABLE CUSTOMER(CUSTNO INTEGER PRIMARY KEY,CUSTNAME VARCHAR(50),ADDRESS VARCHAR(100),EMAIL VARCHAR(50),PHONE INTEGER,PAYMENT VARCHAR(4),CARDNUM VARCHAR(16))";
statement.executeUpdate(customer);
String cust_movie = "IF NOT EXISTS (SELECT *FROM DBO.SYSOBJECTS WHERE NAME = 'CUST_MOVIE') CREATE TABLE CUST_MOVIE(CUSTNUM INTEGER REFERENCES CUSTOMER(CUSTNO),MOVIE VARCHAR(100))";
statement.executeUpdate(cust_movie);
String cust_music = "IF NOT EXISTS (SELECT *FROM DBO.SYSOBJECTS WHERE NAME = 'CUST_MUSIC') CREATE TABLE CUST_MUSIC(CUSTNUM INTEGER REFERENCES CUSTOMER(CUSTNO),MUSIC VARCHAR(100))";
statement.executeUpdate(cust_music);
ResultSet rs = statement.executeQuery("SELECT * FROM CUSTOMER");
rs.last();
if(rs.getRow() == 0)
counter = 1;
else
counter = rs.getRow() + 1;
String value = "INSERT INTO CUSTOMER VALUES("+counter+",'"+name+"','"+address+"','"+email+"',"+phone+",'"+payment+"','"+cardNo+"')";
statement.executeUpdate(value);
if(!(movie[0].equals("default")))
{
for(int i=0;i<movie.length;i++)
{
String movieValue = "INSERT INTO CUST_MOVIE VALUES ("+counter+",'"+movie[i]+"')";
statement.executeUpdate(movieValue);
}
}
if(!(music[0].equals("default")))
{
for(int i=0;i<music.length;i++)
{
String musicValue = "INSERT INTO CUST_MUSIC VALUES("+counter+",'"+music[i]+"')";
statement.executeUpdate(musicValue);
}
}
rs = statement.executeQuery("SELECT * FROM CUSTOMER WHERE CUSTNO = "+counter+"");
out.println("<html><body>");
out.println("<p align = 'center' size = '10'>RECEIPT</p><br><br><br>");
while(rs.next())
{
out.println("Name : "+ rs.getString("CUSTNAME")+"<br><br><br>");
out.println("Address : "+rs.getString("ADDRESS")+"<br><br><br>");
out.println("Email Id : "+rs.getString("EMAIL")+"<br><br><br>");
out.println("Contact Number : "+rs.getInt("PHONE")+"<br><br><br>");
out.println("Payment Mode :"+rs.getString("PAYMENT")+"<br><br><br>");
out.println("Card Number : "+rs.getString("CARDNUM")+"<br><br><br>");
out.println("Movie CDs/DVDs ordered : <br>");
if(!movie[0].equals("default"))
{
out.println("<table>");
rs = statement.executeQuery("SELECT MOVIE,PRICE FROM CUST_MOVIE INNER JOIN MOVIES ON MOVIE = NAME WHERE CUSTNUM = "+counter);
while(rs.next())
{
out.println("<tr><td>"+rs.getString("MOVIE")+"</td><td>Rs. ");
int cost = rs.getInt("PRICE");
out.println(cost+"</td></tr>");
totalCost = totalCost + cost;
}
out.println("</table>");
}
else
out.println("NIL<br>");
out.println("<br>Music CDs/DVDs ordered :<br>");
if(!music[0].equals("default"))
{
out.println("<table>");
rs = statement.executeQuery("SELECT MUSIC,PRICE FROM CUST_MUSIC INNER JOIN MUSIC ON MUSIC = NAME WHERE CUSTNUM = "+counter);
while(rs.next())
{
out.println("<tr><td>" + rs.getString("MUSIC")+ "</td><td>Rs ");
int cost = rs.getInt("PRICE");
out.println(cost+"</td></tr>");
totalCost = totalCost + cost;
}
out.println("</table>");
}
else
out.println("NIL");
out.println("<br><br>Total Cost : Rs. " + totalCost);
}