Click here to Skip to main content
14,599,518 members
Rate this:
Please Sign up or sign in to vote.
See more:
Dear sir ,
I have written java code to save excel data to mysql but it is showing exception: java.sql.SQLException: No value specified for parameter 22.

here's code :

What I have tried:

package com.rfmsData.main;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;

import javax.print.DocFlavor.INPUT_STREAM;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Save2Mysql {
public static Connection conn=null;
public static PreparedStatement pstmt = null;

	public static void main(String[] args) throws IOException {
		 try {
			Class.forName ("com.mysql.cj.jdbc.Driver");
			conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/rfms", "root","root");
			String query="insert into rfmsreports values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(query);
			FileInputStream input_document = new FileInputStream(new File("E:\\RFMS Expenses1.xlsx"));
			XSSFWorkbook my_xls_workbook = new XSSFWorkbook(input_document);
			 XSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
			   Iterator<row> rowIterator = my_worksheet.iterator(); 
               while(rowIterator.hasNext()) {
                       Row row = rowIterator.next(); 
                       Iterator<cell> cellIterator = row.cellIterator();
                               while(cellIterator.hasNext()) {
                                       Cell cell = cellIterator.next();
                                       switch(cell.getCellType()) { 
                                       case Cell.CELL_TYPE_STRING: //handle string columns
                                               pstmt.setString(1, cell.getStringCellValue());                                                                                     
                                               break;
                                       case Cell.CELL_TYPE_NUMERIC: //handle double data
                                               pstmt.setDouble(2,cell.getNumericCellValue() );
                                               break;
                                       }
                                      
                               }
               }
               pstmt.executeUpdate();
               input_document.close();
               pstmt.close();
               conn.commit();
               conn.close();
		}   
		 
               catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// TODO Auto-generated method stub
 catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (FileNotFoundException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
		 
		 

	}

}
Posted
Updated 4-Mar-20 2:24am
v2
Comments
Member 14639038 4-Mar-20 4:46am
   
i have tried this:
while(cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if(row.getCell(1)==null)
{
months = null;
}
else
{
pstmt.setString(1, cell.getStringCellValue());
}

if(row.getCell(2)==null)
{
_adverExpenses = 0;
}
else
{
pstmt.setDouble(2, cell.getNumericCellValue());
}

if(row.getCell(3)==null)
{
_photocopy=0;
}
else
{
pstmt.setDouble(3, cell.getNumericCellValue());
}
if(row.getCell(4)==null)
{
_swachBharatExpenses = 0;
}
else
{
pstmt.setDouble(4, cell.getNumericCellValue());
}

if(row.getCell(5)==null)
{
_postageCourierExpenses = 0;
}
else
{
pstmt.setDouble(5, cell.getNumericCellValue());
}
if(row.getCell(6)==null)
{
_meetingConference = 0;
}
else
{
pstmt.setDouble(6, cell.getNumericCellValue());
}

if(row.getCell(7)==null)
{
_manpowerExpensesContact=0;
}
else
{

pstmt.setDouble(7,cell.getNumericCellValue());
}
if(row.getCell(8)==null)
{
_manpowerFlexi_staffTour=0;
}
else
{
pstmt.setDouble(8, cell.getNumericCellValue());
}
if(row.getCell(9)==null)
{
_salary_FTS = 0;
}

else
{
pst
Richard MacCutchan 4-Mar-20 5:58am
   
The error message is quite clear, you are missing a parameter value. Check your loop and ensure that you get the correct number of items from the worksheet. As far as I can see from your code (which needs formatting) you only set values for parameters 1 and 2.
Member 14639038 4-Mar-20 6:21am
   
but i have set the values for 29 parameters.
Richard MacCutchan 4-Mar-20 7:03am
   
Well that is not what the error message says. And why are you hard coding all thos numbers and not using a simple loop with an index?
Richard Deeming 4-Mar-20 11:00am
   
Your INSERT statement has 30 parameters.

And the code you've shown only ever sets the first two parameters.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Your Excel spreadsheet probably contains cells that are neither numeric (CELL_TYPE_NUMERIC) nor strings (CELL_TYPE_STRING), i.e., booleans, blanks, formulas or errors.

The switch statement on the cell type only looks for cells containing numeric or string values and so there are not enough cells in a row that contain these types to provide the correct number of parameters for your query.
   
Comments
Member 14639038 5-Mar-20 2:43am
   
but i have set the cells to string format
maj000 5-Mar-20 5:35am
   
Are there any blank cells or hidden columns in the Excel spreadsheet?
Member 14639038 5-Mar-20 6:27am
   
no sir
Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
java.sql.SQLException: No value specified for parameter 22.

The value of 22nd column is something not excepted by SQL.
The only thing to do is to look at the value, either in excel of in your code with the debugger.
-----
Your code do not behave the way you expect, or you don't understand why !

There is an almost universal solution: Run your code on debugger step by step, inspect variables.
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't know what your cpde is supposed to do, it don't find bugs, it just help you to by showing you what is going on. When the code don't do what is expected, you are close to a bug.
To see what your code is doing: Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.

Debugger - Wikipedia, the free encyclopedia[^]

Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
Basic Debugging with Visual Studio 2010 - YouTube[^]

http://docs.oracle.com/javase/7/docs/technotes/tools/windows/jdb.html[^]
https://www.jetbrains.com/idea/help/debugging-your-first-java-application.html[^]

The debugger is here to only show you what your code is doing and your task is to compare with what it should do.
   
Comments
Member 14639038 6-Mar-20 6:18am
   
when i written this lines:
int rows = my_worksheet.getLastRowNum();
short cols = row.getLastCellNum();
System.out.println("no. of cols"+cols);
it returns me 22 columns whereas in my excel has 30 columns
Member 14639038 6-Mar-20 6:18am
   
i have not idea

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100