?
Solved

Too few parameters. Expected 1. it is killing me

Posted on 2005-05-15
21
Medium Priority
?
6,978 Views
Last Modified: 2012-08-13
> this is the error
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
[...] line 41


> this is the explanation
SYMPTOMS
If you use ActiveX Data Objects (ADO) to manipulate data in an Access database from within an Active Server Pages (ASP), and you refer to a column in a table that does not exist, you may see the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access 97 Driver] Too few parameters. Expected 1.
CAUSE
This error occurs only with Microsoft Access when one of the column names specified in a select statement does not exist in the table being queried.
RESOLUTION
Remove any invalid column names from the select statement.
http://support.microsoft.com/default.aspx?scid=kb;en-us;216425


> this is the form
<form method="POST" action="[...]">
      level (free | premier):<br><input type="text" name="input_level" value="free" size="50"><br><br>
      visible (yes | no):<br><input type="text" name="input_visible" value="yes" size="50"><br><br>
      business id:<br><input type="text" name="input_businessid" size="50"><br><br>
      short name:<br><input type="text" name="input_shortname" size="50"><br><br>
      full name:<br><input type="text" name="input_fullname" size="50"><br><br>
      address line 1:<br><input type="text" name="input_addressone" size="50"><br><br>
      address line 2:<br><input type="text" name="input_addresstwo" size="50"><br><br>
      city:<br><input type="text" name="input_city" value="Osijek" size="50"><br><br>
      postalcode:<br><input type="text" name="input_postalcode" value="31000" size="50"><br><br>
      state:<br><input type="text" name="input_state" size="50"><br><br>
      country:<br><input type="text" name="input_country" value="Hrvatska" size="50"><br><br>
      phone:<br><input type="text" name="input_phone" value="00385 0" size="50"><br><br>
      <input type="submit" value="Add to the Database" name="submit_add_company">
      <input type="reset" value="Clear All Fields" name="submit_clear_fields">
</form>


> this is the asp code
str_sql = "insert into [tbl_company_list] ([fld_company_level],[fld_company_visible],[fld_company_businessid],[fld_company_shortname],[fld_company_fullname],[fld_company_addressone],[fld_company_addresstwo],[fld_company_city],[fld_company_postalcode],[fld_company_state],[fld_company_country],[fld_company_phone]) values (" & request.form("input_level") & ", '" & request.form("input_visible") & "', '" & request.form("input_businessid") & "', '" & request.form("input_shortname") & "', '" & request.form("input_fullname") & "', '" & request.form("input_addressone") & "', '" & request.form("input_addresstwo") & "','" & request.form("input_city") & "','" & request.form("input_postalcode") & "','" & request.form("input_state") & "','" & request.form("input_country") & "','" & request.form("input_phone") & "') "


> this is copy - paste of names form the database
fld_company_id (this is the autonumber - it gets inputed automaticaly)
fld_company_level
fld_company_visible
fld_company_businessid
fld_company_shortname
fld_company_fullname
fld_company_addressone
fld_company_addresstwo
fld_company_city
fld_company_postalcode
fld_company_state
fld_company_country
fld_company_phone


> THE PROBLEM? i can not find it. every column is referenced ok. am i blind?
help please. thnx.
0
Comment
Question by:qwekovaqwe
  • 9
  • 7
  • 4
20 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006199
I could be wrong, but it looks like you are trying to insert into 13 fields but supplying only 12 values?

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006203
No, I am wrong. Scratch that.

Please do this:

Response.write str_sql
response.end

before you execute the sql. That will allow us to see what's wrong.

FtB
0
 
LVL 3

Author Comment

by:qwekovaqwe
ID: 14006250
> this is the result of
Response.write str_sql
response.end


insert into [tbl_company_list] ([fld_company_level],[fld_company_visible],[fld_company_businessid],[fld_company_shortname],[fld_company_fullname],[fld_company_addressone],[fld_company_addresstwo],[fld_company_city],[fld_company_postalcode],[fld_company_state],[fld_company_country],[fld_company_phone]) values (free, 'yes', '', '', '', '', '','Osijek','31000','','Hrvatska','00385 0')

looks fine to me.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006267
Shouldn't the first value, free, have delimiters around it?

FtB
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 200 total points
ID: 14006271
So, instead of this:

values (" & request.form("input_level") & ", '"

you need:

values ('" & request.form("input_level") & "', '"
0
 
LVL 14

Assisted Solution

by:kiddanger
kiddanger earned 100 total points
ID: 14006282
What are your fields and your table in brackets?
Are any of the blank fields set to require a value?
What does the first value not have single quotes around it?  It's text.
I won't even go into the security issues re: SQL injection.

What happens if you use this instead?

str_sql = "insert into tbl_company_list (fld_company_level,fld_company_visible,fld_company_businessid,fld_company_shortname,fld_company_fullname,fld_company_addressone,fld_company_addresstwo,fld_company_city,fld_company_postalcode,fld_company_state,fld_company_country,fld_company_phone) values ('" & request.form("input_level") & "', '" & request.form("input_visible") & "', '" & request.form("input_businessid") & "', '" & request.form("input_shortname") & "', '" & request.form("input_fullname") & "', '" & request.form("input_addressone") & "', '" & request.form("input_addresstwo") & "','" & request.form("input_city") & "','" & request.form("input_postalcode") & "','" & request.form("input_state") & "','" & request.form("input_country") & "','" & request.form("input_phone") & "') "
0
 
LVL 3

Author Comment

by:qwekovaqwe
ID: 14006284
dammit yeah. i missed that. omg :( i'm stupid and careless.
thnx. will check it out. i am guessing it should work. thnx.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006286
@kiddanger --

I see you that you encorporated my correction into your post?

FtB
0
 
LVL 3

Author Comment

by:qwekovaqwe
ID: 14006295
>  What are your fields and your table in brackets?
what do you mean? why not?

> Are any of the blank fields set to require a value?
no. this is a test case only.

> What does the first value not have single quotes around it?  It's text.
indeed. this seems to be the problem. i shall correct it.

> I won't even go into the security issues re: SQL injection.
SQL injection?? please elaborate, if you will...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006316
That really brings us to a whole new topic, but...

http://www.securiteam.com/securityreviews/5DP0N1P76E.html

but the question that you originally asked is solved now?

FtB
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 14006366
Fritz..

When I read the question, your posts weren't there.  Updates by others are not refreshed if the page has already rendered.  Surely you're aware of that.
If two or more come to the same conclusion to solve an issue, surely the OP will recognize the first post has solved the issue.

qwe...

re: brackets.  Those are only required of your field names have spaces.  Just curious.

It appears fritz has already responded to the SQL injection so no need to duplicate.

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006388
Sorry, I am just trying to enforce:

http://www.experts-exchange.com/help.jsp#hi241
http://www.experts-exchange.com/help.jsp#hi242

These are really important guidelines in the high-traffic topic areas.

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006393
In regard to my making the point about injection being a whole new question, I am just referencing this:

http://www.experts-exchange.com/help.jsp#hi107

FtB
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 14006694
If you've directed that towards me, I have no issues with SQL injection being another question.  I just thought it was important for people to know taking direct uncleansed form input and inserting it into a database is a recipe for disaster and is wide open to attack.
0
 
LVL 3

Author Comment

by:qwekovaqwe
ID: 14006697
ok thnx guys.

@ kiddanger
thnx for your trouble. the [] are used just to be on the safe side. maybe i could loose them.
thnx for the point in the security direction as well.

@fritz_the_blank
thnx for solving my issue. was a fairly simple one, no? even though i failed to see it.
loved your idea to do: response.write to check it out.

i increased the point value of the question so that fritz... can get the original 50, but since kid...did try and help, and was on the correct path and has provided some aditional related and not related info ;) he gets the 25 points as well.
hope you both find this acceptable.

i was not trying to squezze more info out of you by asking about the sql injection ;) but as it popped up i was curious.
anyhow thank you both and have a nice day.
0
 
LVL 3

Author Comment

by:qwekovaqwe
ID: 14006700
whoops. i seem to have selected the wrong one :(
no worries. will be resolved. anyhow thnx guys.
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 14006726
Thanks qwe...  Sorry about the confusion.  Glad your issue is solved.  I'm sure, as you said, a mod will make the corrections.  Fritz did answer your question first so all points can go to him if you wish.

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14006775
Don't worry about it. I am just happy that you have your solution.

FtB

0
 
LVL 3

Author Comment

by:qwekovaqwe
ID: 14059749
thn rommod
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question