CODEWITHSUNDEEP
javajdbc
Efraim Efi Gavrieli
Efraim Efi Gavrieli

Reputation: 51

SQLServerException: Invalid column name 'A' when I try to execute SQL insert query

I'm try to do insert query by jframe and Jtext but I get this error:

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

I insert the values name = A,city = B,street = c,phone = 0542223.

private static final String SQL2 = "insert into Hospital (name,city,street,phone) values (";
private static final String CONN_URL = "jdbc:sqlserver://localhost:1433;databaseName=MedicalDB;integratedSecurity=true;";
          JLabel name = new JLabel("name");
      JTextField name1 = new JTextField();
      name1.setPreferredSize(new Dimension(100, 30));

      JLabel city = new JLabel("city");
      JTextField city1 = new JTextField();
      city1.setPreferredSize(new Dimension(100, 30));

      JLabel street3 = new JLabel("the street");
      JTextField street4 = new JTextField();
      street4.setPreferredSize(new Dimension(100, 30));

      JLabel Phone = new JLabel("Phone num");
      JTextField Phone1 = new JTextField();
      Phone1.setPreferredSize(new Dimension(100, 30));

      String name = name1.getText();
      String city = city1.getText();
      String street = street4.getText();
      String phone = Phone1.getText();

      Statement stmt1 = con.createStatement();
      System.out.println(phone);

      String theString = SQL2 + name + "," +  city + "," + street +"," + phone + ");";
      stmt1.executeUpdate(theString);
    }
    catch (Exception e1) {
        e1.printStackTrace();
        System.out.println("here5");
    }
}

Upvotes: 1

Views: 1433

Answers (2)

Efraim Efi Gavrieli
Efraim Efi Gavrieli

Reputation: 51

After checking some more I figured it out: I've just changed my code from:

String theString = SQL2 + name + "," +  city + "," + street +"," + phone + ");";

to:

String theString = SQL2 + "'"+name+"','"+city+"','"+street+"','"+phone+"'" + ")";

Upvotes: 1

Mureinik
Mureinik

Reputation: 311508

You aren't quoting the string variables you're trying to insert, so the database interprets them as column names, and then fails since these columns don't exist.

The textbook way to avoid both this problem and the vulnerability to SQL Injection attacks is to use a PreparedStatement:

private static final String INSERT_SQL =
    "insert into Hospital (name, city, street, phone) values (?, ?, ?, ?)";

try (ps = con.prepareStatement(INSERT_SQL)) {
    ps.setString(1, name);
    ps.setString(2, city);
    ps.setString(3, street);
    ps.setString(4, phone);
    ps.executeUpdate();
}

Upvotes: 2

Related Questions