Home > Archive > ASP > May 2006 > Intermittent database error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Intermittent database error
|
|
|
| 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] 2006-05-26, 6:56 pm |
| 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"
| |
|
| 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
| |
|
| 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] 2006-05-28, 6:56 pm |
| 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 2006-05-29, 7:56 am |
|
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uaj4XsngGHA.1324@TK2MSFTNGP04.phx.gbl...
> Dave wrote:
>
>
>
>
> 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.
> 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 2006-05-29, 7:56 am |
|
"Anthony Jones" <Ant@yadayadayada.com> wrote in message
news:%23cc6Z8wgGHA.4892@TK2MSFTNGP02.phx.gbl...
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:uaj4XsngGHA.1324@TK2MSFTNGP04.phx.gbl...
>
> 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.
>
>
>
>
| |
| Bob Barrows [MVP] 2006-05-29, 7:56 am |
| Anthony Jones wrote:
>
> 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 2006-05-30, 3:55 am |
|
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:e4zvsUygGHA.4388@TK2MSFTNGP05.phx.gbl...
> Anthony Jones wrote:
> 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"
>
>
|
|
|
|
|