Unspecified Error on connection.open()

[wk6pack]

Hi,

I have a question about my coding practise. I have a class method to return
a value from a database. I open the connection do my search and dispose the
reader. Open the reader with a new recordset and then close the reader and
close the connection.

I do this for every new record I am adding. But I seem to get an error
around the 770 record to add. The error is and unspecified error at the
connection.open() for that record.

Or should I be opening the connection and leave it open for all the records
I'm adding and just return to the beginning of the recordset to restart the
search?

thanks,
Will

Here is my function code:

Public Function lookupGroup(ByVal lPerson As Person) As String
Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;Data Source=" + Path.GetFullPath("EmployeeMatching.mdb")
'W:\Will\Active Directory\Export\EmployeeMatching.mdb"
Dim myReader As OleDbDataReader
Dim catCMD As New OleDbCommand
Dim schooltypecatcode As String

Try
With catCMD
..Connection = New OleDbConnection(sConnString)
..Connection.Open() <******************************
..CommandText = "Select a.schooltypecode from schooltype a, school b where
b.schoolno ='" + lPerson.dept + "' and b.schooltype = a.schooltypeid "
End With

myReader = catCMD.ExecuteReader()
If myReader.HasRows Then
myReader.Read()
schooltypecatcode = myReader.GetValue(0)
Else
schooltypecatcode = Nothing
End If
Catch ex As OleDbException
errorlog(lPerson, ex.Message, " LookupGroup OLEDB:")
Catch e As Exception
errorlog(lPerson, e.Source + ":" + e.Message, " LookupGroup Schooltype:")
Catch
errorlog(lPerson, "Unhandled Exception in schooltypecode lookup. ",
"CatchAll Exception")
Finally

If Not myReader Is Nothing Then myReader.Close()
End Try

If Not schooltypecatcode Is Nothing Then
'get the school type ie. EL for elementary
schooltypecatcode += lPerson.dept
Dim ptype = lPerson.Type
Dim pcat = lPerson.Category
Dim found As Boolean
Try
catCMD.CommandText = "SELECT c.empcatgencode, b.emptypecode,c.empcatcode,
b.emptypedesc, c.empcatdesc FROM EmpTypeCatLink a, emptype b, empcategory c
" + _
" where a.emptypeid = b.emptypeid and a.empcatid = c.empcatid"
myReader = catCMD.ExecuteReader()
Dim schooltype As String
found = False
'cycle thru the table to find the general title ie. TE for teachers
Do While myReader.Read And Not found
If (StrComp(myReader.GetValue(1), ptype) = 0 And
StrComp(myReader.GetValue(2), pcat) = 0) Then
If Not myReader.IsDBNull(0) Then
schooltypecatcode += myReader.GetValue(0)
found = True
Else
schooltypecatcode = Nothing
found = True
End If
End If
Loop
Catch ex As Exception
errorlog(lPerson, ex.Source + ":" + ex.Message, "Lookup Group
Empcatgencode:")
Catch
errorlog(lPerson, "Unhandled Exception: EmpCatGenCode lookup ", "CatchAll
Exception")
Finally
If Not myReader Is Nothing Then myReader.Close()
End Try
End If
If Not catCMD Is Nothing Then catCMD.Dispose()
Return schooltypecatcode

[Jon Skeet [C# MVP]]

wk6pack <wk***@sd61.bc.ca> wrote:

I have a question about my coding practise. I have a class method to return
a value from a database. I open the connection do my search and dispose the
reader. Open the reader with a new recordset and then close the reader and
close the connection.

I do this for every new record I am adding. But I seem to get an error
around the 770 record to add. The error is and unspecified error at the
connection.open() for that record.

Or should I be opening the connection and leave it open for all the records
I'm adding and just return to the beginning of the recordset to restart the
search?

I can't see where you're closing the connection. You're disposing of
the command and closing the reader, but not closing the connection.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[wk6pack]

Hi Jon,

I thought that when I dispose of the object, it would close the connection?
So if that is not the case, I was opening too many connections and it gave
me an error then?

thanks,
Will

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

wk6pack <wk***@sd61.bc.ca> wrote:

I have a question about my coding practise. I have a class method to return a value from a database. I open the connection do my search and dispose the reader. Open the reader with a new recordset and then close the reader and close the connection.

I do this for every new record I am adding. But I seem to get an error
around the 770 record to add. The error is and unspecified error at the
connection.open() for that record.

Or should I be opening the connection and leave it open for all the records I'm adding and just return to the beginning of the recordset to restart the search?

I can't see where you're closing the connection. You're disposing of
the command and closing the reader, but not closing the connection.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

wk6pack <wk***@sd61.bc.ca> wrote:

I thought that when I dispose of the object, it would close the connection?
Which object do you mean? Disposing of a command doesn't close the
connection.
So if that is not the case, I was opening too many connections and it gave
me an error then?

Yes, that's probably the problem.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[wk6pack]

Hi Jon,

I thought disposing of the any object would close the connection. I'll
remember that.

thanks,
Will
"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP***********************@msnews.microsoft.co m...

wk6pack <wk***@sd61.bc.ca> wrote:

I thought that when I dispose of the object, it would close the connection?

Which object do you mean? Disposing of a command doesn't close the
connection.

So if that is not the case, I was opening too many connections and it

gave me an error then?

Yes, that's probably the problem.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Alvin Bruney [ASP.NET MVP]]

disposing of the connection object implicitly closes the connection so you
don't have to do it there

--
Regards,
Alvin Bruney
[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
available at www.lulu.com/owc
_________________________
"wk6pack" <wk***@sd61.bc.ca> wrote in message
news:Oj**************@TK2MSFTNGP14.phx.gbl...

Hi Jon,

I thought disposing of the any object would close the connection. I'll
remember that.

thanks,
Will
"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP***********************@msnews.microsoft.co m...

wk6pack <wk***@sd61.bc.ca> wrote:

> I thought that when I dispose of the object, it would close the connection?

Which object do you mean? Disposing of a command doesn't close the
connection.

> So if that is not the case, I was opening too many connections and it

gave > me an error then?

Yes, that's probably the problem.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

wk6pack <wk***@sd61.bc.ca> wrote:

I thought disposing of the any object would close the connection. I'll
remember that.

Disposing of the *connection* will close it - but you've only disposed
the command.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too