CODEWITHSUNDEEP
sqljsp
sudha vedi
sudha vedi

Reputation: 107

Getting error "error:java.sql.SQLException: Invalid column index" in JSP Code

I am getting error "error:java.sql.SQLException: Invalid column index" with below piece of code. Can you please help where am I doing wrong here?

String SEARCH_SQL_COUNT = "SELECT COUNT(*) FROM (
  SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
  FROM PERSONAL_INFO UNION ALL 
  SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
  FROM ELEC_AUTO_MERC UNION ALL 
  SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
  FROM MISSING_PERSON_INFO UNION ALL 
  SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
  FROM PETS_INFO UNION ALL 
  SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
  FROM REAL_ESTATE UNION ALL 
  SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
  FROM SERVICES_INFO) 
  WHERE UPPER(AD_TITLE) LIKE UPPER('%?%') 
  OR UPPER(DESCRIPTION) LIKE UPPER('%?%') 
  OR UPPER(STATE_NAME) LIKE UPPER('%?%') 
  OR UPPER(CITY_NAME) LIKE UPPER('%?%') 
  OR UPPER(CATEGORY) LIKE UPPER('%?%') 
  OR UPPER(SUBCATEGORY) LIKE UPPER('%?%')";

int ad_count=0;

PreparedStatement st_fetch_product_count_1 = conn.prepareStatement(SEARCH_SQL_COUNT);
st_fetch_product_count_1.setString(1,qs_keyword);
st_fetch_product_count_1.setString(2,qs_keyword);
st_fetch_product_count_1.setString(3,qs_keyword);
st_fetch_product_count_1.setString(4,qs_keyword);
st_fetch_product_count_1.setString(5,qs_keyword);
st_fetch_product_count_1.setString(6,qs_keyword);

ResultSet rs_fetch_product_count_1 = st_fetch_product_count_1.executeQuery();

Upvotes: 0

Views: 613

Answers (1)

Mike Nakis
Mike Nakis

Reputation: 61969

You are executing a SELECT COUNT(*) statement. Such a statement only yields one row containing a single column, the count. Your setString() calls expect 6 columns, so index 2 is already out of range.

Also, you are trying to use setString() on a read-only result set, but that's a different issue.

Upvotes: 1

Related Questions