Selecting Data

  • Thread starter Thread starter CCORDON
  • Start date Start date
C

CCORDON

Hi, I have 2 tables, One containing a list of Companies (ID, Name) Another
Containing a list of companies a user has acces to (IdUser, IdCompany) The
question is how can I load all the companies into a checkBoxList Control and
only check the companies that the user currently has access to?

Thanks,
 
Sorry, I dont understand how that will allow me to check only the ones the
user has access to in my CheckListBox

Thanks.
 
CCORDON said:
Hi, I have 2 tables, One containing a list of Companies (ID, Name) Another
Containing a list of companies a user has acces to (IdUser, IdCompany) The
question is how can I load all the companies into a checkBoxList Control
and only check the companies that the user currently has access to?
Click to expand...

Use a query like this in SQL Server:

SELECT Companies.ID, Companies.Name, CompanyAccess.IdUser
FROM Companies LEFT OUTER JOIN CompanyAccess
ON Companies.ID = CompanyAccess.IdCompany
WHERE CompanyAccess.IdUser = @IdUser

This will return three columns: ID, Name and IdUser. IdUser will either be
the Id of the user you're checking for, or it will be NULL if the user has
no access. Use the first two columns as the text and value of the
CheckBoxList, and set the checkboxes based on the IdUser column.

John Saunders
 
That sounds good...Ill give it a try.

Thanks,

Curt_C said:
My bad...misunderstood,
How about this, when you loop through the dataset to build the list check
the IdUser and if it matches the logged in user then set the Checked=True
property?
Click to expand...
 
Thanks, I am trying that query but only get the list of COmanies that the
user has access to, not all the companies.

Thanks,
 
Curt_C said:
My bad...misunderstood,
How about this, when you loop through the dataset to build the list check
the IdUser and if it matches the logged in user then set the Checked=True
property?
Click to expand...

What query are you suggesting he use? It must be a query which returns all
of the companies and which also indicates which companies the particular
user has access to.

In fact, I should expand on my earlier post:

SELECT Companies.ID, Companies.Name,
CompanyAccess.IdUser, CompanyAccess.IdCompany
FROM Companies LEFT OUTER JOIN CompanyAccess
ON Companies.ID = CompanyAccess.IdCompany
WHERE CompanyAccess.IdUser = @IdUser

This presumes that the primary key for the CompanyAccess table is the
compound key (IdUser, IdCompany).

Keep in mind that he wants a set of checkboxes. When the user checks a box
which had not previously been checked, then a new CompanyAccess row should
be added. But if the user clears a checkbox which had previously been
checked, the corresponding CompanyAcccess row needs to be deleted. By
including the primary key of that table in the query, the row can easily be
deleted in this case.

John Saunders
 
CCORDON said:
Thanks, I am trying that query but only get the list of COmanies that the
user has access to, not all the companies.
Click to expand...

Whoops. You're correct. Please try:

SELECT c.ID, c.Name, (CASE WHEN a.IdUser IS NULL THEN 0 ELSE 1 END) as
HasAccess, a.IdUser, a.IdCompany
FROM Company AS c LEFT OUTER JOIN
(SELECT * FROM CompanyAccess
WHERE IdUser = @IdUser) AS a
ON c.ID = a.IdCompany

John Saunders
 
THANK YOU!!!! :) THAT Query was just what I was looking for. Everything
works grear now. Thank you all.-
 
Back
Top