unknown column in field list

Question

0.00/5 (No votes)

See more:

this is my code block to get the updated values

XML

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h3>Updated Successfuly!!</h3>

       <sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/mysql"
     user="root"  password="root"/>

        <sql:update dataSource="${snapshot}" var="result">
            UPDATE user_reg_ml SET  uname=${param.uname} ,email=${param.email} ,country=${param.country} where id=${param.id}
        </sql:update>
    <%--
        <sql:query var="result" dataSource="${snapshot}">
            select * from user_reg_ml where id=${param.id}
        </sql:query>
         <table>

        <c:forEach var="row" items="${res.rows}">
            <tr>
                <td>
                    ID
                </td>
                 <td>
                    ${row.id}
                </td>
            </tr>
            <tr>
                <td>
                    Name
                </td>
                <td>

                    ${row.uname}
                </td>
            </tr>
            <tr>
                <td>
                    Email
                </td>
                <td>
                    ${row.email}
                </td>
            </tr>
            <tr>
                <td>
                    Country
                </td>
                <td>
                    ${row.country}
                </td>
            </tr>
        </c:forEach>
    </table>
    --%>
    </body>
</html>

and this is my code block to edit values

XML

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        Welcome ${uname}

     <form action="update_mypage.jsp" method="post">

<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/mysql"
     user="root"  password="root"/>

<sql:query dataSource="${snapshot}" var="result">
select * from user_reg_ml where uname='${param.uname}' and pwd='${param.pwd}'
</sql:query>
        <%--input type="hidden" name="user" value="${param.uname}"--%>

        <c:forEach var="row" items="${result.rows}">
            ID: <input type="text" name="id" value="${row.id}" ><br/>

            USERNAME:  <input type="text" name="uname" value="${row.uname}"><br/>

            EMAIL: <input type="text" name="email" value="${row.email}"><br/>

            COUNTRY: <input type="text" name="country" value="${row.country}"><br/>

        </c:forEach>

        <input type="submit" value="update">
</form>

    </body>
</html>

but when i click update button, i get following error

HTTP Status 500 - Internal Server Error

type Exception report

messageInternal Server Error

descriptionThe server encountered an internal error that prevented it from fulfilling this request.

exception
javax.servlet.ServletException:
UPDATE user_reg_ml SET uname=abc ,email=abc@gmail.com ,country=India where id=1
: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com ,country=India where id=1' at line 1

root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com ,country=India where id=1' at line 1

note The full stack traces of the exception and its root causes are available in the GlassFish Server Open Source Edition 4.0 logs.

Posted 12-Aug-15 3:28am

Member 9671810

Add a Solution

Comments

Richard MacCutchan 12-Aug-15 10:09am

What does the documentation say about strings containing the '@' sign?

Member 9671810 12-Aug-15 10:30am

i have error if i pass only uname also.

2 solutions

Solution 1

It is likely that your email string needs to be quoted, like:

SQL

UPDATE user_reg_ml SET  uname=${param.uname}, email='${param.email}', country=${param.country} where id=${param.id}

I also suggest putting your space-comma sequences the other way round as I have. Probably makes no difference but it certinly looks more correct.

Posted 12-Aug-15 4:16am

Richard MacCutchan

Comments

Member 9671810 12-Aug-15 10:29am

again i have the same error

javax.servlet.ServletException:
UPDATE user_reg_ml SET uname=abc ,email=abc@gmail.com ,country=India where id=1UPDATE user_reg_ml SET uname=abc, email='abc@gmail.com', country=India where id=1
: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com ,country=India where id=1UPDATE user_reg_ml SET uname=abc, email=' at line 1

Richard MacCutchan 12-Aug-15 10:37am

Look at that statement. What do you expect?

Member 9671810 12-Aug-15 10:42am

but do you think there is any syntax error?? infact i tried to update maually using uname=abc. then also there is an error saying unknown column in fieldlist.

Richard MacCutchan 12-Aug-15 10:49am

OK, so what are the column names supposed to be?

Member 9671810 12-Aug-15 10:55am

same as what i passed, id,pwd,uname,email and country

Richard MacCutchan 12-Aug-15 11:02am

Well one of them is wrong according to the error message. Either that or the format of your UPDATE statement is still incorrect.

Member 9671810 12-Aug-15 11:54am

i tried updating either uname or country also.. :(

Richard MacCutchan 12-Aug-15 12:32pm

You need to go and check all your configuration information, table and field names etc. Check the spelling carefully, upper vs lower case letters. What you have shown here does not provide enough information to be able to help you.

Member 9671810 12-Aug-15 12:44pm

see.. if i run this command it gets executed
select id,uname,pwd,country from user_reg_ml;

Member 9671810 12-Aug-15 12:48pm

but if i execute this query

update user_reg_ml SET uname=abcd where id='1'

then it gives me following error

Error code 1054, SQL state 42S22: Unknown column 'abcd' in 'field list'

Richard MacCutchan 12-Aug-15 12:59pm

You really need to read the documentation carefully. From what I see the information after the equals sign needs to be a quoted string, as I suggested 2 hours ago.

Member 9671810 12-Aug-15 13:16pm

haha sir.. the problem was. i didn't use double quotes("").

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 9671810 · Accepted Answer · 2015-08-12T07:17:00

Solution 2

SQL

<sql:update dataSource="${snapshot}" var="result">
           update user_reg_ml SET  uname="${param.uname}" ,email="${param.email}" ,country="${param.country}" where id='${param.id}'
       </sql:update>

Posted 12-Aug-15 7:17am

Member 9671810