Database Connection Problem. Please Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi all,

I have a process which first pulls one time all application IDs from a
database and stores them in a table(this process works fine everytime). I
then loop through the table, one at a time, and use application id to pull
details info and process it. For example, if I have 500 records in my table,
then I would have to open database 500 times. Also between processing each
record, my process sleep 3 seconds.

The problem is that after processing about 100 records, using SqlDataReader,
my database connection will starts failing. When I traced the error in my
try catch clause, the exception message just say connection fails to open
database. I also found that all the subsequent connection also failed to
open unless I stop my window service and restart it.

Would some one please give me a hand? I've been trying to find a way to pull
all details for all records at once, instead of one at a time but I have not
been able to do that.

Thanks in advance for everyone's suggestions

Regards,

Sam


Here is part of my process that pull details of an application base on
applicationID
===============================
Dim appReader As SqlDataReader

appReader = GetDataReader("user_ApplicationDetails", _
"@appID", appID)

Do While appReader.Read ' Read applicat

'process data
Loop

If Not appReader Is Nothing Then appReader.Close()

================================
Here is my function that returns sqldataReader

Public Function GetDataReader(ByVal sStoredProc As String, ByVal
sVarName As String, ByVal iInt As Integer) As SqlDataReader

Dim aCommand As New SqlCommand

Try

aConnection = OpenConnection(aConnection) ' OpenConnection
returns sqlconnection

aCommand.CommandText = sStoredProc
aCommand.CommandType = CommandType.StoredProcedure
aCommand.Connection = aConnection

aCommand.Parameters.Add(sVarName, iInt)

Return(aCommand.ExecuteReader(CommandBehavior.CloseConnection))
Catch e As Exception
Throw New Exception(e.Message, e)
Finally
If aConnection.State.Open = True Then
aConnection.Close()
aCommand.Dispose()
End If
End Try

End Function


.

Relevant Pages

  • Re: Database Connection Problem. Please Help
    ... connection again. ... pull details info and process it. ... then I would have to open database 500 times. ... SqlDataReader, my database connection will starts failing. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Database Connection Problem. Please Help
    ... I have a process which first pulls one time all application IDs from a database and stores them in a table. ... The problem is that after processing about 100 records, using SqlDataReader, my database connection will starts failing. ... I've been trying to find a way to pull all details for all records at once, instead of one at a time but I have not been able to do that. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: what is the best way to connect to a sql database?
    ... The most common way to connect to a database is to use the "SqlConnection", ... "SqlCommand" and "SqlDataReader" classes. ... to keep the connection to the database open the whole time. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Database Connection Problem. Please Help
    ... method which takes a data reader and maintain a connection myself ... database and stores them in a table. ... SqlDataReader, my database connection will starts failing. ... Here is part of my process that pull details of an application base on ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SqlDataReader problem... [Code]
    ... The SqlDataReader is a low level provider that works by retrieving one row ... at a time on a active connection to the database. ... SqlDataReader to work with really large resultsets, ... the associated Connection object is closed ...
    (microsoft.public.dotnet.languages.csharp)