SELECT statement in ASP please help

jennypretty · November 24th, 2008, 03:18 PM

Hello,

I do not know asp much, only the basic. I have this question. I am not sure if it is possible to do or not.

I have one table called "cars". I like to create an ASP page to insert into a new table (same db). On this
INSERT page, I like to use the SELECT option to select from "Cars" table. I have two selection boxes.

When a user select car A from the first SELECT box, the 2nd box automatically adjust to have all dates for cars A that were sold in the last 2 months
only. No values for car B and C from the 2nd SELECT box when A was selected on first SELECT box.

Cars Date-Sold
--- ---------
A 02/09/2008
B 3/08/2008
C 8/02/2008
B 9/04/2008
A 11/02/2008
A 10/04/2008

Can you please help me for writing a simple script to do this?

I am too new in ASP.

Big thanks.

Have a good day.
Jenny.

http://www.asiafuns.com/asian_friends/

mat41 · November 24th, 2008, 06:44 PM

Hello - Have you got some code in place which you would like help with or a are you expecting to get the page written for you?

IME - More people will be inclined to give you thier time if you have a go, get stuck and post your problematic code.

There are several ways to do this. Conceptualy I would:

> Populate your two select boxes
> Then place an onChange event inside the select tag of the first select box. This submits a form (remember you can have more than one form on a page. This form would be there just for the onChange event)
> So when a user selects a car (the first select box) the onChange is fired, submits the form and conditionaly alters the contents of the second select boxes content

EG the first select tags onChange event:

<select name="myCarList" onChange="Javascript:formName.submit();">

And the condition which checks if the user has selected a car:

<% if trim(request.form("myCarList")) <> "" then
      'write your SQL for the second select list here. if this condition is entered into the user has selected an option from the car list therefore conditionaly show the seconf list.
   else
      'this is the default page load. Write the SQL which gets all the records you would initialy like for the second select list here
   end if
%>

Wind is your friend
Matt
www.elitemarquees.com.au

jennypretty · November 25th, 2008, 02:52 PM

Here is my code. It does not work.
Can you please help me why my code below does not work?

thanks.

Code:

<%@ Language=VBScript %>
<%Option explicit
Dim oRs, conn, connect, strSQL

set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../../courses.mdb") & ";Persist Security Info=False"
conn.Open connect

%>
<html>
<head>
<title>Example combo box</title>

<script language="javascript">
<!--

function dept_onchange(frmSelect) {
    frmSelect.submit(); 
}

//-->
</script>
</head>
<body>
<form name="frmSelect" method="Post" action="selectcombo.asp">
<% 
Dim carsValue
    carsValue = Request.Form("carSelect") %>
<SELECT name=cars onchange="Javascript:dept_onchange.frmSelect.submit()">
    <%        
    Set oRs=Server.CreateObject("adodb.recordset")
    strSQL = "SELECT DISTINCT Cars FROM tblComboSelect ORDER BY Cars"
    oRs.Open strSQL, conn        

    while not oRs.EOF
            Response.Write "<OPTION VALUE = '" & oRS ("cars") & "' SELECTED>"
            Response.Write oRs("cars") & "</Option>"
            oRs.MoveNext 
    wend        
    %>
</SELECT>

<% if trim(request.form("Cars")) <> "" then %>
    <SELECT name=cars onchange="Javascript:dept_onchange.frmSelect.submit()">
    <%        
    Set oRs=Server.CreateObject("adodb.recordset")
    strSQL = "SELECT Date-Sold FROM tblComboSelect"
    oRs.Open strSQL, conn        

    while not oRs.EOF
            Response.Write "<OPTION VALUE = '" & oRS ("Date-Sold") & "' SELECTED>"
            Response.Write oRs("Date-Sold") & "</Option>"
            oRs.MoveNext 
    wend        
    %>
</SELECT>
<% Else %>
<SELECT name=cars onchange="Javascript:dept_onchange.frmSelect.submit()">
    <%        
    Set oRs=Server.CreateObject("adodb.recordset")
    strSQL = "SELECT Date-Sold FROM tblComboSelect"
    oRs.Open strSQL, conn        

    while not oRs.EOF
            Response.Write "<OPTION VALUE = '" & oRS ("Date-Sold") & "' SELECTED>"
            Response.Write oRs("Date-Sold") & "</Option>"
            oRs.MoveNext 
    wend        
    %>
</SELECT>
<%   end if
%>
</form>
</body>
</html>

http://www.asiafuns.com/asian_friends/

mat41 · November 25th, 2008, 07:21 PM

In future you need to post detaield arror messages, line numbers etc.... help other help you!!!

Anyhow since you 'had a go' I have altered all the code between the <html> and the </html> tags.
There are alot of things wrong with your code, almost to many to mention! Study the differences
to learn. BTW - I have tested the code also.

<html>
<head>
<title>Example combo box</title>
</head>
<body>
<b>Select a car type to view dates sold</b>
<br><bR>
<form name="frmSelect" method="Post" action="selectcombo.asp">
<table border="0">
<tr>
  <td>Select Car Type :</tD>
  <td>
<% Set oRs=Server.CreateObject("adodb.recordset")
    strSQL = "SELECT DISTINCT Cars FROM tblComboSelect ORDER BY Cars;"
    oRs.Open strSQL, conn
    if not oRs.eof then %>
       <SELECT name="cars" onChange="Javascript:frmSelect.submit();">
<% do until oRs.EOF %>
          <OPTION VALUE="<%= oRS(0) %>" <% if trim(request.form("Cars")) = trim(oRS(0)) then response.write " selected " end if %>><%= oRS(0) %></option>
<% oRs.MoveNext
       loop %>
       </SELECT>
<% else %>
       <i>No cars found in the database</i>
<% end if %>
   </tD>
  </tr>
  <tr>
   <td>Date(s) Sold :</tD>
   <td>
<% if trim(request.form("Cars")) <> "" then
      'user has selected a car therefore show dates select box
      strSQL = "SELECT [Date-Sold] FROM tblComboSelect WHERE Cars='" & trim(request.form("Cars")) & "';"
      Set oRs=Server.CreateObject("adodb.recordset")
      oRs.Open strSQL, conn
      if not oRs.eof then %>
         <select name="dateSold">
<% do until oRs.eof %>
            <option value="<%= ors(0) %>"><%= ors(0) %></option>
<% oRs.MoveNext
         loop %>
         </select>
<% else %>
         <i>No records found for that car</i>
<% end if
   else
      'user has not selected a car therefore do not show date select box
      response.write "<i>No car selected yet</i>"
   end if %>
   </tD>
  </tr>
</table>
</form>
</body>
</html>

Wind is your friend
Matt
www.elitemarquees.com.au

Old Pedant · November 25th, 2008, 10:57 PM

This is a minor fix. Matt's code should work in most browsers, but just in case...

Change
<SELECT name="cars" onChange="Javascript:frmSelect.submit();">
to simply
<SELECT name="cars" onChange="this.form.submit();">

Incidentally, if it wasn't obvious, it was your bogus onChange code that was causing the problem before.

MATT: If she(?) didn't have Java debugging turned on, then likely she wouldn't have seen any error messages.
The page would have just sat there doing nothing when the <SELECT> was changed. Explains a lot, no?

Of course, having two <SELECT>s with the same name is pure poison, too.

Old Pedant · November 25th, 2008, 11:03 PM

It's taken me a long time to figure this out, but...

Why don't we just let the SQL query get the "SELECTED" for us???

Code:

<SELECT name="cars" onChange="this.form.submit();">
<%
choice = Trim(Request("cars"))
SQL = "SELECT DISTINCT Cars, IIF(Cars='" & choice & "','selected','') AS SEL " _
    & " FROM tblComboSelect ORDER BY Cars;"
Set oRS = conn.Execute
do until oRs.EOF %>
          <OPTION <%=oRS("SEL")%> ><%=oRS("Cars")%></OPTION>
<%
    oRs.MoveNext
loop 
%>
</SELECT>
...

That's for an Access DB. For MYSQL, replace "IIF" with "IF". For SQL Server use

Code:

SQL = "SELECT DISTINCT Cars, " _
    &       " (CASE WHEN Cars='" & choice & "' THEN 'selected' ELSE '' END) AS SEL " _
    & " FROM tblComboSelect ORDER BY Cars;"

mat41 · November 25th, 2008, 11:10 PM

Thats some snazzy SQL......

I guess I have had to many instances where clients have wanted to change from lets say Access to SQLServer. This is probably why I tend to write more.....whats the word... portable queries and let the VBScript do its thing inside option tags.

Wind is your friend
Matt
www.elitemarquees.com.au

jennypretty · November 26th, 2008, 01:22 PM

I tried this code. It worked. But when I tried to enter a different action page in the <form action="">. It does not work.
Is this code working only within the same page?
I have a different page to execute this page. How do I change to make it work with different action page?

Thanks.

Quote:

quote:Originally posted by mat41
In future you need to post detaield arror messages, line numbers etc.... help other help you!!!

Anyhow since you 'had a go' I have altered all the code between the <html> and the </html> tags.
There are alot of things wrong with your code, almost to many to mention! Study the differences
to learn. BTW - I have tested the code also.

<html>
<head>
<title>Example combo box</title>
</head>
<body>
<b>Select a car type to view dates sold</b>
<br><bR>
<form name="frmSelect" method="Post" action="selectcombo.asp">
<table border="0">
<tr>
<td>Select Car Type :</tD>
<td>
<% Set oRs=Server.CreateObject("adodb.recordset")
    strSQL = "SELECT DISTINCT Cars FROM tblComboSelect ORDER BY Cars;"
    oRs.Open strSQL, conn
    if not oRs.eof then %>
     <SELECT name="cars" onChange="Javascript:frmSelect.submit();">
<% do until oRs.EOF %>
         <OPTION VALUE="<%= oRS(0) %>" <% if trim(request.form("Cars")) = trim(oRS(0)) then response.write " selected " end if %>><%= oRS(0) %></option>
<% oRs.MoveNext
     loop %>
     </SELECT>
<% else %>
     <i>No cars found in the database</i>
<% end if %>
</tD>
</tr>
<tr>
<td>Date(s) Sold :</tD>
<td>
<% if trim(request.form("Cars")) <> "" then
     'user has selected a car therefore show dates select box
     strSQL = "SELECT [Date-Sold] FROM tblComboSelect WHERE Cars='" & trim(request.form("Cars")) & "';"
     Set oRs=Server.CreateObject("adodb.recordset")
     oRs.Open strSQL, conn
     if not oRs.eof then %>
         <select name="dateSold">
<% do until oRs.eof %>
            <option value="<%= ors(0) %>"><%= ors(0) %></option>
<% oRs.MoveNext
         loop %>
         </select>
<% else %>
         <i>No records found for that car</i>
<% end if
else
     'user has not selected a car therefore do not show date select box
     response.write "<i>No car selected yet</i>"
end if %>
</tD>
</tr>
</table>
</form>
</body>
</html>

Wind is your friend
Matt
www.elitemarquees.com.au

http://www.asiafuns.com/asian_friends/

mat41 · November 26th, 2008, 06:25 PM

I find your post bizzare....

;;;But when I tried to enter a different action page in the <form action="">. It does not work Is this code working only within the same page?

The page has been built based on your initial post/problem therefore it posts to its self. The conditions on the page rely on the page being posted to its self, surely you can tell this providing you understand the code. Looking at your post its clear you dont. What part(s) of the code do you not understand? This page contains very simple code. If you want to build ASP pages you need to follow and understand this page first to become a better coder.

;;;I have a different page to execute this page. How do I change to make it work with different action page?
You need to clearly define what page A should do and what page B should do. Your initial post contained:

"When a user select car A from the first SELECT box, the 2nd box automatically adjust to have all dates for cars A that were sold in the last 2 months only. No values for car B and C from the 2nd SELECT box when A was selected on first SELECT box."

Read it again, where does it even suggest a page B? I can only asume your requirment changed after you post? You need to use peoples time more wisely, think before you post....

Wind is your friend
Matt
www.elitemarquees.com.au

Old Pedant · November 28th, 2008, 12:38 AM

I suspect that she(?) means that after the second drop down list is populated, *THEN* the ACTION= should change.

This makes sense: When the second list is not there, you *need* to post back to the same page, so that indeed you *can* produce the second list. But once the second list is there, you presumably want to go on to the next step in the proccess: a different page.,

BUT...

But, Jenny, what happens if the user changes his/her mind and selects a *DIFFERENT* item from the FIRST drop down list??? Don't you *STILL* want the page to post back to itself, so that you can change what is shown in the second drop down list?? Of course you do!

There are several ways to do this, but I think the CLEANEST AND CLEAREST way is to do this:

Code:

<FORM Action="nextPage.asp" Method="Post">
...
<SELECT name="cars" onChange="this.form.action='thisPage.asp';this.form.submit();">
...
</SELECT>
...

Do you see it??? The NORMAL action of the <FORM> is to go forward to the next page. BUT...

But if the user changes the selection in the first drop down, *THEN* you change the ACTION= and send the user back to THIS SAME PAGE!

See? Simplest way to do this.

You can, of course, do it the other way around:

Code:

<FORM Action="samePage.asp" Method="Post">
...
<SELECT name="cars" onChange="this.form.submit();">
...
</SELECT>
...
<SELECT name="dateSold" onChange="this.form.nextPage;">
...
</SELECT>

WIth this code, the user will keep being sent back to the same page UNTIL he/she chooses a value from the *second* dropdown. At which point, finally, the user can move the next page.

Make sense?

[/code]