Help needed for inserting values to a mysql table

Question

I have created a table using mysql:

CREATE TABLE JobCard (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    JobNo Long,
    RegNo VARCHAR(20),
    Service_Type VARCHAR(20),
    Customer_Complaints VARCHAR(100)
);

in cmd.

From Eclipse, i coded for inserting the values using prepared Statement for the table. Since ID is a auto_increment, i didn't include it in the insert statement.

String Query =
    "INSERT INTO JobCard (JobNo, RegNo, Service_Type, Customer_Complaints)
        VALUES (?,?,?,?)";

But the output shows me :

java.sql.SQLException: Parameter index out of range
               (5 > number of parameters, which is 4).
    at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    at
com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3717)
    at
com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3701)
    at
com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4552)
    at
example.Connect.DoInsertIntoDB(Connect.java:40)

Can anyone please tell me how to pass the parameter list? Please help me resolve this error!!

Update:

Here is my code: The method call is:

System.out.println(strLine);
                    
String[] dbColumnValues = strLine.split("%");
Connect.DoInsertIntoDB(Long.parseLong(dbColumnValues[0]),dbColumnValues[1],dbColumnValues[2], dbColumnValues[3]);

The method definition:

public static void DoInsertIntoDB(Long JobNo, String RegNo, String Service_Type, String Customer_Complaints){
    
    
    String Query = "INSERT INTO JobCard (JobNo, RegNo, Service_Type, Customer_Complaints) VALUES (?,?,?,?)";
    try {
        Connection conn = toConnect();
                        
        PreparedStatement pstmt = conn.prepareStatement(Query);
        
        pstmt.setLong(2, JobNo);
        pstmt.setString(3, RegNo);
        pstmt.setString(4, Service_Type);
        pstmt.setString(5, Customer_Complaints);
        
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
        
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}
}

Your execute expects 4 parameters, which is correct. Can you show us the line where you prepare and/or execute the Query? — Konerak
– Konerak, Commented Apr 9, 2011 at 12:20

Charlie · Accepted Answer · 2011-04-09 12:22:04Z

3

Need to read your stack trace. In your code (on line 40 of Connect.java) you're attempting to set a value into the 5th ? but there are only 4 ?s in your prepared statement.

answered Apr 9, 2011 at 12:22

Charlie

7,3691 gold badge39 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anu Over a year ago

Yeah.. ok. Thanks for your help. I was wrong with the prepare statement. Now it works fine!

FxckDead · Accepted Answer · 2011-04-09 12:43:15Z

0

When you set the parameters, you are starting with 2, and it must be 1. If you see, the last parameter is the index 5, and you don't have a 5° parameter, Because of this java say the exception "Parameter index out of range".

You must start in 1.

PS: Sorry for my english.

answered Apr 9, 2011 at 12:43

FxckDead

4406 silver badges16 bronze badges

Comments

Adi Sembiring · Accepted Answer · 2011-04-09 12:45:03Z

0

Prepare statement parameter begin from 1 number, based on your code the parameter should be 1 to 4 but you ended with 5. it cause parameter index out of range

answered Apr 9, 2011 at 12:45

Adi Sembiring

5,96612 gold badges63 silver badges71 bronze badges

Comments

Lucky · Accepted Answer · 2013-01-11 07:35:32Z

0

Your try should look like this,

try {
    Connection conn = toConnect();

    PreparedStatement pstmt = conn.prepareStatement(Query);

    pstmt.setLong(1, JobNo);
    pstmt.setString(2, RegNo);
    pstmt.setString(3, Service_Type);
    pstmt.setString(3, Customer_Complaints);

    pstmt.executeUpdate();
    pstmt.close();
    conn.close();

}

and that should solve the problem....

answered Jan 11, 2013 at 7:35

community wiki

Lucky

Collectives™ on Stack Overflow

Help needed for inserting values to a mysql table

Update:

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update:

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related