SQLServer Exception: Invalid column name

Question

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'IDPaciente'

I am getting this exception. This is my code:

    String query = "INSERT INTO Paciente('IDPaciente', 'NomePaciente', 'IdadePaciente', 'LocalidadePaciente') VALUES('"+IDTextField.getText()+"', '"+NomeTextField.getText()+"', '"+IdadeTextField.getText()+"', '"+LocalidadeTextField.getText()+"')";
    try
    {
        st = con.DatabaseConnection().createStatement();
        rs = st.executeQuery(query);
    }

I suspect the problem might be in the query itself.

I have searched a lot and couldn't find the solution to my problem. I have tried refreshing the cache, changing permissions within the schema, restarting sql server (I am using sql server management studio 2012), I am correctly connected to my database, and nothing seems to work.

What could I be doing wrong? Thank you!

did u tried to execute the code against SSMS directly , is raising the same error ? — ahmed abdelqader, May 07 '17 at 07:21
@ahmedabdelqader Yes, I have tried to execute it directly and it gives the same error — Gazelle, May 07 '17 at 07:27
`'` is for quoting *text literals*, not column names. Remove all the apostrophes, and use a `PreparedStatement` and `?` parameter markers, not String concatenation. — Andreas, May 07 '17 at 07:27
@Gazelle, So Remove the quotes as we said, for more details, go to below answers. — ahmed abdelqader, May 07 '17 at 07:32

Ilyes · Accepted Answer · 2017-05-07T07:34:23.650

4

Remove quotes , try :

String query = "INSERT INTO Paciente(IDPaciente, NomePaciente, IdadePaciente, LocalidadePaciente) VALUES('"+IDTextField.getText()+"', '"+NomeTextField.getText()+"', '"+IdadeTextField.getText()+"', '"+LocalidadeTextField.getText()+"')";
try
{
    st = con.DatabaseConnection().createStatement();
    rs = st.executeQuery(query);
}

Remove also quotes for INT values.

edited May 07 '17 at 07:34

answered May 07 '17 at 07:27

Ilyes

14,640
4
29
55

made some progress but it's still not working. Taking out the quotes give some kind of different errors. If I insert a value like "John" it says I have an incorrect syntax near "John" or "John" is an invalid column name. Oops let me check it first, didnt see your edit. – Gazelle May 07 '17 at 07:40
Keep the quotes for the `Strig` values – Ilyes May 07 '17 at 07:41
2

@Gazelle That is because string literals should be enclosed in single-quotes (not double quotes). But the real solution is provided in the answer by YCF_L: do not use string concatenation, use prepared statements with parameters. – Mark Rotteveel May 07 '17 at 07:42
@Sami one more thing, why does the statement not returning a result set in this context? Thanks in advance – Gazelle May 07 '17 at 08:06

score 4 · Answer 2 · edited Sep 27 '17 at 20:28

Your code is not secure, you can easily get Syntax error or SQL Injection I suggest to use PreparedStatement instead.

You have a problem in your Query, the columns should not be between '' so you can use this instead :

String query = "INSERT INTO Paciente(IDPaciente, NomePaciente, IdadePaciente, "
        + "LocalidadePaciente) VALUES(?, ?, ?, ?)";

try (PreparedStatement insert = con.prepareStatement(query)) {
    insert.setString(1, IDTextField.getText());
    insert.setString(2, NomeTextField.getText());
    insert.setString(3, IdadeTextField.getText());
    insert.setString(4, LocalidadeTextField.getText());

    insert.executeUpdate();
}

If one of your column is an int you have to use setInt, if date setDate, and so on.

Andreas · Answer 3 · 2017-05-07T07:41:49.357

You have four problems, though only the first is giving you the current error:

Single-quotes (') are for quoting text literals, not column names. In MS SQL Server, you can quote column names using double-quotes (") or square brackets ([]), but you don't need to quote them at all.
To prevent SQL Injection attacks, where hackers will steal your data and delete your tables, and to prevent potential syntax errors, never build a SQL statement with user-entered strings, using string concatenation. Always use a PreparedStatement.
Always clean up your resources, preferably using try-with-resources.
Don't use executeQuery() for an INSERT statement. Use executeUpdate(). As the javadoc says:

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

So, your code should be:

String query = "INSERT INTO Paciente" +
              " (IDPaciente, NomePaciente, IdadePaciente, LocalidadePaciente)" +
              " VALUES (?, ?, ?, ?)";
try (PreparedStatement st = con.DatabaseConnection().prepareStatement(query)) {
    st.setString(1, IDTextField.getText());
    st.setString(2, NomeTextField.getText());
    st.setString(3, IdadeTextField.getText());
    st.setString(4, LocalidadeTextField.getText());
    st.executeUpdate();
}

score 0 · Answer 4 · answered May 07 '17 at 07:29

Remove the quotes from your column names.

"INSERT INTO Paciente(IDPaciente, NomePaciente, IdadePaciente, LocalidadePaciente) VALUES('"+IDTextField.getText()+"', '"+NomeTextField.getText()+"', '"+IdadeTextField.getText()+"', '"+LocalidadeTextField.getText()+"')"

score 0 · Answer 5 · answered May 07 '17 at 07:31

The Column names are does not typed within quotes, Remove them and try again.

Demo:-

Create table MyTable (id int , name varchar (50))
go
insert into MyTable (id,name) values (1 , 'ahmed')

Result:-

(1 row(s) affected)

Try insert them again with quotes.

insert into MyTable ('id','name') values (1 , 'ahmed')

Result:-

Msg 207, Level 16, State 1, Line 3
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'name'.

SQLServer Exception: Invalid column name

5 Answers5