Solved

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

Posted on 2009-04-02
15
613 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:AllanMartins
Comment Utility
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
Comment Utility
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
Comment Utility
This should create the database structure for for that.
db-creation.txt
db-insert-simple-data.txt
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hello Allan.
Forgot all about this one.

OK.
Not a problem.

Carrzkiss
0
 
LVL 2

Author Comment

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

Thanks
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The viewer will learn how to dynamically set the form action using jQuery.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now