Intermittent database error

[Dave]

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

 

[Bob Barrows [MVP]]

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

 

[vicky]

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

 

[Bob Barrows [MVP]]

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

 

[Dave]

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?

 

[Anthony Jones]

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

 

[Anthony Jones]

From distant memory (I have

NOT (why hasn't some one come up with a 'thought-to-text' UI by now

 

[Bob Barrows [MVP]]

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.

 

[Anthony Jones]

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

Oops so you did. My bad.