Solved

dynamic drop down box values

Posted on 2011-03-24
12
594 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 30

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 30

Expert Comment

by:Wayne Barron
ID: 35212035
need a little more information on this one please.
0
 
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 30

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 30

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP CDO - Limiting Connections 2 39
Replace &lt; with < 14 55
Reset asp.net login password 4 29
Show/Hide ASP.NET controls with JQuery best practices 3 17
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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

912 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now