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")%>&nbsp;</td>
        <td><%=rs.getString("Lname")%>&nbsp;</td>
        <td><%=rs.getString("Uname")%>&nbsp;</td>
        <td><%=rs.getString("pword")%>&nbsp;</td>
        <td><%=rs.getString("Role")%>&nbsp;</td>
        <td><%=rs.getString("email")%>&nbsp;</td>

    </tr>

    <%
       }
       //clean up all objects
         rs.close();

    %>  
</body>

</html>
<%
   testConn.close();
%>

Please help.

Edited 3 Years Ago by pyTony: fixed formating

I have the same problem, can u post ur working code please..

Greetz,

EEE

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")%>&nbsp;</td>
<td><%=rs.getString("Lname")%>&nbsp;</td>
<td><%=rs.getString("Uname")%>&nbsp;</td>
<td><%=rs.getString("pword")%>&nbsp;</td>
<td><%=rs.getString("Role")%>&nbsp;</td>
<td><%=rs.getString("email")%>&nbsp;</td>


</tr>


<%
}
//clean up all objects
rs.close();


%>
</body>


</html>
<%
testConn.close();
%>

Edited 3 Years Ago by happygeek: fixed formatting

This article has been dead for over six months. Start a new discussion instead.