Question 21998 in Mysql Question posted in
The official MySQL documentation can be found here.

My Id is autoIncrement and other than Id from 1st position, I want to insert the name,price – Mysql

This is the code where I’m trying to use the insert query:

int insertItems(String name,double amt,String exp)throws Exception{
Connection con=getConnection();
    PreparedStatement ps=con.prepareStatement("insert into itemsList (null,name,amt,expire) values (?,?,?,?);");//when I use the 4?s it displays [Exception in thread "main" java.sql.SQLException: No value specified for parameter 1] and if I use 3?s it shows out of range.
        
        //ps.setInt(1,id);//I declared the id as auto-Increment and primary key
        ps.setString(2,name);
        ps.setDouble(3,amt);
        ps.setString(4,exp);
        
        int n=ps.executeUpdate();
        
        return n;
        
    }

I don’t understand that where I’m going wrong please help me to understand it.

getConnection() is where I created the JDBC driver, driver manager till connection and it is returning the connection object to other methods

I can read data easily but when it comes to exp line its shows exception

Choose Option :
1.Add Item              2.View all Items
3.Place Order           4.NA
5.View Orders           6.Exit
-------------------------------------------------------------------------------------------
1
Choose type of Item :
1.Normal item   2.Food
2
Enter Item name :
dragon
Enter the Price :
70
Enter the Expire date :
12/11
Exception in thread "main" java.sql.SQLException: No value specified for parameter 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1084)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
        at com.fixitytech.isodb.ItemDAO.insertItems(ItemDAO.java:37)
        at com.fixitytech.isodb.ShopdbUI.main(ShopdbUI.java:90)
Tags:

2

Answers


  1. 0

    Don’t pass null in insert query.

    PreparedStatement ps=con.prepareStatement("insert into itemsList (name,amt,expire) values (?,?,?);")

ps.setString(1,name);
ps.setDouble(2,amt);
ps.setString(3,exp);
    Login or Signup to reply.
  2. 0

    You define 4 columns in your statement, one is using an invalid column name null. And accordingly you define 4 placeholders, waiting for values to be assigned.

    But you only supply 3. That‘s the cause for the error.

    Solution is to fix the SQL statement and assign values to placeholders at their new position:

    PreparedStatement ps=con
  .prepareStatement("insert into itemsList (name,amt,expire) values (?,?,?)");

ps.setString(1,name);
ps.setDouble(2,amt);
ps.setString(3,exp);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search