Too few parameters. Expected 1.

[scroce]

[Microsoft][ODBC Microsoft Access 97 Driver] Too few parameters. Expected 1.


Microsoft article Q216425 says that the only way this error message can be generated is if you are trying to access a field that doesn't exist. I've checked and rechecked my code, and I can't seem to find an instance of this. I suspect a problem with the SQL - here is a sample of the code that I think is causing the problem.

ElseIf Session("EditMode&quot = "New" Then
sSQL = "insert into Location (LocationCode, Region, Branch, Building, StreetAddress, "
sSQL = sSQL & "City, State, Zip, Phone, Fax) values ("
sSQL = sSQL & "'" & sLocationCode & "' "
sSQL = sSQL & ",'" & sRegion & "' "
sSQL = sSQL & ",'" & sBranch & "' "
sSQL = sSQL & ",'" & sBuilding & "' "
sSQL = sSQL & ",'" & sStreetAddress & "' "
sSQL = sSQL & ",'" & sCity & "' "
sSQL = sSQL & ",'" & sState & "' "
sSQL = sSQL & "," & Session("Zip&quot
sSQL = sSQL & ",'" & sPhone & "' "
sSQL = sSQL & ",'" & sFax & "')"
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
cmd.Execute

I've checked out info the command object in my books, and I've seen one other post on tek-tips about it, but I still can't seem to figure where there's an error.

Can anyone else suggest? How much more water would there be in the ocean if it weren't for sponges?

 

[link9]

You have not enclosed your 'session("zip&quot' in quotes -- don't know what data type it is, but that may be the problem -- all your other ones are not enclosed. Double check the data types on all your fields to ensure that the ones enclosed in 's are in fact character fields and vice versa.

response.write the value of sSQL right before you do the cmd.commandtext = sSQL statement, and post the output here along with the data types of your table if the error is not apparent.

 

[scroce]

Link 9,

Your suggestion totally got me on the right track which ultimately solved this. I did a response.write on the sSQL variable and got this:

update Location set LocationCode = myTestCode, Region = 'test', Branch = 'test', Building = 'test', StreetAddress = 'test', City = 'test', State = 'tx', Phone = '4444444444', Fax = '4444444444', Zip = 44444 where LocationID = 26

which then caused me to get the idea to take that string, and copy it into Acess2000 SQL view and see if it would run - and it didn't work there. AHA! that was the ticket. Let me tell you, it was much easier to trouble shoot the sql in access than it was to keep running the entire web application.

To make a long story short, the problem was the " ' " in the very first line. Here is the difference in the code:

starting code: sSQL = "update Location set LocationCode = "& sLocationCode

ending working code:sSQL = "update Location set LocationCode = " & "'" & sLocationCode & "'"

note the placement of the single quote ('). Also note in the first line of the update query, the value myTestCode is the only value not surrounded by single quotes. This was causing access to think that it was a parameter query. The code written in blue forces the value myTestCode to be passed in as a value, not a parameter.

Once again, thank you for your help, sometimes you just need that little exta kick in the pants.
How much more water would there be in the ocean if it weren't for sponges?

 

[shaddow]

try this way it's mutch easyer (for me it is)

use your database connection variable

<%@ Language=VBScript %>
<%
Const adOpenStatic = 3
Const adUseClient = 3
Const adLockPessimistic = 2

set rs=server.CreateObject(&quot;ADODB.Recordset&quot
'the recordset you use for now on

sub ExecuteSQL(byval cmd)
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Source = cmd
rs.ActiveConnection = YourConnectionVariable 'The record set needs to know what connection to use.
rs.Open
end sub
%>
<%
ElseIf Session(&quot;EditMode&quot = &quot;New&quot; Then
sSQL = &quot;insert into Location (LocationCode, Region, Branch, Building, StreetAddress, &quot;
sSQL = sSQL & &quot;City, State, Zip, Phone, Fax) values (&quot;
sSQL = sSQL & &quot;'&quot; & sLocationCode & &quot;' &quot;
sSQL = sSQL & &quot;,'&quot; & sRegion & &quot;' &quot;
sSQL = sSQL & &quot;,'&quot; & sBranch & &quot;' &quot;
sSQL = sSQL & &quot;,'&quot; & sBuilding & &quot;' &quot;
sSQL = sSQL & &quot;,'&quot; & sStreetAddress & &quot;' &quot;
sSQL = sSQL & &quot;,'&quot; & sCity & &quot;' &quot;
sSQL = sSQL & &quot;,'&quot; & sState & &quot;' &quot;
sSQL = sSQL & &quot;,&quot; & Session(&quot;Zip&quot
sSQL = sSQL & &quot;,'&quot; & sPhone & &quot;' &quot;
sSQL = sSQL & &quot;,'&quot; & sFax & &quot;')&quot;
ExecuteSQL(sSQL)
'use now just rs variable
if rs.RecordCount=0 then
Response.End
end if


%>