JSP-SQL Query to Access DB with Parameterized Query

•

What is DaniWeb IT Discussion Community?

You're currently browsing the JSP section within the Web Development category of DaniWeb, a massive community of 374,011 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,783 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our JSP advertiser: Lunarpages JSP Web Hosting

Thread Tools Display Modes

Views: 591 | Replies: 2

•

Join Date: May 2008

Location: US

Posts: 1

Reputation: Hockey-nut is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

Hockey-nut Hockey-nut is offline

Offline

Newbie Poster

JSP-SQL Query to Access DB with Parameterized Query

May 11th, 2008

I'm new to JSP and trying to do a parameterized query. I've been able to do single stream queries to existing tables without any problem. I'm trying to build a search mechanism to do selective reporting.

I've tried many hours to get the combination of different items to get by the ODBC SQL interface and have not been able to get the combination to work. Your advice/help would be greatly appreciated.

The following is the types of code being used. Html code setting up parameters being passed to .jsp page looks like this:

=====================================================

 Help with Code Tags 
 jsp Syntax (Toggle Plain Text) 
<form id="inputForm" name="inputForm" onsubmit="return validateOnSubmit()" title="schoolsearch"  method="post"  action="sS1.jsp">
 
<h4>
Select Degree Option
</h4>
 
<input type="radio" value="A" name="degreeOption"         />Associate
<input type="radio" value="B" name="degreeOption"         />Bachelor
<input type="radio" value="M" name="degreeOption"         />Masters
<input type="radio" value="P" name="degreeOption"         />Ph. D
<input type="radio" value="T" name="degreeOption"         />Training
<input type="radio" value="C" name="degreeOption"         />Certificate
 
<h4>
Select Search option below:
</h4>
<input type="radio" value="1" name="Search"    />  Program
<input type="radio" value="2" name="Search"    />  Institution
<input type="radio" value="3" name="Search"    />  PDF options
<input type="radio" value="4" name="Search"    />  Mail Report
<input type="radio" value="5" name="Search"    />  Request Other Information
 
<p> If you are looking for a <em>particular Program</em>, please type a search term below:
<br />
 <input type='text' name='progName' id='progName' size='30'  onchange='validateOther(this,'progName');'
</p>
<p> If you are looking for a <em>Institution</em>, please type a search term below:
<br />
<input type='text' name='instName' id='instName' size='30' onchange='validateOther(this,'instName');'
</p>
<form id="inputForm" name="inputForm" onsubmit="return validateOnSubmit()" title="schoolsearch"  method="post"  action="sS1.jsp">

<h4>
Select Degree Option
</h4>

<input type="radio" value="A" name="degreeOption"         />Associate
<input type="radio" value="B" name="degreeOption"         />Bachelor
<input type="radio" value="M" name="degreeOption"         />Masters
<input type="radio" value="P" name="degreeOption"         />Ph. D
<input type="radio" value="T" name="degreeOption"         />Training
<input type="radio" value="C" name="degreeOption"         />Certificate

<h4>
Select Search option below:
</h4>
<input type="radio" value="1" name="Search"    />  Program
<input type="radio" value="2" name="Search"    />  Institution
<input type="radio" value="3" name="Search"    />  PDF options
<input type="radio" value="4" name="Search"    />  Mail Report
<input type="radio" value="5" name="Search"    />  Request Other Information

<p> If you are looking for a <em>particular Program</em>, please type a search term below:
<br />
 <input type='text' name='progName' id='progName' size='30'  onchange='validateOther(this,'progName');'
</p>
<p> If you are looking for a <em>Institution</em>, please type a search term below:
<br />
<input type='text' name='instName' id='instName' size='30' onchange='validateOther(this,'instName');'
</p>

=====================================================
The sS1.jsp page gets the the degreeOption and Search fields. The code within sS1.jsp
looks like this:

 Help with Code Tags 
 jsp Syntax (Toggle Plain Text) 
    input1=request.getParameter("degreeOption");      
     input2=request.getParameter("Search");
     choice = Integer.parseInt(input2) ;
 
     searchTerm=request.getParameter("progName");     
     searchName=request.getParameter("instName");
 
     switch (choice)
      {
        case 1:
           out.println("--> Program selection -- Case 1" ) ;
           sqlQuery = ("SELECT   InstDegreeProgram.Program
                               FROM      InstDegreeProgram
                        WHERE (((InstDegreeProgram.Program) Like " + "\"*\"" + input2 + "\"*\""))
                        ORDER BY InstDegreeProgram.Program); ";
                  break;
        case 2:
           out.println("-- Institution selection -- Case 2" ) ;
           sqlQuery = "Case 2 : " ;
           sqlQuery = ("SELECT  InstDegreeProgram.Program
	                         FROM     InstDegreeProgram
	                         WHERE (((InstDegreeProgram.Program) Like \"*\" "+ input2 + " \"*\"))
                        ORDER BY InstDegreeProgram.Program)" ;
           break;
      } 
 
   java.sql.ResultSet rs = sqlQuery.executeQuery();
    input1=request.getParameter("degreeOption");      
     input2=request.getParameter("Search");
     choice = Integer.parseInt(input2) ;
          
     searchTerm=request.getParameter("progName");     
     searchName=request.getParameter("instName");
     
     switch (choice)
      {
        case 1:
           out.println("--> Program selection -- Case 1" ) ;
           sqlQuery = ("SELECT   InstDegreeProgram.Program
                               FROM      InstDegreeProgram
                        WHERE (((InstDegreeProgram.Program) Like " + "\"*\"" + input2 + "\"*\""))
                        ORDER BY InstDegreeProgram.Program); ";
                  break;
        case 2:
           out.println("-- Institution selection -- Case 2" ) ;
           sqlQuery = "Case 2 : " ;
           sqlQuery = ("SELECT  InstDegreeProgram.Program
	                         FROM     InstDegreeProgram
	                         WHERE (((InstDegreeProgram.Program) Like \"*\" "+ input2 + " \"*\"))
                        ORDER BY InstDegreeProgram.Program)" ;
           break;
      } 

   java.sql.ResultSet rs = sqlQuery.executeQuery();

===================================
JSP -- Error report

Help with Code Tags

(Toggle Plain Text)

String literal is not properly closed by a double-quote # 148
145:       {
146:         case 1:
147:            out.println("--> Program selection -- Case 1" ) ;
148:            sqlQuery = ("SELECT   InstDegreeProgram.Program
149:                         FROM     InstDegreeProgram
150:                         WHERE (((InstDegreeProgram.Program) Like " + "\"*\"" + input2 + "\"*\""))
151:                         ORDER BY InstDegreeProgram.Program); ";

==============================================

The microsoft query that works within the Access Database looks like this:

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
SELECT    InstDegreeProgram.Program
FROM       InstDegreeProgram
WHERE  (((InstDegreeProgram.Program) LIKE "*" & [What Program: ?] & "*"))
ORDER BY InstDegreeProgram.Program;
SELECT    InstDegreeProgram.Program
FROM       InstDegreeProgram
WHERE  (((InstDegreeProgram.Program) Like "*" & [What Program: ?] & "*"))
ORDER BY InstDegreeProgram.Program;

Hope this helps to provide you with background information that I'm working with in the future.

Last edited by peter_budo : May 12th, 2008 at 8:21 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Nov 2004

Location: Netherlands

Posts: 5,588

Reputation: jwenting is a jewel in the rough

Rep Power: 18

Solved Threads: 187

jwenting

Offline

duckman

Re: JSP-SQL Query to Access DB with Parameterized Query

May 12th, 2008

don't use scriptlets, don't put business logic in JSP, don't use Access as a database, use code tags

42 Private messages asking for help will be ignored
In the frozen land of Nador they were forced to eat Steve's iMinstrels, and there was much rejoicing.

•

Join Date: May 2008

Posts: 2

Reputation: sinan.yk is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

sinan.yk

Offline

Newbie Poster

Re: JSP-SQL Query to Access DB with Parameterized Query

May 13th, 2008

hi, guy. i think same as jwenting. and your query string's errror is that you have to write your query as a string. your query string should be like that sqlquery=#
"SELECT InstDegreeProgram.Program
FROM InstDegreeProgram
WHERE (((InstDegreeProgram.Program) Like " + "\"*\"" + input2 + "\"*\""+"))"+"ORDER BY InstDegreeProgram.Program";
you must close string literals. see you..