hi to all

I am trying to make a simple java servlet(like jsp) program which

allow user to update his profile
but I have a problem when user click on update's button

String un = req.getParameter("username");

String Fn = req.getParameter("Fname");
String Ln = req.getParameter("Lname");
String pw = req.getParameter("passwors");



String updateString = "UPDATE T1 SET Fname =" +Fn+", "Lname="+Ln+" 

"WHERE username ="+un+ "";

int Upd;
Upd=theStatement.executeUpdate(updateString);

and this is problem

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'dWHERE username =sara s'.

but when I using this code
String updateString = "UPDATE T1 SET Fname ='xxx',Lname='yyy' WHERE username= 'sara s'";

it is ok and update the database

but i want to read change from user

can any one help me,,please

thanks

i think the error in the syntax :-/
because i don't know what is the syntax of updating row in table

can anyone help me:'(

"UPDATE T1 SET Fname ="  + "'" + Fn + "'" +  ", Lname = " + "'" + Ln + "'" + " WHERE username = " + "'" + un + "'";

:scared: Don't hate me if this didn't work ..

:$ ..

Your "fix" to allow here cobbled together Statement to work, is, technically correct (although there is no reason to do a separate + "'" for everyone of the single quotes), however, she should not be using a cobbled together statement in the first place. What happens if one of the variables used contains a single quote (')? The statement will be broken. For that exact reason, the statement is also left wide open to SQL injection atacks.

@OP, use a PreparedStatement and its set... Methods

String updateString = "UPDATE T1 SET Fname =?, Lname=? WHERE username =?";
preparedStatement pStmt = conn.prepareStatement(updateString);
pStmt.setString(1, Fn);
pStmt.setString(2, Ln);
pStmt.setString(3, un);
int Upd = theStatement.executeUpdate();

And, if you have this code as a scriptlet in a JSP, remove it. Place all actual "code" into one (or more) beans. Scriptlets are strongly discouraged in the current version of JSP.

Comments
Thank you boss ..

I am work in servlet ( java )

i will try your code and give you what is the result

nice code :$

thanks very much
----

ok I will give you green rep point :P

i am trying your code but have 5 errors
some errors in this statement

int Upd = theStatement.executeUpdate();

and I was try to solve it
now there is no error in compiler

but when run in html say this error

No ResultSet was produced

and this is my modified code:

import java.io.*;
import java.util.Enumeration;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.sql.PreparedStatement;
import java.net.*;

public class edit2 extends HttpServlet{


	Connection theConnection;
	private ServletConfig config;

	public void init(ServletConfig config)
		throws ServletException{
		this.config=config;

	}

    public void service (HttpServletRequest req, HttpServletResponse res)
      throws ServletException, IOException {
       	  
	  HttpSession session = req.getSession(true);
	  res.setContentType("text/html");
	  PrintWriter out = res.getWriter();

	try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Loading Sun's JDBC ODBC Driver
            theConnection = DriverManager.getConnection("jdbc:odbc:Dew", "", ""); //Connect to emaildb Data source
Statement theStatement=theConnection.createStatement();

String Fn  = req.getParameter("p1");
String Ln  = req.getParameter("p2");
String Em  = req.getParameter("p3");
String un  = req.getParameter("p4");

String updateString = "UPDATE T1 SET Fname =?, Lname=? WHERE username =?";
PreparedStatement pStmt = theConnection.prepareStatement(updateString);
pStmt.setString(1, Fn);
pStmt.setString(2, Ln);
pStmt.setString(3, un);

ResultSet theResult = pStmt.executeQuery();


            theResult.close();//Close the result set		
            theStatement.close();//Close statement
            theConnection.close(); //Close database Connection 

	     }catch(Exception e){
		out.println(e.getMessage());//Print trapped error.
	}

	}
	public void destroy(){
	
	}

}

can you help me again :icon_sad:

you are not executing your update statement, but querying it .. why??

String updateString = "UPDATE T1 SET Fname =?, Lname=? WHERE username =?";
PreparedStatement pStmt = theConnection.prepareStatement(updateString);
pStmt.setString(1, Fn);
pStmt.setString(2, Ln);
pStmt.setString(3, un);

pStmt.executeUpdate():


Read: http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html


masijade boss don't be harsh on me if i was wrong, hehe .. me learning :P

No, no. I wasn't trying to be harsh before. Sorry, if you took it that way. You're right here, about the cause, anyway, but there are a few other things as well.

public void service (HttpServletRequest req, HttpServletResponse res)
      throws ServletException, IOException {
       	  
        HttpSession session = req.getSession(true);
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();

        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Loading Sun's JDBC ODBC Driver
            theConnection = DriverManager.getConnection("jdbc:odbc:Dew", "", ""); //Connect to emaildb Data source
            Statement theStatement=theConnection.createStatement();

            String Fn  = req.getParameter("p1");
            String Ln  = req.getParameter("p2");
            String Em  = req.getParameter("p3");
            String un  = req.getParameter("p4");

            String updateString = "UPDATE T1 SET Fname =?, Lname=? WHERE username =?";
            PreparedStatement pStmt = theConnection.prepareStatement(updateString);
            pStmt.setString(1, Fn);
            pStmt.setString(2, Ln);
            pStmt.setString(3, un);

            ResultSet theResult = pStmt.executeQuery();

            theResult.close();//Close the result set		
            theStatement.close();//Close statement
            theConnection.close(); //Close database Connection 

        }catch(Exception e){
            out.println(e.getMessage());//Print trapped error.
        }

    }

should be

public void service (HttpServletRequest req, HttpServletResponse res)
      throws ServletException, IOException {
       	  
        HttpSession session = req.getSession(true);
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        PreparedStatement theStatement = null;
        Connection theConnection = null;

        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Loading Sun's JDBC ODBC Driver
            theConnection = DriverManager.getConnection("jdbc:odbc:Dew", "", ""); //Connect to emaildb Data source

            String Fn  = req.getParameter("p1");
            String Ln  = req.getParameter("p2");
            String Em  = req.getParameter("p3");
            String un  = req.getParameter("p4");

            String updateString = "UPDATE T1 SET Fname =?, Lname=? WHERE username =?";
            pStmt = theConnection.prepareStatement(updateString);
            pStmt.setString(1, Fn);
            pStmt.setString(2, Ln);
            pStmt.setString(3, un);

            int num = pStmt.executeUpdate();
        } catch(Exception e) {
            out.println(e.getMessage());//Print trapped error.
        } finally {
            if (theStatement != null) try { theStatement.close(); } catch (Exception e) {} //Close statement
            if (theConnection != null) try { theConnection.close(); } catch (Exception e) {} //Close database Connection 
        }

    }

Always close your connections, statements, and resultsets in a finally block to ensure that they get closed.

You should seriously think about using the connection pooling that the servlet container provides, as creating the connection in the service request is extrememly inefficient.

You should also be providing doPost() and doGet() methods, rather than overriding the service() method.

And, the actual db interaction should be taking place in another class that gets called by the servlet, rather than in the servlet itself. It makes the entire code more maintainable, as the db code can than be changed without affecting the servlet at all.

Also, you should think about using a different driver (and if you're using access, a different db), as the JDBC-ODBC bridge is not thread-safe and a servlet is inherently threaded. When a servlet container starts, it creates an instance of the servlet, than uses this one instance in a separate thread for every request (which is also the reason that you should have, essentially, no instance variables in a servlet (or you should at least not change its value, as this will affect multiple threads).

Comments
hehe @ few .. looks A LOT to me :D .. anyway, you rock :D

thaaaaaaaaaaaaanks masijade and Sulley's Boo very much

I was add this statement because it is not defined before

PreparedStatement pStmt= null;

is it necessary also to close this statement??

any where, your code is run correctly :)

and I will put your precept in my brain
but this code is try ,,
so,I will write professional code when I submit the project to my instructor


I have another question :$
when I want to insert date and time, I will use PreparedStatement as I read.
Can anyone help me how I can use it to insert date and time into table?

I found this code :

Timestamp ts=new Timestamp(tryDate.getTime());
PreparedStatement p = connection.prepareStatement("insert into

test(col1,col2) values(?,?)"
p.setTimestamp(1,ts);
p.setString(2,theMsg.getContent());
p.executeUpdate();

and when I applied it, I found some errors because I don't know
what is tryDate and theMsg and how define it??

import java.io.*;
import java.util.Enumeration;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.net.*;


public class T3 extends HttpServlet{


	//Connection theConnection;
	private ServletConfig config;

	public void init(ServletConfig config)
		throws ServletException{
		this.config=config;

	}

    public void service (HttpServletRequest req, 

HttpServletResponse res)
      throws ServletException, IOException {
       	  
        HttpSession session = req.getSession(true);
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        PreparedStatement theStatement = null;
        PreparedStatement p = null;
        Connection theConnection = null;

        try{
            Class.forName

("sun.jdbc.odbc.JdbcOdbcDriver");//Loading Sun's JDBC ODBC Driver
            theConnection = DriverManager.getConnection

("jdbc:odbc:Dew", "", ""); //Connect to emaildb Data source


String type  = req.getParameter("ProblemType");
String lab   = req.getParameter("LabNum");
String comp  = req.getParameter("ComputerNum");
String desc  = req.getParameter("desc");
String prio  = req.getParameter("priority");


Timestamp ts=new Timestamp(tryDate.getTime());
p = theConnection.prepareStatement("insert into T3(time,date) 

values(?,?)");
p.setTimestamp(1,ts);
p.setString(2,theMsg.getContent());
p.executeUpdate();


        } catch(Exception e) {
            out.println(e.getMessage());//Print trapped error.
        } finally {
            if (theStatement != null) try { theStatement.close(); 

} catch (Exception e) {} //Close statement
            if (theConnection != null) try { theConnection.close

(); } catch (Exception e) {} //Close database Connection 
        }

    }
	public void destroy(){
	
	}

}

and also in table T3 I want to insert another variable like type,lab,comp,desc,prio
How can I add these vareiable??

Are this statements below correct??

Timestamp ts=new Timestamp(tryDate.getTime());
p = theConnection.prepareStatement("insert into T3

(type,Lab#,computer#,description,prio,time,date) values (?,?,?,?,?,?,?)");

p.setString(1, type);
p.setString(2, lab);
p.setString(3, comp);
p.setString(4, desc);
p.setString(5, prio);
p.setTimestamp(1,ts);
p.setString(2,theMsg.getContent());
p.executeUpdate();

This project is self study, :'(
so, I need your aid
to do my project correctly

and also I should submit this project in next days :@
remained 5 days for the delivery of project :S


have a nice day to all :*

Read the API docs for PreparedStatement. There are a plethora of set.... methods. One for each data type in the DB. The descriptions of these methods will tell you what object is needed to use it, and what data type is handled by it.

I found these method

setDate(int parameterIndex, Date x)
setTime(int parameterIndex, Time x)
setTime(int parameterIndex, Time x, Calendar cal)
setTimestamp(int parameterIndex, Timestamp x)
setTimestamp(int parameterIndex, Timestamp x, Calendar cal)

but really I don't know how can I use it :( :(
all the last days I'm crying and searching in the internet :'(
I have a lot of tasks must be done
I feel that I am confused :confused:

I am very tired :icon_sad:

I still search,, but I hope to anyone help me :pretty:

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