 |  |  |  |  |  |  |  |
Dynamic Query
Please support our JSP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
I have a search code from Database. i want to display queries from Database with multiple option. how do i assign variable on both. for example
String jsql= "Select * from UserList WHERE '"+abu+ "' = '"+userSearch+ "'";
the full code:
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*, java.util.*" errorPage="error.jsp"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Chemistry Search</title>
</head>
<body>
<br>
<%
//load the MS Access JDBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//open a connection to the "chemistry_test" database
String url="jdbc:odbc:chemistry_test";
Connection testConn= DriverManager.getConnection(url,"test1","test1");
String userSearch = request.getParameter("Pnumber") ;
String buttonChoice = request.getParameter("searchChoice") ;
//place query results in a ResultSet object
//String jsql="Select * from UserList WHERE uName= '"+userSearch + "'" ;
// String abu = request.getParameter("Pnumber") ;
String abu;
if (buttonChoice == ("username"))
abu = "uName";
else if(buttonChoice == ("userrole"))
abu = "Role" ;
else if (buttonChoice == ("firstname"))
abu = "Fname" ;
String jsql= "Select * from UserList WHERE '"+abu+ "' = '"+userSearch+ "'";
//create a statement object for sending SQL queries
Statement stmt = testConn.createStatement();
ResultSet rs = stmt.executeQuery(jsql);
%>
<br>
<form method="POST" action="ChemistryDataSearchVar.jsp">
<table border="0" width="228" id="table1">
<tr>
<td colspan="2">Please choose</td>
</tr>
<tr>
<td width="150"><input type="text" name="Pnumber" size="20"></td>
<td><input type="submit" value="Search" name="B2"></td>
</tr>
<tr>
<td colspan="2"><input type="radio" value="username" checked name="searchChoice">user name
<input type="radio" value="userrole" name="searchChoice">role
<input type="radio" value="firstname" name="searchChoice">first name</td>
</tr>
</table>
</form>
<br>
<br>
<br>
<tt class="1">User Information</tt><br><br>
<table border="1" width="300" id="table1" cellspacing="0" cellpadding="4">
<tr bgcolor="bcbcbc">
<td width="30">First Name</td>
<td width="50">Last Name</td>
<td width="30">User Name</td>
<td width="20">Password</td>
<td width="30">Role</td>
<td width="140">E-mail Address</td>
</tr>
//assign ResultSet's column 1 to a String variable
<% while(rs.next()){
%>
<tr>
<td><%=rs.getString("Fname")%> </td>
<td><%=rs.getString("Lname")%> </td>
<td><%=rs.getString("Uname")%> </td>
<td><%=rs.getString("pword")%> </td>
<td><%=rs.getString("Role")%> </td>
<td><%=rs.getString("email")%> </td>
</tr>
<%
}
//clean up all objects
rs.close();
%>
</body>
</html>
<%
testConn.close();
%>
Please help.
String jsql= "Select * from UserList WHERE '"+abu+ "' = '"+userSearch+ "'";
the full code:
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*, java.util.*" errorPage="error.jsp"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Chemistry Search</title>
</head>
<body>
<br>
<%
//load the MS Access JDBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//open a connection to the "chemistry_test" database
String url="jdbc:odbc:chemistry_test";
Connection testConn= DriverManager.getConnection(url,"test1","test1");
String userSearch = request.getParameter("Pnumber") ;
String buttonChoice = request.getParameter("searchChoice") ;
//place query results in a ResultSet object
//String jsql="Select * from UserList WHERE uName= '"+userSearch + "'" ;
// String abu = request.getParameter("Pnumber") ;
String abu;
if (buttonChoice == ("username"))
abu = "uName";
else if(buttonChoice == ("userrole"))
abu = "Role" ;
else if (buttonChoice == ("firstname"))
abu = "Fname" ;
String jsql= "Select * from UserList WHERE '"+abu+ "' = '"+userSearch+ "'";
//create a statement object for sending SQL queries
Statement stmt = testConn.createStatement();
ResultSet rs = stmt.executeQuery(jsql);
%>
<br>
<form method="POST" action="ChemistryDataSearchVar.jsp">
<table border="0" width="228" id="table1">
<tr>
<td colspan="2">Please choose</td>
</tr>
<tr>
<td width="150"><input type="text" name="Pnumber" size="20"></td>
<td><input type="submit" value="Search" name="B2"></td>
</tr>
<tr>
<td colspan="2"><input type="radio" value="username" checked name="searchChoice">user name
<input type="radio" value="userrole" name="searchChoice">role
<input type="radio" value="firstname" name="searchChoice">first name</td>
</tr>
</table>
</form>
<br>
<br>
<br>
<tt class="1">User Information</tt><br><br>
<table border="1" width="300" id="table1" cellspacing="0" cellpadding="4">
<tr bgcolor="bcbcbc">
<td width="30">First Name</td>
<td width="50">Last Name</td>
<td width="30">User Name</td>
<td width="20">Password</td>
<td width="30">Role</td>
<td width="140">E-mail Address</td>
</tr>
//assign ResultSet's column 1 to a String variable
<% while(rs.next()){
%>
<tr>
<td><%=rs.getString("Fname")%> </td>
<td><%=rs.getString("Lname")%> </td>
<td><%=rs.getString("Uname")%> </td>
<td><%=rs.getString("pword")%> </td>
<td><%=rs.getString("Role")%> </td>
<td><%=rs.getString("email")%> </td>
</tr>
<%
}
//clean up all objects
rs.close();
%>
</body>
</html>
<%
testConn.close();
%>
Please help.
Here it is. Good Luck
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*, java.util.*"errorPage="error.jsp"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Chemistry Search</title>
</head>
<body>
<br>
<br>
<form method="POST" action="ChemistryDataSearchVar.jsp">
<table border="0" width="270" id="table1">
<tr>
<td colspan="2">Please choose</td>
</tr>
<tr>
<td width="150"><input type="text" name="Pnumber" size="20"></td>
<td><input type="submit" value="Search" name="B2"></td>
</tr>
<tr>
<td colspan="2">
<input type="radio" value="uName" checked name="searchChoice">user name
<input type="radio" value="Role" name="searchChoice">User role
<input type="radio" value="Fname" name="searchChoice">first name</td>
</tr>
</form>
</table>
<br>
<br>
<br>
<tt class="1">User Information</tt><br><br>
<table border="1" width="300" id="table1" cellspacing="0" cellpadding="4">
<tr bgcolor="bcbcbc">
<td width="30">First Name</td>
<td width="50">Last Name</td>
<td width="30">User Name</td>
<td width="20">Password</td>
<td width="30">Role</td>
<td width="140">E-mail Address</td>
</tr>
<%
//load the MS Access JDBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//open a connection to the "chemistry_test" database
String url="jdbc:odbc:chemistry_test";
Connection testConn= DriverManager.getConnection(url,"test1","test1");
String userSearch = request.getParameter("Pnumber") ; //get data from the textbox
String buttonChoice = request.getParameter("searchChoice") ; //get choice from the radio button
String abu = buttonChoice ;
String jsql= "Select *from UserList WHERE "+abu+" = '"+userSearch+"'"; //abu is a variable for executing BD and userSearch is from textbox typed by user
//Select * from UserList WHERE uName= 'admin'
//System.out.println(jsql);
out.println(jsql);
//%>>>><%
//out.println(buttonChoice);
//create a statement object for sending SQL queries
Statement stmt = testConn.createStatement();
ResultSet rs = stmt.executeQuery(jsql);
%>
<% //assign ResultSet's column 1 to a String variable
while(rs.next()){
%>
<tr>
<td><%=rs.getString("Fname")%> </td>
<td><%=rs.getString("Lname")%> </td>
<td><%=rs.getString("Uname")%> </td>
<td><%=rs.getString("pword")%> </td>
<td><%=rs.getString("Role")%> </td>
<td><%=rs.getString("email")%> </td>
</tr>
<%
}
//clean up all objects
rs.close();
%>
</body>
</html>
<%
testConn.close();
%>
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*, java.util.*"errorPage="error.jsp"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Chemistry Search</title>
</head>
<body>
<br>
<br>
<form method="POST" action="ChemistryDataSearchVar.jsp">
<table border="0" width="270" id="table1">
<tr>
<td colspan="2">Please choose</td>
</tr>
<tr>
<td width="150"><input type="text" name="Pnumber" size="20"></td>
<td><input type="submit" value="Search" name="B2"></td>
</tr>
<tr>
<td colspan="2">
<input type="radio" value="uName" checked name="searchChoice">user name
<input type="radio" value="Role" name="searchChoice">User role
<input type="radio" value="Fname" name="searchChoice">first name</td>
</tr>
</form>
</table>
<br>
<br>
<br>
<tt class="1">User Information</tt><br><br>
<table border="1" width="300" id="table1" cellspacing="0" cellpadding="4">
<tr bgcolor="bcbcbc">
<td width="30">First Name</td>
<td width="50">Last Name</td>
<td width="30">User Name</td>
<td width="20">Password</td>
<td width="30">Role</td>
<td width="140">E-mail Address</td>
</tr>
<%
//load the MS Access JDBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//open a connection to the "chemistry_test" database
String url="jdbc:odbc:chemistry_test";
Connection testConn= DriverManager.getConnection(url,"test1","test1");
String userSearch = request.getParameter("Pnumber") ; //get data from the textbox
String buttonChoice = request.getParameter("searchChoice") ; //get choice from the radio button
String abu = buttonChoice ;
String jsql= "Select *from UserList WHERE "+abu+" = '"+userSearch+"'"; //abu is a variable for executing BD and userSearch is from textbox typed by user
//Select * from UserList WHERE uName= 'admin'
//System.out.println(jsql);
out.println(jsql);
//%>>>><%
//out.println(buttonChoice);
//create a statement object for sending SQL queries
Statement stmt = testConn.createStatement();
ResultSet rs = stmt.executeQuery(jsql);
%>
<% //assign ResultSet's column 1 to a String variable
while(rs.next()){
%>
<tr>
<td><%=rs.getString("Fname")%> </td>
<td><%=rs.getString("Lname")%> </td>
<td><%=rs.getString("Uname")%> </td>
<td><%=rs.getString("pword")%> </td>
<td><%=rs.getString("Role")%> </td>
<td><%=rs.getString("email")%> </td>
</tr>
<%
}
//clean up all objects
rs.close();
%>
</body>
</html>
<%
testConn.close();
%>
|
Similar Threads
- Update Query is not Working.. (PHP)
- dynamic query (PHP)
- Creating a dynamic query using MSSQL and PHP (PHP)
- Dynamic query string (Search Engine Optimization)
Other Threads in the JSP Forum
- Previous Thread: stopping further execution
- Next Thread: question
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Latest JSP Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
apache array backbutton combobox comma connection csv database development directorystructure dropdownlist dynamicpagetitles eclipse frames glassfish ie8 imagetodatabse imageupload integer internet java javaee javascript jsf jsp jsppagetitles levels mvc2 mvcmodel2 mysql netbeans network parameters passing ping printinserverinsteadofclient read redirect request.getparameter response seperated servlet servletdopost()readxml sessions software sql ssl state_saving_method stocks sun tomcat tutorial update values video web write
