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!

Solution 1

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.

Solution 2

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.

Solution 3

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

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

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

3. Always clean up your resources, preferably using try-with-resources.

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