Results 1 to 8 of 8
  1. #1
    Lokesh is offline Member
    Join Date
    May 2010
    Posts
    3
    Rep Power
    0

    Default Exception:Result consisted of more than one row

    Hi,
    I am trying to insert variable number of rows in a table using a single insert statement. To do this i am using for loop. It was running fine. But i want to insert values for 4 tables and plus this multiple rows table. the remaining 4 tables insert only one row. i am using stored procedure for this. but the program is throwing an exception saying that "com.mysql.jdbc.exceptions.MySQLSyntaxErrorExcepti on: Result consisted of more than one row ". Please give me a solution for this.

    Thanks in advance
    Lokesk

  2. #2
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    826
    Rep Power
    6

    Default

    Its purely a database specif error.Either you are trying to retrieve or insert multiple rows at a time.

    Show the code.
    Ramya:cool:

  3. #3
    Lokesh is offline Member
    Join Date
    May 2010
    Posts
    3
    Rep Power
    0

    Default

    Hi Rashmi,
    Thanks for your reply, here is my method


    public int insertExistEmpSliDet (SliEmpDetailsVo sliEVO) throws SQLException{
    int rows =0, rows1=0;

    Connection con=null;

    CallableStatement callStat = null;

    try{


    ConnectDatabase connectDatabase=new ConnectDatabase();
    con=connectDatabase.getConnection();
    con.setAutoCommit(false);


    callStat = con.prepareCall("{call sp_sliEmpExist_insert(?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?)}");





    callStat.setInt(1,sliEVO.getEmpId());

    callStat.setString(2, sliEVO.getSliPolicyNum());
    callStat.setInt(3, sliEVO.getSumAssured());
    callStat.setInt(4,sliEVO.getPremium());
    callStat.setDate(5,formatDate(sliEVO.getDateRisk() ));
    callStat.setDate(6,formatDate(sliEVO.getMaturityDa te()));
    sliEVO.setPolicyStatus("Hold");
    callStat.setString(7, sliEVO.getPolicyStatus());
    sliEVO.setFileId(1);
    callStat.setInt(7, sliEVO.getFileId());
    sliEVO.setPolicyTypeId(1);
    callStat.setInt(8, sliEVO.getPolicyTypeId());
    callStat.setDate(9, formatDate(createdDate));
    callStat.setDate(10, formatDate(modifiedDate));
    String numberofrec[]=sliEVO.getNomineevalues();
    for (int i = 0; i < numberofrec.length; i=i+5) {
    callStat.setInt(11,sliEVO.getEmpId());
    callStat.setString(12, sliEVO.getNomineevalues()[i]);
    callStat.setString(13, sliEVO.getNomineevalues()[i+1]);
    callStat.setString(14, sliEVO.getNomineevalues()[i+2]);
    callStat.setString(15, sliEVO.getNomineevalues()[i+3]);
    callStat.setString(16, sliEVO.getNomineevalues()[i+4]);
    }

    rows=callStat.executeUpdate();

    con.commit();

    }

    please go through this n help me

  4. #4
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    826
    Rep Power
    6

    Default

    again in ur code no indentation....no procedures what it is doing exactly.

    Like what i told you are trying to insert multiple values to a single row here in for loop below.This might be the problem.Problem exactly inside the forloop.
    Ramya:cool:

  5. #5
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,433
    Blog Entries
    7
    Rep Power
    20

    Default

    The executeUpdate( ... ) method can only return an int but your query wants to return an entire ResultSet; use the execute( ... ) method yourself and determine from its boolean output if you want to obtain a ResultSet. Also read the JDBC API documentation for the Statement interface.

    kind regards,

    Jos

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,931
    Rep Power
    19

    Default

    Java Code:
    		 callStat = con.prepareCall("{call sp_sliEmpExist_insert(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
    
    // Skip other setters.
    				for (int i = 0; i < numberofrec.length; i=i+5) {
    					callStat.setInt(11,sliEVO.getEmpId());
    					callStat.setString(12, sliEVO.getNomineevalues()[i]);
    					callStat.setString(13, sliEVO.getNomineevalues()[i+1]);
    					callStat.setString(14, sliEVO.getNomineevalues()[i+2]);
    					callStat.setString(15, sliEVO.getNomineevalues()[i+3]);
    					callStat.setString(16, sliEVO.getNomineevalues()[i+4]);
    				}
    				
    				rows=callStat.executeUpdate();
    What are you attempting to do here?
    From what I can see all you're doing is setting and resetting those 5 values, and finally executing the statement for the last set of values...you lose all the earlier ones.

  7. #7
    Lokesh is offline Member
    Join Date
    May 2010
    Posts
    3
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    Java Code:
    		 callStat = con.prepareCall("{call sp_sliEmpExist_insert(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
    
    // Skip other setters.
    				for (int i = 0; i < numberofrec.length; i=i+5) {
    					callStat.setInt(11,sliEVO.getEmpId());
    					callStat.setString(12, sliEVO.getNomineevalues()[i]);
    					callStat.setString(13, sliEVO.getNomineevalues()[i+1]);
    					callStat.setString(14, sliEVO.getNomineevalues()[i+2]);
    					callStat.setString(15, sliEVO.getNomineevalues()[i+3]);
    					callStat.setString(16, sliEVO.getNomineevalues()[i+4]);
    				}
    				
    				rows=callStat.executeUpdate();
    What are you attempting to do here?
    From what I can see all you're doing is setting and resetting those 5 values, and finally executing the statement for the last set of values...you lose all the earlier ones.
    hi,
    Thanks a lot for your replies n sorry for not explaning the scenario. i am having the html form containing ordinary and dynamic text fields. Dynamic text fields will be in the form as table containing 5 columns and dynamic number of rows. For eg., if u click on "
    +" button one row is going to add n if u click on the"-" one row will be deleted and i have given the same names(variable names) for every dynamic field and stored in a String array. Here in the program upto 10 parameters it is ordinary insertion. After 10 "i" represents the number of rows, i, i+1,....i+4 represents the columns, and after insertion of i+4th column, 5 will be added to "i", means the 2nd row will start inserting and continues to insert and stops when "i" is equal to total number of fields.
    I think u got my point now. If u r still not able to understand the problem please do let me know and will clearly explain the things. As i am new to this forums i donno to properly express my doubts..so i am requesting u ppl to giv me a soln :) .Please give me a solution for this problem.
    [i am inserting data to 5 tables, for the 5th table i am trying to dynamically insert rows using the for loop ]
    Thanks

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,931
    Rep Power
    19

    Default

    That's what I thought, however that code won't do what you think it'll do.
    It will simply make the call using the last set of parameters entered in that loop.
    The rest will have been lost.

Similar Threads

  1. Replies: 2
    Last Post: 02-12-2010, 03:39 PM
  2. Problem with the result of the implementation
    By ŖΫ ỏ Ңόρę in forum New To Java
    Replies: 5
    Last Post: 01-11-2010, 05:33 AM
  3. uncorrect result
    By jamborta in forum New To Java
    Replies: 3
    Last Post: 11-11-2009, 01:17 PM
  4. result select sql in jtable
    By herfnai in forum JDBC
    Replies: 0
    Last Post: 08-17-2008, 12:20 PM
  5. getting a random result
    By gradon in forum New To Java
    Replies: 2
    Last Post: 07-19-2007, 03:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •