CODEWITHSUNDEEP
sqlasp.netsql-servervbscript
Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Populate fields in multiple dropdowns based on option selected from any drop down list using Classic ASP

I found this solution to populate one combobox, but what if i need to change multiple comboboxes lists?

What is best performance will be?

  1. Run the same code with required query on each combobox opening each time new connection to Sql

  2. Get all row to vbscript array and work with it

  3. Send multiple select statement in one query

strSQL = "Select Distinct Make From Cars; Select Distinct Model From Cars;...;"

But then i do not know how to get response to variables or in array i get error

Microsoft JET Database Engine error '80040e14'

Characters found after end of SQL statement.

Upvotes: 0

Views: 982

Answers (2)

user359135
user359135

Reputation:

I don't know which is fastest.. I guess passing all queries to the DB at once then processing through the various tables returned.

Personally i would be most worried about the efficiency of maintaining and editing the code more than the execution as unless its exceptional circumstances you will notice little difference between them.

On that basis i would query, get data and populate drop down one at a time, then see how it ran and wait to be proved that this could not be done.

Using this approach you could potentially even have a sub or function that you call with a query (or table and columns) that builds the results into a drop down for you.

Well based on your example I would do something like this initially:

<select name="First">
<%
set rsRecordSet=Server.CreateObject("ADODB.recordset")
rsRecordSet.Open "Select distinct ID,FirstThing FROM FirstTable", connection
While not rsRecordSet.EOF
    %>
    <OPTION VALUE="<%=rsRecordSet("ID")%>"><%=rsRecordSet("FirstThing")%></OPTION> 
    <% 
    rsRecordSet.MoveNext 
Wend 
rsRecordSet.Close
Set rsRecordSet= nothing 
%>
</select> 

<select name="Second">
<%
set rsRecordSet=Server.CreateObject("ADODB.recordset")
rsRecordSet.Open "Select distinct ID,SecondThing FROM SecondTable", connection
While not rsRecordSet.EOF
    %>
    <OPTION VALUE="<%=rsRecordSet("ID")%>"><%=rsRecordSet("SecondThing")%></OPTION> 
    <% 
    rsRecordSet.MoveNext 
Wend 
rsRecordSet.Close
Set rsRecordSet= nothing 
%>
</select> 

<select name="Third">
<%
set rsRecordSet=Server.CreateObject("ADODB.recordset")
rsRecordSet.Open "Select distinct ID,ThirdThing FROM ThirdTable", connection
While not rsRecordSet.EOF
    %>
    <OPTION VALUE="<%=rsRecordSet("ID")%>"><%=rsRecordSet("ThirdThing")%></OPTION> 
    <% 
    rsRecordSet.MoveNext 
Wend 
rsRecordSet.Close
Set rsRecordSet= nothing 
%>
</select>

Upvotes: 1

Zam
Zam

Reputation: 2940

way №4.

run everything in one query, result will be in one single dataset.

strSQL = "Select Distinct Make as Value, 'S1' as SelectionNumber From Cars union all Select Distinct Model as Value, 'S2' as SelectionNumber From Cars"

in this case you will be know that SelectionNumber field contain your ID of query...

What is best performance will be?

I suggest you to use SQL Server Profiler to measure speed of your queries.

Upvotes: 1

Related Questions