Populate drop down list using Classic ASP

[jasonhuibers]

ODBC: TestDB (SQL server 2008)

I am unable to populate the list. When I run the query there is data according to the sql statement. Can someone tell me whats wrong with the below statement?????


<select name="group">

<%
Dim sql_insert
Dim drs

data_source = "TestDB"

sql_insert = "SELECT DISTINCT Community FROM Communities"
set conn2 = CreateObject("ADODB.Connection")
conn2.open data_source
set drs = conn2.execute(sql_insert)

do while not drs.eof %>

<option value="<% = drs("Community") %>"></option>

<% drs.MoveNext
Loop
conn2.close
set conn2 = nothing %>

</select>

 

[foxbox]

You configured "TestDB" via ODBC? Try replacing
data_source = "TestDB"
with
data_source = "DSN=TestDB"

 

[jasonhuibers]

There are 276 records in the table. The list is appearing with 276 blank values. The list is not actually populating with the recirds in the db but the list contains the number of records in that table???

 

[foxbox]

aha, i see it now:
change

<option value="<% = drs("Community") %>"></option>

into
<option><% = drs("Community") %>"></option>

 

[jasonhuibers]

No it doesnt work - I get the drop down with "> appearing in the list....

 

[foxbox]

sorry, my cut/past was too fast.
remove the "> before </option>:

<option><% = drs("Community") %></option>

 

[tsuji]

[tt]<option value="<%= drs("Community").value %>"><%= drs("Community").value %></option>[/tt]

 

[jasonhuibers]

Oh man, still no value appearing in list rather only number of blanks equal to number of records in table from query.

Here is the exact code being used:

<select name="Community">

<%
Dim sql_insert
Dim drs

data_source = "DSN=TestDB"
sql_insert = "SELECT DISTINCT Community FROM Communities"
set conn2 = CreateObject("ADODB.Connection")
conn2.open data_source

set drs = CreateObject("adodb.recordset")
drs.open sql_insert,conn2

do while not drs.eof %>
<option><% = drs("Community") %></option>
<% drs.MoveNext
Loop
conn2.close
set conn2 = nothing %>
</select>

 

[foxbox]

is it possible to run the query "SELECT DISTINCT Community FROM Communities" with MSSQL VS? Then you can actually see what the result set contains.
Maybe you need to select another field..
oh: and normally it's easier to order the pulldown list...

 

[tsuji]

I take all the statements (like the number of blank is expected as the number of records, meaning there is no problem in the connection and looping etc), just for testing replace this line see what is displayed.

><option><% = drs("Community") %></option>

[tt]<option value="<%= escape(drs("Community").value) %>"><%= escape(drs("Community").value) %></option> [/tt]

For what reason you don't like to have value attribute scripted? If your client use ie, it is necessary except the script is a toy script.

 

[jasonhuibers]

Wow, with your suggestion the results in the drop down list are as follows:

1st item in list: %00%00%00%00%00%00%u0332%00%
2nd item in list: %00%00%00%00%00%00%u0332

 

[tsuji]

Those of course are unicode ucs2 encoding. If you try to select the encoding and switch back to the original version of the line, maybe you can still not see those "underlines" (no-spacing underscore). So, at the end, you have to check what are the data really stored in the field.

 

[jasonhuibers]

RESOLVED issue was column was set to varchar(max) and for some reason this does return any data when I change to varchar(50) it works fine...