Selecting Data

[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,

[Curt_C [MVP]]

Use a JOIN in the SQL statement and then specify a WHERE clause with the
IdUser

--
Curt Christianson
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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,

[CCORDON]

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

Thanks.

"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:uO******** ******@TK2MSFTN GP10.phx.gbl...

Use a JOIN in the SQL statement and then specify a WHERE clause with the
IdUser

--
Curt Christianson
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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,

[John Saunders]

"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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?

Use a query like this in SQL Server:

SELECT Companies.ID, Companies.Name, CompanyAccess.I dUser
FROM Companies LEFT OUTER JOIN CompanyAccess
ON Companies.ID = CompanyAccess.I dCompany
WHERE CompanyAccess.I dUser = @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

[Curt_C [MVP]]

My bad...misunders tood,
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?

--
Curt Christianson
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"CCORDON" <tc******@hotma il.com> wrote in message
news:u$******** ******@TK2MSFTN GP09.phx.gbl...

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

Thanks.

"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:uO******** ******@TK2MSFTN GP10.phx.gbl...

Use a JOIN in the SQL statement and then specify a WHERE clause with the
IdUser

--
Curt Christianson
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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,

[CCORDON]

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

Thanks,

"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:uy******** *****@TK2MSFTNG P12.phx.gbl...

My bad...misunders tood,
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?

--
Curt Christianson
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"CCORDON" <tc******@hotma il.com> wrote in message
news:u$******** ******@TK2MSFTN GP09.phx.gbl...

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

Thanks.

"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:uO******** ******@TK2MSFTN GP10.phx.gbl...

Use a JOIN in the SQL statement and then specify a WHERE clause with the
IdUser

--
Curt Christianson
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...
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,

[CCORDON]

Thanks, I am trying that query but only get the list of COmanies that the
user has access to, not all the companies.

Thanks,

"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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?

Use a query like this in SQL Server:

SELECT Companies.ID, Companies.Name, CompanyAccess.I dUser
FROM Companies LEFT OUTER JOIN CompanyAccess
ON Companies.ID = CompanyAccess.I dCompany
WHERE CompanyAccess.I dUser = @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

[John Saunders]

"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:uy******** *****@TK2MSFTNG P12.phx.gbl...

My bad...misunders tood,
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?

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.I dUser, CompanyAccess.I dCompany
FROM Companies LEFT OUTER JOIN CompanyAccess
ON Companies.ID = CompanyAccess.I dCompany
WHERE CompanyAccess.I dUser = @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

[John Saunders]

"CCORDON" <tc******@hotma il.com> wrote in message
news:u3******** ******@TK2MSFTN GP12.phx.gbl...

Thanks, I am trying that query but only get the list of COmanies that the
user has access to, not all the companies.
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

Thanks,

"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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?

Use a query like this in SQL Server:

SELECT Companies.ID, Companies.Name, CompanyAccess.I dUser
FROM Companies LEFT OUTER JOIN CompanyAccess
ON Companies.ID = CompanyAccess.I dCompany
WHERE CompanyAccess.I dUser = @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

[CCORDON]

THANK YOU!!!! :) THAT Query was just what I was looking for. Everything
works grear now. Thank you all.-
"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

"CCORDON" <tc******@hotma il.com> wrote in message
news:u3******** ******@TK2MSFTN GP12.phx.gbl...

Thanks, I am trying that query but only get the list of COmanies that the
user has access to, not all the companies.

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

Thanks,

"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

"CCORDON" <tc******@hotma il.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...
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?

Use a query like this in SQL Server:

SELECT Companies.ID, Companies.Name, CompanyAccess.I dUser
FROM Companies LEFT OUTER JOIN CompanyAccess
ON Companies.ID = CompanyAccess.I dCompany
WHERE CompanyAccess.I dUser = @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