Data Retrieving Problem in JDBC

[pelican71]

Hi,

Anyone can help?


Specification:
1) Use JSP
2) Apache Tomcat/4.0.4
2) Use Acesss for the "COLLEGESEMESTER" table
3) 3 fields : CS_ID (Primary key, 11, text)
DESCRIPTION (100, text)
COLLEGE_NAME (70, text)



I dun understand why always having the error "No data found" (refer to the error message)
when trying to have "rsSearch.getString("cs_id&quot". This is also happen in other table such
as "Subject", "Lecturer" table. Other field is OK to display but only happened on the Primary
key. It is fine when remark away &quot;out.println(&quot;<P>CS_ID : &quot; + rsSearch.getString(&quot;cs_id&quot +&quot;</P>&quot;&quot;
. Anything wrong with my coding or database? Please help...

Thank you.

Best Regards,
Eng Choon






***********************************************************************************************
My coding to retrieve the data and display
-------------------------------------------------------------



Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot.newInstance();
Connection connProject = DriverManager.getConnection(&quot;jdbcdbcroject&quot;

//if user select CS_ID as search criteria
if (s_category.equalsIgnoreCase(&quot;CS_ID&quot)
{
//select semester with sorted by CS_ID
String sqlSearch = &quot;SELECT * FROM COLLEGESEMESTER WHERE COLLEGESEMESTER.CS_ID LIKE '%&quot; + s_text + &quot;%' ORDER BY COLLEGESEMESTER.CS_ID ASC&quot;;
Statement stmtSearch = connProject.createStatement();
ResultSet rsSearch = stmtSearch.executeQuery(sqlSearch);

while (rsSearch.next())

//found one or more semesters and each semester has its own &quot;view details&quot; function
{

out.println(&quot;<P>CS_ID : &quot; + rsSearch.getString(&quot;cs_id&quot +&quot;</P>&quot;
out.println(&quot;<P>Description : &quot; + rsSearch.getString(&quot;description&quot +&quot;</P>&quot;

}


***********************************************************************************************

Error message from Apache Tomcat
----------------------------------------------------



Apache Tomcat/4.0.4 - HTTP Status 500 - Internal Server Error

--------------------------------------------------------------------------------

type Exception report

message Internal Server Error

description The server encountered an internal error (Internal Server Error) that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: No data found
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:471)
at org.apache.jsp.semester_0005fdisplay_0005flisting$jsp._jspService(semester_0005fdisplay_0005flisting$jsp.java:162)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
at java.lang.Thread.run(Thread.java:484)


root cause

java.sql.SQLException: No data found
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6212)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3266)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5398)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:326)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:383)
at org.apache.jsp.semester_0005fdisplay_0005flisting$jsp._jspService(semester_0005fdisplay_0005flisting$jsp.java:105)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
at java.lang.Thread.run(Thread.java:484)

 

[venur]

Hi,

Did you make sure that the query what you are trying to execute is giving the results as you expected.

Try giving column index rather than column name ?

Ex : rsSearch.getString(1);

It is always safest to reference columns by the column numbers as some DB's are casesensitive.

Try this and let me know.

Cheers,
Venu

 

[pelican71]

Hi Venu,

Have tried yr suggestions but still getting the same error..

choon

 

[Hattusas]

As an answer for the second situation :
When you type * from how can you be sure that rs.getString(1) will give you the cs_id?
Or as an answer for the first situation :
You should type your field as case sensitive as venur stated.

Venur is right in some ways.So, I'd suggest doing this.
Select CS_ID,DESCRIPTION,COLLEGE_NAME instead of &quot;*&quot; then
get your result set by result.getString(1) or the field name itself.


Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr

 

[pelican71]

Hi Salih,

I have tried yr suggestion also but still get the error &quot;no data found&quot; when getString(1)...


anyway below is my original code and more details on the problem:

smaple data in COLLEGESEMESTER table:

CS_ID = CS200401001
DESCRIPTION = HELP CHARLES STURT UNIVERSITY MASTER OF INFORMATION TECHNOLOGY 1ST SEMESTER 2004
COLLEGE_NAME = HELP INSTITUTE KUALA LUMPUR


Below is my original source code for user to key in the search string in order to get the record.


semester_search.jsp
-------------------

<html>

<head>
<title>Semester Search Page (Administrator)</title>
</head>

<!-- to avoid empty field in the search function-->

<SCRIPT LANGUAGE=&quot;JavaScript&quot;>
<!-- start script here
function checkfield( thisform )
{
if (thisform.T1.value == null || thisform.T1.value == &quot;&quot; )
{
alert (&quot;Search Text Cannot Be Blank!&quot ;
thisform.T1.focus() ;
thisform.T1.select() ;
return false ;
}
}

// End of script -->
</SCRIPT>

<body bgcolor=&quot;#66CCFF&quot;>

<p> </p>

<form method=&quot;POST&quot; action=&quot;

http://localhost:8080/testing/semester_display_listing.jsp&quot;

onSubmit=&quot;return checkfield( this )&quot;>
<p align=&quot;center&quot;>
<select size=&quot;1&quot; name=&quot;D1&quot; tabindex=&quot;1&quot;>
<option selected>CS_ID</option>
<option>Description</option>
</select> <input type=&quot;text&quot; name=&quot;T1&quot; size=&quot;25&quot; tabindex=&quot;2&quot;>
<input type=&quot;submit&quot; value=&quot;Search&quot; name=&quot;B1&quot; tabindex=&quot;3&quot;>
</p>
</form>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>

</body>

</html>



semester_display_listing.jsp
----------------------------
<%@ page import=&quot;java.sql.*&quot; %>

<html>

<head>

<title>Semester Display Listing (Administrator)</title>
</head>

<body bgcolor=&quot;#66CCFF&quot;>


<font size=&quot;4&quot; color=&quot;#663300&quot;>Semester Display Listing</font></p>
<HR NOSHADE>

<%

//store user input category and the search string into variable
String s_category = (String) request.getParameter(&quot;D1&quot;
String s_text = (String) request.getParameter(&quot;T1&quot;
boolean found = false;

Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot.newInstance();
Connection connProject = DriverManager.getConnection(&quot;jdbcdbcroject&quot;

//if user select CS_ID as search criteria
if (s_category.equalsIgnoreCase(&quot;CS_ID&quot)
{
//select semester with sorted by CS_ID
String sqlSearch = &quot;SELECT * FROM COLLEGESEMESTER WHERE CS_ID LIKE '%&quot; + s_text + &quot;%' ORDER BY CS_ID ASC&quot;;

Statement stmtSearch = connProject.createStatement();
ResultSet rsSearch = stmtSearch.executeQuery(sqlSearch);

while (rsSearch.next())

//found one or more semesters and each semester has its own &quot;view details&quot; function
{

out.println(&quot;<P>CS_ID : &quot; + rsSearch.getString(&quot;CS_ID&quot +&quot;</P>&quot;
out.println(&quot;<P>Description : &quot; + rsSearch.getString(&quot;DESCRIPTION&quot +&quot;</P>&quot;

//View details form starts here
out.println(&quot;<form method=\&quot;POST\&quot; action=\&quot;

http://localhost:8080/&quot;

+&quot;testing/semester_view_details.jsp?semesterid=&quot; + rsSearch.getString(&quot;cs_id&quot+ &quot;\&quot;>&quot;
out.println(&quot;<p align=\&quot;center\&quot;><input type=\&quot;submit\&quot; value=\&quot;View Details\&quot; name=\&quot;B1\&quot;></p>&quot;
out.println(&quot;</form>&quot;
out.println(&quot;<HR NOSHADE>&quot;

found=true;
}

// cannot locate the correct member
if (found == false)
response.sendRedirect(&quot;

http://localhost:8080/testing/semester_not_exists_error.jsp&quot;);

}



//if user select Description as search criteria
else if (s_category.equalsIgnoreCase(&quot;Description&quot)
{
//select semester with sorted by Description
String sqlSearch2 = &quot;SELECT * FROM COLLEGESEMESTER WHERE COLLEGESEMESTER.DESCRIPTION LIKE '%&quot; + s_text + &quot;%' ORDER BY COLLEGESEMESTER.DESCRIPTION ASC&quot;;
Statement stmtSearch = connProject.createStatement();
ResultSet rsSearch = stmtSearch.executeQuery(sqlSearch2);

while (rsSearch.next())

//found one or more semesters and each semester has its own &quot;view details&quot; function
{
out.println(&quot;<P>CS_ID : &quot; + rsSearch.getString(&quot;CS_ID&quot +&quot;</P>&quot;
out.println(&quot;<P>Description : &quot; + rsSearch.getString(&quot;DESCRIPTION&quot +&quot;</P>&quot;

//View details form starts here
out.println(&quot;<form method=\&quot;POST\&quot; action=\&quot;

http://localhost:8080/&quot;

+&quot;testing/semester_view_details.jsp?semesterid=&quot; + rsSearch.getString(&quot;cs_id&quot+ &quot;\&quot;>&quot;
out.println(&quot;<p align=\&quot;center\&quot;><input type=\&quot;submit\&quot; value=\&quot;View Details\&quot; name=\&quot;B1\&quot;></p>&quot;
out.println(&quot;</form>&quot;
out.println(&quot;<HR NOSHADE>&quot;

found=true;
}

// cannot locate the correct semester
if (found == false)
response.sendRedirect(&quot;

http://localhost:8080/testing/semester_not_exists_error.jsp&quot;);

}

connProject.close();

%>

<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>


</body>

</html>

 

[venur]

Hi,

The problem is you are using the rsSearch.getString(&quot;cs_id&quot twice. Some of the drivers do not provied the factility.

Try this

String cs_id = rsSearch.getString(&quot;cs_id&quot

use the String cs_id with in the resuletset loop.

Your Code Change it to >>

while (rsSearch.next())

//found one or more semesters and each semester has its own &quot;view details&quot; function
{
String cs_id = rsSearch.getString(&quot;CS_ID&quot;
out.println(&quot;<P>CS_ID : &quot; + cs_id +&quot;</P>&quot;
out.println(&quot;<P>Description : &quot; + rsSearch.getString(&quot;DESCRIPTION&quot +&quot;</P>&quot;

//View details form starts here
out.println(&quot;<form method=\&quot;POST\&quot; action=\&quot;

http://localhost:8080/&quot;;

+&quot;testing/semester_view_details.jsp?semesterid=&quot; + cs_id + &quot;\&quot;>&quot;
out.println(&quot;<p align=\&quot;center\&quot;><input type=\&quot;submit\&quot; value=\&quot;View Details\&quot; name=\&quot;B1\&quot;></p>&quot;
out.println(&quot;</form>&quot;
out.println(&quot;<HR NOSHADE>&quot;

found=true;
}




Cheers
-Venu

 

[pelican71]

Hi Venu,

U r GREAT!!!! Your suggestion was working...thanks a lot..i have this post this problem to 7 different forum and u r the only one to solve my problem....again thanks...can i ask u other prolem in future?

choon