Strange Database Problem

[arrian]

I'm having an issue with the program I'm working on. It seems like for some reason, every once in a while the connection to the database errors out. It works fine most of the time, but on occasion, it errors out. It's not giving me ANY reasons for the errors, just error 5: Unspecified error. The database is being run localy. If anyone needs some snippets of the code, I can provide that as well.

 

[ca8msm]

Have you tried trapping the database exception?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ThatRickGuy]

What type of data base is it? Have you tried checking the Exception.InnerException.Message to see if there is a database error that is getting wrapped up by ADO.Net or the data provider?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[arrian]

I've tried trapping the oledb.exception, as well as the innerexception. The innerexception doesn't contain anything, unfortunatly... The oledb exception hasn't come up yet...

 

[ThatRickGuy]

Just catch all exceptions ie:

try
catch exc as exception
'what is this error?
end try

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[arrian]

Ok, here's the code for where the error is popping up:

Sub Execute(ByVal strSQL As String)
        Dim conExecute As OleDbConnection
        Dim comExecute As OleDbCommand

        conExecute = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & GetDatabaseLocation() & ";user id=admin;password=")
        comExecute = New OleDbCommand(strSQL, conExecute)
        If conExecute.State <> ConnectionState.Open Then
            Try
                conExecute.Open()
            Catch ex As Exception
                ErrHandle(Err, "Execute")
            End Try
        End If
        Try
            comExecute.ExecuteNonQuery()
            conExecute.Close()
        Catch ex As Exception
            ErrHandle(Err, "Execute")
        End Try
    End Sub

And here's the ErrHandle sub

Sub ErrHandle(ByVal errError As ErrObject, ByVal strLoc As String)
        MsgBox("Error " & Err.Number & ": " & Err.Description, MsgBoxStyle.OKOnly, strLoc)
    End Sub
[code]

The error comes up when it opens the connection. It doesn't allways happen, it seems fairly random. It gives me back error 5: Unspecified Error.

 

[ThatRickGuy]

            Catch ex As Exception
                ErrHandle(Err, "Execute")
            End Try

It doesn't look like you are doing anything with the exception object here.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[arrian]

Should I pass the exception to the error handler rather than the error object, then?

 

[ThatRickGuy]

That would be my recomendation.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[arrian]

Ok, I've changed a couple things, and now I'm getting a more specific error. The Execute routine is:

Sub Execute(ByVal strSQL As String, ByVal conExecute As OdbcConnection)
        Dim comExecute As OdbcCommand

        comExecute = New OdbcCommand(strSQL, conExecute)

        Try
            comExecute.ExecuteNonQuery()
        Catch ex As Exception
            ErrHandle(ex, "Execute")
        End Try
    End Sub

And my connection string is being passed along with the SQL statement. The connection string is as follows:

conTES = New System.Data.Odbc.OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};Dbq=" & GetDatabaseLocation() & " ;Uid=Admin;Pwd=;")

The error I'm getting now is:

ERROR [08004] [Microsoft] [ODBC Mircosoft Access Driver] Too many client tasks.

Any ideas?

 

[ThatRickGuy]

What does the SQL look like?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[arrian]

The SQL varies. Just average Insert, update or delete commands. They work sometimes, but not others. The exact same SQL string will work once, then not the next.

 

[ca8msm]

"Too many client connections"

That would suggest to me that there are too many connections open to the database. I suggest that you are closing all your connections properly as there may be a leak somewhere (oe it may just be that access can't cope so you may have to look at using a real database!).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chrissie1]

real database!).

How cruel.



Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[Rhys666]

How cruel.

...but unfortunately true

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra
If life were fair, Dan Quayle would be making a living asking 'Do you want fries with that?' John Cleese

 

[ca8msm]

Yep - I'm totally biased against access and I'll freely admit it!


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[arrian]

All my database connections are being closed properly, and I've only got a max of maybe 5 at any one time. Generally, I only have 1, maybe 2 open.

 

[arrian]

I'm still having this problem. I've tried ignoring the error and waiting 2 seconds before attempting again, and a few times that's worked fine.

Eventually, though, I get to one spot where it just won't open. This is the full error I get:

ERROR [08004] [Microsoft] [ODBC Mircosoft Access Driver] Too many client tasks.
ERROR [IM006] [Microsoft] [ODBC Driver manager] Driver's SQLSetConnectAttr failed

Any ideas what that means???

 

[ca8msm]

Looks like the problem may be what I described above.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Rhys666]

As previously stated you've got too many simultaneous connections to your database. If you are certain you are closing and disposing (I don't see any .Dispose() calls in your code) of your connection objects appropriately then to the best of my knowledge, this problem cannot be solved by continuing to use MS Access.

You could ensure connection objects are closed, then disposed and then force the Garbage Collector to run but this could induce a serious performance hit, I doubt anyone else would recommend it, (I'm not, I'm suggesting its a possibility), and I don't think it will resolve your problem either.

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra
If life were fair, Dan Quayle would be making a living asking 'Do you want fries with that?' John Cleese