Validation for the primary key in the database

[Thendral]

hai..

I have a database with a primary key(Employee id).

I am having an insert form, display form, delete form and main page form.

I want to validate the Employee id entered by the user in the insert form that means I want to check with the database, If it is already there in the database. I want to give an alert message when I try to submit the form.

Employee id is a primary key for that table. so i did "Not null validation and Numeric validation" in javascript.

How can I check the duplicate entry for Employee id while submitting the form?

[peter_budo]

Well you will have to submit data and let controler(servlet) compare it against DB, if Employee ID already exist you will have to go back and display error message. Otherwise you continue with data processing.
Other option is to have this ID's retrived from DB before any page displayed, something like empty index page with 1 sec redirection to servlet whitch retrive data from DB and save them in the session. After that provide user with form to fill and you can check for duplicats. But I will not advice to use second option

[Pinky.Rozita]

this is the code ... it checks whether the id exist or no !! .. when the person insert ..


<%@ page language="java" import="java.sql.*" errorPage="" %>
<%!

String sqlStat;
Connection conn;
Statement stmt;
int prodno, price;
String prodname, ptype;
%>
<%

try {

prodno = Integer.parseInt(request.getParameter("prodno"));
prodname = request.getParameter("prodname");
ptype = request.getParameter("ptype");
price = Integer.parseInt(request.getParameter("price"));

sqlStat = "INSERT INTO itemTBL (PRODNO, PRODNAME, PTYPE, PRICE) VALUES ";
sqlStat = sqlStat + "(" + prodno + ",";
sqlStat = sqlStat + "'" + prodname + "',";
sqlStat = sqlStat + "'" + ptype + "',";
sqlStat = sqlStat + "" + price + ")";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:prodDSN","","");
stmt = conn.createStatement();
stmt.executeUpdate(sqlStat);

out.print("Product ID already exist");
%>


Click
view all
to bla bla
<%
conn.close();

} catch (Exception e) {
out.println("Error : " + e.getMessage() );
}
%>

[masijade]

Well, first of all, it would be much safer and reliabe to use a PreparedStatement rather than a Statement.

Second of all, a JSP is meant for viewing, changes/actions should happen in a Servlet, and scriptlets should not be used at all in JSP.

Also, if you must actually check before inserting, use a Select. But simpler (if the item you would need to check is the Primary Key) is to simply attempt to insert it and catch the SQLException. If a Primary Key constraint violation is returned, the record already existed.

And the only thing you can do from the server side, is to return an error page, or return to the form. You can't pop up an alert before the submit, unless you want to attempt to use AJAX, as those alert popups that you are probably thinking of, are JavaScript and the JavaScript running on the client would have no access to the database.