view documents with specific department id

Question

1.00/5 (1 vote)

See more:

i have a table which is department like

SQL

departmentid  department type
 1             computer science
 2             hr
 3               finance

here is the store procedure to view documents

SQL

ALTER PROCEDURE [dbo].[statuschange]
AS
SELECT  di.DocID, 
    di.DocName, 
    di.Uploadfile, 
    dt.DocType,
    d.DepType, 
    at.ApproveType
FROM    DocumentInfo di
JOIN
    DocType dt ON dt.DocTypeID = di.DocTypeID
JOIN 
    Department d ON d.DepID = di.DepID
LEFT OUTER JOIN
    Approval a ON a.DocID = di.DocID
JOIN
    ApproveType at ON at.ApproveID = ISNULL(a.Approveid, 3)

now i want to show documents with specific department id

computer-science admin will be able to view only computer-science documents and hr admin view only hr documents

any solution?

Posted 21-Oct-13 5:00am

Diya Ayesa

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2013-10-21T05:07:00

Solution 1

You just need to pass a parameter to your stored procedure. For example:

SQL

ALTER PROCEDURE [dbo].[statuschange]
(
 @department_id INT
)
AS
SELECT  di.DocID...
WHERE department_id = @department_id

Or, if it is based on user id you can pass in the user id and then do your logic to determine what they have access to.

Posted 21-Oct-13 5:07am

ZurdoDev

Comments

Diya Ayesa 21-Oct-13 11:09am

when i add like this WHERE DepID = @DepID it show me error
Ambiguous column name 'DepID'.

ZurdoDev 21-Oct-13 11:11am

That means you have the column DepID in more than one table and it does not know which one you are talking about.

Do either d.DepID or di.DepID in your WHERE clause.

Diya Ayesa 21-Oct-13 11:18am

i change it but when i login through computer science admin then it still shows me all documents??

Diya Ayesa 21-Oct-13 11:22am

how i done this with userid? beacuse i define users through departments like which user is link to which department

ZurdoDev 21-Oct-13 11:27am

Then pass in user id and join to the users table to get the department id.

ZurdoDev 21-Oct-13 11:52am

1. Is your table really named Userss (2 s)?
2. You are joining the Userss table twice. If you need to do that alias one as Userss u1 and another as u2, for example, so it knows the difference between them. But I doubt you actually need to join to it twice. Why would you?

Diya Ayesa 21-Oct-13 12:04pm

yes i deleted this post .....

Diya Ayesa 21-Oct-13 11:49am

ok i try this

create procedure [dbo].[dddddd]
@userid int
as

SELECT di.DocID,
di.DocName,
di.Uploadfile,
dt.DocType,
d.DepType,
at.ApproveType
FROM DocumentInfo di
JOIN
DocType dt ON dt.DocTypeID = di.DocTypeID
JOIN
Department d ON d.DepID = di.DepID

join
Userss ON d.DepID = dbo.Userss.DepID AND di.DocID = dbo.Userss.DocID

LEFT OUTER JOIN
Approval a ON a.DocID = di.DocID
JOIN
ApproveType at ON at.ApproveID = ISNULL(a.Approveid, 3)

where Userss.USERID=@userid

and then create a fucntion
public DataTable getdocall1(int userid)
{
return db.ExecuteDataSet("dddddd").Tables[0];

}

and then

protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = dwww.getdocall1(Convert.ToInt32(Session["UserID"]));
GridView1.DataBind();
}

then it shows me error

The number of parameters does not match number of values for stored procedure.

in this line

return db.ExecuteDataSet("dddddd").Tables[0];

ZurdoDev 21-Oct-13 11:52am

Do you not understand the error? Or any of these errors? This is really a trivial issue.

Diya Ayesa 21-Oct-13 11:59am

i understand the error but i pass only one paramater so why this error comes?

ZurdoDev 21-Oct-13 12:02pm

Where are you passing the parameter?

Diya Ayesa 21-Oct-13 12:07pm

i passing the parameter in this
getdocall1(int userid)
and in this (Convert.ToInt32(Session["UserID"]));

i aready save userid in sessions

ZurdoDev 21-Oct-13 12:10pm

getdocall1 is code I can't see. All I know is the error is not lying. :)

Diya Ayesa 21-Oct-13 12:14pm

i posted plz check

ZurdoDev 21-Oct-13 12:16pm

You are not passing in a parameter. Some you have to have something like this:
cmd.Parameters.AddWithValue("@userid", Session["userid"]);

Diya Ayesa 21-Oct-13 12:19pm

where i done this

ZurdoDev 21-Oct-13 12:21pm

Who knows. You aren't posting all of the code. You posted db.ExecuteDataSet but what is db. Adding parameters is also trivial so I am not sure if you are too new or what the issue is. I don't have access to your code so I can't tell you where.