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

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"

[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

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

[Bob Barrows [MVP]]

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"

[Anthony Jones]

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

[Anthony Jones]

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

[Bob Barrows [MVP]]

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"

[Anthony Jones]

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