Multiple selection boxes - please help

[jennypretty]

Hello,

Can anyone please help me on multiple selection box?

I have this table:

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

I tried to create a INSERT page that uses the data from an existing table for selection boxes:

1. When a user select "Cars type"

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

3. When Date sold/shipped was selected, Seller's names will show up based on "date sold/shiped" selected


I keep getting this error below. I already turned on the friendly errors url.

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



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
-----------------------------------------------------


<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.

[Goodkat]

Jenny,

Can you post the whole file or highlight what line 63 is? I'll see what I can come up with.

Goodkat

[jennypretty]

Thanks for your reply.
Here is line 63:
DateSold='" & trim(request.form("DateSold")) & "' AND DateShippedIn='" & trim(request.form("DateShippedIn")) & "';"

Especially this: cars=1&dateSold=1%2F6%2F2008+-+2%2F9%2F2008

I already tried CDATE but it does not work.

Thanks.

[Goodkat]

I'm not sure if this would work, but sometimes I've found sql statements to be weird about functions running in WHERE statements. I would try something like this:

DSold = rtrim(request.form("DateSold")
DShippedIn = rtrim(request.form("DateShippedIn")

strSQL = "SELECT sellerLast, sellerFirst FROM tblComboSelect WHERE DateSold='" & DSold & "' AND DateShippedIn='" & DShippedIn & "';"

But, looking further into your initial form the values are only coming from the select statement "dateSold" which is combining the two variables. You would have to split these dates up in order to use them in a sql statement like you are trying. So if that is the case try this:

'This will split up the dateSold string at the - character. Then trim and assign them to the variables to use in the sql statement.

DateStr = request.form("dateSold")
Dim DateArray
DateArray = Split(DateStr, "-")
DShippedIn=trim(DateArray(0))
DSold=trim(DateArray(1))

strSQL = "SELECT sellerLast, sellerFirst FROM tblComboSelect WHERE DateSold='" & DSold & "' AND DateShippedIn='" & DShippedIn & "';"

Hope this helps, I'll be online all day. Let me know how it goes.