Solved

dynamic drop down box values

Posted on 2011-03-24
12
597 Views
Last Modified: 2012-05-11

Hi
Here i got the problem with sql query to get the values in the drop down list box.
This  is the query i used to get the result in sql.
so the same values i need in drop down list box..

this is my sql query
sql="select col1 from table1 union all select col2 from table2



can anyone give a clue to get the list box values..

If i use the same query ,i can't give two column names in list box..
0
Comment
Question by:lucky20
  • 7
  • 4
12 Comments
 
LVL 31

Accepted Solution

by:
Wayne Barron earned 250 total points
ID: 35211850
Is this what you are referring too?
(Classic ASP)

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Set sql = Server.CreateObject("ADODB.Command")
sql.ActiveConnection=objConn
sql.Prepared = true
sql.commandtext="select col1 from table1 union all select col2 from table2"
set rs = sql.execute
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Test Run Combo Fill</title>
</head>

<body>

<select name="MySelect">
<option>Choose</option>
<%
while not rs.eof
if not rs.eof then
col1 = rs("col1")
%>
<option value="<%=col1%>"><%=col1%></option>
<%rs.movenext
wend
rs.close
set rs=nothing
%>
</select>
</body>
</html>

Open in new window

Carrzkiss
0
 
LVL 1

Author Comment

by:lucky20
ID: 35211975
@carrzkiss

yes..
but here we are using only col1..
col1 = rs("col1")

i need a combine result of col1 and col2..

col1||col2

is it possible..

0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 35212035
need a little more information on this one please.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:lucky20
ID: 35212086
@carrskiss..

I  need to display the data in drop down box from (each column from one table,it is like col1 from one table ,another column from another table)
union of both the columns
table1
col1 has a,b,c

table2
col1 has d,e,f

i need to display in drop down box as a,b,c,d,e,f

so after user made the selection i need to display values from the database..
0
 
LVL 1

Author Comment

by:lucky20
ID: 35212093
so i used this sql statement to combine the two columns.. it perfectly displaying what i need.. Now i want this data in drop down list box..

I hope u can understand this now..
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 35212162
If I understand you correctly, then it "might" be something like this

<select name="MySelect">
<option>Choose</option>
<%
while not rs.eof
if not rs.eof then
col1 = rs("col1")&rs("col2")
%>
<option value="<%=col1%>"><%=col1%></option>
<%rs.movenext
wend
rs.close
set rs=nothing
%>
</select>

Open in new window

0
 
LVL 14

Assisted Solution

by:dejaanbu
dejaanbu earned 250 total points
ID: 35213429
instead of union u can have 2 select statements also..

you have to bind the dropdown manually...

for ex,

 Dim ds As DataSet = '' here get the Dataset from ur storedprocedure
   
            Dim li As New ListItem("CAPTION1", "")
            li.Attributes.Add("style", "font-weight:bold;border-bottom:solid 1px;margin:5px 0px 5px 0px;")
            li.Attributes.Add("disabled", "true")
            ddl.Items.Add(li)
            For Each drow As DataRow In ds.Tables(0).Rows
                ddl.Items.Add(New ListItem("-" & drow.Item("COLUMN1"), "COL1_" & drow.Item("COLUM1 Unique ID")))
            Next
            Dim mg As New ListItem("CAPTION2", "")
            mg.Attributes.Add("style", "font-weight:bold;border-bottom:solid 1px;margin:5px 0px 5px 0px;")
            mg.Attributes.Add("disabled", "true")
            ddl.Items.Add(mg)
            For Each drow As DataRow In ds.Tables(1).Rows
                ddl.Items.Add(New ListItem("-" & drow.Item("COLUMN2"), "COL2_" & drow.Item("COLUMN2 UniqueId")))
            Next

Open in new window


instead of Dataset , you can use Data reader also, if u r using datareader then use NextResult() method.

The reason here to use 2 select statements is, You can get control for doing the functionality of selected index changed event

Hope this helps
0
 
LVL 1

Author Comment

by:lucky20
ID: 35214542
@carrzkiss
Ya i need to combine col1 and col2 like that..
but that solution was wrong..i tried it..it is giving unterminated string constant error..


@dejaanbu
i will try this now and let u know..
thanks..
0
 
LVL 1

Author Comment

by:lucky20
ID: 35215134
@dejaanbu

it is giving two dropdown boxes..
I don't need two drop downs...
I need a single drop down with two columns data from two different tables..

Thanks..
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 35215139
lucky20
Are you using ASP Classic (or) .NET?

My code is Classic
dejaanbu, is .net

Please let us know what you are using.
0
 
LVL 1

Author Comment

by:lucky20
ID: 35216434
i am using classic asp only..
I didn't use his code..


Any way i got the result..
i used same sql query..

<td> <select name="opt" >
<option value="">Please Choose</option>
 <% Do While Not rs.EOF %>
<option value='<%=rs("col1")%>'> <%=rs("col1")%></option>
<%
rs.MoveNext
Loop
                                                
%>
</select></td>

this one perfectly showing what i need..
0
 
LVL 1

Author Closing Comment

by:lucky20
ID: 35279651
No one posted the answer.. I got the result before experts giving answer..
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Auto-merge multiple transaction ledgers in Excel 5 35
How to fix error "424" object required. 6 49
Validating Date 4 29
Problem to copy file 14 54
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question