470,862 Members | 1,828 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,862 developers. It's quick & easy.

Select DB Columns Dynamically!

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

Aug 30 '05 #1
8 2037
"Arpan" <ar******@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
[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).Val ue)
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

Aug 31 '05 #2
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).Val ue) %>

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

Aug 31 '05 #3
"Arpan" <ar******@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
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).Val ue) %>

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.

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

Arpan

Sep 1 '05 #5
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

Sep 1 '05 #6
"Arpan" <ar******@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
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?
Sep 1 '05 #7
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

Sep 4 '05 #8
gsunit.com

"Arpan" <ar******@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
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

May 21 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Terry Olsen | last post: by
1 post views Thread by andy | last post: by
6 posts views Thread by tuxedo | last post: by
reply views Thread by Don Miller | last post: by
2 posts views Thread by Roger Frei | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.