File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Struts and the fly likes Urgent Help Required for Registration Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Struts
Bookmark "Urgent Help Required for Registration" Watch "Urgent Help Required for Registration" New topic
Author

Urgent Help Required for Registration

janice ang
Greenhorn

Joined: Jan 03, 2007
Posts: 5
I'm currently working on a Team Registration function where user is allow to input a maximum of 5 members. User is able to only input 1 or 2 members although there are 5 input fields in this page. I've ran into a problem in the SQL query. My codes are as follows:

public class TeamRegistrationAction extends Action {
public ActionForward execute(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {


TeamRegistrationFormBean userBean = (TeamRegistrationFormBean)form;
String teamName = userBean.getTeamName();
String teamMember1 = userBean.getTeamMember1();
String teamMember2 = userBean.getTeamMember2();
String teamMember3 = userBean.getTeamMember3();
String teamMember4 = userBean.getTeamMember4();
String teamMember5 = userBean.getTeamMember5();
String remarks = userBean.getRemarks();
String term = userBean.getTerm();
String seekMembers = userBean.getSeekMembers();
Date date = new Date();
DateFormat formatter = new SimpleDateFormat ("yyyy/MM/dd");
String fDate = formatter.format(date);

try {

HttpSession mySession = request.getSession();
String myloginId = (String)mySession.getAttribute("userId");
System.out.println("This is" + myloginId);
Statement stmt;
Statement stmt1;
ResultSet rsTeamRegister;
ResultSet rsTeamRegister1;
String strQuery;
String strQuery1;
String strQuery2;
String strQuery3;

String idexist = "no";
String memberId1 = "no";
String memberId2 = "no";
String memberId3 = "no";
String memberId4 = "no";
String memberId5 = "no";
String teamId = "no";
DbConnection myconn = new DbConnection();
Connection conn = myconn.getConnection();

strQuery = "SELECT * FROM Student";

stmt = conn.createStatement();
rsTeamRegister = stmt.executeQuery(strQuery);

ActionMessages messages = new ActionMessages();

if((teamMember1!=null)||(teamMember2!=null)||(teamMember3!=null)||(teamMember4!=null)||(teamMember5!=null)){

while (rsTeamRegister.next()) {
if (myloginId.equals(rsTeamRegister.getString("studentId"))){

idexist ="yes";
}

if(teamMember1.equals(rsTeamRegister.getString("studentId"))){

memberId1 = "yes";

}
if(teamMember2.equals(rsTeamRegister.getString("studentId"))){

memberId2 = "yes";

}
if(teamMember3.equals(rsTeamRegister.getString("studentId"))){

memberId3 = "yes";

}
if(teamMember4.equals(rsTeamRegister.getString("studentId"))){

memberId4 = "yes";

}

if(teamMember5.equals(rsTeamRegister.getString("studentId"))){

memberId5 = "yes";

}


if (idexist.equals ("yes")|| memberId1.equals ("yes") || memberId2.equals ("yes") || memberId3.equals ("yes") || memberId4.equals ("yes") || memberId5.equals ("yes") ){
if (rsTeamRegister.getString("teamId")!=null) {

teamId = "yes";

} else{

teamId = "no";
}
}

} rsTeamRegister.close();

} else{

}

stmt1 =conn.createStatement();
if (teamId.equals ("no") ){

strQuery1 = "INSERT INTO Team(teamName, term, seekMembers, dateRegistered, createdById, remarks) VALUES ('" + teamName + "' , '" + term + "', '" + seekMembers + "', '" + fDate + "', '" + myloginId + "', '" + remarks + "' )";
stmt1.executeUpdate(strQuery1);

strQuery2 = "SELECT teamId FROM Team where createdById = ('" + myloginId + "')";

rsTeamRegister1=stmt.executeQuery(strQuery2);

while(rsTeamRegister1.next()){
String myTeamId = rsTeamRegister1.getString("teamId");

strQuery3 = "UPDATE Student SET teamId = ('" + myTeamId + "') , dateJoinedTeam =('" + fDate + "' ) WHERE studentId = ('" + myloginId + "'), ('" + teamMember1 + "') , ('" + teamMember2 + "') , ('" + teamMember3 + "') , ('" + teamMember4 + "') , ('" + teamMember5 + "' ) ";

stmt1.executeUpdate(strQuery3);
}
rsTeamRegister1.close();


} //else{

//}

stmt.close();
stmt1.close();
myconn.closeConnection();



}catch ( SQLException e ) {
System.err.println("SQL Exception occured while accessing the table" );
e.printStackTrace();
return null;

} catch ( Exception e ) {
e.printStackTrace();
return null;
}

return(mapping.findForward("registerTeamComplete"));
}
}


it doesn't seemed to recognize the WHERE condition in Query 3. My errors are as follows:

SQL Exception occured while accessing the table
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ('cheryl.tan.2003') , ('') , ('') , ('') , ('' )' at line 1


Can anyone kindly help me? Thanks a dozen
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
The problem appears to be that you are trying to insert 5 values into only one database row. The row "seekmembers" must contain only one value, not five. If it's supposed to contain a comma-delimited list of names you will have to compose that list of names before adding it to the INSERT SQL statement.


Merrill
Consultant, Sima Solutions
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Urgent Help Required for Registration
 
Similar Threads
2 conditions to be checked
java.lang.NullPointerException
Recording session value (parameter) in the bank via jsp
Number Format Exception Error
Displaying image which is outside my web application