What is the use of prepareStatement?

[jerald m]

Hi,
i have written jsp for retrieving the responseText for Javascript but it throw error as

ERROR

Type mismatch: cannot convert from String to int
while(rs.next())
 {
   ProjectCod = ""+ rs.getInt(1);
        }
   out.print(ProjectCod);
 
 

my Code comes below

stateId = Integer.parseInt(request.getParameter("dil_ProjectCode"));
 
       stmt = con.prepareStatement("select projectcode from jerald where projectcode ="+stateId+"");
 
    rs = stmt.executeQuery();
    int ProjectCod ;
    while(rs.next())
    {
 
        ProjectCod = ""+ rs.getInt(1);
        }
    out.print(ProjectCod);
}
 
 

[nathj]

Not sure I know what the question is here. If it is about prepareStatement this is used to do just that it prepares a SQL statement for use with your database connection. Basically, reading the code you see that you have a connection, you know the SQL you want to run so you prepare the SQL with the connection giving you a Statement object that contains a query which you can execute to return a resultSet.

If you have a question about converting String to int then you need to use the parseInt function something like:

String i = "12"
String output = "the integer is " + i; // using a string as a string
System.out.println(output);
 
int i2 = 13;
// convert i to int to do some maths
Integer result = Integer.parseInt(i) + i2;
// output as a string
system.out.println("Result: " + result.toString());
 

This should work for you. If I have misunderstood the question then please post back and I'll happily try again.

Thanks
nathj

[jerald m]

Hi Nathj thanks for ur reply..,

now i'm getting no error,but it display a blank window
it is not entering into while(rs.next){ please help me

JavaScript Code

function loadContent(formj)
{
 
 xmlhttp=GetXmlHttpObject();
 
  if (xmlhttp==null)
  {
   alert ("Your browser does not support Ajax HTTP");
   return;
  }
 
    var stateValue = formj.dil_ProjectCode.value;
    alert(stateValue);
    var url="Code.jsp";
    url=url+"?dil_ProjectCode="+stateValue;
    alert(url);
    xmlhttp.onreadystatechange=getOutput;
    xmlhttp.open("GET",url,true);
    xmlhttp.send(null);
}
 
 
function getOutput()
{
  if (xmlhttp.readyState==4)
  {
 
     var result = xmlhttp.responseText;
    alert(result);
  }
}
function GetXmlHttpObject()
{
    if (window.XMLHttpRequest)
    {
       return new XMLHttpRequest();
    }
    if (window.ActiveXObject)
    {
      return new ActiveXObject("Microsoft.XMLHTTP");
    }
 return null;
}
 
 

Form

<input type="text" name="dil_ProjectCode" id="dil_ProjectCode" >
 

Code.jsp

stateId = request.getParameter("dil_ProjectCode");
 
    String query = "select projectcode from jerald where projectcode ="+stateId+"";
    stmt = con.prepareStatement(query);
 
    rs = stmt.executeQuery();
 
    while(rs.next())
    {
 
    String ProjectCod = ""+ rs.getString(1);
        }
    out.print(ProjectCod);
}
 

Please Help me out..,

[nathj]

Hi,

Try something like this:

PreparedStatement statement = new PreparedStatement("select projectcode from jerald where projectcode = ?");
statement.setString(1, stateId);
 
ResultSet rs = statement.executeQuery();
 

This should give you what you are after.

Thanks
nathj

[jerald m]

Hi Nathj Thanks for ur reply.I'm newly joined in the Company.
i need complete this form as soon as possible.
i stick with this error for 3 day's.
all the steps are completed in the Form. this one ERROR ONLY.., please help me Out..

No Boss ur Code is not working..
But now i Found out the error.,
Same code only..
in Code.jsp here i'm not getting any value i don't Y..,
But this is the ERROR
CODE.jsp

 
stateId = request.getParameter("dil_ProjectCode");
 
 

[nathj]

Hi,

I'm sorry to say I'm not sure I follow what the problem is here.

Can you let me know what you are expecting, what you are actually getting and what line you think or know is causing the problem.

Cheers
nathj

[jerald m]

Hi nathj,
Sorry 4 disturbing u again..,

i need to get input from the user(text box field value) and check whether the input is given by the user is in the database or not.

if the user entered the input in the database i need to give the alert msg as "value is already exist's" else {
it needs to save into database..(this is i want),
any IDEA..,



Existing post say's i need to get matched data from the database in alert msg.
Next STep i need to give condition to check and giving alert msg.

But the response it self is not coming.So if the response is come means my work is finished.

once again thank's nathj..,
Help me oUt..,

[nathj]

Hi,

Don't worry about disturbing me - I make time each day to help other out.

Now it seems to me that you want to have an onChange() event that calls some AJAX. This would then pass the value of the textbox to some Java function that checks the database for the value. If the value is found you return a message that the Javascript function writes out next to the text box . If the value is not found you write a different message out to explain that all is well.

I would check out some tutorials on AJAX and JSP. Take a look at this Google Search. Getting an understanding of what is going on will give you a good foundation for future projects.

Cheers
nathj

[jerald m]

hi nathj,

Now i change my idea in coding.
Form Code

<form name="form1" action="http://localhost:8085/NewForm/dil_ProjectMaster.jsp" method="POST" onsubmit="return checkform(this) && Dat(this);">
 

<javascript>

<%
if(request.getAttribute("jerald")!=null)
{%>
alert("Project Code is Already is in the Database");
 
<%}
 

server Code:

Connection con = null;
PreparedStatement stmt=null;
PreparedStatement stmtRead=null;
//Statement stmt=null;
ResultSet rs1=null;
int updateQuery = 0;
 
 
 
Connection con = null;
PreparedStatement stmt=null;
PreparedStatement stmtRead=null;
//Statement stmt=null;
ResultSet rs1=null;
int updateQuery = 0;
 
 
 
 
try{
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","distil","distil");
 
 
      String queryRead = "select projectcode from DIL_PROJECTMASTER where projectcode ='"+proCode+"'";
 
    stmtRead = con.prepareStatement(queryRead);
 
    rs1 = stmtRead.executeQuery();
 
 
 
    if(rs1.next())
    {
 
        request.setAttribute("jerald","Record is there");
 
        %><jsp:include page="/Form1.jsp"/><%}
    else
    {
 
 
 
stmt = con.prepareStatement("INSERT INTO DIL_PROJECTMASTER(PROJECTCODE,PROJECTNAME,WORKLEVEL1,WORKLEVEL2,WORKLEVEL3,NOTES,PROJECTSTDATE,PROJECTENDATE) values(?,?,?,?,?,?,?,?)");
 
 
            stmt.setInt(1,proCode);
 
            stmt.setString(2,proName);
 
            stmt.setString(3,option1);
 
            stmt.setString(4,option2);
 
            stmt.setString(5,option3);
 
            stmt.setString(6,notes);
 
            stmt.setDate(7,new java.sql.Date(result1.getTime()));
 
            stmt.setDate(8,new java.sql.Date(result2.getTime()));
 
updateQuery=stmt.executeUpdate();
 
    stmt.close();
//con.close();
 
 
 
if( updateQuery != 0)
    {
 
 
%>
    <jsp:forward page="/TimeForm.jsp"/>
<%
 
}
}
}
catch (SQLException ex){
System.err.println(ex.getMessage());
}
catch (Exception e){
e.printStackTrace();
}
finally{
    con.close();
}
%>
 

the alert msg is coming in server page but i want that alert msg to come in form.
wat i need to do..,

[nathj]

Hi,

I noticed on you form you have the onsubmit returning the results of boolean addition:

checkform(this) && Dat(this)
 

I assume the javascript you posted relates to checkform what does dat() do?

Also, on the form you need to have an element - prehaps a div or a span that the warning or success messgae can be posted into. Then with the onchange event of the textbox you can call the AJAX to find if the data is valid. The server side codes returns either true or false. The Javascript then uses

// snippet
var target = document.getElementById(outputMsg);
if(valid == true)
{
target.innerHTML='<p class="success">valid</p>';
}
else
{
target.innerHTML='<p class="fail">invalid</p>';
}
 

That little snippet would go in the Javascript that is called by the onchange event of the text box. Alternativley you could have a simple button next to the text box that the user click to check the name.

Cheers
Nathan

[jerald m]

Hi Nathan,
Thanks i completed that form.i used setAttribute and getAttribute to display the alert msg in client side itself.once again thanks to u for helping me.
change's of the code are following

Server Side Code

try{
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","username","password");
 
 
    String queryRead = "select projectcode from DIL_PROJECTMASTER where projectcode ='"+proCode+"'";
 
    stmtRead = con.prepareStatement(queryRead);
    rs1 = stmtRead.executeQuery();
 
    if(rs1.next())
    {
    request.setAttribute("jerald",proCode);
    String strViewPage="Form1.jsp";
    RequestDispatcher dispatcher =  request.getRequestDispatcher(strViewPage);
 if (dispatcher != null){
 dispatcher.forward(request, response);
}
 %><jsp:include page="/Form1.jsp"/><%}
else
{
 
stmt = con.prepareStatement("INSERT INTO DIL_PROJECTMASTER(PROJECTCODE,PROJECTNAME,WORKLEVEL1,WORKLEVEL2,WORKLEVEL3,NOTES,PROJECTSTDATE,PROJECTENDATE)  values(?,?,?,?,?,?,?,?)");
    stmt.setInt(1,proCode);
        stmt.setString(2,proName);
        stmt.setString(3,option1);
    stmt.setString(4,option2);
    stmt.setString(5,option3);
    stmt.setString(6,notes);
    stmt.setDate(7,new java.sql.Date(result1.getTime()));
stmt.setDate(8,new java.sql.Date(result2.getTime()));
updateQuery=stmt.executeUpdate();
stmt.close();
 
if( updateQuery != 0)
{
%>
 <jsp:forward page="/TimeForm.jsp"/>
<%
 
}
}
}
 

this code i putted inside javascript(Form.jsp)

<%
 if(request.getAttribute("jerald")!=null)
{%>
alert("Project Code is Already is in the Database");
 
<%}
 %>
 

this is i changed in my program..,
Once again thanks for helping me THANKS..,

they gave me another Login form(admin side,projectuser side) Exercise. i want create it and join with exist's form.

i want same help in tat process also please...,
Bye Take Care..