Link to home
Start Free TrialLog in
Avatar of Bandito1234
Bandito1234

asked on

ASP.NET Application throwing errors randomly

I am well into development of an ASP.NET application.

I have it built so I have a DAL that talks to my codebehind pages, which
determine what to write out to the screen.  All my data classes use stored
procedures on a SQL Server 2000 DB

90% of the time this code works but sometimes I get errors(If I sit and hit
refresh 100 times, I will get errors approx 10 times):

The errors are
    - Internal connection fatal error, System.Data at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)

    - A severe error occurred on the current command. The results, if any,
should be discarded. .Net SqlClient Data Provider at
System.Data.SqlClient.SqlCommand.ExecuteReader

These all seem to be SQL Server connection problems associated with the
SQLCommand.ExecuteReader.

An example method from my Data Access Layer class is below:

Any help would be greatly appreciated!

Public Function getUserPreferenceIDs() As ArrayList

    Dim conn As New SqlConnection(Common.ConnString)
    Dim cmd As SqlCommand
    Dim para As SqlParameter
    Dim dr As SqlDataReader
    Dim arr As New ArrayList()

     conn.Open()

     cmd = New SqlCommand("portGetUserPreferenceIDs", conn)
     cmd.CommandType = CommandType.StoredProcedure

     para = cmd.Parameters.Add(New SqlParameter("@contactID",
SqlDbType.Int))
     para.Direction = ParameterDirection.Input
     para.Value = _contactid

    dr = cmd.ExecuteReader

     Do While dr.Read()

        If Not dr("ID").Equals(System.DBNull.Value) Then
           arr.Add(dr("ID"))
        End If

     Loop

     dr.Close()
     conn.Close()

     Return arr

End Function
Avatar of Mikal613
Mikal613
Flag of United States of America image

IF your using MSDE for SQL server 2000 the most connections at a time is around 7 unless you bought licenses for more otherwise you cant go above 7 (5 is the set maximum but you can get away with 7 or amaybe a little more) Everytime you make a connection its new waiting for the others to die.
Avatar of Bandito1234
Bandito1234

ASKER

I am using SQL Server 2000, not MSDE
did you purchase extra licenses?? if no than you have the limit of connections (sorry ,, Msde same restrictions)
Don't know why the newest comment is redirecting to this question, but here it goes again.

Check out this article.  It might help.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;319345

It refers to not closing the connections properly.

Good luck..
Srini

If there is an error during the execution of the stored procedures the connection wont be closed
So if you use connection pooling the maximum number of open connections is 25.

You can catch if this is the error by restarting the application when the error appears. If there is no errors this is the problem.

I recommend you to use try..catch..finally and close connection in the finally statement
By this you will close all connections at time

B..G
Why are you recreating a connection object every time.  Why not just create one connection object, and reuse it?
Do you mean a global connection object for each class?
I mean a module-level object at the top of the class.
TheLearnedOne's solution is correct
you can also make your own class and manage your connection there
So in every moment you can open or close the global connection

B..G
Okay...I really appreciate your assistance.

The problem is still happening.  I have tried all the solutions offered:

1. I put try catches around all my code, and in "finally" i close the connection object.
2.  I used TheLearnedOne's suggestion on the module level object at the top of the class.

In case I am missing something, here is a code snippet of my new and improved code:


Namespace Portal
    Public Class EnviroResume
      Private conn As New SqlConnection(Common.ConnString)

      Public Function addComputerskills(ByVal computerSkillID)

         Dim cmd As SqlCommand
         Dim para As SqlParameter

         conn.Open()

         Try
            cmd = New SqlCommand("portAddResumeComputerSkill", conn)
            cmd.CommandType = CommandType.StoredProcedure

            para = cmd.Parameters.Add(New SqlParameter("@resumeID", SqlDbType.Int))
            para.Direction = ParameterDirection.Input
            para.Value = _resumeID

            para = cmd.Parameters.Add(New SqlParameter("@luID", SqlDbType.Int))
            para.Direction = ParameterDirection.Input
            para.Value = computerSkillID

            Return cmd.ExecuteNonQuery()
         Catch er As SqlException
            Return er.Message & er.Source & er.Procedure
         Finally
            conn.Close()
         End Try

      End Function

...
...
There is also er.LineNumber, er.Number, and er.State that might help debug this one.  

What is the exact text of the SqlException?
System.Data
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Portal.Contact.getProfDesignationIDs() at Portal.jobseekerResume.bindcontrols() at Portal.jobseekerResume.Page_Load(Object sender, EventArgs e)

All of my functions/subs are coded the same way as the above one.
i think there is something else in the exception
do you get the error in the debug mode ?
I dont have remote debugging installed/working on the server.



Exception Details: System.InvalidOperationException: Internal connection fatal error.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:


[InvalidOperationException: Internal connection fatal error.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
   System.Data.SqlClient.SqlCommand.ExecuteReader() +42
   Portal.Contact.getProfDesignationIDs()
   Portal.jobseekerResume.bindcontrols()
   Portal.jobseekerResume.Page_Load(Object sender, EventArgs e)
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731

 
This is a System.InvalidOperationException.  What you have in the Try...Catch block is an SQLException error.  Add:

Catch ier As System.InvalidOperationException
    Return ier.ToString

 
Is this a long SQL?  How long does it take to fill the array?  

This can be an asychronous process, so that when you hit the refresh, it hasn't completely filled the array, and it is off and running to try the next one.  This can be dangerous depending on the coding and the sequence of events.
Pardon the lengthy code snippet below:
So it does not go into the ier catch block, it throws the regular exception here.

My EnviroResume class containts a single users Resume so its not HUGE, but bug
My Contact Class contains contact specific information about that contact, also not HUGE, but big

Below is code from my aspx.vb page that uses my classes:

      Private Sub bindcontrols()

         Dim li As New ListItem()
         Dim i As Int32
         Dim tempstr As String
         Dim strb As New StringBuilder()

         Try
            Dim res As New EnviroResume(_contactID, _resumeID)

            Dim c As New Contact(_contactID)

            i = 0

            While res.educationCount - 1 >= i
               strb.Append(res.instituteNames(i) & " <A HREF=""editEducation.aspx?id=" & res.ResumeEducationIDs(i) & """>edit</A>&nbsp;<A HREF=""deleteEducation.aspx?id=" & res.ResumeEducationIDs(i) & """>delete</A><BR>")
               i += 1
            End While

            pEducation.Text = strb.ToString

            strb = New StringBuilder()

            i = 0

            While res.workbackgroundCount - 1 >= i
               strb.Append(res.workNames(i) & " <A HREF=""editWorkbackground.aspx?id=" & res.workbackgroundIDs(i) & """>edit</A>&nbsp;<A HREF=""deleteWorkbackground.aspx?id=" & res.workbackgroundIDs(i) & """>delete</A><BR>")
               i += 1
            End While

            pWork.Text = strb.ToString

            CareerObjectives.Text = res.careerObjective

            For i = 1 To 12
               If i.ToString.Length < 2 Then
                  tempstr = "0" & i.ToString
               Else
                  tempstr = i.ToString
               End If
               li = New ListItem()
               li.Text = tempstr
               li.Value = tempstr
               obtainedmonth.Items.Add(li)
               frommonth.Items.Add(li)
               tomonth.Items.Add(li)
            Next

            For i = 1980 To System.DateTime.Today.Year()
               li = New ListItem()
               li.Text = i.ToString
               li.Value = i.ToString
               obtainedyear.Items.Add(li)
               fromyear.Items.Add(li)
               toyear.Items.Add(li)
            Next
            tomonth.Items.Insert(0, "")
            toyear.Items.Insert(0, "current")
            With Province
               .DataSource = luTable.Province
               .DataTextField = "title"
               .DataValueField = "id"
               .DataBind()
               .Items.Insert(0, "")
               .SelectedIndex = 0
            End With


            Dim profarr As New ArrayList()
            profarr = c.getProfDesignationIDs()
            profDesignation.SelectionMode = ListSelectionMode.Multiple

            For i = 0 To profarr.Count - 1
               profDesignation.Items(profDesignation.Items.IndexOf(profDesignation.Items.FindByValue(profarr(i)))).Selected = True
            Next

         Catch er As Exception
            ' Throw er
            Response.Write("Bind Controls Error<BR>")
            Response.Write("Er.Message:" & er.Message & "<BR>Er.Source: " & er.Source & "<BR>Er.StackTrace: " & er.StackTrace & "<BR><BR>")
            '            Response.Write("I"<BR><BR>")        
         Catch ier As System.InvalidOperationException
            Response.Write("INVALID OPERATION EXCEPTION: " & ier.ToString & ier.Message)
         End Try

      End Sub
' -- =======================
Below is the code that loads my contact object (the resume object is very similar).  This is called from the constructor:

     Private Sub fillcontact(ByVal contactID As Int32)

         Dim dr As SqlDataReader

         Dim cmd As SqlCommand
         Dim para As SqlParameter
         Dim i As Int32


         conn.Open()

         Try

            cmd = New SqlCommand("portGetContact", conn)
            cmd.CommandType = CommandType.StoredProcedure
            para = cmd.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int))
            para.Direction = ParameterDirection.Input
            para.Value = contactID
            dr = cmd.ExecuteReader

            If dr.Read() Then
               If Not dr("username").Equals(System.DBNull.Value) Then
                  _Username = dr("username").ToString
               End If
               If Not dr("password").Equals(System.DBNull.Value) Then
                  _Password = dr("password").ToString
               End If
               If Not dr("firstname").Equals(System.DBNull.Value) Then
                  _Firstname = dr("firstname").ToString
               End If
               If Not dr("lastname").Equals(System.DBNull.Value) Then
                  _Lastname = dr("lastname").ToString
               End If
               If Not dr("initial").Equals(System.DBNull.Value) Then
                  _Initial = dr("initial").ToString
               End If
               If Not dr("frenchcorrespondence").Equals(System.DBNull.Value) Then
                  _Correspondence = dr("frenchcorrespondence").ToString
               End If
               ' _Memo = cmd.Parameters("firstname").Equals(System.DBNull.Value)
               If Not dr("datelastmodified").Equals(System.DBNull.Value) Then
                  _Lastmodified = dr("datelastmodified")
               End If

               If Not dr("primaryluUserGroupID").Equals(System.DBNull.Value) Then
                  _primaryUserGroupID = dr("primaryluUserGroupID").ToString
               End If

               If Not dr("datecreated").Equals(System.DBNull.Value) Then
                  _Created = dr("datecreated")
               End If
               ' If Not dr("verifiedAt").Equals(System.DBNull.Value) Then
               If dr("verifiedAt").GetType.ToString <> "System.DateTime" Then
                  _VerifiedAt = "1/1/1900"
               Else
                  _VerifiedAt = dr("verifiedAt")
               End If
            End If

            dr.Close()

            cmd.Dispose()
            cmd = New SqlCommand("portGetContactType", conn)
            cmd.CommandType = CommandType.StoredProcedure

            para = cmd.Parameters.Add(New SqlParameter("@contactId", SqlDbType.Int))
            para.Direction = ParameterDirection.Input
            para.Value = _contactid

            dr = cmd.ExecuteReader

            While dr.Read

               If dr("lu_projectID").Equals(System.DBNull.Value) Then
                  _ProjectID.Add(dr("lu_projectID"))
               End If

               If dr("lu_usergroupId").Equals(System.DBNull.Value) Then
                  _UsergroupID.Add(dr("lu_usergroupId"))
               End If

               i += 1

            End While

            dr.Close()

         Catch er As Exception
            Response.Write("<BR>Contact.FillContact.Exception<BR>Message: " & er.Message & "<BR>" & er.Source & "<BR>" & er.StackTrace & "<BR>")
         Catch ersql As SqlException
            Response.Write("<BR>Contact.FillContact.SQL Exception<BR>Message: " & ersql.Message & "<BR>Source: " & ersql.Source & "<BR>StackTrace : " & ersql.StackTrace & "<BR>")
         Finally
            ' added nov 24, 2003
            conn.Close()
         End Try
Rearrange  Catch er As Exception and Catch ier As System.InvalidOperationException.  The generic form of an Exception should be the last one, since it will always apply.  Error catch starts at the top, and tries to find a catcher to handle the error.  If none is found, then you get the 'Unhandled Exception' error.  

One way that I have used in the past, is to use a module-level flag variable:

Private m_ReadingData As Boolean

In the Page_Load event, check to see if m_ReadingData is True.  If true, then don't start reading again.  Also, remember to clear the value in the Final section of the Try...Catch block.

This will prevent starting another read until the first read is complete.  With ASP.NET, though, that would leave the array unfilled.
Sorry...Its my understanding that every time a page is refreshed, a new object e.g. contact is created in memory, and I no longer have access to the old one.  So I'm not sure how I can read the m_ReadingData from the object that existed before the page was refreshed...can you maybe write some pseudocode for dummies?

I am in the process of moving my ier try catch blocks around, didnt realise the order of them mattered.
The Invalid Exception is:
INVALID OPERATION EXCEPTION: System.InvalidOperationException: Internal connection fatal error. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Portal.Contact.getProfDesignationIDs() at Portal.jobseekerResume.bindcontrols()Internal connection fatal error.

In this instance it leads me question my profDesignationID's....where I am loading an array...hmmm

Class Contacnt


      Public Function getProfDesignationIDs() As ArrayList

         Dim cmd As SqlCommand
         Dim para As SqlParameter
         Dim dr As SqlDataReader
         Dim arr As New ArrayList()

         conn.Open()
         Try
            cmd = New SqlCommand("portGetContact_ProfDesignations", conn)
            cmd.CommandType = CommandType.StoredProcedure

            para = cmd.Parameters.Add(New SqlParameter("@contactID", SqlDbType.Int))
            para.Direction = ParameterDirection.Input
            para.Value = _contactid

            dr = cmd.ExecuteReader

            While dr.Read()
               If Not dr("luProfDesignation").Equals(System.DBNull.Value) Then
                  arr.Add(dr("luProfDesignation"))
               End If
            End While

            dr.Close()

         Catch er As SqlException
            Throw er
         Finally
            conn.Close()
         End Try

         Return arr

      End Function

End Class
What is getProfDesignationIDs?
A little funciton that loads a users professional designations into an arrayList
Okay, what I see here is a lot of opening and closing of connections.  What I would suggest is that you try a global connection object, that you leave open, and see what effect that has.
So to close the connection object i will create a public method called contact.closeConn.

SHould I move the conn.close from the finally in my try catches to the catch

so instead of
try
' do stuff
catch
' throw er
finally
conn.close
end try

do:
try
' do stuff
catch
' throw er
finally
conn.close
end try
i mean
ry
' do stuff
catch
conn.close
' throw er
end try
in finnally just call contact.closeConn ( conn )
and int the closeConn check if the conn state is open

B..G
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys, I'm going to give this a shot in the morning and I'll award the points once its working, but it makes sense - you're tips, and help is greatly appreciated.
The real problem was something on the SQL server, the code worked fine on the live server. But TheLearnedOne deserves the points for all the great input. Thanks
Hello, can you tell me what the problem was on the SQL Server?  I'm having the same issues. thank you.
Bandito, did you ever figure out what was causing this on SQL, like cwitty, I'm seeing this too.  Thank you.
sorry, I never figured the real problem out, we still have this issue on our dev site. It looks like it has soemthing with our network setup, since we also see problems just by printing something to our printer. Sometimes the letters get scribbled up just like we see it when we post data.