Ouch: 80004005 (adding multiple records)

PeterVR · September 3rd, 2004, 07:44 AM

I'm writing a Forum; when a message is being posted it's being cut into 50-char-blocks and each block is being saved into a separate Access DB record.

Everything seems to be going well but when messages get "large" (say over 250 chars) I sometimes get the infamous
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Could not update; currently locked by user 'admin' on machine 'NET3-NL-IIS-40'.

/SP/inc/SubInputMessage.asp, line 39
When entering the same message, the error can occur after the third block as well as after the seventh, the fifteenth, ... There really is no system. Sometimes the posting succeeds.

Underneath is SubInputMessage.asp, the file throwing the error.
Anyone find the catch?
(BTW who is the "admin" user blocking my DB? Could this be my provider running some checking program??)

Thanks
Peter VR.

<% Sub InputMessage(Act, MsgID, MsgSubj, MsgTxt, MsgSeq, Menu, Thread, Author, ReqVal, Link, LinkEnd)

    Dim cnnDB_NewMg ' DB Connectie
    Dim NewRS ' Recordset van Berichten
    Set NewRS = Server.CreateObject("ADODB.Recordset")
    Dim InsertQ ' Querystring

    InsertQ = "SELECT * FROM Bericht WHERE MsgID=#" & MsgID & "#"

     Set cnnDB_NewMg = Server.CreateObject("ADODB.Connection")
    cnnDB_NewMg.Open "DBQ=D:\www\database\SP.mdb; DRIVER={Microsoft Access Driver (*.mdb)}", "", ""

  NewRS.Open InsertQ, cnnDB_NewMg, 0, 2

  Response.Write InsertQ

    If Act="New" then
        NewRS.AddNew
    End If

    If Thread="" then
        Thread=NULL
    End If

    NewRS.Fields("MsgID") = MsgID
  NewRS.Fields("MsgSubj") = MsgSubj
  NewRS.Fields("MsgTxt") = Left(MsgTxt,50)
  NewRS.Fields("MsgSeq") = MsgSeq
  NewRS.Fields("Menu") = Menu
  NewRS.Fields("Thread") = Thread
  ' Response.Write "Thread: [" & Thread & "]"
  NewRS.Fields("Author") = Author
  NewRS.Fields("ReqVal") = ReqVal
  NewRS.Fields("Link") = Link
  NewRS.Fields("LinkEnd") = LinkEnd
' Response.Write MsgID
  NewRS.Update

  NewRS.Close
  Set NewRS = Nothing

    If Act="Update" then
        Call DelMessage(MsgID, "Tail")
    End If

    If Len(MsgTxt) > 50 then
         Dim TimeStamp
        TimeStamp=FormatDateTime(DateAdd("s",1,MsgID),vbge neraldate) ' 1 seconde verder!
        TimeStamp=Left(TimeStamp,Len(TimeStamp)-3)
       Call InputMessage("New", TimeStamp, NULL, Right(MsgTxt,Len(MsgTxt)-50), MsgID, Menu, Thread, Author, ReqVal, Link, LinkEnd)
    End If

  cnnDB_NewMg.Close
  Set cnnDB.NewMsg = Nothing
End Sub %>

happygv · September 3rd, 2004, 02:31 PM

Do you by any chance keep your mdb file open using ms access and try executing this asp file? That is the only reason I could think of seeing this error. Otherwise your code looks fine.

_________________________
- Vijay G
Strive for Perfection

PeterVR · September 6th, 2004, 04:24 AM

Not directly (I even have FTP-only access to my online files).

But what bothers me is this:
I work with include files a lot. My Main page looks like this:

<HTML>
<HEAD>
<TITLE>Startpunt</TITLE>
<LINK HREF="css/SP.css" REL="STYLESHEET" TYPE="text/css" CHARSET="iso-8859-2" HREFLANG="en-us">
</HEAD>

<BODY marginheight="0" marginwidth="0" topmargin="0" leftmargin="0" >
<% Session("RootPath")="" %>

<% Server.Execute("input/" & Session("MenPar") & "_" & Session("MenL2") & "_" & Session("MenL3") & ".asp") %>

<BR>

</BODY>
</HTML>

MainMenuCheckHeader checks the URL and gets the menu-values out of it.
MainMenuDBHeader opens DB connections.
StdMainPageStart takes care of the layout & Menu.

The real page contents are in <% Server.Execute("input/" & Session("MenPar") & "_" & Session("MenL2") & "_" & Session("MenL3") & ".asp") %>
which results in a file name like 3_20_34.asp (Three-layered menu: main menu no 3, first sub-menu no 20 and second sub-menu no 34.)

Now, my Forum is in the eighth main menu, in sub-menu no 42 so the main Forum page is called 8_42_0.asp
In opening this page I can't use the DB connection I opened in MainMenuDBHeader. I get this error when I try:
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/sp/input/8_42_0.asp, line 24

Re-opening the DB is the only thing that helps. Like you can see, I also need to re-open the DB in my Sub InputMessage.
This means I have three DB connections open:
- one in the Main page (used for menu)
- one in the Contents page (used for showing forum messages)
- one in the Sub (in order to be able to delete the record(s))
... and indeed in each next-level call (the Sub calls itself!...)

Am I doing anything wrong of do I indeed need to re-open the DB again and again? How else can I keep using the DB connection I opened first?

Cheers
Peter VR.

PeterVR · September 6th, 2004, 07:04 AM

Watching the code, I realised I could close my DBconn lots earlier, even before the InputMessage-call.
So I moved my
cnnDB_NewMg.Close
Set cnnDB.NewMsg = Nothing
lines until right after the
NewRS.Close
Set NewRS = Nothing
lines.
And it got rid of the 80004005 Error Message:
I got myself a brand new one!
Microsoft VBScript runtime error '800a0007'

Out of memory: 'Server.CreateObject'

/SP/inc/SubInputMessage.asp, line 7
Well a MEM overflow was also to be expected since I'm still just opening too many DB conn's I guess.
How can I keep working with one "centralised" DBconn???

happygv · September 6th, 2004, 03:42 PM

I would suggest you to have DB connection open related code in one include file and use that on top of the page where required, so that even if you wanted to used other include files within this page, you don't have to open the connection in thoss include files, as the connection is already open. That way you can make use of the same connection. But see to that you close the connection and set the connection object to nothing once at the end of this page. Follow the same in all other pages wherever db interaction is require.

Yout connection.inc or DBConn.asp would look like...

Code:

<%@ LANGUAGE="VBSCRIPT" %>
<%Option Explicit%>

<%
    Dim Conn

    Response.Expires = 0 
    Response.Buffer = True  
    Set conn = Server.CreateObject("ADODB.CONNECTION") 
    Conn.ConnectionTimeout = 30

    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.mappath("myDB.mdb") & ";User Id=admin;Password=;"
%>

Then you can code your page as

Code:

Some other code...

Some other code...

Some other code...

Conn.Close
Set Conn=Nothing

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

PeterVR · September 7th, 2004, 07:05 AM

That's exactly what I'm trying!..
In MainMenuDBHeader.asp I have this code:
Dim cnnDB ' DB Connectie
(...)
Set cnnDB = Server.CreateObject("ADODB.Connection")
cnnDB.Open "DBQ=D:\www\database\SP.mdb; DRIVER={Microsoft Access Driver (*.mdb)}", "", ""
and as you can see this file is included in the Main.asp file.
But when - further in the Main.asp file - I include any file called (for example) 7_25_0.asp I can't use my cnnDB - even though I only close it later in MainMenuDBFooter.asp. When I try, I get this error:
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/sp/input/7_25_0.asp, line 24

Could the reason be that I'm not working with an "include" command but with Server.Execute()? I have to, since I can't #include a file with variable file name...

happygv · September 11th, 2004, 08:55 AM

What is on line 24 in 7_25_0.asp ?

_________________________
- Vijay G
Strive for Perfection

PeterVR · September 14th, 2004, 06:36 AM

The recordset open command was on that line.

Anyway, I reckon the problems were all caused by including the files using a Server.Execute command instead of a genuine #include.
I got out of problems by changing the MainMenuDBHeader file into:
(...)
    Set Session("cnnDB") = Server.CreateObject("ADODB.Connection")
    Session("cnnDB").Open "DBQ=D:\www\database\SP.mdb; DRIVER={Microsoft Access Driver (*.mdb)}", "", ""
(...)
    Set Session("RecordSet") = Server.CreateObject("ADODB.Recordset")
(...)
Now whenever I need a recordset I can use the session variables for DBconn and RS.
Next to this I handled some memory overflow errors and now everything works fine!

Thanks for your help happygv --
Happy programming everybody.

Peter VR.