protected void TxtSearch_TextChanged(object sender, EventArgs e) { con.Open(); SqlCommand cmd = new SqlCommand("select firstName from del_user where firstname like'" + (TxtSearch.Text).Trim() + "%'", con); SqlDataReader rdr = cmd.ExecuteReader(); LstItems.DataSource = rdr; LstItems.DataTextField = "FirstName"; LstItems.DataBind(); }
void carga_productos(int d_familia, int id_categoria, int id_subcategoria) { string sql = ""; sql = "SELECT dbo.tbl_Items.Id_Linea_Venta, dbo.tbl_Familias_Productos.ID_Familia, dbo.tbl_Items.Id_Categoria, dbo.tbl_Items.Id_SubCategoria, Publicar_Web, "; sql = sql + "Ultimo_Factor, Ultimo_Precio , Fecha_precio, dbo.tbl_Items.Sigla, dbo.tbl_Items.Precio, dbo.tbl_Items.ID_Item, dbo.tbl_Items.Codigo, dbo.tbl_Items.Descripcion, "; sql = sql + "dbo.tbl_Categorias.Nombre AS Categoria, "; sql = sql + "dbo.tbl_Subcategorias.Nombre AS SubCategoria, dbo.tbl_Proveedores.Nombre_Fantasia AS Proveedor, dbo.tbl_Items.Unidad, dbo.tbl_Items.Activo, "; sql = sql + "dbo.tbl_Items.Cod_Barra "; sql = sql + "FROM dbo.tbl_Items with(nolock) LEFT OUTER JOIN "; sql = sql + "dbo.tbl_Categorias ON dbo.tbl_Items.Id_Categoria = dbo.tbl_Categorias.ID_Categoria LEFT OUTER JOIN "; sql = sql + "dbo.tbl_Subcategorias ON dbo.tbl_Items.Id_SubCategoria = dbo.tbl_Subcategorias.ID_SubCategoria LEFT OUTER JOIN "; sql = sql + "dbo.tbl_Proveedores ON dbo.tbl_Items.Id_proveedor = dbo.tbl_Proveedores.ID_Proveedor LEFT OUTER JOIN "; sql = sql + "dbo.tbl_Familias_Productos ON dbo.tbl_Familias_Productos.ID_Familia = dbo.tbl_categorias.Id_Familia "; sql = sql + "WHERE dbo.tbl_Familias_Productos.ID_Familia = " + id_familia; sql = sql + "AND dbo.tbl_Items.Id_Categoria = " + id_categoria; sql = sql + "AND dbo.tbl_Items.Id_SubCategoria = " + id_subcategoria; using (SqlConnection connection = new SqlConnection(Sserver)) { try { connection.Open(); SqlDataAdapter reader = new SqlDataAdapter(sql, connection); DataSet dr = new DataSet(); reader.Fill(dr, "tbl_Items"); LstItems.DataSource = dr; LstItems.DataBind(); connection.Close(); connection.Dispose(); } catch (Exception ex) { lbl_error.Text = ex.Message; connection.Close(); connection.Dispose(); } } }
protected void Buscar_Click(object sender, EventArgs e) { string sql = ""; sql = "SELECT tbl_Items.id_item ID, tbl_Items.codigo Codigo, dbo.PadreHijo(id_item) ph, isnull(tbl_Items.sigla, '') Letra, tbl_items.descripcion, tbl_items.Unidad, "; sql = sql + "isnull(tbl_Familias_Productos.ID_Familia,0) ID_Familia, isnull(tbl_Categorias.ID_Categoria,0) ID_Categoria, isnull(tbl_Subcategorias.ID_SubCategoria,0) ID_SubCategoria, "; sql = sql + " isnull((select 1 from tbl_items_web where Id_Item = dbo.tbl_Items.ID_Item),0) item_web "; /* sql = "SELECT tbl_Items.id_item, tbl_Items.codigo,dbo.PadreHijo(id_item) ph, dbo.tbl_Items.Id_Linea_Venta, dbo.tbl_Items.Id_Categoria, dbo.tbl_Items.Id_SubCategoria, Publicar_Web, Ultimo_Factor, Ultimo_Precio , Fecha_precio, dbo.tbl_Items.Sigla, dbo.tbl_Items.Precio, dbo.tbl_Items.ID_Item, dbo.tbl_Items.Codigo, dbo.tbl_Items.Descripcion, dbo.tbl_Categorias.Nombre AS Categoria, "; * sql = sql + " dbo.tbl_Subcategorias.Nombre AS SubCategoria, dbo.tbl_Proveedores.Nombre_Fantasia AS Proveedor, dbo.tbl_Items.Unidad, dbo.tbl_Items.Activo, "; * sql = sql + " dbo.tbl_Items.Cod_Barra";*/ sql = sql + " FROM dbo.tbl_Items LEFT OUTER JOIN"; sql = sql + " dbo.tbl_Categorias ON dbo.tbl_Items.Id_Categoria = dbo.tbl_Categorias.ID_Categoria LEFT OUTER JOIN"; sql = sql + " dbo.tbl_Familias_Productos ON tbl_Familias_Productos.ID_Familia = dbo.tbl_Categorias.Id_Familia LEFT OUTER JOIN "; sql = sql + " dbo.tbl_Subcategorias ON dbo.tbl_Items.Id_SubCategoria = dbo.tbl_Subcategorias.ID_SubCategoria LEFT OUTER JOIN"; sql = sql + " dbo.tbl_Proveedores ON dbo.tbl_Items.Id_proveedor = dbo.tbl_Proveedores.ID_Proveedor"; sql = sql + " where tbl_Items.Activo = 1 "; if (Txt_Codigo.Text != "") { sql = sql + " and tbl_items_web.codigo like '" + Txt_Codigo.Text + "%'"; } if (Txt_CodigoProv.Text != "") { sql = sql + "and tbl_items_web.Codigo_prov like '" + Txt_CodigoProv.Text + "%'"; } if (Lst_Cat.SelectedItem.Value.ToString() != "0") { sql = sql + "and tbl_items.Id_Categoria = " + Lst_Cat.SelectedItem.Value.ToString(); } if (Lst_SubCat.SelectedItem.Value.ToString() != "0") { sql = sql + "and tbl_items.Id_Subcategoria = " + Lst_SubCat.SelectedItem.Value.ToString(); } if (Lst_Letra.SelectedItem.Value.ToString() != "0") { sql = sql + "and tbl_items.Sigla = '" + Lst_Letra.SelectedItem.Value.ToString() + "'"; } if (Lst_LV.SelectedItem.Value.ToString() != "0") { sql = sql + "and tbl_items.Id_Linea_Venta = '" + Lst_LV.SelectedItem.Value.ToString() + "'"; } if (Lst_division.SelectedItem.Value.ToString() != "0") { sql = sql + "and tbl_Familias_Productos.Id_Familia = '" + Lst_division.SelectedItem.Value.ToString() + "'"; } if (Lst_Prov.SelectedItem.Value.ToString() != "0") { sql = sql + "and tbl_items.Id_proveedor = " + Lst_Prov.SelectedItem.Value.ToString(); } if (Chk_sin_cat.Checked) { sql = sql + "and isnull(tbl_items.Id_Categoria,0) = 0 "; } if (Chk_publicados.Checked) { sql = sql + " and tbl_Items.Publicar_Web = 1"; } if (Chk_sin_cat.Checked) { sql = sql + " (tbl_Items.ID_Categoria = 0 or tbl_Items.ID_Categoria IS Null)"; } using (SqlConnection connection = new SqlConnection(Sserver)) { try { connection.Open(); SqlCommand command = new SqlCommand(sql, connection); SqlDataAdapter reader = new SqlDataAdapter(sql, connection); DataSet dr = new DataSet(); reader.Fill(dr, "tbl_clientes"); LstItems.DataSource = dr; LstItems.DataBind(); //SqlCommand command = new SqlCommand(sql, connection); //SqlDataReader reader = command.ExecuteReader(); //DataSet ds = new DataSet(); //DataTable table = new DataTable("items"); //table.Columns.Add(new DataColumn("Id", typeof(int))); //table.Columns.Add(new DataColumn("Código", typeof(string))); //table.Columns.Add(new DataColumn("PH", typeof(string))); //table.Columns.Add(new DataColumn("Sigla", typeof(string))); //table.Columns.Add(new DataColumn("Descripción", typeof(string))); //table.Columns.Add(new DataColumn("Unidad", typeof(string))); //table.Columns.Add(new DataColumn("ID_Familia", typeof(int))); //table.Columns.Add(new DataColumn("ID_Categoria", typeof(int))); //table.Columns.Add(new DataColumn("ID_SubCategoria", typeof(int))); //table.Columns.Add(new DataColumn("item_web", typeof(int))); //int v_id = 0; //string v_codigo = ""; //string v_ph = ""; //string v_sigla = ""; //string v_descripcion = ""; //string v_unidad = ""; //int v_id_fam = 0; //int v_id_cat = 0; //int v_id_subcat = 0; //int v_web = 0; //while (reader.Read()) //{ // v_id = reader.GetInt32(0); // v_codigo = reader.GetString(1); // v_ph = reader.GetString(2); // v_sigla = reader.GetString(3); // v_descripcion = reader.GetString(4); // v_unidad = reader.GetString(5); // v_id_fam = reader.GetInt32(6); // v_id_cat = reader.GetInt32(7); // v_id_subcat = reader.GetInt32(8); // v_web = reader.GetInt32(9); // table.Rows.Add(v_id, // v_codigo, // v_ph, // v_sigla, // v_descripcion, // v_unidad, // v_id_fam, // v_id_cat, // v_id_subcat, // v_web); //} // LstItems.DataSource = table; //LstItems.DataBind(); connection.Close(); connection.Dispose(); } catch (Exception ex) { lbl_error.Text = ex.Message; connection.Close(); connection.Dispose(); } } }