aspose file tools*
The moose likes JSP and the fly likes Help with JSP and Access queries! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Reply locked New topic
Author

Help with JSP and Access queries!

Bhaskar Duvvuri
Greenhorn

Joined: Nov 30, 2003
Posts: 7
Hi all! I have a JSP which queries a table (Access db). This table has 3 records, but my jsp returns only one record (the first one only). I am using Tomcat 4-1-29. Needless to say I am a novice to tomcat and/or the jsp programming. Enclosed is my jsp code. The same query returns me all three rows if I use the Access query facility. I also downloaded a program from internet, which takes the sql as a parameter and spits out the results. Even that is showing 3 rows. What am I doing wrong? Please...please help!!! If I put the same thing in a db bean and call it via my jsp, nothing is returned. The error message says (1 record via jsp direct, zero records via the db bean) SQLState: Null. Message: No data found.
I am really desperate. Please help. Here is my JSP code:
System.out.println("Inside the ViewAds.jsp\n");
int ad=0;
Connection dbconn;
ResultSet rs;
PreparedStatement sql;
String dbUrl = "jdbc dbc rojectdb";
String dbUserName = "";
String dbPassword = "";

try
{
//loading the database jdbc driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
try
{
dbconn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
sql = dbconn.prepareStatement("SELECT AdStore.AdId, AdStore.AdDate, " +
"AdStore.AdStatus, AdStore.AdType, " +
"AdStore.AdDesc, AdStore.AdPrice, AdStore.HighBid FROM AdStore " +
"WHERE (((AdStore.AdStatus)='A'))");
rs = sql.executeQuery();
int recnum = 0;
while(rs != null && rs.next())
{
if (rs != null)
{
System.out.println("Got more rows\n");
recnum ++;
}
%>
<tr bgcolor="#CCCCCC">
<td width="28%" height="30"><div align="center"><font color="#FFFFFF"><%=rs.getInt(1) %></font></div></td>
<td width="33%"><div align="center"><font color="#FFFFFF"><%=rs.getString(4) %></font></div></td>
<td width="39%"><div align="center"><font color="#FFFFFF"><%=rs.getString(5) %></font></div></td>
<td width="40%"><div align="center"><font color="#FFFFFF">
<A HREF="<%=base%>?action=PlaceBid=1%>">Bid on this Item</font></div></td>
</tr>
<%
System.out.println("Total recs: " + recnum + "\n");
System.out.println("Fetched row: " +
rs.getInt(1) + rs.getString(4) + rs.getString(5) + "\n");
}
}
catch (SQLException ex) {
System.out.println("*** SQLException caught ***\n");
while (ex != null) {
System.out.println("SQLState: \n" + ex.getSQLState());
System.out.println("Message: \n" + ex.getMessage());
System.out.println("Vendor: \n" + ex.getErrorCode());
ex = ex.getNextException();
}
}
catch (java.lang.Exception ex) {
ex.printStackTrace();
}
}
catch (ClassNotFoundException e)
{
System.out.println(e.toString());
}
%>
Nagaseshagiri poola
Greenhorn

Joined: Oct 18, 2003
Posts: 25
Hi,
i dont c anything wrong there but why do u need to check the null in the while loop. when rs.next() is not true it will not enter n except that evrything seems to be fine.
have a nice time
Bhaskar Duvvuri
Greenhorn

Joined: Nov 30, 2003
Posts: 7
Thanks! I just put that in as part of my debugging. Anyhow, I am running Tomcat on windows 2000. Is there anything from the settings of tomcat I need to worry about?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61654
    
  67

Sunny SideUp,
You'll find this forum a great place to seek help on JSP pages, and there aren't many rules you'll have to worry about, but one is that proper names are required. Please take a look at the JavaRanch Naming Policy and change your display name to match it.
Thanks!
bear
JSP Fourm Bartender


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61654
    
  67

Nagaseshagiri, your posts will be better understood if you limit your use of 'chat room' contractions.
thanks,
bear
JSP Bartender
Anselm Paulinus
Ranch Hand

Joined: Sep 05, 2003
Posts: 389
How many of these three rcords in AdStore have AdStatus = 'A'?
Check to make sure the three records do; if so remove the where clause in querystring and try again. You sure will be able to find the problem by so doing.
You can even try an ordinary Select * to see what it will generate. By incresing iteratively you sure will know where the error is coming from.
Bhaskar Duvvuri
Greenhorn

Joined: Nov 30, 2003
Posts: 7
Thank you very much for replying. The thing is that I tried all combinations of sql with the jsp and no matter what I do, it only returns one row (the first row). I tried "SELECT *" and the result is the same. Is this something to do with tomcat 4.1.29 on Windows 2000? Please...help!!!
Bhaskar Duvvuri
Greenhorn

Joined: Nov 30, 2003
Posts: 7
OK! The problem I found was with showing the returned records. For some reason with the formatting I used in the While loop is killing the result set from fetching the next row. What I am doing wrong? If I removed the formatting (starting with <TR> in the while (rs.next()) loop), then I see all records in stdout.log. Help??
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Help with JSP and Access queries!