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_SCH EMA.COLUMNS WHERE
TABLE_NAME='tbl Sheet' ORDER BY ORDINAL_POSITIO N"
..............
..............
objRS.Open strSQL,objConn

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

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

& this is the ASP page that retrieves the records:

----------------------------------------
<%
Dim strColNames,arr ColName,strEach ColName
strColNames=Req uest.Form("coln ame")
arrColName=Spli t(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("ColumnNa me") would have sufficed but how do I do the same here?

Thanks,

Arpan

[Chris Hohmann]

Re: Select DB Columns Dynamically!

"Arpan" <arpan_de@hotma il.com> wrote in message
news:1125444087 .055906.168660@ o13g2000cwo.goo glegroups.com.. .
[snip][color=blue]
> & this is the ASP page that retrieves the records:
>
> ----------------------------------------
> <%
> Dim strColNames,arr ColName,strEach ColName
> strColNames=Req uest.Form("coln ame")
> arrColName=Spli t(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("ColumnNa me") would have sufficed but how do I do the same here?[/color]

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.HTMLEnco de(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]

Re: Select DB Columns Dynamically!

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.HTMLEnco de(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]

Re: Select DB Columns Dynamically!

"Arpan" <arpan_de@hotma il.com> wrote in message
news:1125447033 .989468.307380@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> 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.HTMLEnco de(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.[/color]

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]

Re: Select DB Columns Dynamically!

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

Arpan

[Arpan]

Re: Select DB Columns Dynamically!

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]

Re: Select DB Columns Dynamically!

"Arpan" <arpan_de@hotma il.com> wrote in message
news:1125549669 .895844.29860@z 14g2000cwz.goog legroups.com...[color=blue]
> 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
>[/color]

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

[Arpan]

Re: Select DB Columns Dynamically!

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

[News Reader]

Re: Select DB Columns Dynamically!

gsunit.com

"Arpan" <arpan_de@hotma il.com> wrote in message
news:1125821607 .907418.114770@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> 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
>[/color]