Solved

Failed update

Posted on 2004-09-09
11
227 Views
Last Modified: 2010-04-01
Is there any reason that this isn't working?

The call:

    public int updateRecord(String id, String slug, String username, String content) throws Exception {
        String query = "UPDATE news SET (slug, username, content, timestamp) VALUES (?, ?, ?, ?) WHERE slug = ?";
        stmt = conn.prepareStatement(query);
        java.util.Date now = new java.util.Date();
        stmt.setString( 1, slug);
        stmt.setString( 2, username);
        stmt.setString( 3, content);
        stmt.setTimestamp( 4, new java.sql.Timestamp( now.getTime() ) );
        stmt.setString( 5, id);
        int ret = stmt.executeUpdate();
        return(ret);
    }

The jsp:

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%@page import="news.*" %>

<%
 if (session.getAttribute("logged")==null) {
            response.sendRedirect(response.encodeRedirectURL("login.jsp"));
  } else {
      String user = (String)session.getAttribute("logged");
 %>

<% NewsConnect nc = new NewsConnect();%>
<%
String id = request.getParameter("id");
String slug = request.getParameter("slug");
String content = request.getParameter("content");
String contact = request.getParameter("username");
String submit = request.getParameter("submit");
String msg = "";
if (submit != null) {
    nc.dbConnect();
    if ((nc.updateRecord(id, slug, contact, content)) > 0) {
        msg = "Your record for '"+ slug + "' has been changed!! <a href=records.jsp>Go back</a>";
        } else {
        msg = "Failed to update the record!";
        }
    nc.dbDisconnect();
    %> <%= msg %> <%
    }
}
%>


The only thing I can think of, is that I am not including every field in my update, just the ones I want to update. Though I don't know why this would be an issue.

Or, the field id in the database is an auto increment int and when I use string to reference that record for update it fails, I tried to int id = Integer.parseInt() but I got the same results. A blank page after I click submit and the record unchanged.

Any idea?

thanks!
0
Comment
Question by:k41d3n
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 7

Expert Comment

by:lhankins
ID: 12022181
On your WHERE clause, do you really mean to have "WHERE slug = ?" (seems like this should be "WHERE id = ?")

>
>   String query = "UPDATE news SET (slug, username, content, timestamp) VALUES (?, ?, ?, ?) WHERE slug = ?";
>


Also - Can't tell from what you've posted, but what is the autocommit set to for the JDBC Connection...?   If you have autocommit set to false, then your update won't get applied until you issue a commit (this is how you support transactions with JDBC).

If you need autocommit set to false, just be sure and call :

   conn.commit();

When you're done with your update.

If you want to change the value of the autocommit for the current connection, just do :

   c.setAutoCommit(true);

0
 
LVL 2

Author Comment

by:k41d3n
ID: 12022225
First, you are right, I forgot to switch the operator back to id from slug.

neither work.

and I can't see needing to use autocommit, as I can update and add records with the same function on another table.

which leads me to believe it's because of the id.....
0
 
LVL 7

Expert Comment

by:lhankins
ID: 12022437
One thing which might help debug the problem is to switch your prepared statement to a normal SQL statement (all strings) and then do a System.out.println on it just before executing.

Then copy the output of your debug statement into your favorite SQL editor and see it works from there.   If it doesn't work in the SQL editor, you'll have an easier time debugging there.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 12022816
How do you know that database is not updated?
Is there a cache on web browser?
To make sure, you should check it by using SQL edit to make a query to check whether you update operatio success or not.

0
 
LVL 2

Author Comment

by:k41d3n
ID: 12023385
yeah, I know it isn't updating because I check the database physically.

And it's a local database.
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 12023540
Have you tested simple update statement like this in SQL editor yet?

UPDATE news SET (slug, username, content, timestamp) VALUES ("some slug", "some name", "some content", "some timestamp") WHERE id = 123
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 12023682
Are you seeing any exception coming out? I think your time stamp is doing something funny...
what is the data type of timestamp field?
0
 
LVL 21

Expert Comment

by:MogalManic
ID: 12025572
Try this statement:
UPDATE news SET slug=?, username=?, content=?, timestamp=?
WHERE id = ?

The update statement you are using is not standard SQL
0
 
LVL 2

Author Comment

by:k41d3n
ID: 12026769
I'm a bone head.

In my form I had the id field as hidden, but I left out the name="id" so the id was never getting passed to the edit action.

I figured it out by printing the variables with the error message.
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12064310
Please read entire thread.  Asker solved or content important.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Running Java Servlet inside Eclipse Luna 6 168
issue with pdf generation 2 96
constructor overloading 2 93
sortaSum challenge java 17 174
Active Directory security has been a hot topic of late, and for good reason. With 90% of the world’s organization using this system to manage access to all parts of their IT infrastructure, knowing how to protect against threats and keep vulnerabil…
In order to fulfill our mission of inspiring learning in the technology community, Experts Exchange is launching a Course of the Month program. Premium and Team Account members will have access to one course per month as a part of their membership, …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question