?
Solved

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

Posted on 2009-04-02
15
Medium Priority
?
638 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:Allan Martins
  • 8
  • 5
13 Comments
 
LVL 31

Accepted Solution

by:
Wayne Barron earned 504 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:Allan Martins
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 31

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:Allan Martins
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 31

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:Allan Martins
ID: 24064457
This should create the database structure for for that.
db-creation.txt
db-insert-simple-data.txt
0
 
LVL 31

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 31

Assisted Solution

by:Wayne Barron
Wayne Barron earned 504 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:Allan Martins
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 31

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:Allan Martins
ID: 24198794
carrzkiss, these links are not working, is there another server so I can download it?/

Thanks
0
 
LVL 31

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 31

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
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…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

840 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