Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

hope you can help me. I get the error

"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Smith' in 'where clause'".

I have a database called "aspirante" and it has a table (I already filled it) named "datos". Here I show you how I created the table:

CREATE TABLE `datos` (
  `id_asp` int(11) NOT NULL AUTO_INCREMENT,
  `ficha` mediumint(4) NOT NULL,
  `apellido1` varchar(30) NOT NULL,
  `apellido2` varchar(30) NOT NULL,
  `nombre` varchar(50) NOT NULL,
  `genero` char(1) NOT NULL,
  `telefono1` varchar(20) NOT NULL,
  `telefono2` varchar(20) NOT NULL,
  `carrera` varchar(50) NOT NULL,
  `promedio_sec` varchar(5) NOT NULL,
  PRIMARY KEY (`id_asp`)
)

The method looks like this.

void BuscarAspiranteEditar(String id) {
    String sSQL = "";
    String ap1 = "", ap2 = "", nom = "", gen = "", tel1 = "", tel2 = "", car = "", prom = "";

    ConexionMySQL mysql = new ConexionMySQL();
    Connection cn = mysql.Conectar();

    sSQL = "SELECT id_asp, ficha, apellido1, apellido2, nombre, genero, telefono1, telefono2, promedio_sec FROM datos " +
            "WHERE id_asp = "+id;

    try {
        Statement st = cn.createStatement();
        ResultSet rs = st.executeQuery(sSQL);

        while (rs.next()) {
            ap1 = rs.getString("apellido1");
            ap2 = rs.getString("apellido2");
            nom = rs.getString("nombre");
            gen = rs.getString("genero");
            tel1 = rs.getString("telefono1");
            tel2 = rs.getString("telefono2");
            car = rs.getString("carrera");
            prom = rs.getString("promedio_sec");
            id_actualizar = id;
        }

        txtPrimerApellido.setText(ap1);
        txtSegundoApellido.setText(ap2);
        txtNombre.setText(nom);
        txtPrimerTelefono.setText(tel1);
        txtSegundoTelefono.setText(tel2);
        txtCarrera.setText(car);
        txtPromedio.setText(prom);

    }
    catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, ex);
    }
}

I am not into Java so I hope you can hel me guys, I'd appreciate it. Thanks in advance!

share|improve this question
 
what is the value of ID? –  今 草 顿 웃 May 2 at 1:25
 
It seems that you're passing a String value to the ID field which is supposed to be an integer value. –  AsirC May 2 at 1:26
 
Look up SQL injection. It's very, very bad practice to directly use user's input in an SQL query. –  pickypg May 2 at 2:48
add comment

2 Answers

up vote 3 down vote

where is your method that passes the parameter id ? if your id is a string you must enclosed it with quotes 'id' in like Where id_asp='"+ id +"'"

share|improve this answer
 
+1 I think you're right: I think the user entered "smith" instead of a number. It's the only explanation I can think of. –  Bohemian May 2 at 1:37
add comment
up vote 1 down vote

So it looks like your problem is the id you are passing for the where clause is incorrect. You are passing in a last name "Smith" instead of the asp id.

As a note about programming with SQL, to be safe you really should be writing your queries with the mysql "?". This allows mysql to put in the appropriate quotes if needed, and do checks for sql injection.

sSQL = "SELECT id_asp, ficha, apellido1, apellido2, nombre, genero, telefono1, telefono2, promedio_sec FROM datos WHERE id_asp = ?";
PreparedStatement ps = connection.prepareStatement(sSQL);
ps.setObject(1, my_id);
ResultSet rs = ps.executeQuery();

It's a bit safer and you can pass in variables directly without knowing what needs to be wrapped in quotes etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.