inserting option values into drop down lists with getstring when using distinct in the SQL

[Bob Barrows]

I don't know what your problem is. It works fine for me. Here is my code:

<%
dim cn,rs
set cn=server.CreateObject("adodb.connection")

sSQL="SELECT job_desc FROM dbo.jobs"
cn.open "provider=sqloledb;data source=censored;" & _
"user id = censored; password=censored;initial catalog=pubs"
set rs=cn.execute(sSQL,,adCmdText)
Response.Write "<select name=""artist"">" & _
"<option selected>Choose</option>"
Response.Write("<option value='")
Response.Write RS.GetString( ,,,"</option><option>")
Response.Write("</option></select>")
RS.close
set RS = Nothing
cn.close
Set cn = Nothing
%>

HTH,
Bob Barrows

julian wrote:

> hi
>
> i have a problem here that i think may be obvious but i cant see it.
> Please can someone help?
>
> With .ASP i am pulling records from an Access database and placing
> them into a drop down list. I am using getstring() but cannot get the
> option values (line 15) for the drop down list to insert.
>
> It works if my SQL (line 3) is
> Set RS = db.Execute("SELECT DISTINCT Filed3, Field3 FROM IaxData")
> But this doesnt look efficient or right?
>
>
>
> 1. Set db = Server.CreateObject("ADODB.Connection")
> 2. db.Open "dsn=database2;"
> 3. Set RS = db.Execute("SELECT DISTINCT Field3 FROM IaxData")
> 4.
> 5. If RS.eof then
> 6. Response.Write "Sorry, No records were found.<br>"
> 7. set objRS = Nothing
> 8. db.close
> 9. Set db = Nothing
> 10 Response.End
> 11 End If
> 12
> 13 Response.Write "<select name=""artist""><option
> selected>Choose</option>"
> 14 Response.Write("<option value='")
> 15 Response.Write RS.GetString(,,,"</option><option>")
> 16 Response.Write("</option></select>")
> 17
> 18 RS.close
> 19 set RS = Nothing
> 20 db.close
> 21 Set db = Nothing
>
>
> thx anyone in advance

[Tim Williams]

Response.Write("<option value='")
Response.Write RS.GetString( ,,,"</option><option>")
Response.Write("</option></select>")

Perhaps this is not the best method, since you need to get the value twice
for each row, once for the value parameter and once for the text.

Your code would return (line breaks added for clarity)

<option value='val1</option>
<option>val2</option>
etc...
</option>
</select>

First item has no closing single quote and none of the other items have a
value attribute.


Tim.



"julian" <julian.cheung@talk21.com> wrote in message
news:1994ccb7.0307241120.2d345e3d@posting.google.c om...

> hi
>
> i have a problem here that i think may be obvious but i cant see it.
> Please can someone help?
>
> With .ASP i am pulling records from an Access database and placing
> them into a drop down list. I am using getstring() but cannot get the
> option values (line 15) for the drop down list to insert.
>
> It works if my SQL (line 3) is
> Set RS = db.Execute("SELECT DISTINCT Filed3, Field3 FROM IaxData")
> But this doesnt look efficient or right?
>
>
>
> 1. Set db = Server.CreateObject("ADODB.Connection")
> 2. db.Open "dsn=database2;"
> 3. Set RS = db.Execute("SELECT DISTINCT Field3 FROM IaxData")
> 4.
> 5. If RS.eof then
> 6. Response.Write "Sorry, No records were found.<br>"
> 7. set objRS = Nothing
> 8. db.close
> 9. Set db = Nothing
> 10 Response.End
> 11 End If
> 12
> 13 Response.Write "<select name=""artist""><option
> selected>Choose</option>"
> 14 Response.Write("<option value='")
> 15 Response.Write RS.GetString(,,,"</option><option>")
> 16 Response.Write("</option></select>")
> 17
> 18 RS.close
> 19 set RS = Nothing
> 20 db.close
> 21 Set db = Nothing
>
>
> thx anyone in advance

[Bob Barrows]

Very true.
A better technique would be (assuming SQL Server):
SQL = "Select distinct '<option value=""' + field3 + '"">'" & _
" + field3 +'</option>' from IaxData"
Set RS = db.Execute(SQL,,1)
'check for EOF then
Response.Write "<select name=""artist""><option" & _
"selected>Choose</option>" & vbcrlf
Response.Write RS.GetString(,,,vbcrlf)
Response.Write("</select>")

HTH,
Bob Barrows


Tim Williams wrote:

> Response.Write("<option value='")
> Response.Write RS.GetString( ,,,"</option><option>")
> Response.Write("</option></select>")
>
> Perhaps this is not the best method, since you need to get the value
> twice for each row, once for the value parameter and once for the
> text.
>
> Your code would return (line breaks added for clarity)
>
> <option value='val1</option>
> <option>val2</option>
> etc...
> </option>
> </select>
>
> First item has no closing single quote and none of the other items
> have a value attribute.
>
>
> Tim.
>
>
>
> "julian" <julian.cheung@talk21.com> wrote in message
> news:1994ccb7.0307241120.2d345e3d@posting.google.c om...

>> hi
>>
>> i have a problem here that i think may be obvious but i cant see it.
>> Please can someone help?
>>
>> With .ASP i am pulling records from an Access database and placing
>> them into a drop down list. I am using getstring() but cannot get the
>> option values (line 15) for the drop down list to insert.
>>
>> It works if my SQL (line 3) is
>> Set RS = db.Execute("SELECT DISTINCT Filed3, Field3 FROM IaxData")
>> But this doesnt look efficient or right?
>>
>>
>>
>> 1. Set db = Server.CreateObject("ADODB.Connection")
>> 2. db.Open "dsn=database2;"
>> 3. Set RS = db.Execute("SELECT DISTINCT Field3 FROM IaxData")
>> 4.
>> 5. If RS.eof then
>> 6. Response.Write "Sorry, No records were found.<br>"
>> 7. set objRS = Nothing
>> 8. db.close
>> 9. Set db = Nothing
>> 10 Response.End
>> 11 End If
>> 12
>> 13 Response.Write "<select name=""artist""><option
>> selected>Choose</option>"
>> 14 Response.Write("<option value='")
>> 15 Response.Write RS.GetString(,,,"</option><option>")
>> 16 Response.Write("</option></select>")
>> 17
>> 18 RS.close
>> 19 set RS = Nothing
>> 20 db.close
>> 21 Set db = Nothing
>>
>>
>> thx anyone in advance