CODEWITHSUNDEEP
javamysqlinsertauto-increment
Anu
Anu

Reputation: 161

Help needed for inserting values to a mysql table

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();
    }
}
}

Upvotes: 1

Views: 2439

Answers (4)

Lucky
Lucky

Reputation: 17345

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....

Upvotes: 0

Adi Sembiring
Adi Sembiring

Reputation: 5936

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

Upvotes: 0

FxckDead
FxckDead

Reputation: 440

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.

Upvotes: 0

Charlie
Charlie

Reputation: 7349

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.

Upvotes: 3

Related Questions