Error using LIKE to search by name

Question

Error using LIKE to search by name

Navigation

#1 by (4 votes)
#2 by (1 votes)
#3 by (1 votes)

3

When I used Mysql I could use LIKE ? to do searches by name, code, etc ... Now in SQL Server I can not.

I have a TextField and a search button, I want to get the value of the textField and move on to the search. I'm trying to do it this way:

StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n"
                + " Contrato.AutoId                         AS                Contrato_ID,\n"
                + " Contrato.Codigo                         AS                Codigo_contrato,\n"
                + " Pessoa.Nome                             AS                Contratante,\n"
                + " SuspensaoVinculo.VinculoRescindido      AS                Vinculo_rescindido,\n"
                + " SuspensaoVinculo.DataSuspensao          AS                Data_suspencao,\n"
                + " SuspensaoVinculo.DataReativacao         AS                Data_reativacao ");
        sql.append("FROM Contrato                           WITH (NOLOCK)\n"
                + " INNER JOIN Pessoa                       WITH(NOLOCK) ON Contrato.Contratante = Pessoa.AutoId\n"
                + " LEFT JOIN SuspensaoVinculo              WITH(NOLOCK) ON SuspensaoVinculo.Contrato = Contrato.AutoId ");
        sql.append("WHERE\n"
                + " Pessoa.Tipo             =   2 "
                + "     Pessoa.Nome LIKE ? ");
        sql.append("ORDER BY Pessoa.Nome ");

        /* Abre a conexão */
        Connection conn = Conexao.abrir();

        /* Mapeamento objeto relacional */
        PreparedStatement comando = conn.prepareStatement(sql.toString());
        comando.setString(1, "%" + c.getContratante() + "%"); 
        ....

And in my view I recover it like this:

private void atualizarInicial() {
        Cadastros cadastros = new Cadastros();
        ConsultaCadastro consulta = new ConsultaCadastro();
        List<Cadastros> lista;
        try {
            cadastros.setContratante(pesquisar.getText());
            lista = consulta.buscar(cadastros);
            tblCadastros.setItems(FXCollections.observableArrayList(lista));
            ...

This is the exception:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'Contrato'.

sql java javafx

asked by anonymous 17.06.2015 / 19:09

3 answers

1

Check here for the lack of an "AND" (or other operator) that may be the source of your error:

sql.append("WHERE\n" 
           + " Pessoa.Tipo             =   2"
           + "  AND   Pessoa.Nome LIKE ? ");

17.06.2015 / 19:17

1

In addition to the AND after the Person.type try adding single quotes in the LIKE, SQL uses like this:

comando.setString(1, "'%" + c.getContratante() + "%'");

17.06.2015 / 19:19

What is the best validation strategy before the data persist? [closed] Enter minute by minute in function

score 4 · Accepted Answer

4

An AND is missing

"WHERE\n"
+ " Pessoa.Tipo             =   2 **AND**"
+ "     Pessoa.Nome LIKE ? ");

17.06.2015 / 19:15