Solved

Intermittent error '80040e10' -

Posted on 2006-11-01
23
348 Views
Last Modified: 2012-08-14
Having moved my site to a new host, I'm getting the following error about 1 in every 10 times I view the page.

-------------------------------------------
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/S_includes/header.asp, line 4
--------------------------------------------

The SQL is really simple

"select * from pages where id = "& pageid

which response.writes as

"select * from pages where id = 1"

so I know it's not the normal 80040e10 error where field names are spelt wrongly, are illegal or where the variable (pageid) is empty or of the wrong type.

Any ideas?

0
Comment
Question by:Garve
  • 12
  • 3
  • 2
  • +4
23 Comments
 
LVL 6

Expert Comment

by:davecestria
ID: 17848349
Can you post your code please.
0
 
LVL 7

Author Comment

by:Garve
ID: 17848387
For what it's worth.


Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=e:\inetpub\abc\db.mdb"

pageid = 1
mysql = "select * from pages where id = "& pageid &" "
response.write(mysql &"<br>")
set rs = con.execute(mysql)

response.write(rs("title"))

rs.close
set rs=nothing
con.close
set con=nothing
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17849053
ok, so pageid always equals 1?
0
 
LVL 7

Author Comment

by:Garve
ID: 17849185
For these test purposes yes - the exact code as given above (only with a different db path) gives the error between 5 and 10% of the time.

I thought perhaps the query was being executed before the connection to the database had been opened properly might be an explanation - just a wild guess though.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17849245
you are executing the query correctly and in the right sequence....

this is a strange case indeed...

instead of selecting all records (the *) try selecting only title
0
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17849497
I noticed your database is located on Drive e: - Is this a local disk or a mapped network drive? The reason I ask is because I've suffered intermittent errors on DB communications in the past that often give a misleading cause of the error and quite often the problem is due to network problems or permissions.

If the drive is a local, fixed disk then feel free to ignore this avenue and continue down the current route of investigation.
0
 
LVL 7

Author Comment

by:Garve
ID: 17849542
Actually, it's new hosting we've arranged with a big ISP.  (I changed the path in the code above). It might well be the case that the database is on a different box from the ASPs.
0
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17849638
In that case, the problem may not be the SQL itself (which looks perfectly fine BTW) but rather a DB communication issue.

The method I used to detect/cure this was to write a function to connect to the DB, retrieve the recordset and pass the results (if any) back to the caller. This allowed me to trap for errors in one place and also helped me to reduce the code by not having to repeatedly write the code to connect to the DB.

Additionally, I used a single DB connection for all queries which I set as part of the Application_OnStart event in the GLOBAL.ASA file. This approach produces mixed responses from ASP developers, depending on their own personal view but the benefit for my application was, only I only ever needed to check if one DB connection was already connected, if not, force it to reconnect before using it to return my results.

Sadly I'm at work at the moment so I don't have direct access to my own sample code at home (our firewall here blocks a lot of useful ports) but I'm sure others can supply some samples in the meantime, failing that, wait till I get home in about 4-5 hours and I'll happily dig out something for you.
0
 
LVL 12

Expert Comment

by:Preece
ID: 17850378
Curiously, I didn't see this line in your code:

set rs=Server.CreateObject("ADODB.Recordset")

Preece
0
 
LVL 7

Author Comment

by:Garve
ID: 17850611
Hi Preece

No, I never use that - doesn't seem to cause any problems!!!

Dragonlaird

I'll await your code - in the meantime I'll contact the hosting support and ask them if they've had anything similar.
0
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17850636
I managed to retrieve some code for you to experiment with...

First thing to note, the connection string for the DB is stored in Application("DBC") - Modify this to suit your needs...

Next thing to note, you no-longer need to write the code to open/close DB connections for every SQL statement, just call it like this

Set rs = NewRS(sSQL)
... Use your recordset as before
Set rs = Nothing

Now the functions... I've stripped a bit of code out that's not required as it was specific to my application but the resulting functions should still work... I've used another Application variable to help you detect if the DB is not connected... Application("Status") = "Offline" || "Online"

      Function NewRS(sSQL)
            On Error Resume Next
            If Application("DB") = "" Or IsNull(Application("DB")) Or Application("DB") = Nothing Then
                  Call DB_Connect()
            End If
            If Application("DB").State = adStateClosed Then
                  ' DB is offline - try to reconnect
                  Call DB_Connect()
            End If
            ' Hopefully, DB is now connected, if not we ignore the request
            ' Now we execute the command and return any results
            NewRS.Close
            Set NewRS = Nothing
            Err.Clear
            Set NewRS = Server.CreateObject("ADODB.RecordSet")
            If (Application("DB").State AND adStateOpen) = adStateOpen Then
                  Set NewRS.ActiveConnection = Application("DB")
                  NewRS.LockType = 3
                  NewRS.Open sSQL
            End If
      End Function

      Sub DB_Connect()
            Dim CN
            Dim RS
            Dim sSQL
            ' DB/ResultSet Status
            Const adSchemaTables = 20
            Const adSchemaColumns = 4
            On Error Resume Next
            ' Create the initial database connection
            If (Application("DB").State And adStateOpen) = adStateOpen Then
                  ' DB already connected?
                  Exit Sub
            Err.Clear
            ' Seems to be disconnected - Try reconnecting
            Application.Lock
            Set Application("DB") = Nothing
            Set Application("DB")=Server.CreateObject("ADODB.Connection")
            Application("DB").ConnectionTimeout = 10
            Application("DB").Open Application("DBC")
            If Err.Number <> 0 Then
                  ' Database connection failed
                  Application("Status") = "Offline"
            Else
                  If (Application("DB").state And adStateOpen) = adStateOpen Then
                        ' DB Connection is open
                        Application("Status") = "Online"
                  Else
                        ' DB Connection is not open
                        Application("Status") = "Offline"
                  End If
            End If
                Application.Unlock
      End Sub
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 20

Expert Comment

by:jitganguly
ID: 17851652
id could be a reserved word, try with braces

"select * from pages where [id] = "& pageid
0
 
LVL 7

Author Comment

by:Garve
ID: 17865043
jitganguly - that's certainly not the problem, as it would cause an error every time if it were. However, out of desperation I tried it just to be sure, but still get the problem.

My webhost responded to tell me that my www and private folders (ie my ASPs and my DB) were on the same machine, but just in case I moved the DBs into the www folder and still get the problem.

Dragonlaird - I'm now going to have a try with some of your ideas.
0
 
LVL 7

Author Comment

by:Garve
ID: 17930032
Further info:-

I decided to create a very simple test page.

I have an Access 2002 database with one table - table1
It has one record with two fields - ID and textinfo - ID is autonumber, textinfo is text

The page (bug.asp) connects to the database, runs a query and returns the text to the page. The page refreshes every 2 seconds and has an incremental counter. I can't really make it any simpler.

The code is as follows:-

--------------------------------------------

<%
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=D:\wwwroot\test.mdb"

con.execute("update table1 set textinfo = 'Page is still working' where id = 1")

set rs = con.execute("select textinfo from table1 where id = 1")

nooftimes = request("no")
if nooftimes = "" then nooftimes = 1
%>

<html>
<head>
<meta http-equiv="refresh" content="5;url=bug.asp?no=<%=nooftimes + 1%>">
</head>

<body>
Text from database is:- <%=rs("textinfo")%>.<br>No of refreshes is: <%=nooftimes%>.
</body>
</html>
<%
rs.close
set rs=nothing
con.close
set con=nothing
%>

------------------------------------------------------------------

Sometimes it'll keep refreshing up to 50 times, other times it'll only do it once or twice before it comes up with this error:-

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/bug.asp, line 5

------------------------------------------------------------------

Any other ideas how I can get more information about why this is happening????
0
 
LVL 7

Author Comment

by:Garve
ID: 17930392
My hosts advised me to use a different connection string.

Set Con = Server.CreateObject("ADODB.Connection")
conString = "DBQ=d:\p\l\plexusmedia\private\test.mdb"
Con.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & conString

Still getting an intermittent error - looks to be the same though the wording's slightly different.

----------------------------------------

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/bug.asp, line 6
0
 
LVL 7

Author Comment

by:Garve
ID: 17954424
Further update on this one for those of you still interested.

Have determined that the error only occurs when a WHERE clause is part of the SQL.

set rs = con.execute("select textinfo from table1 where id = 1") causes the intermittent error

but

set rs = con.execute("select textinfo from table1") does not.

Anybody have any ideas?
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 17955273
Use square braces.
Select textinfo from table1 where [Id]=1
0
 
LVL 7

Author Comment

by:Garve
ID: 17956335
Again, out of desperation I've tried that now

set rs = con.execute("select textinfo from table1 where [id] = 1")

but still have the problem. If ID were a reserved word it would be very strange as Microsoft automatically make it the default name for an Autonumber field. Anyway, if it were a reserved word it would cause the error every time, not one in 10 times as it does here.

cheers

Garve
0
 
LVL 7

Author Comment

by:Garve
ID: 17963600
Still need help - increased to 500 points.
0
 
LVL 7

Author Comment

by:Garve
ID: 17980250
Tentatively, it looks as though I might have solved this !!!

It seems like the connection is sometimes CASE SENSITIVE

set rs = con.execute("select textinfo from table1 where id = 1") causes the intermittent error

but

set rs = con.execute("select textinfo from table1 where ID = 1") doesn't!

Phew!!!

Why this should be the case on this hosting but not on others, I don't know, and am even more puzzled why it should be intermittent?????
0
 
LVL 7

Author Comment

by:Garve
ID: 18004507
I've requested that my answer be marked as the solution, but that I can then split the points between all those who helped. It seems this isn't possible, so if one of you would copy my answer and post it yourselves then I'll accept that as the solution and split the points.

I don't want this question to be deleted as it will hopefully help someone else in the future.

cheers

Garve
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 18025265
PAQd, 500 points refunded.

DarthMod
CS Moderator
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now