SQL - Restrict query data

Question

Good morning,

I have an app in WebForms than from two DropDownList obtain data from a database in MS SQL Server 2012.

At first DropDownList it will present the name of several companies in the database with the following SELECT statement:´

SELECT [nome] FROM [empresas]

In the second DropDownList it will present a list of employees who are associated with that selected enterprise above, since in the employee table the code of the enterprise is associated with the employee. But however I did not realize how I can solve this, currently it presents all employees in the database without restricting anything, being with the SELECT statement basic of:

SELECT [nome] FROM [colaboradores]

How can I solve this problem?

Answer 1

You can load the Dropdownlist from your Companies table on Page_Load, and fill in the property IsPostBack true.

To load the Contributors Dropdownlist, Voce can use the event SelectedIndexChanged of the Dropdownlist Company and make the query stating which company was selected.

See the example below:

    <%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="EmpresaColaboradores.aspx.cs" Inherits="stackoverflow.EmpresaColaboradores" %>

    <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
        <table>
            <tr>
                <td class="Label">
                    <asp:Label ID="lblIdEmpresa" runat="server" Text="Empresas"></asp:Label>
                </td>
                <td class="Field">
                    <asp:DropDownList ID="ddlIdEmpresa" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlIdEmpresa_SelectedIndexChanged">
                    </asp:DropDownList>
                </td>
            </tr>
            <tr>
                <td class="Label">
                    <asp:Label ID="lblIdColaboradore" runat="server" Text="Colaboradores"></asp:Label>
                </td>
                <td class="Field">
                    <asp:DropDownList ID="ddlIdColaboradore" runat="server">
                    </asp:DropDownList>
                </td>
            </tr>
        </table>
    </asp:Content>

    using System;
    using System.Linq;
    using System.Web.UI.WebControls;

    namespace stackoverflow
    {
        public partial class EmpresaColaboradores : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (IsPostBack)
                    return;

                using (var ctx = new stackoverflowEntities())
                {
                    var qrCategoria = ctx.Categoria.ToList();

                    ddlIdEmpresa.DataTextField = "Nome";
                    ddlIdEmpresa.DataValueField = "CategoriaId";
                    ddlIdEmpresa.DataSource = qrCategoria;
                    ddlIdEmpresa.DataBind();

                }
                ddlIdEmpresa_SelectedIndexChanged(null, null);
            }

            protected void ddlIdEmpresa_SelectedIndexChanged(object sender, EventArgs e)
            {
                var IdEmpresa = Convert.ToInt32(ddlIdEmpresa.SelectedValue);
                ddlIdColaboradore.Items.Clear();
                ddlIdColaboradore.Enabled = IdEmpresa > 0;

                if (IdEmpresa > 0)
                {
                    using (var ctx = new stackoverflowEntities())
                    {
                        var qrSubCategoria = ctx.SubCategoria
                            .Where(sc => sc.CategoriaId == IdEmpresa)
                            .ToList();

                        ddlIdColaboradore.DataTextField = "Nome";
                        ddlIdColaboradore.DataValueField = "SubCategoriaId";
                        ddlIdColaboradore.DataSource = qrSubCategoria;
                        ddlIdColaboradore.DataBind();
                    }
                }
            }
        }
    }

See the results:

Answer 2

You can use the SQL search parameters system.

When you use:

    SELECT [nome] FROM [colaboradores];

It Grabs all contributors, since you don’t set a search parameter.

    SELECT [nome] FROM [colaboradores] WHERE id_empresa = `ID DA EMPRESA`;

It only takes employees who in the "id_company", is equal to the id that came from the other list.

Answer 3

Try using the following instruction to retrieve the names of collaborators:

SELECT [name] FROM [employees] WHERE [Idempresa] = @Variável_idempresa