Solved

ASP Form - Dropdown menu with data from SQL (and default value set)

Posted on 2009-04-02
15
621 Views
Last Modified: 2012-05-06
Hi there,

I've been looking in a way on ASP to query a table and fill with all the values on it, then, it should set the default value from the item code (which will be assigned to a variable).

Follow part of the code I've been using. I was able to do something with it, but it fills the drop down menu with the values, set the default value based on the variable, but it generates the dropdown menu like that:

Value 1
<blank space>
Value 2
<blank space>
Value 3
<blank space>
Value 4
<blank space>

Any idea how I can solve this???
<%          	Dim computer
          	computer = request.querystring("computer")
 
openconnection
sql = "select tblComputers.FKComputerTypeID, tblComputers.FKComputerModelID, [...]
 
Set rs=cn.execute(sql)                                                                         	                               
 
Dim ComputerID
Dim ComputerTypeID
[...]
 
ComputerID = rs.Fields("PKComputerID")
ComputerTypeID = rs.Fields("FKComputerTypeID")
[...]
 
%>
 
here comes the coding for the page...
 
[.................]
 
 <%
openconnection
sql = "select * from tblComputerType"
Set rs=cn.execute(sql) 
%>
 
<select name="lstComputerType" size="1" id="lstComputerType">
<%
	Do While not rs.Eof 
	If ComputerTypeID = rs("PKComputerTypeID") Then
	Response.Write("<option value='" & rs("PKComputerTypeID") & "' selected>" & rs("ComputerTypeDesc") & "<option>")
	Else
	Response.Write("<option value='" & rs("PKComputerTypeID") & "'>" & rs("ComputerTypeDesc") &"<option>")
	End If
  
  rs.MoveNext
  Loop
%>
              </select>

Open in new window

0
Comment
Question by:AllanMartins
  • 8
  • 5
15 Comments
 
LVL 30

Accepted Solution

by:
Wayne Barron earned 126 total points
ID: 24055965
Something like this

Let me know if this is what you are wanting?

Carrzkiss
<select name="WkImage" style="font: 8pt verdana;"  OnChange=SelectPic(this);>
<option value="<%=rs("WeeklyImage")%>"><%=rs("ComputerTypeDesc")%></option>
                                      <%
While (NOT rs.EOF)
%>
<option value="<%=(rs.Fields.Item("PKComputerTypeID").Value)%>" <%if (CStr(rs.Fields.Item("ComputerTypeDesc").Value) = CStr(rs.Fields.Item("PKComputerTypeID").Value)) then Response.Write("SELECTED") : Response.Write("PKComputerTypeID")%>><%=(rs.Fields.Item("ComputerTypeDesc").Value)%></option>
<%
  rs.MoveNext()
Wend
If (rs.CuRSMorType > 0) Then
  rs.MoveFiRSMt
Else
  rs.Requery
End If
%>
</select>

Open in new window

0
 
LVL 2

Author Comment

by:AllanMartins
ID: 24056199
Thanks for the answer carrzkiss,

Well, this one populated the list with all the data, but it places the first record as the "selected" option. It's not the solution I've been looking for.

Thanks anyway!
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24056229
You need to read the actual record from your database.
So, lets say that your recordset is:

sql = "SELECT record1, recordID FROM Table1 WHERE ID = " & request.quesrystring("id")&""
rs1.open..........

rs1("ComputerTypeDesc")  ' as long as the ID is in the Querystring, you will get it's value
(It is different from your)
rs("ComputerTypeDesc") ' This is your existing recordset that reads the records into the ComboBox.

Basically.

1 recordset to show all records (The dropdown list)   rs
1 recordset to show the record that is choosen....      rs1

Do you understand?
Carrzkiss
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 2

Author Comment

by:AllanMartins
ID: 24058411
I get a little bit of the idea, but I could not understand how I can fill the dropdown list with that code.

Thanks,
Allan
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24062534
hello allan.
Send me over a demo of your database, and I will make you up something.

Carrzkiss
0
 
LVL 2

Author Comment

by:AllanMartins
ID: 24064457
This should create the database structure for for that.
db-creation.txt
db-insert-simple-data.txt
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24064546
I do not have SQL Server.
I use Access to do all my work.

Do you have the ability to make a demo Access database?
0
 
LVL 30

Assisted Solution

by:Wayne Barron
Wayne Barron earned 126 total points
ID: 24064708
I hope this is what you are wanting....

http://ee.cffcs.com/Q_24290861/Q_24290861.asp?id=1
http://ee.cffcs.com/Q_24290861/Q_24290861.asp?id=2
http://ee.cffcs.com/Q_24290861/Q_24290861.asp?id=3

Just change the ID= to what ever you want up to 10
And it will select the option for you.
Code
http://ee.cffcs.com/Q_24290861/Q_24290861.zip

Let me know if this is what you want.
And it is done using Access database, and I am pretty sure that you will be able to use in your SQL
As everyone that I have built these for in access that use SQL have never had a problem.

Good Luck
Carrzkiss
0
 
LVL 2

Author Comment

by:AllanMartins
ID: 24183205
Sorry for takint to long to reply, I've been out on a business trip and I couldnt go any furhter on that.

I should take a look later today.
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24183317
Hello Allan.
Forgot all about this one.

OK.
Not a problem.

Carrzkiss
0
 
LVL 2

Author Comment

by:AllanMartins
ID: 24198794
carrzkiss, these links are not working, is there another server so I can download it?/

Thanks
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24199156
yea they are.
Or, I should say, they are now.

There was some server issues earlier.

Carrzkiss
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24410596
The code that does what the Asker Wants is here: http:Q_24290861.html?cid=238#a24064708
(Links work)

Have a good one.
Carrzkiss
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax for query to update table 2 29
VBScript on Html 15 47
CREATE DATABASE 3 28
ORA-06502: PL/SQL: numeric or value error: character to number conversion error 4 55
An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

856 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