[Dooogie352]

I'm new to this forum and to ASP, so please be patient. I am attempting to complete an ASP project and all is well except for an error message I receive when I submit one page. I figure I am missing something obvious, but I have spent a few hours playing with this code with no luck. I am hoping for some assistance from the experts so I can finish this before I go nuts. Feel free to reply to me directly at doogie352@aol.com. Any assistance would be greatly appreciated.

The error message is:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/schedule/vehicle_select.asp, line 41

The code is:

<%
' # Open the database to get Column names for the drop down list #####
'################################################# #####################
set Rsemaa = Server.CreateObject("ADODB.Recordset")
Rsemaa.ActiveConnection = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="& server.MapPath("/databases/schedule.mdb")
Rsemaa.Source = "SELECT * FROM tbl_vehicles"
Rsemaa.CursorType = 0
Rsemaa.CursorLocation = 2
Rsemaa.LockType = 3
Rsemaa.Open()

'### If the search has been submitted create the SQL string and open another DB connection for the search
'################################################# ################################################## #####

If request.querystring() <> "" then

Dim SQLstring

If request.form("field") = "" then
Response.Write("No records returned. Please select status from drop down.")
Else
SQLstring = "SELECT * FROM tbl_vehicles WHERE vehicle_number=" & request.form("field") & " "
End if


set RsSearch = Server.CreateObject("ADODB.Recordset")
RsSearch.ActiveConnection = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="& server.MapPath("/databases/schedule.mdb")
RsSearch.Source = SQLstring
RsSearch.CursorType = 0
RsSearch.CursorLocation = 2
RsSearch.LockType = 3
RsSearch.Open()
End if
%>



<form name="form1" method="post" action="vehicle_select.asp?search">
<br>
Vehicle Name&nbsp;
<select name="field">
<% do while not rsemaa.eof %>

<option value="<%= rsemaa("vehicle_number")%>"><%= rsemaa("vehicle_number")%></option>

<%
rsemaa.movenext
Loop
%>
</select>

<input type="submit" name="Submit" value="Submit">

<hr>
<br>

<font face="Arial, Helvetica, sans-serif" size="2">

<%
'#### Print the search results to the a table on the page
If request.querystring() <> "" then %>

<table border=0 align=center cellpadding=3 cellspacing=0 width=764><thead><tr>
<td><b>Vehicle</td>
</tr></thead><tbody>
<tr>
<td><hr></td>
</tr>

<% do while not RsSearch.EOF %>
<tr>
<td><a href="vehicle_select2.asp?ID=<%= RsSearch("vehiclenumber") %>"><%= RsSearch("vehiclenumber") %></A></td>
</tr>
<tr>
<td><hr></td>
</tr>
<%
RsSearch.MoveNext
Loop
%>
</tbody></table><p>
<%
'#### Close it off ######
RsSearch.Close()
Set RsSearch = nothing
End if %>

[MrWizard]

I'm not yet a 'guru' but I've seen this error plenty of times.

My experience with this error message says that you're somehow defining your SQL query using a field that doesn't exist in the database.

I don't know how your data is set up, so you might want to check that your SQL statement against your table definition...

SQLstring = "SELECT * FROM tbl_vehicles WHERE vehicle_number=" & request.form("field") & " "

Is there a tbl_vehicles with a field named vehicle_number? And I'm just a little suspicious about how this will act if you pass a blank value in your "field" variable.

Tim

[Dooogie352]

I appreciate the reply....unfortunately there is a table and field as described. I double checked and it's there. But thans for the idea. Any other suggestions?

[bciarcia]

try putting single quotes around the value like this;

SQLstring = "SELECT * FROM tbl_vehicles WHERE vehicle_number= '" & request.form("field") & "'"

because you are looking for an integer SQL likes to have " ' " around the variables.

Let me know if it works.

Originally posted by MrWizard
I'm not yet a 'guru' but I've seen this error plenty of times.

My experience with this error message says that you're somehow defining your SQL query using a field that doesn't exist in the database.

I don't know how your data is set up, so you might want to check that your SQL statement against your table definition...

SQLstring = "SELECT * FROM tbl_vehicles WHERE vehicle_number=" & request.form("field") & " "

Is there a tbl_vehicles with a field named vehicle_number? And I'm just a little suspicious about how this will act if you pass a blank value in your "field" variable.

Tim

[MrWizard]

Just another thought....

Since I don't know what your data looks like, I thought it was at least worth mentioning that one common source of SQL errors of this type is having apostrophes or pipe characters in your data.

If by chance your data contains either a " ' " or a " | " character, then this could definitely cause the error you got, in which case you would just have to clean up the data before putting it in the SQL string.

Tim

[rhs98]

if the request.form("field") is not a number this error may be caused by the sql parser beliving that the data inserted is a field your requesting;

i.e.

SQLstring = "SELECT * FROM tbl_vehicles WHERE vehicle_number= " & request.form("field") & " "
response.write sqlstring

would output this if the form field called field = "x"

SELECT * FROM tbl_vehicles WHERE vehicle_number=x

the sql parser will assume this to be a column. Strings must be delimited using the ' symbol usually