aspose 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
 
Consider Paul's rocket mass heater.
 
subject: Urgent Help Required for Registration