Error trying to filter records using a stored procedure

Question

Error trying to filter records using a stored procedure

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm trying to filter records using a stored process and when I start entering the title of the record in the textbox I get an error.

Alamacenado procedure

CREATE PROCEDURE filtrar_articulo 
@filtro varchar(45)
AS
BEGIN

    SELECT titulo, palabras_claves, correo_elec, localizacion, tipo from Articulos
    WHERE Titulo like @filtro + '%'

END
GO

Inquiry Class

public void filter(DataGridView data, string buscarTitulo)
{
    try
    {
        string Conexion = @"string de conexion";
        SqlConnection dataConnection = new SqlConnection(Conexion);
        SqlCommand sql = new SqlCommand("filtrar_articulos", dataConnection);
        sql.CommandType  = CommandType.StoredProcedure;
        sql.Parameters.Add("@filtro",SqlDbType.VarChar, 45).Value= buscarTitulo;

        sql.ExecuteNonQuery();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(sql);
        da.Fill(dt);
        data.DataSource = dt;
    }
    catch(Exception ex)
    {
        MessageBox.Show("Los datos no se pudieron cargar: "+ex.ToString());
    }
}

** And this is the message that appears **

c# stored-procedure

asked by oscar 04.07.2017 в 17:23

source

2 answers

Segment violation in Nasm with Debian Call event click

score 0 · Answer 1

This is wrong as you are doing, it is not necessary to execute ExecuteNonQuery to return a set of data. ExecuteNonQuery is usually used to perform an action, such as an abm.

That is why it is required to open the connection to execute said method. You should remove ExecuteNonQuery.

public void filter(DataGridView data, string buscarTitulo)
{
    try
    {
        string Conexion = @"string de conexion";
        SqlConnection dataConnection = new SqlConnection(Conexion);
        SqlCommand sql = new SqlCommand("filtrar_articulos", dataConnection);
        sql.CommandType  = CommandType.StoredProcedure;
        sql.Parameters.Add("@filtro",SqlDbType.VarChar, 45).Value= buscarTitulo;

        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(sql);
        da.Fill(dt);
        data.DataSource = dt;
    }
    catch(Exception ex)
    {
        MessageBox.Show("Los datos no se pudieron cargar: "+ex.ToString());
    }
}

score 0 · Answer 2

The problem is that you execute an unnecessary ExecuteNonQuery , and it needs the connection to be open to run. Simply delete that line, the Fill method already opens and closes the connection and retrieves the data in DataTable . On the other hand, it is convenient for objects that implement IDisposable using the structure using in them so that the resources they use are correctly released:

public void filter(DataGridView data, string buscarTitulo)
{
    try
    {
        string Conexion = @"string de conexion";
        using (SqlConnection dataConnection = new SqlConnection(Conexion))
        {
            SqlCommand sql = new SqlCommand("filtrar_articulos", dataConnection);
            sql.CommandType  = CommandType.StoredProcedure;
            sql.Parameters.Add("@filtro",SqlDbType.VarChar, 45).Value= buscarTitulo;

            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql);
            da.Fill(dt);
            data.DataSource = dt;
        }
        catch(Exception ex)
        {
            MessageBox.Show("Los datos no se pudieron cargar: "+ex.ToString());
        }
    }
}