asked on

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

ASKER CERTIFIED SOLUTION

Wayne Barron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allan Martins

ASKER

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!

Wayne Barron

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

Allan Martins

ASKER

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

Wayne Barron

hello allan.
Send me over a demo of your database, and I will make you up something.

Carrzkiss

Allan Martins

ASKER

This should create the database structure for for that.
db-creation.txt
db-insert-simple-data.txt

Wayne Barron

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?

SOLUTION

Wayne Barron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allan Martins

ASKER

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.

Wayne Barron

Hello Allan.
Forgot all about this one.

OK.
Not a problem.

Carrzkiss

Allan Martins

ASKER

carrzkiss, these links are not working, is there another server so I can download it?/

Thanks

Wayne Barron

yea they are.
Or, I should say, they are now.

There was some server issues earlier.

Carrzkiss

Wayne Barron

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

Have a good one.
Carrzkiss