Search data in sql and display it on grid

Question

Search data in sql and display it on grid

Navigation

#1 by (5 votes)

1

I am trying to search for products from the database and display them in grid . The code executes the search but does not throw any value.

In the database I have the following:

create proc [dbo].[spbuscararticulo_venta_codigo]
@textobuscar varchar(50)
as
select d.iddetalle_ingreso,a.Codigo,a.Nombre,
c.nombre as Categoria,p.nombre as Presentacion,
d.stock_actual,d.precio_compra,d.precio_venta,
d.fecha_vencimiento
from articulo a inner join categoria c
on a.idcategoria=c.idcategoria
inner join presentacion p
on a.idpresentacion=p.idpresentacion
inner join detalle_ingreso d
on a.idarticulo=d.idarticulo
inner join ingreso i
on d.idingreso=i.idingreso
where a.codigo=@textobuscar
and d.stock_actual>0
and i.estado<>'ANULADO'

on my button:

//Método BuscarNombre
    private void MostrarArticulo_Venta_Nombre()
    {
        this.dataListado.DataSource = NVenta.MostrarArticulo_Venta_Nombre(this.txtBuscar.Text);
        this.OcultarColumnas();
        lblTotal.Text = "Total de Registros: " + Convert.ToString(dataListado.Rows.Count);
    }

to double click:

private void dataListado_DoubleClick(object sender, EventArgs e)
{
    FrmVenta form = FrmVenta.GetInstancia();
    string par1, par2;
    decimal par3, par4;
    int par5;
    DateTime par6;
    par1 = Convert.ToString(this.dataListado.CurrentRow.Cells["iddetalle_ingreso"].Value);
    par2 = Convert.ToString(this.dataListado.CurrentRow.Cells["nombre"].Value);
    par3 = Convert.ToDecimal(this.dataListado.CurrentRow.Cells["precio_compra"].Value);
    par4 = Convert.ToDecimal(this.dataListado.CurrentRow.Cells["precio_venta"].Value);
    par5 = Convert.ToInt32(this.dataListado.CurrentRow.Cells["stock_actual"].Value);
    par6 = Convert.ToDateTime(this.dataListado.CurrentRow.Cells["fecha_vencimiento"].Value);
    form.setArticulo(par1, par2, par3, par4, par5, par6);
    this.Hide();
}

Nventa class:

public static DataTable MostrarArticulo_Venta_Nombre(string textobuscar)
        {
            DVenta Obj = new DVenta();
            return Obj.MostrarArticulo_Venta_Nombre(textobuscar);
        }

another class, this is called by Nventas in another layer

//Mostrar Artículos por su nombre
public DataTable MostrarArticulo_Venta_Nombre(String TextoBuscar)
{
    DataTable DtResultado = new DataTable("articulos");
    SqlConnection SqlCon = new SqlConnection();
    try
    {
        SqlCon.ConnectionString = Conexion.Cn;
        SqlCommand SqlCmd = new SqlCommand();
        SqlCmd.Connection = SqlCon;
        SqlCmd.CommandText = "spbuscararticulo_venta_nombre";
        SqlCmd.CommandType = CommandType.StoredProcedure;

        SqlParameter ParTextoBuscar = new SqlParameter();
        ParTextoBuscar.ParameterName = "@textobuscar";
        ParTextoBuscar.SqlDbType = SqlDbType.VarChar;
        ParTextoBuscar.Size = 50;
        ParTextoBuscar.Value = TextoBuscar;
        SqlCmd.Parameters.Add(ParTextoBuscar);

        SqlDataAdapter SqlDat = new SqlDataAdapter(SqlCmd);
        SqlDat.Fill(DtResultado);

    }

The grid is sorted by query, but it does not show me anything (and I do have values in the database).

The strange thing is that I have similar functions and cases that change in name and work but I do not see the error here.

c# .net sql-server

asked by Gilberto Asuaje 07.11.2016 в 06:03

source

1 answer

Close Form Open a new Form c # Compare arrays in python

score 5 · Accepted Answer

Failure to return results may be caused by several points that you should validate.

The first thing I notice is that in the code you define spbuscararticulo_venta_nombre but the procedure is called spbuscararticulo_venta_codigo , it is clear that the name is different, as the first step would be to validate that you invoke the correct procedure.

Because both code to execute the query

public DataTable MostrarArticulo_Venta_Nombre(String TextoBuscar)
{
    using(SqlConnection SqlCon = new SqlConnection(Conexion.Cn))
    {
        SqlCommand SqlCmd = new SqlCommand("spbuscararticulo_venta_codigo",SqlCon);
        SqlCmd.CommandType = CommandType.StoredProcedure;
        SqlCmd.Parameters.Add("@textobuscar", SqlDbType.VarChar, 50).Value = TextoBuscar;

        SqlDataAdapter SqlDat = new SqlDataAdapter(SqlCmd);
        DataTable DtResultado = new DataTable();
        SqlDat.Fill(DtResultado);

        return DtResultado;
    }
}

As you can see it can be reduced, do not define a try..catch in that code, catch the problem in higher layers when it reaches the UI, also use the using to define the connection block, if you will make the adobe pooling maintain the connection instance.

The query that defiens in the procedure has many INNER JOIN, any relation that does not have data could cause that it does not return records, you must validate the data that you use executing the SELECT in the management studio to validate that all the join are correct.

If it is a search by proximacion and the text entered is not complete you should use the LIKE

where a.codigo LIKE '%' + @textobuscar + '%'

In this way you will find records even if you do not enter the matching code in all its contents.