Research Doubt

Navigation
2

IhaveGrid,butwhenItypesomethinginthetextboxfield,thesearchisnotdoneandnothingisdisplayedinGrid.

Followthecodemadein4layers:

  

Form:

privatevoidtxtPesquisa_TextChanged(objectsender,EventArgse){UsuarioDTOusuDto=newUsuarioDTO();usuDto.Nome=txtPesquisa.Text;dgvListaUsuario.DataSource=UsuarioModel.PesquisaUsuario(usuDto);}
  

Class:Model

publicstaticIList<UsuarioDTO>PesquisaUsuario(UsuarioDTOusuDto){try{returnnewUsuarioDAO().PesquisaUsuario(usuDto);}catch(Exceptionex){throwex;}}
  

Class:DAO

publicIList<UsuarioDTO>PesquisaUsuario(UsuarioDTOusuDto)//esperandoumretornodoUsuarioDTOqueéolistaUsuarioDTO{//fazerotrycatchparaotratamentodeerrotry{SqlConnectionCON=newSqlConnection();//conexãocomoBancodeDadosSQLCON.ConnectionString=Properties.Settings.Default.CSEmpilhadeiras;//criarumaStringdeconexãoSqlCommandCM=newSqlCommand();//CM.CommandType=System.Data.CommandType.Text;CM.CommandText="SELECT tbUsuario WHERE nome_usu LIKE %@nomeUsu%";
            CM.Parameters.Add("nomeUsu", System.Data.SqlDbType.VarChar).Value = usuDto.Nome;

            CM.Connection = CON;

            SqlDataReader ER;// colocar um dataread trazer o conteudo

            // listaUsuarioDTO este é o objeto que vou carregar e retornar
            IList<UsuarioDTO> listaUsuarioDTO = new List<UsuarioDTO>();

            CON.Open();// abrir a conexão
            ER = CM.ExecuteReader();// executa o comando
            if (ER.HasRows) // se tem alguma linha. teve algum retorno ?
            {
                while (ER.Read())// ler linha a linha 
                {
                    // criar um usuarioDTO
                    UsuarioDTO usuario = new UsuarioDTO();
                    usuario.Codigo = Convert.ToInt32(ER["cod_usu"]);
                    usuario.Nome = Convert.ToString(ER["nome_usu"]);
                    usuario.Login = Convert.ToString(ER["login_usu"]);
                    usuario.Senha = Convert.ToString(ER["senha_usu"]);
                    usuario.Email = Convert.ToString(ER["email_usu"]);
                    usuario.Status = Convert.ToChar(ER["status_usu"]);
                    usuario.DtCadastro = Convert.ToDateTime(ER["dtCadastro_usu"]);
                    usuario.Nivel = Convert.ToChar(ER["nivel_usu"]);

                  //  listaUsuarioDTO.Add(usuario);
                }
            }

            return listaUsuarioDTO;

        }
        catch (Exception ex)
        {
            // se cair o Banco, se estiver fora 
            throw ex; // devolve o erro para quem Chamou que é o formulario
        }

    }
  

Class: UserDTO 

public class UsuarioDTO
{
    private int codigo;
    private String nome;
    private String login;
    private String senha;
    private String email;
    private Char status;
    private DateTime dtCadastro;
    private Char nivel;

    #region   Getters e Setters

    public int Codigo
    {
        get { return codigo; }
        set { codigo = value; }
    }
    public String Nome
    {
        get { return nome; }
        set { nome = value; }
    }


    public String Login
    {
        get { return login; }
        set { login = value; }
    }

    public String Senha
    {
        get { return senha; }
        set { senha = value; }
    }


    public String Email
    {
        get { return email; }
        set { email = value; }
    }

    public Char Status
    {
        get { return status; }
        set { status = value; }
    }


    public DateTime DtCadastro
    {
        get { return dtCadastro; }
        set { dtCadastro = value; }
    }

    public Char Nivel
    {
        get { return nivel; }
        set { nivel = value; }
    }
}

I can not identify the error!

error

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in HTSisEmpeners.Model.dll

Additional information: Invalid column name 'username'.

Invalid column name 'tbUsuario'.

    
asked by anonymous 20.02.2015 / 23:12

3 answers

1

Fixed.

The problem is that you did not execute the query in the DB, one possibility would be (note that it's just an example, I do not know the field name in the table): 

public IList<UsuarioDTO> PesquisaUsuario(UsuarioDTO usuDto)
{
    using(SqlConnection CON = new SqlConnection())
    {
        CON.ConnectionString = Properties.Settings.Default.CSEmpilhadeiras;
        SqlCommand CM = new SqlCommand();
        CM.CommandType = System.Data.CommandType.Text;
        CON.Open();

        CM.CommandText = "SELECT * FROM tbUsuario WHERE nome_usu LIKE @nomeUsu";
        CM.Parameters.Add("nomeUsu", System.Data.SqlDbType.Varchar).Value = "%" + usuDto.Nome + "%";
        CM.Connection = CON;

        var dr = CM.ExecuteReader();

        var usuarios = new List<UsuarioDTO>();

        while(dr.Read())
        {
                UsuarioDTO usuario = new UsuarioDTO();
                usuario.Codigo = Convert.ToInt32(dr["cod_usu"]);
                usuario.Nome = Convert.ToString(dr["nome_usu"]);
                usuario.Login = Convert.ToString(dr["login_usu"]);
                usuario.Senha = Convert.ToString(dr["senha_usu"]);
                usuario.Email = Convert.ToString(dr["email_usu"]);
                usuario.Status = Convert.ToChar(dr["status_usu"]);
                usuario.DtCadastro = Convert.ToDateTime(dr["dtCadastro_usu"]);
                usuario.Nivel = Convert.ToChar(dr["nivel_usu"]);

                usuarios.Add(usuario);
        }

        return usuarios;
    }
}

It's just a snippet, you may want to handle multiple results, errors, etc.

Receiving the datasource: 

private void txtPesquisa_TextChanged(object sender, EventArgs e)
{
    UsuarioDTO usuDto = new UsuarioDTO();
    usuDto.Nome = txtPesquisa.Text;
    var source = new BindingSource();
    source.DataSource = UsuarioModel.PesquisaUsuario(usuDto);
    dgvListaUsuario.DataSource = source;
}

Change method signature: 

public static IList<UsuarioDTO> PesquisaUsuario(UsuarioDTO usuDto)
    
21.02.2015 / 00:05
0

@DenilsonCarlos, the problem is in your query, but specifically in the LIKE condition.

You must concatenate the % with the variable, as in the example below. 

DECLARE @tabela as table(
    guid uniqueidentifier primary key, 
    nome varchar(50)
);

INSERT INTO @tabela VALUES ('7290713f-c2d3-4bc3-89b2-e58685e594bb', 'Sarah Garcia');
INSERT INTO @tabela VALUES ('b3d0d2cf-089b-4684-8892-fc947375614a', 'Mark Thatcher');
INSERT INTO @tabela VALUES ('1145f4d6-8c47-4124-a48c-10a0610bbf39', 'Arthur Lake');
INSERT INTO @tabela VALUES ('d87cd5d1-2a9d-49de-8e37-0ff857e0e068', 'Kenneth Major');
INSERT INTO @tabela VALUES ('a8cdc2f8-2b90-459c-9eff-63ba410d1830', 'Sarah Robinson');
INSERT INTO @tabela VALUES ('2362e37c-6e4f-42e7-b265-9939c1d011e3', 'Adam Davies');
INSERT INTO @tabela VALUES ('cb350a4c-7701-4934-b37b-d6fb518db7d5', 'Kimberly Taylor');
INSERT INTO @tabela VALUES ('eb17d0ce-40fb-4d03-9d8f-c6580200fa05', 'Margaret Stevens');
INSERT INTO @tabela VALUES ('e3e1c825-1366-4759-804e-bd1b355936e1', 'Ursula Washington');

DECLARE @nome as varchar(50);
SET @nome = 'ar'

SELECT * FROM @tabela WHERE nome LIKE '%' + @nome + '%'
    
21.02.2015 / 02:07
0

I believe there are two situations that are causing this behavior:

  

CM.Parameters.Add ("username", System.Data.SqlDbType.VarChar) .Value = UserName.

The correct one would be: 

CM.Parameters.AddWithValue("@nomeUsu", "%" + usuDto.Nome + "%");

and the other thing is that the rescued users are not being added to the return list in the DAO class:

  

// listUserDTO.Add (user);

You should uncomment this line.

    
21.02.2015 / 16:24
Retrieve the ID of an insert in a table and use it to create a relationship with another table resultCode returns RESULT_CANCELED on onActivityResult