JSP-SQL Query to Access DB with Parameterized Query

[Hockey-nut]

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:

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

<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:

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

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:

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.

[jwenting]

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

[sinan.yk]

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..