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
– ahmed abdelqader, Commented May 7, 2017 at 7:21
@ahmedabdelqader Yes, I have tried to execute it directly and it gives the same error — Gazelle
– Gazelle, Commented May 7, 2017 at 7:27
' is for quoting text literals, not column names. Remove all the apostrophes, and use a PreparedStatement and ? parameter markers, not String concatenation. — Andreas
– Andreas, Commented May 7, 2017 at 7:27

Ilyes · Accepted Answer · 2017-05-07 07:34:23Z

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 7, 2017 at 7:34

answered May 7, 2017 at 7:27

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gazelle Over a year ago

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.

Ilyes Over a year ago

Keep the quotes for the Strig values

Mark Rotteveel Over a year ago

@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.

Gazelle Over a year ago

@Sami one more thing, why does the statement not returning a result set in this context? Thanks in advance

Graham · Accepted Answer · 2017-09-27 20:28:49Z

4

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.

edited Sep 27, 2017 at 20:28

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered May 7, 2017 at 7:32

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Comments

Andreas · Accepted Answer · 2017-05-07 07:41:49Z

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();
}

Ryan Tuosto · Accepted Answer · 2017-05-07 07:29:32Z

0

Remove the quotes from your column names.

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

answered May 7, 2017 at 7:29

Ryan Tuosto

1,95115 silver badges23 bronze badges

Comments

ahmed abdelqader · Accepted Answer · 2017-05-07 07:31:29Z

0

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'.

answered May 7, 2017 at 7:31

ahmed abdelqader

3,58220 silver badges38 bronze badges

Collectives™ on Stack Overflow

SQLServer Exception: Invalid column name

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related