Select DB Columns Dynamically!

[Arpan]

A Form has a select list which lists all the column names of a SQL
Server database table. Users will select one or more than one column
from this select list & after submitting the Form, the records of only
those columns that he had selected in the previous page will be
displayed to him. This is the Form code:

----------------------------------------
strSQL="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME='tblSheet' ORDER BY ORDINAL_POSITION"
..............
..............
objRS.Open strSQL,objConn

<form........>
<select name="colname" multiple size=5>

Do Until(objRS.EOF)
%>
<option><%= objRS("COLUMN_NAME") %></option>
<%
objRS.MoveNext
Loop
%>
</select>
----------------------------------------

& this is the ASP page that retrieves the records:

----------------------------------------
<%
Dim strColNames,arrColName,strEachColName
strColNames=Request.Form("colname")
arrColName=Split(strColNames,", ")
.............
.............
.............
Dim strSQL
strSQL="SELECT " & strColNames & " FROM tblSheet"
.............
.............
.............
objRS.Open strSQL,objConn
%>
<table border=2>
<tr>
<%
For Each strEachColName In arrColName
%>
<th><%= strEachColName %></th>
<%
Next
%>
</tr>
<%
Do Until(objRS.EOF)
%>
<tr>
----------------------------------------

Now how do I loop through the recordset to display the recordset to the
user? Had the column names not been generated dynamically,
objRS("ColumnName") would have sufficed but how do I do the same here?

Thanks,

Arpan

 

[Chris Hohmann]

[snip]

& this is the ASP page that retrieves the records:

----------------------------------------
<%
Dim strColNames,arrColName,strEachColName
strColNames=Request.Form("colname")
arrColName=Split(strColNames,", ")
.............
.............
.............
Dim strSQL
strSQL="SELECT " & strColNames & " FROM tblSheet"
.............
.............
.............
objRS.Open strSQL,objConn
%>
<table border=2>
<tr>
<%
For Each strEachColName In arrColName
%>
<th><%= strEachColName %></th>
<%
Next
%>
</tr>
<%
Do Until(objRS.EOF)
%>
<tr>
----------------------------------------

Now how do I loop through the recordset to display the recordset to the
user? Had the column names not been generated dynamically,
objRS("ColumnName") would have sufficed but how do I do the same here?

Iterate the arrColName within the do loop, just like you did to create the
header row.

....
Do Until(objRS.EOF)
Response.Write "<tr>"
For Each strEachColName In arrColName
Response.Write "<td>"
Response.Write Server.HTMLEncode(objRS.Fields(strEachColName).Value)
Response.Write "</td>"
Next
Response.Write "</tr>"
objRS.MoveNext
Loop
....

Notes:
1. The order in which the "For Each...Next" statement iterates through
elements may not be deterministic. You may want to iterate the arrColName
array by index using the "For..Next" statement instead.

2. You should avoid dynamic sql, it will leave you open to sql injection
attacks. Here's a compelling paper on the pros and cons of dynamic sql:
http://www.sommarskog.se/dynamic_sql.html

3. Consider using the GetRows method of the recordset object to retrieve the
data into a two-dimensional array and iterate the array instead of iterating
the recordset object. Here's an article:
http://aspfaq.com/show.asp?id=2467

 

[Arpan]

That's exactly what I did but I am getting the "Item cannot be found in
the collection corresponding to the requested name or ordinal" error
which points to

<%= Server.HTMLEncode(objRS.Fields(strEachColName).Value) %>

I even did a Response.Write(strSQL), copied the output from the browser
& executed it in the Query Analyzer & it works fine! So where am I
erring?

I will definitely go through the articles you have cited.

Thanks,

Regards,

Arpan

 

[Chris Hohmann]

That's exactly what I did but I am getting the "Item cannot be found in
the collection corresponding to the requested name or ordinal" error
which points to

<%= Server.HTMLEncode(objRS.Fields(strEachColName).Value) %>

I even did a Response.Write(strSQL), copied the output from the browser
& executed it in the Query Analyzer & it works fine! So where am I
erring?

I will definitely go through the articles you have cited.

Iterate through the field names of the returned recordset and compare those
with the values in arrColName. Look for reserved words and/or spaces in the
field names as possible causes.

 

[Arpan]

No, Chris, I still can't find out where I am going wrong. It's driving
me crazy! Any other suggestion?

Arpan

 

[Arpan]

Chris, I have at last unearthed where I was going wrong. So please
neglect my last follow-up query.

Thanks once again for all your help.

Regards,

Arpan

 

[Chris Hohmann]

Chris, I have at last unearthed where I was going wrong. So please
neglect my last follow-up query.

Thanks once again for all your help.

Regards,

Arpan

Could you tell us what is was so other might avoid the same pitfall in the
future?

 

[Arpan]

Oh! sure, Chris When I get so much help from unknown people like
you here, why shouldn't I reciprocate & try to help others in whatever
little way I can!

Well the table was imported from Excel & some of the column names
included periods (.)s. After importing it to SQL Server, the periods
were automatically converted to #s. Some of the column names included
special characters as well like '/', '&', '+', '{', '}' & a blank space
after the last letter of the column name which I didn't notice which
resulted in the error!

Thanks once again,

Regards,

Arpan