Why SQL INSERT query takes the entered values as column names

Question

I have a problem with the SQL INSERT QUERY. Whenever I execute the INSERT QUERY in the below code, what happens, is the query understands the values to be entered as the column names. My code is :

try
{
    Class.forName("com.mysql.jdbc.Driver");
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost/db","root","123456");
    Statement s = con.createStatement();
    int start = 8, end = 10;
    char buf[] = new char[end-start];   // for extracting day alone.

    dvalue = new String();
    ddvalue = new String(); // for adding the extracted day to the table.
    dvalue = cb3.getSelectedItem().toString();
    dvalue.getChars(start, end, buf, 0);System.out.println(buf);
    ddvalue = String.copyValueOf(buf);


    s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values (hello,"+cb3.getSelectedItem()+")");
}
catch(SQLException s)
{
    System.out.println("SQL statemnet is not executed!");
    System.out.println(s);
}

The error that I get after executing the query is :-

SQL statemnet is not executed! com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'hello' in 'field list'

-- EDIT-- Actually, my code was :

s.executeUpdate("insert into "+nameoftab+" (sname,"+""+ddvalue+""+") values ("+cb5.getSelectedItem()+","+cb3.getSelectedItem()+")");

When I insert the quotes like everyone said, what happens is that the term "cb5.getSelectedItem()" is being entered into the table. The case with "+cb3.getSelectedItem()+" is that, it just enteres some garbage value.

give strings in single quotes giv me ur table name and column name — Parvathy, Feb 19 '13 at 04:52
Actually, I don't know the names of columns. It depends on the input by the user. The only column name I know is sname. The other column name is obtained by the user input through the JComboBox object cb3. — Roshan George, Feb 19 '13 at 04:54

Tyler Ferraro · Accepted Answer · 2013-02-19T04:59:05.647

2

You need to quote the string hello in your query.

s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values ('hello',"+cb3.getSelectedItem()+")");

edited Feb 19 '13 at 04:59

answered Feb 19 '13 at 04:51

Tyler Ferraro

3,753
1
21
28

Thanks man, that actually worked out like a charm. Actually, my code was s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values ("+cb5.getSelectedItem()+","+cb3.getSelectedItem()+")"); When I insert the quotes like you specified, what happens is that the term "cb5.getSelectedItem()" is being entered into the table. The case with "+cb3.getSelectedItem()+" is that, it just enteres some garbage value. – Roshan George Feb 19 '13 at 05:00

score 0 · Answer 2 · answered Feb 19 '13 at 04:53

0

Try with single quotes around hello - e.g 'hello'

answered Feb 19 '13 at 04:53

Kumar Sambhav

7,503
15
63
86

score 0 · Answer 3 · answered Feb 19 '13 at 04:55

0

Try this

  s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values ('hello',"+cb3.getSelectedItem()+")");

answered Feb 19 '13 at 04:55

chhameed

4,406
4
26
44

Ricky Youssef · Answer 4 · 2013-02-19T05:09:12.230

0

This is because you need to add quotes to strings. Please, try doing this:

>     s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values
> ('hello','"+cb3.getSelectedItem()+"')");

Note I added the char ' at the beginning and at the end of the string you are trying to insert.

Why SQL thinks your string was a column? Imagine how the string you send to sql is:

"insert into OneTable (Field1, Field2) values (Hello, George)"
Instead of (the correct one):

"insert into OneTable (Field1, Field2) values ('Hello', 'George')"

edited Feb 19 '13 at 05:09

answered Feb 19 '13 at 04:57

Ricky Youssef

228
4
10

s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values ("+cb3.getSelectedItem()+","+cb3.getSelectedItem()+")"); How to use quotes for this ? – Roshan George Feb 19 '13 at 05:31
Hi Roshan, you should do something like this: `s.executeUpdate("insert into "+nameoftab+" (sname,"+""+ddvalue+""+") values ('"+cb3.getSelectedItem()+"','"+cb3.getSelectedItem()+"')");` Assuming both values are strings. I'd suggest to you String.Format in order to build this string in a clearer way. Kind regards! – Ricky Youssef Feb 21 '13 at 09:28

score 0 · Answer 5 · answered Feb 19 '13 at 04:57

you need to insert quotation marks around hello , this is how mysql came to know that the value is integer or string,
use either ' '

 s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values ('hello',"+cb3.getSelectedItem()+")");

or \" \" both do work

 s.executeUpdate("insert into "+nameoftab+" (sname,"+"_"+ddvalue+"_"+") values (\"hello\","+cb3.getSelectedItem()+")");

score 0 · Answer 6 · answered Feb 19 '13 at 04:57

0

You might want to consider using PreparedStatement instead, so as not to have to deal with such quoting issues -- and you get protection from SQL injection attacks as an added benefit.

answered Feb 19 '13 at 04:57

fenway

416
2
8

Even when trying with PreparedStatement, it was generating the same issue. So, I reverted back. – Roshan George Feb 19 '13 at 05:02
I simply meant that when binding placeholders, the need for `CONCAT()` is usually plainly obvious; otherwise it's merely `?`, unobscured by string concatenation to form the statement itself. – fenway Feb 19 '13 at 05:15

Why SQL INSERT query takes the entered values as column names

6 Answers6