Get all users from table with the right id

[Nikola]

heey guys 

I have a problem how can I get the department with the all current Users I created a View that connect this both tables(user/department) 

but i got this strange error (Must declare the scalar variable "@UserId".) 

my coder






protected void LoadDE(string businessUnitId)

       {

           SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["HolidayTrackerConnectionString"].ConnectionString);

 

           SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM DepartmentView  WHERE BusinessUnitId=@BusinessUnitId AND UserId=@UserId", connection);

           adapter.SelectCommand.Parameters.AddWithValue("@BusinessUnitId", businessUnitId);

           DataTable dt = new DataTable();

           adapter.Fill(dt);

 

           rcbDepartments.DataTextField = "Name";

           rcbDepartments.DataValueField = "DepartmentId";

           rcbDepartments.DataSource = dt;

           rcbDepartments.DataBind();

           rcbDepartments.Items.Insert(0, new RadComboBoxItem("-Select a DEP-"));

       }

Thanks for every help and fast answer ! ^^


 

[Casey]

Hi Nikola,

The issue appears to be that in your where clause your query is expecting an @UserId parameter, which is not being passed. If you only want to view the users for a department, then you don't need the "AND UserId=@UserId" 

I hope this helps!
Casey

protected void LoadDE(string businessUnitId)
       {
           SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["HolidayTrackerConnectionString"].ConnectionString);

           SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM DepartmentView  WHERE BusinessUnitId=@BusinessUnitId", connection);
           adapter.SelectCommand.Parameters.AddWithValue("@BusinessUnitId", businessUnitId);
           DataTable dt = new DataTable();
           adapter.Fill(dt);

           rcbDepartments.DataTextField = "Name";
           rcbDepartments.DataValueField = "DepartmentId";
           rcbDepartments.DataSource = dt;
           rcbDepartments.DataBind();
           rcbDepartments.Items.Insert(0, new RadComboBoxItem("-Select a DEP-"));
       }

[Nikola]

Heey thanks for the answer but now i have that much department like users ... (i'm little bit a noob) what shloud i do if i want just to get the department one time in the drop down list 

SELECT     dbo.HtDepartment.Name, dbo.HtUser2HtDepartment.DepartmentId, dbo.HtDepartment.BusinessUnitId, dbo.HtUser.FirstName
FROM         dbo.HtDepartment INNER JOIN
                      dbo.HtUser2HtDepartment ON dbo.HtDepartment.DepartmentId = dbo.HtUser2HtDepartment.DepartmentId INNER JOIN
                      dbo.HtUser ON dbo.HtUser2HtDepartment.UserId = dbo.HtUser.UserId

Thanks for help 

[Casey]

Nikola,

No problem.

If you just want a distinct list of all departments and department Ids, then you shouldn't need to join to the other tables. 

SELECT     dbo.HtDepartment.Name, dbo.HtDepartment.DepartmentId, dbo.HtDepartment.BusinessUnitId
FROM         dbo.HtDepartment

If you want a list of all of the departments that has users belonging to it, then you could query your view as follows:

SELECT    DISTINCT dbo.HtUser2HtDepartment.Name, dbo.HtUser2HtDepartment.DepartmentId, dbo.HtUser2HtDepartment.BusinessUnitId
FROM         dbo.HtUser2HtDepartment

I hope this helps!
Casey

[Nikola]

Heey

How you can see on my pic if i select the department i and click the show button (you cant see it here beausp it's under the list)

it should show me al useres from selected departemnt and not all ..

hope that help ^^

[Casey]

If you want to see all of the users for a selected department, then the following query should provide that. The @DepartmentId will be the SelectedValue from your Department combobox.

SELECT dbo.HtUser.*
FROM         dbo.HtUser,
 dbo.HtUser2HtDepartment
WHERE  dbo.HtUser.UserId = dbo.HtUser2HtDepartment.UserId
 and dbo.HtUser2HtDepartment.DepartmentId = @DepartmentId

[Nikola]

Heey

Where should i put this query  here

protected void LoadDE(string businessUnitId)

       {

 

           SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["HolidayTrackerConnectionString"].ConnectionString);

 

 

 

           SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM HtDepartment WHERE BusinessUnitId=@BusinessUnitId", connection);

 

           adapter.SelectCommand.Parameters.AddWithValue("@BusinessUnitId", businessUnitId);

 

           DataTable dt = new DataTable();

 

           adapter.Fill(dt);

 

 

 

           rcbDepartments.DataTextField = "Name";

 

           rcbDepartments.DataValueField = "DepartmentId";

 

           rcbDepartments.DataSource = dt;

 

           rcbDepartments.DataBind();

 

           rcbDepartments.Items.Insert(0, new RadComboBoxItem("-Select a DEP-"));

 

       }


??

Thanks for help and fast answer

<%@ Page Title="" ValidateRequest="false" EnableEventValidation="false" Language="C#" MasterPageFile="~/ABBMaster.Master" AutoEventWireup="true" CodeBehind="Report.aspx.cs" Inherits="HolidayTracker.Administrator.Report" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainRegionContentPlaceHolder" runat="server">
    <link rel="Stylesheet" type="text/css" href="ReportStyle.css" />
    <telerik:RadScriptManager runat="server" ID="RadScriptManager1"></telerik:RadScriptManager>

    <script type="text/javascript">
        function onRequestStart(sender, args) {
            if (args.get_eventTarget().indexOf("ExportToExcelButton") >= 0 ||
             args.get_eventTarget().indexOf("ExportToWordButton") >= 0 ||
             args.get_eventTarget().indexOf("ExportToCsvButton") >= 0) {
                args.set_enableAjax(false);
            }
        }

    </script>
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <ClientEvents OnRequestStart="onRequestStart" />
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="UserGrid">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="UserGrid" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <div class="qsf-demo-canvas">
        <div class="LBU" runat="server" id="lbu_div">
            <telerik:RadComboBox ID="rcbLocationBusinessUnits" runat="server" Visible="false" Width="186px" Label="LocationBusinessUnit: " AutoPostBack="true" OnSelectedIndexChanged="RadComboBox1_SelectedIndexChanged"></telerik:RadComboBox>
        </div>
        <div class="BU">
            <telerik:RadComboBox ID="rcbBusinessUnits" runat="server" Visible="false" Width="186px" Label="BusinessUnit: " AutoPostBack="true" OnSelectedIndexChanged="RadComboBox2_SelectedIndexChanged"></telerik:RadComboBox>
        </div>
        <div class="Dep">
            <telerik:RadComboBox ID="rcbDepartments" runat="server" Visible="false" Width="186px" Label="Departments: " AutoPostBack="true"></telerik:RadComboBox>
        </div>
        <p class="Button">
            <asp:Button ID="Button1" runat="server" Text="Show Report" OnClick="Button1_Click" />
        </p>


        <div class="UserGrid">
            <telerik:RadGrid ID="UserGrid" runat="server" Visible="false" AllowSorting="True" CellSpacing="0" GridLines="None" AllowPaging="true" PageSize="10" OnItemCommand="UserGrid_ItemCommand">
                <ExportSettings HideStructureColumns="true">
                </ExportSettings>
                <MasterTableView CommandItemDisplay="Top">
                    <PagerStyle Mode="NextPrevNumericAndAdvanced" />
                    <CommandItemSettings ShowExportToExcelButton="true" ShowExportToCsvButton="false" ShowExportToWordButton="false" />
                    <RowIndicatorColumn Visible="True" FilterControlAltText="Filter RowIndicator column">
                        <HeaderStyle Width="20px"></HeaderStyle>
                    </RowIndicatorColumn>
                    <ExpandCollapseColumn Visible="True" FilterControlAltText="Filter ExpandColumn column">
                        <HeaderStyle Width="20px"></HeaderStyle>
                    </ExpandCollapseColumn>

                    <Columns>
                        <telerik:GridBoundColumn DataField="FirstName" FilterControlAltText="Filter FirstName column" HeaderText="First Name" SortExpression="FirstName" UniqueName="FirstName">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="LastName" FilterControlAltText="Filter LastName column" HeaderText="Last Name" SortExpression="LastName" UniqueName="LastName">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="VacationDays" FilterControlAltText="Filter VacationDays column" HeaderText="Vacation Days" SortExpression="VacationDays" UniqueName="VacationDays">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="UsedVacationDays" FilterControlAltText="Filter UsedVacationDays column" HeaderText="Used" SortExpression="UsedVacationDays" UniqueName="UsedVacationDays">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="BookedVacationDays" FilterControlAltText="Filter BookedVacationDays column" HeaderText="Booked" SortExpression="BookedVacationDays" UniqueName="BookedVacationDays">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="ForecastVacationDays" FilterControlAltText="Filter ForecastVacationDays column" HeaderText="Forecast" SortExpression="ForecastVacationDays" UniqueName="Forecast">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="January" FilterControlAltText="Filter January column" HeaderText="January" SortExpression="January" UniqueName="January">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="February" FilterControlAltText="Filter Febuary column" HeaderText="February" SortExpression="February" UniqueName="February">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="March" FilterControlAltText="Filter Febuary column" HeaderText="March" SortExpression="March" UniqueName="March">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="April" FilterControlAltText="Filter Febuary column" HeaderText="April" SortExpression="April" UniqueName="April">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="May" FilterControlAltText="Filter May column" HeaderText="May" SortExpression="May" UniqueName="May">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="June" FilterControlAltText="Filter June column" HeaderText="June" SortExpression="June" UniqueName="June">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="July" FilterControlAltText="Filter July column" HeaderText="July" SortExpression="July" UniqueName="July">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="August" FilterControlAltText="Filter August colum" HeaderText="August" SortExpression="August" UniqueName="August">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="September" FilterControlAltText="Filter September colum" HeaderText="September" SortExpression="September" UniqueName="September">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="October" FilterControlAltText="Filter October colum" HeaderText="October" SortExpression="October" UniqueName="October">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="November" FilterControlAltText="Filter November colum" HeaderText="November" SortExpression="November" UniqueName="November">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="December" FilterControlAltText="Filter December colum" HeaderText="December" SortExpression="December" UniqueName="December"></telerik:GridBoundColumn>

                    </Columns>
                </MasterTableView>

                <FilterMenu EnableImageSprites="False"></FilterMenu>
            </telerik:RadGrid>
        </div>
    </div>

    <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server"></telerik:RadAjaxLoadingPanel>
</asp:Content>

[Nikola]

Got it by myself

it's a little bit complicate i have mad it with view state etc... woudl you like you to see it  ?