SQL Server Performance Forum – Threads Archive

data display

We have a new requirement to be flexible in what fields show on our web site. We have approx. 125 businesses that upload data to our web site(they upload about 50 fields each; same fields uploaded by each business). Currently we display all of their upload fields on our site. Now each of these want to be able to indicate which fields we display for them on our site. So, for ex, if a search is performed and the data found is from business A, show x fields, etc… Any ideas on a way to implement this?

Do you mean that a single user would always see the same set of columns (albeit configurable) or that the columns shown depend on the businesses found and there could be a mix of businesses in a list of results? I’d say that you’d need to do this on the ASP side of the fence rather than in SQL, although with SQL containing the data which says which columns to show for which user Cheers
Twan

ex. Field Name: Listing_Details
All businesses currently upload this field with their data.
All data in this field, for every business, is currently shown on our public web site.
Now, each business wants to choose whether each field is shown on the web site of not.
So with over 50 fields, I do not want to associate a bit with each indicating whether or not its displayable. Must be a better solution. ex.
Business FieldName Display
——— ———- ——–
BusinessA Listing_Details Yes etc.
Thats seems messy.

My suggestions lie much more in the area of Development than General DBA solution and therefore the moderators are welcome to move or delete this post if they deem it necessary.
Two solutions come to mind here. Both of them involve more effort on the ASP side than the SQL side. Suggestion 1:
~~~~~~~~~~~~~
(Assumes ‘tblBusinessValues’ as an example) Allow the users of each business to specify the fields they want to see by presenting a web display of all available field names with checkboxes next to each name.
Make the HTML checkbox control names the same as the data field names.
The user can then mark/check the fields they wish to see and Submit the page.
The ASP code can then loop through the names and "on" values of the checkbox controls and use a string variable to dynamicaly build the SQL Query string to Select only the Fields that user wants to see. Like this: Set Rs = Db.Execute("SELECT TOP 1 * FROM tblBusinessValues")
SQL = "SELECT "
For i = 0 to Rs.Fields.Count – 1
If Request(Rs(i).Name) = "on" Then
SQL = SQL & Rs(i).Name & ","
End If
Next
SQL = Left(Len(SQL)-1)’This gets rid of the last comma
SQL = SQL & " FROM tblBusinessValues WHERE [Business Name]=’Business A’" Set Rs = Db.Execute(SQL)
Suggestion 2:
~~~~~~~~~~~~
(Assumes ‘tblBusinessValues’ as an example) Create a reference table to store the unique Field ID values that each business wants to see.
tblFieldRef structure something like:
[Business Name][Field ID]
Business A3,5,6,7,8,9,12,14
Business B1,2,3,4,5,6,7,8,9,12,13 [Field ID] can be a varchar(100) data type.
To populate the [Field ID] for each business,
Run a query like this: SELECT t1.[colid],t1.[name] FROM syscolumns t1 INNER JOIN sysobjects t2 ON t1.[id]=t2.[id]
WHERE t2.xtype=’U’ AND t2.[name]=’tblBusinessValues’ ORDER BY t1.colorder Make note of the [colid] values of each Field name and use this to populate the
[Field ID] in your tblFieldRef reference table with the [colid] values that your client wants to see.
Once tblFieldRef is populated for each Business, your ASP code can reference it like this
to dynamicaly build a unique SQL Query for each business depending on the User: Set Rs = Db.Execute("SELECT [Field ID] FROM tblFieldRef WHERE [Business Name]=’Business A’") SQL = "SELECT t1.[name] FROM syscolumns t1 INNER JOIN sysobjects t2 ON t1.[id]=t2.[id]" & _
" WHERE t2.xtype=’U’ AND t2.[name]=’tblBusinessValues’" & _
" AND t1.colid IN (" & Rs(0).Value & ") ORDER BY t1.colorder"
Set Rs = Db.Execute(SQL) SQL = "SELECT "
Do Until Rs.Eof
SQL = SQL & Rs(0).Value & ","
Rs.MoveNext
Loop
SQL = Left(Len(SQL)-1)’This gets rid of the last comma
SQL = SQL & " FROM tblBusinessValues WHERE [Business Name]=’Business A’" Set Rs = Db.Execute(SQL) —————————————– Hope that gives you some useful ideas.

As the suggestions are more related to non-SQL form, thread moved to appropriate section from General DBA section. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>