Dropdown menu feeded from MS SQL doesn't work...

Hi out there.

I have a weird issue. I converted a MySQL database to MS SQL. I use the same Classic asp interface, but of course I have to make some alterations related to SQL.
I have this dropdown menu that pulls some values from a table in the database. It used to Work fine with MySQL, but now it wont Work anymore. Here's the codes:

Recordset:

<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]"
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0
%>

And here's the dropdown menu:

<select name="diagnose_icd10" id="diagnose_icd10">
            <option value="null"></option>
            <%
While (NOT Recordset10.EOF)
%>
            <option value="<%=(Recordset10.Fields.Item("icd10").Value)%>" ><%=(Recordset10.Fields.Item("icd10").Value)%></option>
            <%
  Recordset10.MoveNext()
Wend
If (Recordset10.CursorType > 0) Then
  Recordset10.MoveFirst
Else
  Recordset10.Requery
End If
%>
          </select>


What am I doing wrong?  The dropdown menu is empty... and it should be stuffed. The SQL view Works fine.

Best regards

Ulrich
ullenulleAsked:
Who is Participating?
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.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
When you are using dreamweaver to create this recordset and you test the recordset in dreamweaver, can you see records?

What do you get if you try this on a page,
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0

do until Recordset10.eof
    response.write Recordset10.Fields.Item("icd10").Value&"<br>"
Recordset10.movenext
loop

Open in new window


If you are using this recordset multiple times on the page, the more appropriate thing to do would be using getrows to send the data to an array and then you can use the array over and over without going back to the database.   For now, give this a try.   If it does not work, can you click on your view in sql manager studio and copy and paste the sql and paste it back here.   All I am looking for is everything being spelled correctly with matching upper and lower case characters.  You can of course do that on your own.
0
ullenulleAuthor Commented:
Hi Scott.

Thanks for your response. When I add the "do until....", then all values show up just fine. But they still doesn't show up in the dropdown menu... Weird!

Best regards

Ulrich
0
ullenulleAuthor Commented:
Oh... and I only use the recordset once on the page... and as in your suggestion, then I can also see all records in the view via MySQL Workbench.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0
%>
<select name="diagnose_icd10" id="diagnose_icd10">
<%
do until Recordset10.eof
    response.write "<option value='"&Recordset10.Fields.Item("icd10").Value&"'>"&Recordset10.Fields.Item("icd10").Value&"</option>"
Recordset10.movenext
loop
%>
</select>

Open in new window

0
ullenulleAuthor Commented:
Originally I had a "CONCAT" in the recordset when I used MySQL. That worked fine. Then when it didn't Work in MS SQL, then I thought it could help to avoid the "+" in the Microsoft SQL edition of CONCAT and make a view.
Before I made the view I tried to narrow Down to only one variable, and then all showed up. But obviously the dropdown menu doesn't like the combination of two variablels (number + text).
0
ullenulleAuthor Commented:
Your select-suggestion didn't Work either...
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
View the source and what you see
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Make sure you are testing this ONLY with the code I posted and nothing else except your connection string of course.

If this works
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0

do until Recordset10.eof
    response.write Recordset10.Fields.Item("icd10").Value&"<br>"
Recordset10.movenext
loop

Open in new window

Then this should work
<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0
%>
<select name="diagnose_icd10" id="diagnose_icd10">
<%
do until Recordset10.eof
    response.write "<option value='"&Recordset10.Fields.Item("icd10").Value&"'>"&Recordset10.Fields.Item("icd10").Value&"</option>"
Recordset10.movenext
loop
%>
</select>

Open in new window

0
ullenulleAuthor Commented:
I tried Again. All values print out fine on the page, but the dropdown menu is still empty... Weird part is, that is appears as is something is in the menu. The scroll-bar is there as if all 135 values were there...
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Did you view the source?   copy and paste that here.

Also try this in your sql asp code try to output just 10 values.  

Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10] LIMIT 10"
0
ullenulleAuthor Commented:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '10'.

 LIMIT is for MySQL only. :-)  But I used "SELECT TOP 10...". The dropdown is still empty BUT it's shorter... as if there would be 10 rows...
What do you mean if I viewed the source?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I'm sorry, I thought you said MySql in MS SQL it is

Recordset10_cmd.CommandText = "SELECT TOP 10 icd10 FROM [KFE_DB].[dbo].[view_icd10] "
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
When you view the source in the browser, can you see the code being generated?  Maybe something is mangled. I probably left out a piece.  

Does this work?
<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0
%>
<select name="diagnose_icd10" id="diagnose_icd10">
<%
do until Recordset10.eof
    %>
     <option value="123">Test 123</option>
   <%
Recordset10.movenext
loop
%>
</select>

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
If that last bit worked...

<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0
%>
<select name="diagnose_icd10" id="diagnose_icd10">
<%
do until Recordset10.eof
    %>
     <option value="<%=Recordset10.Fields.Item("icd10").Value%>"><%=Recordset10.Fields.Item("icd10").Value%></option>
   <%
Recordset10.movenext
loop
%>
</select>

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0
%>
<select name="diagnose_icd10" id="diagnose_icd10">
<%
do until Recordset10.eof

     response.write "<option value='"&Recordset10.Fields.Item("icd10").Value&"'>"&Recordset10.Fields.Item("icd10").Value&"</option>"

Recordset10.movenext
loop
%>
</select>

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Example putting results into an array using getrows.  It allows multiple loops of the data without hitting the db again (requery). Also faster for outputting a lot of data.

<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0

arrayDropDown = Recordset10.getrows()

arrayDropDown.Close ' close recordset
Set arrayDropDown = Nothing

response.write "<select name='diagnose_icd10=' id='diagnose_icd10'>"
 For r = LBound(arrayDropDown, 2) To UBound(arrayDropDown, 2)
   		theID		= arrayDropDown(0,r)
                    response.write "<option value='"&theID&"'>"&theID&"</option>"

next
response.write "</select>
response.write "<h2>Repeat Again</h2>"
 For r = LBound(arrayDropDown, 2) To UBound(arrayDropDown, 2)
   		theID		= arrayDropDown(0,r)
                    response.write theID&"<br>"

next
response.write "<h2>Repeat Again</h2>"
 For r = LBound(arrayDropDown, 2) To UBound(arrayDropDown, 2)
   		theID		= arrayDropDown(0,r)
                    response.write theID&"<br>"

next

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0

arrayDropDown = Recordset10.getrows()

' below 2 lines get changed
Recordset10.Close ' close recordset
Set Recordset10 = Nothing

response.write "<select name='diagnose_icd10=' id='diagnose_icd10'>"
 For r = LBound(arrayDropDown, 2) To UBound(arrayDropDown, 2)
   		theID		= arrayDropDown(0,r)
                    response.write "<option value='"&theID&"'>"&theID&"</option>"

next
response.write "</select>
response.write "<h2>Repeat Again</h2>"
 For r = LBound(arrayDropDown, 2) To UBound(arrayDropDown, 2)
   		theID		= arrayDropDown(0,r)
                    response.write theID&"<br>"

next
response.write "<h2>Repeat Again</h2>"
 For r = LBound(arrayDropDown, 2) To UBound(arrayDropDown, 2)
   		theID		= arrayDropDown(0,r)
                    response.write theID&"<br>"

next

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
one other thing to consider if Scott's suggestions don't resolve the issue.

does your field that populates Recordset10.Fields.Item("icd10").Value contain any special chars, or a double quote? if so, that would mess up the rendering of the html and cause the dropdown to appear empty
0
ullenulleAuthor Commented:
Hi Again.

Of course... "source"... doohhh.. I had a blonde moment. ;-)   That was a big surprise. Here's the code for the dropdown menu:

<select name="diagnose_icd10" id="diagnose_icd10" style="width:250">
            <option value="null"></option>
                        <option value="C00 - Læbekræft" ></option>
                        <option value="C01 - Kræft i basis af tunge" ></option>
                        <option value="C02 - Kræft i andre og ikke specificerede dele af tungen" ></option>
                        <option value="C03 - Kræft i tandkødet" ></option>
                        <option value="C04 - Kræft i mundgulvet" ></option>
                        <option value="C05 - Kræft i ganen" ></option>
                        <option value="C06 - Kræft i andre og ikke specificerede dele af mundhulen" ></option>
                        <option value="C07 - Kræft i ørespytkirtel" ></option>
                        <option value="C08 - Kræft i andre og ikke specificerede store spytkirtler" ></option>
                        <option value="C09 - Kræft i tonsiller" ></option>
                      </select>

See? 10 options (after the TOP 10 in the recordset). But they're completely invisible in the browser. So now we know, that it actually pulls the data from the database.
So can is be, that Classic asp doesn't Work well with data from MS SQL? Why is it invisible this way?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
looks like there are special chars coming from the database, try changing your option output line to:

<option value="<%=Server.HTMLEncode( Recordset10.Fields.Item("icd10").Value  )%>" ><%=Server.HTMLEncode( Recordset10.Fields.Item("icd10").Value )%></option>
0
ullenulleAuthor Commented:
I got a VBScript runtime error: Type mismatch: 'HTMLEncode'...
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post the code you used?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Try using this as your template.  Or make sure the top line is codepage 65001 and in your html you are specifying utf-8

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>test</title>
</head>
<body>
<%
Dim Recordset10
Dim Recordset10_cmd
Dim Recordset10_numRows

Set Recordset10_cmd = Server.CreateObject ("ADODB.Command")
Recordset10_cmd.ActiveConnection = MM_xxxxxx_STRING
Recordset10_cmd.CommandText = "SELECT icd10 FROM [KFE_DB].[dbo].[view_icd10]" 
Recordset10_cmd.Prepared = true

Set Recordset10 = Recordset10_cmd.Execute
Recordset10_numRows = 0
%>
<select name="diagnose_icd10" id="diagnose_icd10">
<%
do until Recordset10.eof
    %>
     <option value="<%=Recordset10.Fields.Item("icd10").Value%>"><%=Recordset10.Fields.Item("icd10").Value%></option>
   <%
Recordset10.movenext
loop
%>
</select>
</body>
</html>

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Actually, you do not have anything between the option tags.  Can you post the asp code you are using?

<select name="diagnose_icd10" id="diagnose_icd10" style="width:250">
            <option value="null"></option>
                        <option value="C00 - Læbekræft" ><!-- this is blank b--></option>
        
                      </select>

Open in new window

0
ullenulleAuthor Commented:
<select name="diagnose_icd10" id="diagnose_icd10" style="width:250">
            <option value="null"></option>
            <%
While (NOT Recordset10.EOF)
%>
			<option value="<%=Server.HTMLEncode(Recordset10.Fields.Item("icd10").Value)%>" ><%=Server.HTMLEncode(Recordset10.Fields.Item("icd10").Value)%></option>
            <%
  Recordset10.MoveNext()
Wend
If (Recordset10.CursorType > 0) Then
  Recordset10.MoveFirst
Else
  Recordset10.Requery
End If
%>
          </select>

Open in new window


And I do have the same codepage and utf-8...
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Let's get rid of the while/wend please and use do/loop instead.  Also get rid of that if/then.  That is old dreamweaver code not needed.

<select name="diagnose_icd10" id="diagnose_icd10" >
            <option value="null"></option>
            <%
do until Recordset10.EOF
%>
			<option value="<%=Server.HTMLEncode(Recordset10.Fields.Item("icd10").Value)%>" ><%=Server.HTMLEncode(Recordset10.Fields.Item("icd10").Value)%></option>
  <%
  Recordset10.MoveNext()
Loop
%>
          </select>

Open in new window

IF that does not work, try this for fun
<select name="diagnose_icd10" id="diagnose_icd10" >
            <option value="null"></option>
            <%
do until Recordset10.EOF
%>
			<option value="<%=Server.HTMLEncode(Recordset10.Fields.Item("icd10").Value)%>" ><%=Server.HTMLEncode(Recordset10.Fields.Item("icd10").Value)%>TESTING 123</option>
  <%
  Recordset10.MoveNext()
Loop
%>
          </select>

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Another option

<select name="diagnose_icd10" id="diagnose_icd10" >
            <option value="null"></option>
            <%
do until Recordset10.EOF
theData = Recordset10.Fields.Item("icd10").Value
%>
			<option value="<%=theData%>" ><%=theData%></option>
  <%
 theData=""
  Recordset10.MoveNext()
Loop
%>
          </select>

                    

Open in new window

0

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
ullenulleAuthor Commented:
Hello.

Just for your info: I'm on vacation for another 12 days, so I will return to this issue after that. :-)

best regards

Ulrich
0
ullenulleAuthor Commented:
Hey Scott!

I'm back Again. Sorry for my loooooooooong response. I just tested all your suggestions, and the last one seems to Work!!!!!! :-)  Can you explain what made the difference? "theData"? Where does that come from?

Best and happy regards

Ulrich
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
theData = Recordset10.Fields.Item("icd10").Value

That is a value from your recordest field icd10
0
ullenulleAuthor Commented:
Ooops... doohh... Of course. I missed that. :-)  Thank you BIG time!

Best regards

Ulrich
0
ullenulleAuthor Commented:
Thank you Again BIG time for your patience! :-)
0
ullenulleAuthor Commented:
Hello Scott? Still here? I will open a new question with the same subject, but I'm sure you know the answer. ;-)  It's the same dropdown menu. I just need stored values to be selected in the dropdown menu. I can't make it Work. Any suggestion?
And Again: I will create this as a new question once I got hold of you. ;-)

Best regards

Ulrich
0
ullenulleAuthor Commented:
I just figured it out myself! ;-)  Thanks to your previous responses.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Sorry, I have been very busy with my own stuff lately. I'm glad you have it worked out!
0
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.