May 24, 2007 at 8:09 pm
I know basic SQL so I'm hoping I just don't know the correct statement to pull the data I need. I have a page that needs to display a datagrid with the Filename stored and what category its in based on the page I'm on. This datagrid would have a column for the filename and then a column for each category(I'd basically be putting a check in the category each file is in, so it would look kind of like excel).
So I have a Documents_Table, Page_Table, Page_Category_Table, and XREF_Doc_Page. My XREF_Doc_Page has DocID, PageID, and PageCategoryID so if I load a certain page I can query the XREF Table and see what documents are on this page and which category they're in.
My question is I have 4 categories in the Page_Category_Table. I'd like to display this data in a datagrid/gridview but my columns need to be FileName, and then each of the Page_Category_Name, so there would be 5 columns. Is there some sort of select within a select or crosstab-query to pull this data out in this format.
Did I explain this well enough??
Kyle
June 5, 2007 at 3:00 pm
I have some questions about your problem.
1. Is each document has one filename ?
2. Does each page has its own category or each document has its own category?
In your design, I don't even see where you put the filename in which table.
September 25, 2007 at 10:33 pm
Your database is fine, it's your controls that are the problem. You basically need one Column to display the Categories assigned to the File. Ideally you'd have a Drop-Down CheckBoxList so the user could update as many Categories as they want from the one column. Most professional Grids support this and even show a comma-delimited list of the selected values (with a Tooltip showing them as well because they usually run off the control).
However, if you're doing this on the Web, there isn't an Ideal solution. You should show the selected Categories as a Comma-delimited list in one column (use can use a very simple and efficient User-Defined Function in SQL Server for this purpose). However, to allow the user to change the Categories, you'll need to redirect them to another page for that File where they can check-off which Categories they want.
I would probably design this as the data being retrieved from a view with a column called "CategoryList" that is the comma-delimited list of Category Names.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply