SELECT statement in ASP please help

jennypretty · December 1st, 2008, 08:40 AM

The reason I like to have a different ACTION page is because this is an INSERT page. It uses existing data in the database for all the drop-down selection values. When user submit the form, it points to a different page.

I thought I could finish the rest but I got errors again. So, I have to post my question again.

I tried to add a new ASP block to select "Seller's names" who sold any specific cars from the "Date Sold" field on
previous selection. What I mean is:

1. When a user select "Cars type"

2. Date sold and shipped will show up based on "cars type" selected

3. Seller's names will show up based on "date sold/shiped" selected

I keep getting this error below.

Can you please help?

Thanks you so much!

Technical Information (for support personnel)

Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/tran/selectcombo2.asp, line 63

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)

Page:
POST 43 bytes to /tran/selectcombo2.asp

POST Data:
cars=1&dateSold=1%2F6%2F2008+-+2%2F9%2F2008

Time:
Monday, December 01, 2008, 10:18:57 AM
-----------------------------------------------------

Code:

<b>Select a car type to view dates sold</b>
<br><bR>
<form name="frmSelect" method="Post" action="selectcombo2.asp">
<fieldset>
    Select Car Type :
<%  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();">
       <OPTION VALUE = "1" >
       <%     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 %>
  <br />
  Date(s) Shipped and Sold :
<% if trim(request.form("Cars")) <> "" then
      'user has selected a car therefore show dates select box
      strSQL = "SELECT DateShippedIn, DateSold 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("DateShippedIn") %> - <%= ors("DateSold") %>"><%= ors("DateShippedIn") %> - <%= ors("DateSold") %></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 %>
   </fieldset>

     <br />
  Sellers :
<% if trim(request.form("dateSold")) <> "" then
      strSQL = "SELECT sellerLast, sellerFirst FROM tblComboSelect WHERE DateSold='" & trim(request.form("DateSold")) & "' AND DateShippedIn='" & trim(request.form("DateShippedIn")) & "';"
      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("sellerLast") %>, <%= ors("sellerFirst") %>"><%= ors("sellerLast") %>, <%= ors("sellerFirst") %></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 %>
   </fieldset>
</form>

Thanks.

Old Pedant · December 1st, 2008, 04:05 PM

First, fix your browser.

Click on TOOLS menu.
Click on INTERNET OPTIONS menu item.
Click on ADVANCED tab.
*UN*CHECK "Show friendly HTTP error messages"
OK.

Now get a *REAL* error message instead of the hokey MSIE fake-o message.

And if you can't figure it out right away, start DEBUGGING. Add tons of RESPONSE.WRITE statements
to see where you are at and what's going on. At a minimum, try doing
Response.Write "DEBUG SQL: " & strSQL & "" & vbNewLine

And if you have to ask again, AT LEAST tell us what line of your code is being referred to by the error message.

jennypretty · December 2nd, 2008, 10:12 AM

Hello,

I set up the way you said on my IE. I still got the same error.

Thanks very much.

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

Old Pedant · December 2nd, 2008, 08:56 PM

Bummer.

Well, you still have to tell us WHAT LINE that error message refers to, at a minimum.

I can't tell just by looking at your code.

jennypretty · December 2nd, 2008, 09:55 PM

I tried Mat's code and it worked. When I added this part, it does not work. I got that errror (above).

<% if trim(request.form("dateSold")) <> "" then
    Ã Ã strSQL = "SELECT sellerLast, sellerFirst FROM tblComboSelect WHERE DateSold='" & trim(request.form("DateSold")) & "' AND DateShippedIn='" & trim(request.form("DateShippedIn")) & "';"
    Ã Ã 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("sellerLast") %>, <%= ors("sellerFirst") %>"><%= ors("sellerLast") %>, <%= ors("sellerFirst") %></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 %>

I do not know why this code above generate errors.
Can you please help?
Thanks very much.

Old Pedant · December 3rd, 2008, 01:03 AM

Oh, of course. Access *REQUIRES* that date constants be enclosed in #...#. Your code is using '...'.

Your code is also remarkably unsafe, so I hope this is for an inTRAnet site and not for some inTERnet site
where the hackers will tear you to pieces.

Anyway:

Code:

strSQL = "SELECT sellerLast, sellerFirst " _
       & " FROM tblComboSelect " _
       & " WHERE DateSold=#" & CDate(trim(request("DateSold"))) & "# " _
       & " AND DateShippedIn=#" & CDate(trim(request("DateShippedIn"))) & "#"

Doesn't anybody in your shop have some experience with databases and/or web programming?
I feel that you are simply bumping your way along from one thing to the next with
no real sense of direction. You seem to need way more hope that we can
give you in a forum of this nature.

    Ã Ã 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("sellerLast") %>, <%= ors("sellerFirst") %>"><%= ors("sellerLast") %>, <%= ors("sellerFirst") %></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 %>

I do not know why this code above generate errors.
Can you please help?
Thanks very much.
Edited by - jennypretty on 12/02/2008 5:58:35 PM
Go to Top of Page
       Topic Next Topic
New Topic Reply to Topic Subscribe to this topic Subscribe to this topic Send Topic to a Friend Printer Friendly
Jump To:
Spell Checker Quick Reply
Message:

* HTML is OFF
* Forum Code is ON

Include Signature