dBase System Resource Error

Do you close and null the connection and recordset objects?

Set con = Server.CreateObject("ADODB.Connection")
Set rec = Server.CreateObject("ADODB.Recordset")
con.Open ...
rec.Open ...
rec.Close
con.Close
Set rec = Nothing
Set con = Nothing

I would think robbert is on the right track here.  

1. Also examine any loops you are using (Do...Loop, For...Next, etc.), and look for possible runaway situations where variables are getting instantiated repeatedly, or other resource-using activites could occur.  

2. Can you post your code?  What is in your global.asa, this could play a large role.  

3. Are you using any include files that are repeating actions that they shouldn't?

4. I am doubting the conection pooling theory at the moment.  You are using too small a number of connections before it crashes for this to matter.

5. Can you duplicate your code using another ODBC driver to verify that you have not found an ODBC bug?  Import the dbf into Access and try the Jet oDBC driver with the same code, just modify your connection string.

Keep us posted.  I am around much of the day, and robbert owns this place!

Thanks guys for your assistance.

Here is my code, very simple. Yet causing so much drama.

<--code-->
'get dBase data
Set oConndBase = Server.CreateObject("ADODB.Connection")

Set oCmddBase = Server.CreateObject("ADODB.Command")
                  
oConndBase.Open "DSN=00001033_dBase;UID=;PWD="

Set oCmddBase.ActiveConnection = oConndBase
Set oRsdBase = Server.CreateObject("ADODB.Recordset")

strSQL = " SELECT Hour(CDate([Sdate])) AS HVal, DateValue(CDate([Sdate])) AS DVal, Hr_wghts.C2TPH, Hr_wghts.C10TPH, IIf(CLng([C2TPH])<>0 or CLng([C10TPH])<>0,CLng([C2TPH])/(CLng([C2TPH])+CLng([C10TPH])),0) AS Rrecov FROM Hr_wghts WHERE (((CDate([SDATE])) Between #" & dBaseStartDate & "# And #" & dBaseFinishDate &"#)) ORDER BY CDate([SDATE]) "

'Response.Write "dbase= " & strsql & "<bR>"

oRsdBase.Open strSQL, oConndBase, adOpenStatic, adLockReadOnly, adCmdText

If ( oRsdBase.BOF And oRsdBase.EOF ) Then
    CountEmpty = CountEmpty + 1
    dBaseEmpty = true
'Response.Write "No dBase Data "
Else
    AlldBaseData = oRsdBase.GetRows            
End If

oRsdBase.close
Set oRsdBase = Nothing
      
oConndBase.close
Set oConndBase = Nothing      
<--end code-->


Robbert as you can see I close them all off ok.

Could it be my cursor types?? or the fact that I'm using Access style SELECT statements direct to the DBF??

I am only ever doing this once per page load.

I then use the values in the array to do everything. This takes the load off the recordset.

Drittich in response to your points.

1) I don't have any loops in this part of the code. Just the IF statement.
2) Code is as above, and I am not using a global.asa for this project.
3) No include files.
4) This is good know that it might not be a connection pooling issue.
5) I imported the dbf into Access and then used the MSAccess driver to get the data. It worked perfectly over about 40 attempts. With the DBF driver I would normally only reach 20 atttemps.

To get around this problem, could I automate an import of the DBF file into an ACCESS one, and then just communicate with Access??

I would hope that it doesn't come to this but it might have to. I have one day left to come up with something.

Thanks again guys.
Aaron.

dBase shouldn't be scoping with too many connections.

Robbert

I don't understand. scoping?

Thanks

Sorry, this post was after yours. - I meant: dBase shouldn't be able to handle many connections.

But, in your case, it's the GetRows() statement, as I assume.

Try instead:

RecCount = oRsdBase.RecordCount

(or just do nothing after opening the recordset!) to make sure that the problem isn't because of this method.

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

BTW, you're using:

, adOpenStatic, adLockReadOnly, adCmdText

- This isn't known to ASP - and will be evaluated as ", 0, 0, 0"

Use:

, 3, 1, &H0001

instead or, include:

<!--#include file="adovbs.inc"-->

Robbert

I have progressed this issue further, as per your last comments.

I quite simply put the page with a meta tag of refresh every 20secs so that I didn't have to sit there and keep probing it.

Using this method I was able to work out the GetRows method is not the culprit.

Even with doing nothing, just the opening of the record set and then closing it. I still get the system resource error after about 20-30 times.

It's looking bad for me isn't it.

Do you have any other ideas?

Thanks for your assistance so far.

ADODB.Command doesn't provide a method "Open()".
Change:

Set oCmddBase = Server.CreateObject("ADODB.Command")

-to-

Set oCmddBase = Server.CreateObject("ADODB.Recordset")

Sorry, the last comment was dumb.

- I tested your code (other SQL statement / database) with a dBase 5 database (exported from Access).
Refreshing in very short periods worked.
When the GetRows() method was not outcommented, sometimes the error occured:
The database engine could not lock table 'customer' because it is already in use by another person or process.
But, that's OK.

- Your SQL statement is strange, is it a dBase-specific SQL dialect? Maybe, try a simple SQL statement, first.

- Did you try drittich's proposal of porting the database to Access - to see if it's an dBase / ODBC or, ADO, issue?

If you are in a hurry I would go ahead with the conversion to Access - it has been used extensively with ADO/ODBC and the problems are well-documented here and elsewhere.  

You don't need to automate an import.  You can create an Access database where the tables are linked to the Excel spreadsheet.  So updates in the spreadsheet are automatically refelcted in the database.  This is a very inefficient way to construct a database, but if it solves your problem for now, go ahead with it and work out the details later.

Gentleman

Thank you again for your responses.

After even more work. These are my findings.

Robbert, I tried a simple SELECT * FROM statement and it still errored out. So it's not the statement. That's about it for things to test. It must just be the driver or something wrong with my software install. I doubt it because I can run this on another NTserver with the same error occuring.

I did try Drittich's comments about Importing into Access. This works like a charm, but causes me a lot of grief to set this up.

Drittich. Linking to the DBF from Access still causes the error. I linked with DbaseIII format. Just a comment. You mentioned Excel spreadsheet?? I tried to import the DBF as XLS as well but it wouldn't let me.

So if I want to use Access I have to Import it and not link it.

Note: from Robbert's method of exporting a DBF from Access. I imported the DBF into Access, then exported it as a dBaseIII file again.

Tests on this newly generated file yield the same result. Error. At least it's consistant.

Looks like I can't crack this nut.

Unless you guys have anymore ideas, do you know how to automate an import into Access????

Thanks again so far, I really appreciate your comments, it's at least led me down the right path to knowing there is nothing I can do.

Sorry, I was losing my mind when I said Excel.

You *can* link to a .dbf file.  No problem.  Try it and see.  That should be no hassle at all to setup.

Drittich

Some confusion here.

I was successful in linking the DBF, which would have been great, but I still get the same system resource error.

I think Access must internally use ODBC to talk to the DBF. Otherwise there is no other way that it could still be getting the same error, even after changing my ODBC to be for Access.

In summary. I've linked the DBF into Access, and changed my ODBC connection to an Access one. Same Error.

Any thoughts?

Hmm, that makes sense.  Of course it uses ODBC, what was I thinking?

Ok, I am with the program now.  

What version of IIS are you running, and what Service Pack level is the NT server at?  

Aoasalam

Thank you for your response. It has filled me with renewed hope.

However I'm having trouble implementing your solution.

I get this error relating to the missing Command object that was in my previous code.

Microsoft VBScript runtime error '800a01a8'

Object required: '[string: "DSN=00001033_dBase;U"]'

/plant/dbase.asp, line 131

Any ideas??

Hi all

Some additional information.

I have found an article in a Citect (SCADA package) help forum. This is an exerpt from one help article.

<snip>
We have also found that a large number of ODBC drivers also leak memory every time you open a connection. That is every time you disconnect from the database the ODBC driver does not free all the memory it allocated when it connected. The result is that after connecting and disconnecting many times you can cause the performance of your computer to degrade and may even crash.
</snip>

In my case then this is what could be causing the problem and along the lines of what Steve has commented about.

In an effort to get around this problem. I have created an Access database that has the DBF table linked. This Access database then every 5mins updates an Access table which is a replica of the DBF table.

Doing ADO queries on that is 100% ok.

The only drawback is that Access needs to be continually running on the machine. Oh well. Better than nothing.

I will leave this question open for now as I still need a solution.

I will look to posting this into other forums.

Thank you all for all your assistance with this issue.

Regards
Aaron

Wasn't dBase III a DBMS in the early 90's? At that time noone thought of multiple connections, or even needed them...

If the reason that you use it is a special SQL dialect, we could translate the statements easily to the Access dialect.

If you're not using Access because you get dBase data from elsewhere, regularly, we could write a simplest import script.

If you think, Access had to be installed on the server - only the driver has to be installed and, that came with MSDAC / NT Option Pack.

But, if you love it as others their oldtimer car, enjoy! :-)