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

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

LVL 2
Allan MartinsICT TechnicianAsked:
Who is Participating?
 
Wayne BarronConnect With a Mentor Author, Web DeveloperCommented:
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
 
Allan MartinsICT TechnicianAuthor Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Allan MartinsICT TechnicianAuthor Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
hello allan.
Send me over a demo of your database, and I will make you up something.

Carrzkiss
0
 
Allan MartinsICT TechnicianAuthor Commented:
This should create the database structure for for that.
db-creation.txt
db-insert-simple-data.txt
0
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
Wayne BarronConnect With a Mentor Author, Web DeveloperCommented:
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
 
Allan MartinsICT TechnicianAuthor Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
Hello Allan.
Forgot all about this one.

OK.
Not a problem.

Carrzkiss
0
 
Allan MartinsICT TechnicianAuthor Commented:
carrzkiss, these links are not working, is there another server so I can download it?/

Thanks
0
 
Wayne BarronAuthor, Web DeveloperCommented:
yea they are.
Or, I should say, they are now.

There was some server issues earlier.

Carrzkiss
0
 
Wayne BarronAuthor, Web DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.