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

[Dave]

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

%>

 

[Steven Cheng[MSFT]]

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

 

[Anthony Jones]

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.

 

[Bob Barrows [MVP]]

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.inetserver.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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

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

 

[Dave]

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

 

[Bob Barrows [MVP]]

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:<br>"
response.write err.description & "<hr">
response.write "sql contains """ & sql & """<hr>"
cn.close:set cn=nothing
response.end
end if
on error goto 0


Also, try these variants:

Dim sql,rs
Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.open Application("cn_App")
sql="SELECT questionid, question, Hint_Image FROM question"
on error resume next
set rs=cn..execute(sql,,1) '1=adCmdText

if err<>0 then
response.write "Error occurred when opening recordset:<br>"
response.write err.description & "<hr">
response.write "sql contains """ & sql & """<hr>"
cn.close:set cn=nothing
response.end
end if
on error goto 0


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")
on error resume next
rs.Open sql,cn,,,1 '1=adCmdText

if err<>0 then
response.write "Error occurred when opening recordset:<br>"
response.write err.description & "<hr">
response.write "sql contains """ & sql & """<hr>"
cn.close:set cn=nothing
response.end
end if
on error goto 0

 

[Steven Cheng[MSFT]]

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.

 

[Dave]

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 (e-mail address removed).

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

 

[Dave]

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
%>
<html>
<head>
</head>
<body>
<!--#include file="includes/db_connections.inc"-->
<table border="0" cellspacing="2" cellpadding="3">
<tr>
<td>
Test page
</td>
</tr>
<%
Dim sql,rs
Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.open cn_App

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

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

With rs
.ActiveConnection=cn
.cursortype = 0
.LockType = 1
.source = sql
End With

on error resume next

'rs.Open ,,,,1
'set rs=cn.execute(sql,,1)
rs.Open sql,cn,,,1 '1=adCmdText

if err<>0 then
response.write "Error occurred when opening recordset:<br>"
response.write err.description & "<hr>"
response.write "sql contains """ & sql & """<hr>"
cn.close:set cn=nothing
response.end
end if
on error goto 0

Do while not rs.eof
%>

<tr>
<td>

<%Response.Write (rs.Fields("questionid") & ".")%>
&nbsp;
<%Response.Write (rs.Fields("question")) %>
</td>
</tr>

<%
rs.movenext

Loop

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

If cn.State Then
cn.Close
End If

%>

</table>
</body>
</html>

 

[Bob Barrows [MVP]]

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.

 

[Steven Cheng[MSFT]]

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.

 

[Dave]

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

 

[Evertjan.]

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? ;-)

 

[Bob Barrows [MVP]]

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.

 

[Steven Cheng[MSFT]]

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.

 

[Evertjan.]

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.

 

[xvaducpctc]

Hello! Good Site! Thanks you! gnvukdqrttq