I'd like to create a DataGridView to display the following info in a form for a specific book, which will be passed to that form by name (or bookId):
Book Name: Test Book
_________________________________________
|Library Name | Library Location | Is Book Present |
--------------------------------------------------------
|Library One | 12345 Street | [x] |
|Library Two | 54321 Street | [ ] |
|..... |
-------------------------------------------------------
(the above diagram, even though it looks terrible, represents the DataGridView I would like to build - the [ ] represents a checkbox).
This data will be populated from a single DataSet that contains 3 DataTables:
Books:
- BookId
- BookName
- BookAuthor
- LibraryId
- LibraryName
- LibraryLocation
- BookId
- LibraryId
Now in my DataGridView up above, I'd like to be able to have the DataGridView list all of the library locations and then when the user clicks a checkbox it adds the library book to the corresponding location.
My question is, what's the proper way to do this in .NET 2.0 Even though this is a really trivial example it's definitely not trivial for me to implement. Any advice is much appreciated!
DataGridView - junction table, CheckBoxes
Sanjay More - webwaretech
You just need the right SQL query. Something like:
select L.LibraryName, L.LibraryLocation,
CASE
When LB.BookID Is Null Then 0
Else 1
END as HasBook
from Books B
inner join LibraryBooks LB on B.BookID = LB.BookID and B.BookName = @BookName
right outer join Library L on LB.LibraryID = L.LibraryID
order by L.LibraryName
HasBook will contain 1 if the library has the book and 0 if it doesnt. Bind the results of this query to a DataGridView and you're done.
If you are looking for information on executing the query and getting the results, read the documentation on the System.Data namespace. If you are looking for information on setting up the DataGridView, check out its documentation for examples.