'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_onS tart()

Application.Loc k

sCn_App= "PROVIDER=MICRO SOFT.JET.OLEDB. 4.0; " & _
"DATA SOURCE=e:\inetp ub\clients\fric kcpa.com\fpdb\t vom.mdb"

Application.Con tents.Item("cn_ App")=sCn_App

Application.UnL ock

End Sub

In ASP page:

<%

Dim sql,rs

Dim cn
Set cn = Server.CreateOb ject("ADODB.Con nection")
cn.open Application("cn _App")
sql="SELECT questionid, question, Hint_Image FROM question"

Set rs = Server.CreateOb ject("ADODB.rec ordset")

With rs
.ActiveConnecti on=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
.ActiveConnecti on=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/subscripti...ult.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/subscripti...t/default.aspx.

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

[Anthony Jones]

"Dave" <da*******@news group.nospamwro te in message
news:ug******** ******@TK2MSFTN GP03.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_onS tart()

Application.Loc k

sCn_App= "PROVIDER=MICRO SOFT.JET.OLEDB. 4.0; " & _
"DATA SOURCE=e:\inetp ub\clients\fric kcpa.com\fpdb\t vom.mdb"

Application.Con tents.Item("cn_ App")=sCn_App

Application.UnL ock

End Sub

In ASP page:

<%

Dim sql,rs

Dim cn
Set cn = Server.CreateOb ject("ADODB.Con nection")
cn.open Application("cn _App")
sql="SELECT questionid, question, Hint_Image FROM question"

Set rs = Server.CreateOb ject("ADODB.rec ordset")

With rs
.ActiveConnecti on=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

[Bob Barrows [MVP]]

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

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

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.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. ...

>

<snip>

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

Set rs = Server.CreateOb ject("ADODB.rec ordset")

With rs
.ActiveConnecti on=cn

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

Set .ActiveConnecti on=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?

--
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"

[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]]

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:<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.CreateOb ject("ADODB.Con nection")
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.CreateOb ject("ADODB.Con nection")
cn.open Application("cn _App")
sql="SELECT questionid, question, Hint_Image FROM question"
Set rs = Server.CreateOb ject("ADODB.rec ordset")
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
--
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"

[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/subscripti...t/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 su*****@myhosti ng.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

[Bob Barrows [MVP]]

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"

[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.