Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

dynamic drop down box values

Posted on 2011-03-24
12
Medium Priority
?
603 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

963 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