"80040e10 Error: No value given for one or more required parameters

I am getting the error above intermittantly with an ASP 3.0 page using an MS
Access 2003 database.

I have searched Google extensively and found the following possible causes
for this error:

A field name was spelled incorrectly.
One or more of the values was blank.
You tried to insert the wrong datatype (e.g. surrounded a numeric value with
quotes, or forgot to put quotes around a string).
Capitalization /spelling

However, none of these seem to be the cause of my problem.

As you can see below, I am not passing parameters, my data access statement
is an unqualified SELECT

I have written out the connection string and sql to the page and they appear
fine.

Attached is a summary of my code. Does anyone have any idea why this is
intermittantly occurring?



In global.asa:

Sub Application_onStart()

Application.Lock

sCn_App= "PROVIDER=MICROSOFT.JET.OLEDB.4.0; " & _
"DATA SOURCE=e:\inetpub\clients\frickcpa.com\fpdb\tvom.mdb"

Application.Contents.Item("cn_App")=sCn_App

Application.UnLock

End Sub



In ASP page:

<%

Dim sql,rs

Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.open Application("cn_App")


sql="SELECT questionid, question, Hint_Image FROM question"

Set rs = Server.CreateObject("ADODB.recordset")

With rs
.ActiveConnection=cn
.source=sql
.Open <---Error here at line 132
End With


Do while not rs.eof

.....

rs.movenext

Loop

If rs.State Then
rs.close
set rs=nothing
End if


If cn.State Then
cn.Close
End If

%>

RE: "80040e10 Error: No value given for one or more required parameters

Hi Dave,

From your description, you got some parameter missing related error when
running an ASP page that use ADO objects to query database, correct?

As you mentioned that the error occur at the following lines

>>>>>>>>>>>
With rs
.ActiveConnection=cn
.source=sql
.Open <---Error here at line 132
End With
<<<<<<<<<<

I think it is possible that RecordSet.Open method require some parameters.
I suggest you try supply some of the properties through the "Open" method
(as mentioned in the following reference) to see whether it works:

#ADO Open Method
http://www.w3schools.com/ado/met_rs_open.asp

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx .

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

Re: "80040e10 Error: No value given for one or more required parameters

"Dave" wrote in message
news:ugxYk9X1HHA.4428@TK2MSFTNGP03.phx.gbl...
> I am getting the error above intermittantly with an ASP 3.0 page using an
MS
> Access 2003 database.
>
> I have searched Google extensively and found the following possible causes
> for this error:
>
> A field name was spelled incorrectly.
> One or more of the values was blank.
> You tried to insert the wrong datatype (e.g. surrounded a numeric value
with
> quotes, or forgot to put quotes around a string).
> Capitalization /spelling
>
> However, none of these seem to be the cause of my problem.
>
> As you can see below, I am not passing parameters, my data access
statement
> is an unqualified SELECT
>
> I have written out the connection string and sql to the page and they
appear
> fine.
>
> Attached is a summary of my code. Does anyone have any idea why this is
> intermittantly occurring?
>
>
>
> In global.asa:
>
> Sub Application_onStart()
>
> Application.Lock
>
> sCn_App= "PROVIDER=MICROSOFT.JET.OLEDB.4.0; " & _
> "DATA SOURCE=e:\inetpub\clients\frickcpa.com\fpdb\tvom.mdb"
>
> Application.Contents.Item("cn_App")=sCn_App
>
> Application.UnLock
>
> End Sub
>
>
>
> In ASP page:
>
> <%
>
> Dim sql,rs
>
> Dim cn
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.open Application("cn_App")
>
>
> sql="SELECT questionid, question, Hint_Image FROM question"
>
> Set rs = Server.CreateObject("ADODB.recordset")
>
> With rs
> .ActiveConnection=cn
> .source=sql
> .Open <---Error here at line 132
> End With
>
>
> Do while not rs.eof
>
> .....
>
> rs.movenext
>
> Loop
>
> If rs.State Then
> rs.close
> set rs=nothing
> End if
>
>
> If cn.State Then
> cn.Close
> End If
>
> %>
>

Are you sure the field names provided actually exist in a table called
question?
Typically JET assumes that an identifier that can't be found amoungst the
fields in the tables in the query are parameters. Hence a typo in your
field list can result in this error.



--
Anthony Jones - MVP ASP/ASP.NET

Re: "80040e10 Error: No value given for one or more required parameters

Dave wrote:
> I am getting the error above intermittantly with an ASP 3.0 page
> using an MS Access 2003 database.
>
> I have searched Google extensively and found the following possible
> causes for this error:
>
> A field name was spelled incorrectly.
> One or more of the values was blank.
> You tried to insert the wrong datatype (e.g. surrounded a numeric
> value with quotes, or forgot to put quotes around a string).

This last one is not an issue if you take my oft-given advice to use
parameters rather than dynamic sql:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


> Capitalization /spelling

JetSQL is case-insensitive so capitalization will never be an issue

>
Add another one :
You've used a reserved keyword in your table design. This does not seem to
be the case in your situation, but it cannot hurt to surround the table and
field names with brackets [] to see if that resolves the error. If it does,
then it's a matter of figuring out which word is reserved. This can help:
http://www.aspfaq.com/show.asp?id=2080)

> However, none of these seem to be the cause of my problem.
>
> As you can see below, I am not passing parameters, my data access
> statement is an unqualified SELECT
>
> I have written out the connection string and sql to the page and they
> appear fine.

You need to verify the sql by attempting to run it in Access using the
Access Query Builder

>
> Attached is a summary of my code. Does anyone have any idea why this
> is intermittantly occurring?
>

Intermittent?? Typically this error is constant, unless you are dynamically
building a sql statement. ...

>
>

> Dim sql,rs
> sql="SELECT questionid, question, Hint_Image FROM question"
>
> Set rs = Server.CreateObject("ADODB.recordset")
>
> With rs
> .ActiveConnection=cn

Probably nothing to do with your problem, but this shoud be:

Set .ActiveConnection=cn

Alternatively, pass the connection object in the Open statement:
..Open ,cn

> .source=sql
> .Open <---Error here at line 132

To Steven's point, you should always use the Open method's
argument to specify the command type (again, nothing to do with your error)

..Open ,,,,1 '1=adCmdText

I really see nothing here that could cause this error. Are you showing us
your real code? Could it be that your actual code is dynamically building a
sql statement?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: "80040e10 Error: No value given for one or more required parameters

Thanks everyone for your help.

To answer your question Bob, yes that is the real code and it is not
dynamically building the query. It is simply a hard coded SELECT statment
with no WHERE clause.

I spent much time this week end re-writing several pages but with no luck.
The funny thing is is the error occurs only on the production site, I have
never ben able to reproduce it in DEV.

And it is entirely random and intermittent. I might request a page over 50
times without getting the error. But then on the 51st, the error appears and
then may disappear or continue for subsequent page requests.

Here is a link if anyone has time to check it out:

http://www.frickcpa.com/tvom/TVOM_Quiz.asp

Re: "80040e10 Error: No value given for one or more required parameters

Dave wrote:
> Thanks everyone for your help.
>
> To answer your question Bob, yes that is the real code and it is not
> dynamically building the query. It is simply a hard coded SELECT
> statment with no WHERE clause.
>
> I spent much time this week end re-writing several pages but with no
> luck. The funny thing is is the error occurs only on the production
> site, I have never ben able to reproduce it in DEV.
>
> And it is entirely random and intermittent. I might request a page
> over 50 times without getting the error. But then on the 51st, the
> error appears and then may disappear or continue for subsequent page
> requests.
> Here is a link if anyone has time to check it out:
>
> http://www.frickcpa.com/tvom/TVOM_Quiz.asp

Sorry, but running your web page will help no one debug a server-side issue
(if you were having an html/css/client-side code issue, this would be
helpful). You need to trap the error and display everything about the
context when the error occurs:

on error resume next
..Open ,,,,1 '1=adCmdText
if err<>0 then
response.write "Error occurred when opening recordset:
"
response.write err.description & "
response.write "sql contains """ & sql & """

Re: "80040e10 Error: No value given for one or more required parameters

Hi Dave,

By running the page you provided and perform several refreshes, I did be
able to get such an error screen:

===============
Error occurred when opening recordset:
No value given for one or more required parameters.
------------------------------------------------------------ ----------------
----
sql contains "SELECT questionid, question, Hint_Image FROM question"
=================

So far, based on my research in some former cases, most of the error are
caused by set recordset's commandtype or parameter incorrectly(at our code
part) or something incorrect with the db provider.

Also, as you mentioned that it only occurs randonly on that particular box,
I think problem is likely coupled with the ADO component or db provider on
that server rather than code logic. Due to the complexiy on troubleshooting
or debugging on this, I would suggest you consider contact CSS and open an
support incident on this if you feel this an urgent and important issue.

http://msdn.microsoft.com/subscriptions/support/default.aspx

Anyway, if you have any other questions or anything we can help you ,please
feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

Re: "80040e10 Error: No value given for one or more required parameters

Thanks Steven

I am afraid that opening a support ticket may be problematic: this is a
hosted web site and I have no access to the server configuration settings or
hardware. The only thing I control is the ASP code which I push to the
hosted web server from our DEV server.

My web hosting company, MyHosting.com (SoftCom Technology), has reproduced
the problem but according to their tech support contact ""They have
determined that something is missing in your code but are unable to assist
further."

I do not think the problem is with the code because 1) the error message is
not consistent with the code (it states that "No value given for one or
more required parameters" for a query that does not use parameters); 2) I am
unable to produce the error on another server; and 3) the error is
inconsistent; sometimes the page renders properly and sometimes it does not.
Code, even bad code, tends to produce consistent results.

There is no dispute that this intermittent problem exists; the mystery is
the source. As I see it, there are three possiblities:
1. a coding error
2. a server configuration or hardware problem
3. a bug in Microsoft server or application software

MyHosting.com does not have technical phone support, all incidents must be
handled through email. I emailed them and asked 1) if we could try placing
my web on a different server? 2) Are they sure the server is using the
latest MDAC and service packs? and 3) If I open a tech support incident
with Microsoft can you provide support and answer their questions?

This is the response I received:

Greetings,

Domain : frickcpa.com

Our servers are patched with the latest Service Pack 2 and so is the
MDAC. If you open a support ticket with microsoft, you have to provide
support and answer their questions.

If you need any further assistance, please do not hesitate to contact our
24/7 support team at support@myhosting.com.

Thank you for choosing myhosting.com as your web hosting provider.
Regards,
Belinda
Customer Support
http://myhosting.com
http://mail2web.com
http://softcom.biz


Thus I do not believe they are going to be very coorportaive in helping to
resolve this issue.

Can you give me any insight into how Microsoft might be able to provide
techncal support in a case like this where we must deal with a recalcitrant
web hosting provider?

Thanks
Dave

Re: "80040e10 Error: No value given for one or more required parameters

Thank you Bob.

I tried all 3 different variations of opening the recordset but the error
still returns intermittently. Sometimes I must request the page more than
250 times to produced the error, other times I will get it straight off
after I post a change.

Below is posted a simplified bare-bones version of my code that will produce
the error.

In your opinion, is there anything in there that could possibly be throwing
this error?

I am aware that I am using non-standard technologies (classic ASP rather
than .Net and an Access rather than SQL Server database). But these are
mature technologies and they should be reliable as technologies go.

Do you think it may be related to ASP 3.0 or possibly the Access database?
Am I even connecting to the database before it errors out?

Have you ever encountered corrupt MDAC components that might throw out some
unrelated error messages? Or maybe the ISAPI extension / ASP.dll are
corrupt? Are these still supported or has certain of the functionality been
depreciated? Are there any tests that can be performed to validate
server/MDAC functionality?

I am just throwing ideas out here in the hope that it may trigger a thought
as to the possible cause.

Thanks for your help
Dave


cn_app= "PROVIDER=MICROSOFT.JET.OLEDB.4.0; " & _
"DATA SOURCE=e:\inetpub\xxxxxx\xxxxx\tvom.mdb"



<%@ Language=VBScript %>
<%
Option Explicit
Response.Buffer = True
%>

Re: "80040e10 Error: No value given for one or more required parameters

Dave wrote:
> Thank you Bob.
>
> I tried all 3 different variations of opening the recordset but the
> error still returns intermittently. Sometimes I must request the
> page more than 250 times to produced the error, other times I will
> get it straight off after I post a change.
>
> Below is posted a simplified bare-bones version of my code that will
> produce the error.
>
> In your opinion, is there anything in there that could possibly be
> throwing this error?
>

Sorry. I see nothing in there that could cause that error. I have never
encountered an intermittent "missing parameter value" error such as you are
getting. Whenever I have encountered the error, both in my own coding and in
posts to these newsgroups, it has always been both constant and easily
traceable to an error in the sql statement.

Grasping at straws ... perhaps a corrupted Access database? Try compacting
and repairing it.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: "80040e10 Error: No value given for one or more required parameters

Thanks for your reply Dave,

Yes, you're right. I've missed the point that your application is suffering
the problem on the public hoster site. IMO, I think the problem is much
likely on the server machine's data accessing component or anything related
to the database communication. For the code logic, it is straighforward to
verify:

** run a simple typical ADO page to see the problem

** move the same page to another server to test

Have you tried a typical ADO page for testing on that hoster? If the host
provider insist that the problem is in our code logic, it does be quite
hard to continue work on the issue.

Anyway, please feel free to let me know if there is anything we can help.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

Re: "80040e10 Error: No value given for one or more required parameters

Steven & Bob

Thank you for all your help. I am pleased to report that the issue has been
resolved!

First let me give credit where credit is due. Brian, the technical lead at
MyHosting.com (Softcom Technology) worked very hard with me at resolving
this problem (recording over 3K page hits).

What he eventually came up with is that the raw SQL in the ASP is case
sensitive. (See
http://www.candypress.com/Cpforum/forum_posts.asp?TID=7719).

So referencing a lowercase field name in the ASP as uppercase will
intermittently throw the totally unrealted error message "No value given for
one or more required parameters."

Once the SQL in the ASP was changed to match the case of the names in the
database, the error stopped and has not recurred.

Of course I must take the blame for using raw SQL in an ASP when I should
have been calling a saved query.

Also I should point out that this error occured using an Access 2000 file.
I initially misspoke and said I was using Access 2003. I was using the
Access 2003 application but the mdb file was in Access 2000 file format.

In any event, I decided to upgrade the entire web application to SQL Server
this week-end utilizing stored procedures like I should have in the very
beginning. It's been a very long week-end.

Steven: Is this worth a KB article to alert others to the problem?

Thanks again for your help.
Dave

Re: "80040e10 Error: No value given for one or more required parameters

Dave wrote on 13 aug 2007 in microsoft.public.inetserver.asp.general:

> Steven & Bob
>
> Thank you for all your help. I am pleased to report that the issue
> has been resolved!
>
> First let me give credit where credit is due. Brian, the technical
> lead at MyHosting.com (Softcom Technology) worked very hard with me at
> resolving this problem (recording over 3K page hits).

I agree. The few times I needed the help of the MyHosting team over the
years, it was always prompt, even disregarding the [usually 6 hour] time
zone difference.

> What he eventually came up with is that the raw SQL in the ASP is case
> sensitive. (See
> http://www.candypress.com/Cpforum/forum_posts.asp?TID=7719).
>
> So referencing a lowercase field name in the ASP as uppercase will
> intermittently throw the totally unrealted error message "No value
> given for one or more required parameters."
>
> Once the SQL in the ASP was changed to match the case of the names in
> the database, the error stopped and has not recurred.
>
> Of course I must take the blame for using raw SQL in an ASP when I
> should have been calling a saved query.
>
> Also I should point out that this error occured using an Access 2000
> file. I initially misspoke and said I was using Access 2003. I was
> using the Access 2003 application but the mdb file was in Access 2000
> file format.
>
> In any event, I decided to upgrade the entire web application to SQL
> Server this week-end utilizing stored procedures like I should have in
> the very beginning. It's been a very long week-end.

4 questions remain:

1 Why is the problem intermittent?
[surely Jet-SQL is not "sometimes" case sensitive?]

2 Why would a stored procedure not be case sensitive, when a direct
["raw"?] SQL is?
[I am doing fine without stored procedures for years (sorry, Bob.),
but I am a sticker for lowercase field names without knowing the hidden
benefits]

3 Does this have to do with the different Access implentation files,
or is that unrelated thing? What harm did that do?

4 "totally unrelated" error message.
It seems to me that "No value given for one or more required parameters."
is a good way of describing that the parameter of WHERE does not point to
an existing field name. Is this a question of mine? ;-)


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: "80040e10 Error: No value given for one or more required parameters

Evertjan. wrote:
> 4 questions remain:
>
> 1 Why is the problem intermittent?
> [surely Jet-SQL is not "sometimes" case sensitive?]

Extremely good question.

>
> 2 Why would a stored procedure not be case sensitive, when a direct
> ["raw"?] SQL is?

Another good question. The answer may be that with a stored procedure, the
sql statement encapsulated in the procedure cannot be effected by the client
application.
it seems to me that this might somehow be a code page issue. Jet is NOT case
sensitive. Perhaps the incorrect characters are being sent for some reason.
That doesn't make sense to me either - pure speculation.


> [I am doing fine without stored procedures for years (sorry, Bob.),
> but I am a sticker for lowercase field names without knowing the
> hidden benefits]

I don't need to maintain your code so there is no need to apologize to me
for using less efficient techniques ;-)

>
> 3 Does this have to do with the different Access implentation files,
> or is that unrelated thing? What harm did that do?
>

I'm thinking that it might confirm the the "corrupted database" hypothesis I
floated a couple posts ago.

> 4 "totally unrelated" error message.
> It seems to me that "No value given for one or more required
> parameters." is a good way of describing that the parameter of WHERE
> does not point to an existing field name. Is this a question of mine?
> ;-)

Agreed.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: "80040e10 Error: No value given for one or more required parameters

Hi Dave,

Thanks for your followup and share the result to all of us.

Sure, I agree that it's a good idea to work out a KB article on this issue.
Actually, I think this thread has already been a good reference for anyone
else who meet the similar problem.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

Re: "80040e10 Error: No value given for one or more required parameters

Steven Cheng[MSFT] wrote on 14 aug 2007 in
microsoft.public.inetserver.asp.general:

> Hi Dave,
>
> Thanks for your followup and share the result to all of us.
>
> Sure, I agree that it's a good idea to work out a KB article on this
> issue.

What issue?

Oh please Steven,

the "Always quote on usenet" does not stop at the doors of Microsoft.

> Actually, I think this thread has already been a good reference
> for anyone else who meet the similar problem.
>
> Sincerely,
>
> Steven Cheng
>
> Microsoft MSDN Online Support Lead

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: "80040e10 Error: No value given for one or more r...

Hello! Good Site! Thanks you! gnvukdqrttq