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 ! ^^
7 Answers, 1 is accepted
0

Casey
Top achievements
Rank 1
answered on 07 Nov 2012, 02:04 PM
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
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-"
));
}
0

Nikola
Top achievements
Rank 2
answered on 07 Nov 2012, 02:53 PM
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
Thanks for help
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
0

Casey
Top achievements
Rank 1
answered on 07 Nov 2012, 03:10 PM
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.
If you want a list of all of the departments that has users belonging to it, then you could query your view as follows:
I hope this helps!
Casey
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
0

Nikola
Top achievements
Rank 2
answered on 07 Nov 2012, 03:18 PM
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 ^^
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 ^^
0

Casey
Top achievements
Rank 1
answered on 07 Nov 2012, 03:40 PM
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
0

Nikola
Top achievements
Rank 2
answered on 08 Nov 2012, 07:46 AM
Heey
Where should i put this query here
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>
0

Nikola
Top achievements
Rank 2
answered on 08 Nov 2012, 02:24 PM
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 ?
it's a little bit complicate i have mad it with view state etc... woudl you like you to see it ?