As a newcomer to JSP (not JPS) you should be aware that putting Java code into a JSP is not a good start. Java scriptlets (code in a JSP) was obsoleted over 12 years ago with the introduction of the EL and JSTL with JSP 2.0.

If you are learning scriptlets in order to support legacy applications that still use them, that's one thing. If you are learning them in order to use them in new code, then you are heading down the wrong path.

Please find study material that covers modern JSP, which means, no Java code in the pages.

If this is not for a legacy system, then your first step is to move all that data access code to its own Java class. Not only will it improve the structure of the web app, it will make it easier to test.

You should also be using a Prepared statement for your DB query. What you have there is open to SQL injection attacks.

P.S. You should be testing your SQL statements using a SQL tool such as Squirrel SQL or DbVisualizer. Testing it in the code is time-consuming. Determine what the correct syntax is first; then write the code.

Andreas Mutota
Greenhorn

Joined: Dec 13, 2012
Posts: 5

posted Feb 02, 2014 10:08:13

0

Was JSP ever used to generate paginated records using MSSQL Server database?

The main purpose is to have an idea how such records using JSP and MSSQL Server 2008 database. The only function I can see now used by someone using mysql database and work fine is this
“SELECT SQL_CALC_FOUND_ROWS * FROM Country” . When I am trying that in Server 2008, I get this massage=
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'
“
The MYSQL database understands the following functions:
SQL_CALC_FOUND_ROWS and
FOUND_ROWS().
Which functions does the MSSQL Server 2008 database understands instead?

Sample Code:

String sqlPagination="SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPageNo+","+iShowRows+"";
psPagination=conn.prepareStatement(sqlPagination);
rsPagination=psPagination.executeQuery();
//// this will count total number of rows
String sqlRowCnt="SELECT FOUND_ROWS() as cnt";
psRowCnt=conn.prepareStatement(sqlRowCnt);
rsRowCnt=psRowCnt.executeQuery();
”
Thanks in Advance