Populating dropdown menu using classic asp and mySQL

Hi all,

Being fairly new to coding in general. I have a page with dropdown menus available. I would like one of these to be customer  controlled and updateable. I know how to insert and update values in a mySQL database. However populating a dropdown menu using data from that database is an issue. I have 3 records in my database (Table name: Lokation - Column name: Lokation_navn). The below code clearly appears to be displaying these 3 records (it is 3 records long when I view it on the web page). However there is no text visible???? Any help and/or direction much appreciated!

Here is my code - strConnect represents the database credentials and comes from an include file at the top of the page:

<%
Dim objconn,objRS,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT distinct(Lokation_navn) FROM Lokation ORDER BY Lokation_navn Desc"
objRS.Open strSQL, objconn
Response.Write "<select name=lokation><option value='''' selected>Vælg lokation</option>"
Do While Not objRS.EOF 
Response.Write "<option value=''" & objrs("Lokation_navn") &"''>"& objRs("Lokation_navn") &"</option>"
objRS.MoveNext
Loop
Response.Write "</select>"
objRs.Close
objconn.Close

%>

Open in new window

Michael AmbechAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
looks like you have an extra set of single quotes surrounding your VALUE attribute, which could be screwing things up. try the following:

<%
Dim objconn,objRS,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT distinct(Lokation_navn) FROM Lokation ORDER BY Lokation_navn Desc"
objRS.Open strSQL, objconn
Response.Write "<select name='lokation'><option value='' selected>Vælg lokation</option>"
Do While Not objRS.EOF 
Response.Write "<option value='" & objrs("Lokation_navn") &"'>"& objRs("Lokation_navn") &"</option>"
objRS.MoveNext
Loop
Response.Write "</select>"
objRs.Close
objconn.Close

%>

Open in new window

sammySeltzerCommented:
A much cleaner way to populate your dropdown.

     <%
	  strSQL = "SELECT distinct(Lokation_navn) FROM Lokation ORDER BY Lokation_navn Desc"
	     set objRS = objconn.Execute(strSQL)
            %>
            <select name="lokation">
            <option value="">Vælg lokation</option>
            <%
             Do Until objRS.EOF
              lokationID = objRS("Lokation_navn")
             If lokationID = lokation Then sel = "SELECTED" Else sel = ""
            %>
            <OPTION Value="<%=lokationID%>" <%=sel%> > <%=objRS("Lokation_navn")%>
           <%
            objRS.MoveNext
            Loop
            %>
          </select>

Open in new window


First, you can spot an error very quickly.

Second, if value exist, it is becomes the default value.

Otherwise, it is blank with selected text being the default message.
Michael AmbechAuthor Commented:
HI both, thanks for your responses. I tried both, but none of them solved the problem though. I added two more records to the database - just to be sure. As you can see in the attached file, the dropdown list is still empty - but now it contains 5 records insted of three (excluding the selected one) - so the data is there?
This is how it looks
My database contains two columns as mentioned. Column setup posted here.

Id - MediumInt(11) - AUTOINCREMENT - This column is defined as an index (BTREE)
Lokation_navn - Text - utf8_danish_ci

Hope any of the info provided gave you something to Work with.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Michael AmbechAuthor Commented:
Hi Again - I found the error - Changing the data column Lokation_navn format from Text to Varchar  solved the issue.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne BarronAuthor, Web DeveloperCommented:
Accept yourself as answer, and award the points to one of the above or both experts for their assisted code.

I myself, like sammySeltzer's code, as it is clean, and helps you to troubleshoot much better than using the response.write all over your code.
(Do not accept this as assisted answer)
Good Luck with your ASP Classic Work.
Carrzkiss
Michael AmbechAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for hjalteambech's comment #a41207745
Assisted answer: 50 points for The_Big_Daddy's comment #a41205985
Assisted answer: 50 points for sammySeltzer's comment #a41206966
Assisted answer: 400 points for carrzkiss's comment #a41240664

for the following reason:

Changing database column datatype to VARCHAR solved the problem. Thanks for all your input!
sammySeltzerCommented:
what??????
Michael AmbechAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for hjalteambech's comment #a41207745
Assisted answer: 100 points for The_Big_Daddy's comment #a41205985
Assisted answer: 200 points for sammySeltzer's comment #a41206966

for the following reason:

It solved the underlying problem
Michael AmbechAuthor Commented:
Okay... Maybe I'm just too stupid for this website... The usability regarding awarding points to me at least is rather confusing...
Michael AmbechAuthor Commented:
I give up... Tried givng sammy 300 and monty 200. Didn't happen... Sorry guys!
sammySeltzerCommented:
Of course you can award yourself the points.
Michael AmbechAuthor Commented:
And if I actually read Mr. Wolfes comments and followed his links I would have been fine to begin with. Sorry guys... I'm off to stand upside down in a bucket of piranha fish....
Michael AmbechAuthor Commented:
A said before ... It solved the underlying problem. Is it okay to award myself points? I have no problem with distributing it between Sammy and Big Monty. Sorry about the confusion. To me at least it is rather annoying being asked to grade (taken to the bottom of the page) when you are reviewing answers for grading.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.