Solved

dynamic drop down box values

Posted on 2011-03-24
12
593 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
Comment Utility
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
Comment Utility
@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
Comment Utility
need a little more information on this one please.
0
 
LVL 1

Author Comment

by:lucky20
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 14

Assisted Solution

by:dejaanbu
dejaanbu earned 250 total points
Comment Utility
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
Comment Utility
@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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
No one posted the answer.. I got the result before experts giving answer..
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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/…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 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

15 Experts available now in Live!

Get 1:1 Help Now