Hi,
I can seem to get this SELECT statement to work in the
JSP... but it works fine in QUERY ANALYSER on SQL server, and it work work in JSP code... and I don't understand why it is doing that...
Here is the error:
javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'select'.
I know that it is the second SELECT it does not like...
Here is the code:
<%
// Request the information from the url
String catid = request.getParameter("catid");
// Create a new instance of the DataAccess Class
DataAccess d = new DataAccess();
// This call the database connection method from the DataAccess Class
d.ConnectDatabase();
/* This gets the threads from database for the categories */
// This is initilzing the variables
String threadname = null;
String handle = "";
String messagedate = "";
String threaddate = "";
int threadid = 0;
int recordcount = 0;
// This sets the sql variable to nothing
String sql = "";
// This sets the rs variable to null
ResultSet rs = null;
// This is doing a SELECT on the
thread table based on the category id
sql += "select max(m.fm_date) as date, count(m.fm_messageid) as rcount,";
sql += "ft.threadname as threadname, ft.handle, ft.threadid, ft.threaddate";
sql += "from (select t.ft_threadname 'threadname', p.fp_handle 'handle'";
sql += "t.ft_threadid 'threadid', t.ft_threaddate 'threaddate'";
sql += "from forum_threads t, forum_profile p";
sql += "where t.ft_profileid = p.fp_profileid";
sql += "and t.ft_catid = 2) as ft";
sql += "left join forum_messages m on m.fm_threadid = ft.threadid";
sql += "group by ft.threadid, ft.threadname, ft.handle, ft.threaddate";
rs = d.SQLQuery(sql);
/* End of categories */
%>
<%
while (rs.next()){
messagedate = rs.getString("date");
recordcount = rs.getInt("rcount");
threadname = rs.getString("threadname");
handle = rs.getString("ft.handle");
threadid = rs.getInt("ft.threadid");
threaddate = rs.getString("ft.threaddate");
}
%>