SQL - Restrict query data

Question

SQL - Restrict query data

Navigation

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

9

Good morning,

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

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

SELECT [nome] FROM [empresas]

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

SELECT [nome] FROM [colaboradores]

How can I solve this problem?

sql-server c# asp.net sql-server-2012

asked by anonymous 15.04.2016 / 10:50

2 answers

Two "return" in a function How not to apply opacity on a child element?

score 0 · Answer 1

You can use the DropDownList of your Companies table in Page_Load , if you want to do this when IsPostBack is false. After that you only have to use OnSelectedIndexChanged to query the colaboradores and load your second DropDownList as follows.

<%@ 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 results

Be careful to change to your correct tables.

score 0 · Answer 2

0

Try using the following statement to retrieve the names of the contributors:

SELECT [name] FROM [contributors] WHERE [CompanyID] = @CompanyIndex

30.05.2017 / 20:48