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();
}
In your code, Sname
is String, then 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.
Parameterized Queries
for secure your data.PreparedStatement
instead Statement
.<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>