Why got "Column 'ASD' is either not in any table in the FROM list or appears within a-CodePudding

While run my code I got the following exception:

java.sql.SQLSyntaxErrorException: Column 'ASD' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'ASD' is not a column in the target table. This is the error that print for exception e

Here is my code:

String driver = "org.apache.derby.jdbc.ClientDriver"; 
String connectionUrl = "jdbc:derby://localhost:1527/"; 
String database = "EmployeeDB"; 
String DBid = "app"; 
String DBpass = "app";
    <%
    try{
    Connection connection = DriverManager.getConnection(connectionUrl database, DBid, DBpass);
    Statement stt=connection.createStatement();
    //String sql ="select * from USERPROFILE where username=" Sname;
    String sql ="select * from USERPROFILE where username=" Sname;
    

    out.print("<br>4Welcome to Session Page: SQL " sql);
    out.print("<br>5Welcome to Session Page: result " Spass);
    ResultSet resultSQL = stt.executeQuery(sql);
    out.print("<br>6Welcome to Session Page: result " Spass);
    while(resultSQL.next()){
    out.print("<br>7Welcome to Session Page: Name " Sname  " Pass " Spass);
%>
<!DOCTYPE html>
<html>
<body>
<button onclick="history.back()">Go Back</button>
<h1>Update data from database in jsp</h1>
<form method="post" action="update-process.jsp">
<br>
<input type="hidden" name="id" value="<%=resultSQL.getString("id") %>">
Username:<br>
<input type="text" name="username" value="<%=resultSQL.getString("username") %>">
<br>
Password:<br>
<input type="text" name="password" value="<%=resultSQL.getString("password") %>">
<br>
Contact:<br>
<input type="text" name="contact" value="<%=resultSQL.getString("contact") %>">
<br>
Email:<br>
<input type="text" name="password" value="<%=resultSQL.getString("email") %>">
<br>
Work hour per Week<br>
<input type="text" name="workhour" value="<%=resultSQL.getString("workhour") %>">
<br>
Reward:<br>
<input type="text" name="reward" value="<%=resultSQL.getString("reward") %>">
<br>
<br><br>
<input type="submit" value="submit" onclick="return confirm('Are you sure you want to update?');">
</form>
<%
    }
    connection.close();
    } catch (Exception e) {
    e.printStackTrace();
    }

CodePudding user response：

In your code, Sname is String, your query should be SELECT * FROM USERPROFILE WHERE username='" Sname "'". for Integer use quotes like " Integer " and for String use quotes like '" String "'.

Here down is some mistake in your code.

This query is not secure, you have to use Parameterized Queries for secure your data.
To avoid any Syntax error, you have to use PreparedStatement instead Statement.
Put all code inside <form> tag because if code is outside to the <form> tag then you are not allowed for submit in form.

Here down is modified code with Parameterized Queries.


<!DOCTYPE html>
<html>
<body>
   <button onclick="history.back()">Go Back</button>
   <h1>Update data from database in jsp</h1>
   <form method="post" action="update-process.jsp">
   <%
       try{
           String sql ="SELECT * FROM USERPROFILE WHERE username = ?";
           Connection connection = DriverManager.getConnection(connectionUrl   database, DBid, DBpass);
           PreparedStatement stt = connection.prepareStatement(sql);
           stt.setString(1, Sname);

           out.print("<br>4Welcome to Session Page: SQL "   sql);
           out.print("<br>5Welcome to Session Page: result "   Spass);

           ResultSet resultSQL = pst.executeQuery();

           out.print("<br>6Welcome to Session Page: result "   Spass);

           while(resultSQL.next()){
                out.print("<br>7Welcome to Session Page: Name "   Sname   " Pass "   Spass);
   %>
   <br>
   <input type="hidden" name="id" value="<%=resultSQL.getString("id") %>">
   Username:<br>
   <input type="text" name="username" value="<%=resultSQL.getString("username") %>">
   <br>
   Password:<br>
   <input type="text" name="password" value="<%=resultSQL.getString("password") %>">
   <br>
   Contact:<br>
   <input type="text" name="contact" value="<%=resultSQL.getString("contact") %>">
   <br>
   Email:<br>
   <input type="text" name="password" value="<%=resultSQL.getString("email") %>">
   <br>
   Work hour per Week<br>
   <input type="text" name="workhour" value="<%=resultSQL.getString("workhour") %>">
   <br>
   Reward:<br>
   <input type="text" name="reward" value="<%=resultSQL.getString("reward") %>">
   <br>
   <br><br>
   <input type="submit" value="submit" onclick="return confirm('Are you sure you want to update?');">
<%
           }
           sst.close();
           connection.close();
       } catch (Exception e) {
           e.printStackTrace();
       }
%>
</form>