We help IT Professionals succeed at work.

Intermittent error '80040e10' -

Garve
Garve asked
on
Medium Priority
391 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?

Comment
Watch Question

Can you post your code please.

Author

Commented:
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

Commented:
ok, so pageid always equals 1?

Author

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

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

Author

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

Commented:
Curiously, I didn't see this line in your code:

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

Preece

Author

Commented:
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.
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
id could be a reserved word, try with braces

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

Author

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

Author

Commented:
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????

Author

Commented:
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

Author

Commented:
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?
Use square braces.
Select textinfo from table1 where [Id]=1

Author

Commented:
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

Author

Commented:
Still need help - increased to 500 points.

Author

Commented:
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?????

Author

Commented:
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
Commented:
PAQd, 500 points refunded.

DarthMod
CS Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.