Solved

ASP.NET Application throwing errors randomly

Posted on 2003-11-24
32
1,225 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Bandito1234
  • 14
  • 9
  • 4
  • +4
32 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 9812732
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.
0
 

Author Comment

by:Bandito1234
ID: 9812925
I am using SQL Server 2000, not MSDE
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9812955
did you purchase extra licenses?? if no than you have the limit of connections (sorry ,, Msde same restrictions)
0
 
LVL 5

Expert Comment

by:snimmaga
ID: 9812959
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

0
 
LVL 28

Expert Comment

by:mmarinov
ID: 9812972
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9813414
Why are you recreating a connection object every time.  Why not just create one connection object, and reuse it?
0
 

Author Comment

by:Bandito1234
ID: 9813455
Do you mean a global connection object for each class?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9813472
I mean a module-level object at the top of the class.
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 9813502
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
0
 

Author Comment

by:Bandito1234
ID: 9813567
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

...
...
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9813634
There is also er.LineNumber, er.Number, and er.State that might help debug this one.  

What is the exact text of the SqlException?
0
 

Author Comment

by:Bandito1234
ID: 9813641
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.
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 9813661
i think there is something else in the exception
do you get the error in the debug mode ?
0
 

Author Comment

by:Bandito1234
ID: 9813696
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

 
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9813725
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

 
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9813765
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Bandito1234
ID: 9813821
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9813859
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.
0
 

Author Comment

by:Bandito1234
ID: 9813907
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.
0
 

Author Comment

by:Bandito1234
ID: 9813944
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9813992
What is getProfDesignationIDs?
0
 

Author Comment

by:Bandito1234
ID: 9813999
A little funciton that loads a users professional designations into an arrayList
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9814049
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.
0
 

Author Comment

by:Bandito1234
ID: 9814094
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
0
 

Author Comment

by:Bandito1234
ID: 9814116
i mean
ry
' do stuff
catch
conn.close
' throw er
end try
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 9814118
in finnally just call contact.closeConn ( conn )
and int the closeConn check if the conn state is open

B..G
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 400 total points
ID: 9814143
You have one connection, and a lot of stuff going on around it.  Maybe when you hit the getProfDesignationIDs function, the connection has been closed by something else.  It gets very confusing with all those events happening asychronously, and it is hard to predict their behavior at any one point in time.
0
 

Author Comment

by:Bandito1234
ID: 9814178
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.
0
 

Author Comment

by:Bandito1234
ID: 10253732
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
0
 

Expert Comment

by:cwitty
ID: 10393650
Hello, can you tell me what the problem was on the SQL Server?  I'm having the same issues. thank you.
0
 

Expert Comment

by:tradsud
ID: 10628254
Bandito, did you ever figure out what was causing this on SQL, like cwitty, I'm seeing this too.  Thank you.
0
 

Author Comment

by:Bandito1234
ID: 10864033
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now