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 426,500 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,173 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: 717 | Replies: 2

Previous Post in Thread

Next Post in Thread

•

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;