473,555 Members | 2,333 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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

Aug 30 '05 #1
8 2175
"Arpan" <ar******@hotma il.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .
[snip]
& 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?


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

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.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

Aug 31 '05 #3
"Arpan" <ar******@hotma il.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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.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.


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******@hotma il.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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******@hotma il.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
376
by: Terry Olsen | last post by:
I've got my datgrid loaded (finally). I can select rows & delete them. But I can't select columns to delete them. When I click on a column header, all that happens is the grid is resorted by that column. How can I change it so I can select the column to delete it? Thanks! *** Sent via Developersdex http://www.developersdex.com ***...
1
1104
by: andy | last post by:
I have a datagrid that will display data from the database. Each user will specify list of columns he wants to see and in the order he wants to see. User A can specify that he wants to see only columns A,B,C and in the order A,B,C and the user might say I want to see only only columns A,G,H but the order should be G,A,H. It's possible that...
5
2115
by: GU | last post by:
Is it possible to use an simple cgi script to select one graphic dynamically and use this one as background image? if using <body background="/httpdocs/pics/test.jpg"> blah </body> i'll get this backgroung picture. but i want to get the backgroungimage dynamically. so i'd like to use an simple cgi script <body...
6
2172
by: tuxedo | last post by:
I have a fixed html structure, where only one form and a simple select menu will exist on an html page, as follows: <form action="order" method="POST"> <select name="dinner"> <option value="1">Pizza</option> <option value="2">Hot Vindaloo</option> <option value="3">Fish-n-Chips</option> <option value="4">Currywurst</option>
0
1476
by: Don Miller | last post by:
I'd like to use a programmatically bound GridView to display a dataset (or work with a datareader) that may include everchanging numbers of database columns, in unpredictable order, with varying datatypes. I will be letting end-users pick which columns to display and wonder how I could format columns (e.g. centered vs. left-aligned, short-date...
1
1263
Gyro
by: Gyro | last post by:
Hi all, I'm a php newbie and have read various posts/articles on populating select lists dynamically from a db table. However, I cant seem to get it working in the way I want... I have 3 select lists on a form and want to use a single piece of code to display each one by passing the relevant query to the code. My problem is that I dont know...
2
1830
by: Roger Frei | last post by:
Hello ng, I have a datagrid that is bound to a datasource. That works good so far. Now I want to add another column to my grid dynamically. That also works good until the first postback. The column is still there but for some reason its content is empty. I added the code underneath this post. The problem is that the delegate...
6
18555
by: vinod allapu | last post by:
Hi boss, I have a gridview, bound to a datasource . Here i want to add columns dynamically containing empty textboxes. Number of columns are variant . The added columns are not bound to datasource they are added just for dislay. The user fills the data in columns(textboxes) and saves the grid as excel. Can anybody give an idea...
1
2283
by: nawedita | last post by:
Hi everyone how i add and delete rows,and columns(textbox,dropdown) dynamically onclick button and store data in database with insert,update,delete record.that support all browsers.Because i am facing problem with IE & mozilla at the time of insert & update record. Please provide me full code with insert,update query.
0
7622
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7825
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8060
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7904
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5171
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3596
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3575
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
865
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.