By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,359 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,359 IT Pros & Developers. It's quick & easy.

Intermittent database error

P: n/a
I am getting an intermittent database error on my asp page.

I am using Access 2003 with classic ASP.

The error is this:
Microsoft JET Database Engine Error 80040e10
No value given for one or more required parameters
/subweb/search.asp, line 163

Line 163 is starred below:

With rs
.CursorLocation=3 'adUserClient
.ActiveConnection=cn
.source=sSql
.Open <-error here
End With

The
May 26 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Dave wrote:
I am getting an intermittent database error on my asp page.

I am using Access 2003 with classic ASP.

The error is this:
Microsoft JET Database Engine Error 80040e10
No value given for one or more required parameters
/subweb/search.asp, line 163

Line 163 is starred below:

With rs
.CursorLocation=3 'adUserClient
.ActiveConnection=cn
.source=sSql
.Open <-error here
End With

Without seeing the sql statement or how it is built, one can only guess.

The error can be caused by:
1. Using a word in the query which cannot be resolved to an object (such as
a field or table) in the database. In this case, Jet assumes that you are
attempting to use a parameter and, when it finds no value for the parameter
has been passed, throws an error.
2. Rarely, using a reserved keyword for a database object name can cause
this error, but more likely this will result in a syntax error being thrown.

My suggestion, given that the symptom is intermittent, is to trap the error
and log the sql statement to a text file so it can be inspected.

Bob Barrows

--
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"
May 26 '06 #2

P: n/a
Hi Dave,

Just check if you are passing a blank value in sql query, print the
query and check it, if a blank value is getting passed then put a '-'
or any valid character.

Vicky

May 27 '06 #3

P: n/a
Thanks Bob and Vickey.

I get this error inconsistently using the same parameters.

For example, when I click on link to load a new page, I pass a parameter to
load a record in a new page. I can click on the same link over and over
again and sometimes it works and sometimes it errors out.

I am wondering if the problem may be due to the use of aggregates in a view
(saved query).

Here is what my code looks like in the ASP page:

sSQL= "SELECT WordID, Word, "
sSQL= ssql & "CountExample, CountMastery, CountProficient, CountDeficient
"
sSQL= ssql & " FROM vWords "

if sFirstLetterSelect <> "All" then
sWhere = " WHERE word LIKE '" & trim(sFirstLetterSelect) & "%' "
sSQL = sSQL & sWhere
end if

Select Case lcase(sSort)
case "word":
sSQL = sSQL & " ORDER BY word"
case "wordid":
sSQL = sSQL & " ORDER BY wordid, word"
case "countexample":
sSQL = sSQL & " ORDER BY countexample DESC, word"
case "countdeficient":
sSQL = sSQL & " ORDER BY countdeficient DESC, word"
case "countproficient":
sSQL = sSQL & " ORDER BY countproficient DESC, word"
case "countmastery":
sSQL = sSQL & " ORDER BY countmastery DESC, word"
case else
sSQL = sSQL & " ORDER BY word"
End Select
When I print out the SQL string with Response.Write(sSQL) it looks like
this:

SELECT WordID, Word, CountExample, CountMastery, CountProficient,
CountDeficient FROM vWords WHERE word LIKE 'A%' ORDER BY word

What is a little different from what I normally do in Access is I reference
a saved query rather than a table. So vWord is a view with the following
definition:

SELECT Word.WordID
, Word.Word
, (SELECT count(*) from WordExample WHERE wordid=word.wordid ) AS
CountExample
, (SELECT count(*) from Answer WHERE gradeid=1 and
answer.wordid=word.wordid ) AS CountMastery
, (SELECT count(*) from Answer WHERE gradeid=2 and
answer.wordid=word.wordid ) AS CountProficient
, (SELECT count(*) from Answer WHERE gradeid=3 and
answer.wordid=word.wordid ) AS CountDeficient
FROM Word;

I though that perhaps if the wordid is null in the Answer table then the
query might choke. However, this is not the case; there are times when
wordid is null and the query executes properly.

Does anyone see any potential problems with what I am doing?




May 28 '06 #4

P: n/a
Dave wrote:
Thanks Bob and Vickey.

I get this error inconsistently using the same parameters.

When I print out the SQL string with Response.Write(sSQL) it looks
like this:

SELECT WordID, Word, CountExample, CountMastery, CountProficient,
CountDeficient FROM vWords WHERE word LIKE 'A%' ORDER BY word

Is this the sql statement that caused the error? I.E., did you trap the
error and response.write it when the error occurred?


I though that perhaps if the wordid is null in the Answer table then
the query might choke. However, this is not the case; there are
times when wordid is null and the query executes properly.

Does anyone see any potential problems with what I am doing?


I see nothing that would lead to this error.

I would suggest using a saved parameter query instead of dynamic sql ...

--
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"
May 28 '06 #5

P: n/a

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:ua**************@TK2MSFTNGP04.phx.gbl...
Dave wrote:
Thanks Bob and Vickey.

I get this error inconsistently using the same parameters.

When I print out the SQL string with Response.Write(sSQL) it looks
like this:

SELECT WordID, Word, CountExample, CountMastery, CountProficient,
CountDeficient FROM vWords WHERE word LIKE 'A%' ORDER BY word

Is this the sql statement that caused the error? I.E., did you trap the
error and response.write it when the error occurred?


I though that perhaps if the wordid is null in the Answer table then
the query might choke. However, this is not the case; there are
times when wordid is null and the query executes properly.

Does anyone see any potential problems with what I am doing?


I see nothing that would lead to this error.


From distant memory (I have used JET in earnest for some time now) but
doesn't JET treat any identifier not in the fieldset as parameter. I seem
to remember getting this error often because of a typo in field name left
JET thinking it was looking for a parameter.

I would suggest using a saved parameter query instead of dynamic sql ...

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

May 29 '06 #6

P: n/a

"Anthony Jones" <An*@yadayadayada.com> wrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:ua**************@TK2MSFTNGP04.phx.gbl...
Dave wrote:
Thanks Bob and Vickey.

I get this error inconsistently using the same parameters.

When I print out the SQL string with Response.Write(sSQL) it looks
like this:

SELECT WordID, Word, CountExample, CountMastery, CountProficient,
CountDeficient FROM vWords WHERE word LIKE 'A%' ORDER BY word

Is this the sql statement that caused the error? I.E., did you trap the
error and response.write it when the error occurred?


I though that perhaps if the wordid is null in the Answer table then
the query might choke. However, this is not the case; there are
times when wordid is null and the query executes properly.

Does anyone see any potential problems with what I am doing?


I see nothing that would lead to this error.


From distant memory (I have


NOT (why hasn't some one come up with a 'thought-to-text' UI by now :)
used JET in earnest for some time now) but
doesn't JET treat any identifier not in the fieldset as parameter. I seem
to remember getting this error often because of a typo in field name left
JET thinking it was looking for a parameter.

I would suggest using a saved parameter query instead of dynamic sql ...

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


May 29 '06 #7

P: n/a
Anthony Jones wrote:
When I print out the SQL string with Response.Write(sSQL) it looks
like this:

SELECT WordID, Word, CountExample, CountMastery, CountProficient,
CountDeficient FROM vWords WHERE word LIKE 'A%' ORDER BY word

Is this the sql statement that caused the error? I.E., did you trap
the error and response.write it when the error occurred?


I though that perhaps if the wordid is null in the Answer table then
the query might choke. However, this is not the case; there are
times when wordid is null and the query executes properly.

Does anyone see any potential problems with what I am doing?


I see nothing that would lead to this error.


From distant memory (I have used JET in earnest for some time now) but
doesn't JET treat any identifier not in the fieldset as parameter. I
seem to remember getting this error often because of a typo in field
name left JET thinking it was looking for a parameter.

I believe I stated that in my first reply ... yes, that was the first reason
I mentioned.

--
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"
May 29 '06 #8

P: n/a

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:e4**************@TK2MSFTNGP05.phx.gbl...
Anthony Jones wrote:
When I print out the SQL string with Response.Write(sSQL) it looks
like this:

SELECT WordID, Word, CountExample, CountMastery, CountProficient,
CountDeficient FROM vWords WHERE word LIKE 'A%' ORDER BY word
Is this the sql statement that caused the error? I.E., did you trap
the error and response.write it when the error occurred?

I though that perhaps if the wordid is null in the Answer table then
the query might choke. However, this is not the case; there are
times when wordid is null and the query executes properly.

Does anyone see any potential problems with what I am doing?

I see nothing that would lead to this error.

From distant memory (I have used JET in earnest for some time now) but
doesn't JET treat any identifier not in the fieldset as parameter. I
seem to remember getting this error often because of a typo in field
name left JET thinking it was looking for a parameter.

I believe I stated that in my first reply ... yes, that was the first

reason I mentioned.
Oops so you did. My bad. :)

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

May 30 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.