Solved

ASP.Net page hangs on DB calls

Posted on 2008-10-13
32
991 Views
Last Modified: 2010-04-21
I tried to find answers to this, but I wasnt sure how to search considering my issue.  Let me explain as best as I can.  I have an asp.net 2.0 project in visual studio 2005.  I created a basic page with a datagrid that returns data and updates on a button command.  I used the standard wizard stuff to generate the datasource and command parameters, etc.  This page connects to a remote db and works fine.  I also have a more detailed form that I created where I use my own code to connect to the same remote db.  I have used this code in hundreds of projects and it follows the basic format I have in the code snippet.

This form also works very well on my local machine.  My problem is that while both pages work on my local machine connecting to the remote SQL server 2005 db, the form I created using the code in the snippet does not work on the staging machine.  It just hangs and never gives an error or displays any content.

I can provide more code if it will help, but Im inclined to think its not the db code I use since I use the same code in other projects and since the page does work locally.  Ive never had this issue occur in staging.

Thanks for any advice!



DataTable dtS = new DataTable("S");
 

        try

        {

            using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ORConnectionString"].ConnectionString))

            {

                con.Open();

                SqlCommand cmd = new SqlCommand();

                cmd = sprocs.procGetCategories(con);

                SqlDataAdapter objDa = new SqlDataAdapter(cmd);

                objDa.Fill(dtS);

                objDa.Dispose();

            }

        }

        catch (SystemException)

        {

            throw;

        }

Open in new window

0
Comment
Question by:clintfield
  • 16
  • 8
  • 5
  • +1
32 Comments
 
LVL 21

Expert Comment

by:silemone
ID: 22702370
Have you debugged the code while on the staging machine?  and instead of throwing the error, why not place the the contents into a string variable and create a log file...


i.e.


catch (SystemException ex)
        {
            strError += ex.StackTrace.ToString();
           //here open or create file if it doesn't exist and append new log info...


        }
0
 
LVL 1

Author Comment

by:clintfield
ID: 22702437
silemone:
Do you mean step through the code as it runs in staging?  I unfortunately dont have access to do this.  

The odd thing about this page is that the form will be displayed if I dont make any db calls.  If I make even one db call it hangs without displaying anything.  Are you saying that it might be generating an error in my try statement that never manages to exit the catch?  Im curious if it will even write to a log file since it cant exit the catch (assuming this is where the issue is.)
0
 
LVL 21

Expert Comment

by:silemone
ID: 22702514
well if it's supposed to exit the catch error, just add return;  after last statement...it will write to the file however...That's the only other option I see since you can't debug/step through code...If it only happens with the database, I'm sure the throwing of the error is causing a problem...you could try adding return after that line also just to see what happens...writing to a file would be simple...sounds like either the app is timing out because it can't either find the db server or it can't access db server (security not acceptable...)
0
 
LVL 1

Author Comment

by:clintfield
ID: 22702922
silemone:
Im using the same connection string in the web config file for both the page that works and the page that doesnt.  I would assume this means that security and location info is correct.

0
 
LVL 1

Author Comment

by:clintfield
ID: 22705192
I decided to try your file write idea just to see if I could conclude where the code was hanging.  I updated my code to what is in the snippet below.  After I browsed to the site, I noticed that the file did get created, but there was no text in it.  I realize that I should have written this a little better(i.e. the file wont close unless it succeeds or exits the catch), but it still tells me that it created the file, then encountered an error without ever going into the catch statement...probably because it hangs in the connection statement somewhere.  

Im lost as to why it would hang on the connection statement.  The page that does work uses the same connection string.  All pages work on my machine while connecting to the remote db.  What could possibly be different on the staging box that would cause me to hang while connecting?  Why wouldnt I get an error page?  The web config file contains the connection string you see in the code snippet below.
        DataTable dtS = new DataTable("S");
 
 

        TextWriter tw = new StreamWriter(@"C:\inetpub\wwwroot\OR\xFile.txt");
 

        try

        {
 

            tw.WriteLine("opening");

            

            using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ORConnectionString"].ConnectionString))

            {

                con.Open();

                SqlCommand cmd = new SqlCommand();

                tw.WriteLine("open...calling sproc");

                cmd = sprocs.procGetOrderTypes(con);

                SqlDataAdapter objDa = new SqlDataAdapter(cmd);

                tw.WriteLine("fill the dts");

                objDa.Fill(dtS);

                objDa.Dispose();

            }
 

            tw.WriteLine("closing");

            tw.Close();

        }

        catch (SystemException ex)

        {

            string er = ex.StackTrace.ToString();
 

            tw.WriteLine(er);

            tw.Close();
 

        }
 

        return dtS;
 
 
 

//-------------------------web config setting--------------------//

  <connectionStrings>

    <add name="ORConnectionString" connectionString="Data Source=abcdefg;Initial Catalog=OR;Persist Security Info=True;User ID=xxx;Password=xxx"

      providerName="System.Data.SqlClient" />

  </connectionStrings>

Open in new window

0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22705472
1. Try not to use "using" block.
2. If you use SqlDataAdapter, you will not need con.Open(), because SqlDataAdapter will do "Open" and "Close" for you automatically.
0
 
LVL 1

Author Comment

by:clintfield
ID: 22705630
prairiedog:
Thanks for the good advice, but do you think a using block could cause everything to hang?  I use the same code in other 2.0 projects and it works fine.
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22706304
your code in the using block looks good to me
just one suggestion comment out all the logging code i.e. text writer code and then try on your staging server where the page completely becomes dead
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22706681
Try adding "Connection Timeout=30" in your connection string to see if it helps.
Also, check the event log on your staging server to see if you find any error there.
0
 
LVL 1

Author Comment

by:clintfield
ID: 22710329
prairiedog:
I liked the connection timeout idea, but I got no results with it.  However, your suggestion to check the event log appears to have been fruitful.  I pasted the event log details in the code snippet below.  The first two events I pasted are from the system log and are recorded many times.  The second event I pasted from the Application log is only in there once, so Im not sure it has much bearing, although it does name the page Im having issues with.  There are no errors recorded in the security log.
--------------------------------

System LOG Details

--------------------------------

#1

---

Application popup: Assertion Failed: Abort=Quit, Retry=Debug, Ignore=Continue : 
 
 

    at SqlCommandGenerator.GenerateCommand(SqlConnection connection, MethodInfo method, Object[] values)  

    at employees.GetActiveEmployees()  

    at addcase.Page_Load(Object sender, EventArgs e)  

    at CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)  

    at CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)  

    at Control.OnLoad(EventArgs e)  

    at ControlAdapter.OnLoad(EventArgs e)  

    at Control.LoadRecursive()  

    at Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)  

    at Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)  

    at Page.ProcessRequest()  

    at Page.ProcessRequestWithNoAssert(HttpContext context)  

    at Page.ProcessRequest(HttpContext context)  

    at addcase_aspx.ProcessRequest(HttpContext context)  

    at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()  

    at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)  

    at ApplicationStepManager.ResumeSteps(Exception error)  

    at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)  

    at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)  

    at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)  

    at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)  
 
 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
 
 

#2

----
 

Application popup: Assertion Failed: Abort=Quit, Retry=Debug, Ignore=Continue : 
 
 

    at SqlCommandGenerator.GenerateCommand(SqlConnection connection, MethodInfo method, Object[] values)  

    at employees.GetActiveEmployees()  

    at addcase.Page_Load(Object sender, EventArgs e)  

    at CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)  

    at CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)  

    at Control.OnLoad(EventArgs e)  

    at Control.LoadRecursive()  

    at Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)  

    at Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)  

    at Page.ProcessRequest()  

    at Page.ProcessRequestWithNoAssert(HttpContext context)  

    at Page.ProcessRequest(HttpContext context)  

    at addcase_aspx.ProcessRequest(HttpContext context)  c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\OR\455ee5e2\63c716fe\App_Web__uvnumre.3.cs

    at CallHandlerExecutionSte......

<truncated>
 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
 
 
 
 

---------------------------------

Application LOG Details

---------------------------------
 

Event code: 3005 

Event message: An unhandled exception has occurred. 

Event time: 10/13/2008 2:45:03 PM 

Event time (UTC): 10/13/2008 6:45:03 PM 

Event ID: 146c4bdedeb84363b05089ae4af3fc58 

Event sequence: 4 

Event occurrence: 1 

Event detail code: 0 

 

Application information: 

    Application domain: /LM/W3SVC/1/Root/OR-9-128683970989985782 

    Trust level: Full 

    Application Virtual Path: /OR 

    Application Path: c:\inetpub\wwwroot\OR\ 

    Machine name: abcdefg

 

Process information: 

    Process ID: 17348 

    Process name: w3wp.exe 

    Account name: NT AUTHORITY\NETWORK SERVICE 

 

Exception information: 

    Exception type: ObjectDisposedException 

    Exception message: Cannot write to a closed TextWriter. 

 

Request information: 

    Request URL: http://abcdefg/OR/addcase.aspx 

    Request path: /OR/addcase.aspx 

    User host address: 10.202.23.22 

    User:  

    Is authenticated: False 

    Authentication Type:  

    Thread account name: NT AUTHORITY\NETWORK SERVICE 

 

Thread information: 

    Thread ID: 28 

    Thread account name: NT AUTHORITY\NETWORK SERVICE 

    Is impersonating: False 

    Stack trace:    at System.IO.__Error.WriterClosed()

   at System.IO.StreamWriter.Flush(Boolean flushStream, Boolean flushEncoder)

   at System.IO.StreamWriter.Write(Char[] buffer, Int32 index, Int32 count)

   at System.IO.TextWriter.WriteLine(String value)

   at cases.GetOrderTypes()

   at addcase.Page_Load(Object sender, EventArgs e)

   at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)

   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)

   at System.Web.UI.Control.OnLoad(EventArgs e)

   at System.Web.UI.Adapters.ControlAdapter.OnLoad(EventArgs e)

   at System.Web.UI.Control.LoadRecursive()

   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

 

 

Custom event details: 
 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Open in new window

0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22710342
in you application log the exception clearly says Cannot write to a closed TextWriter.
did you try my suggestion ID:22706304
0
 
LVL 1

Author Comment

by:clintfield
ID: 22710396
ragi0017:
Thanks for your suggestion, but my issue was occurring before I put in the textwriter code.  I added the textwriter logging per silemone's suggestion - ID: 22702370.  It has been removed subsequently, but I still pasted event log errors to be sure I didnt leave anything out that may be of relevance.
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22710504
i coudnt try what this line meant
cmd = sprocs.procGetCategories(con);

so i took the liberty of modifying the commnad objet which accepts a sp name

can you try the following
using (SqlConnection con = new SqlConnection (System.Configuration.ConfigurationManager.ConnectionStrings["ORConnectionString"].ConnectionString))
{
      con.Open ();
      using (SqlCommand cmd = new SqlCommand ())
      {
            cmd.CommandText = "spname";
            cmd.Connection = con;
            
            SqlDataAdapter objDa = new SqlDataAdapter(cmd);
            objDa.Fill (dtS);
      }
}
0
 
LVL 1

Author Comment

by:clintfield
ID: 22710838
ragi0017:
I pasted the code for the sprocs.procGetCategories(con); in the code snippet below.

Your suggestion worked as I thought it probably would.  Im mostly confused why the code in the snippet below would not work.  I use it on the same database, server and version of asp.net in different projects.  This is the first time I have ever had an issue with this style of code.  Ideas?  I would really like to use this as it makes my development much easier and faster (I wrote an exe that connects to my db and generates all the methods for my sprocs like the snippet below. Then I dont have to worry about sproc misspellings, parameter issues, etc.)


    [SqlCommandMethod(CommandType.StoredProcedure)]

    public static SqlCommand procGetCategories(

     [NonCommandParameter] SqlConnection connection)

    {

        return SqlCommandGenerator.GenerateCommand(connection, null, SqlCommandGenerator.NoValues);

    }
 
 

//here is an example of one with parameters

    [SqlCommandMethod(CommandType.StoredProcedure)]

    public static SqlCommand procGetEmployeeInfo(

     [NonCommandParameter] SqlConnection connection

        , [SqlParameter(10)]     string szEID)

    {

        return SqlCommandGenerator.GenerateCommand(connection, null, new object[]{          

           szEID});

    }

Open in new window

0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22711120
Hi clintfield,
How do you handle exception error in your application?
0
 
LVL 1

Author Comment

by:clintfield
ID: 22711200
prairiedog:
Basically the aspx page calls a class like snippet #1.

cases.GetOrderTypes(); looks like snippet #2.

sprocs.procGetOrderTypes looks like snippet #3.

The only error handling is located in the cases class file where it throws the error back to the aspx page.


#1:

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            ckbOrders.DataSource = cases.GetOrderTypes();

            ckbOrders.DataTextField = "Code";

            ckbOrders.DataValueField = "ID";

            ckbOrders.DataBind();
 

        }

    }
 

#2:

    public static DataTable GetOrderTypes()

    {

        DataTable dtS = new DataTable("S");
 

        try

        {

            using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ORConnectionString"].ConnectionString))

            {

                con.Open();

                SqlCommand cmd = new SqlCommand();

                cmd = sprocs.procGetOrderTypes(con);

                SqlDataAdapter objDa = new SqlDataAdapter(cmd);

                objDa.Fill(dtS);

                objDa.Dispose();

            }

        }

        catch (SystemException)

        {

            throw;

        }
 

        return dtS;
 

    }
 

#3:

    [SqlCommandMethod(CommandType.StoredProcedure)]

    public static SqlCommand procGetOrderedForTypes(

     [NonCommandParameter] SqlConnection connection)

    {

        return SqlCommandGenerator.GenerateCommand(connection, null, SqlCommandGenerator.NoValues);

    }

Open in new window

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 1

Author Comment

by:clintfield
ID: 22711266
I found an article that discusses the approach I use with the sprocs class.  It can be found here - http://msdn.microsoft.com/en-us/magazine/cc301780.aspx.

Based on ragi0017 suggestion in ID: 22710504, I think its clear that I dont have a security issue with the db.  The code using SqlCommandGenerator.GenerateCommand also works on my local machine, so why would it not work in staging?  Does the event log posted above (ID: 22710329) give any clues?
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22711356
It seems that somewhere Debugger.Assert call failed and waited for reply.
0
 
LVL 1

Author Comment

by:clintfield
ID: 22713734
prairiedog:
Any ideas on what this could be?
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22714037
The event log shows this:
at SqlCommandGenerator.GenerateCommand(SqlConnection connection, MethodInfo method, Object[] values)  
    at employees.GetActiveEmployees()  

 You may want to look into employees.GetActiveEmployees to see if there is anything abnormal, because somehow SqlCommandGenerator can't generate sqlcommand for GetActiveEmployees.
0
 
LVL 1

Author Comment

by:clintfield
ID: 22714156
prairiedog:

employees.GetActiveEmployees() is essentially the same as code snippet #3 in ID: 22711200. I generate this code and use it for other apps as well and Ive never had issues before, even on the same server. It also works on my local machine. I might be able to get remote debugging configured for me, but it would be nice if there was a way to figure out why the SqlCommandGenerator isnt working in staging.  Do you know of any tricks that might help me figure out specifically what the issue is?  I feel like Im getting close and your suggestion to checking the event log has helped alot.
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22714237
I surely don't know any tricks to figure this out, sorry. I would definitely try remote debugging on your staging server to see what is going on there.
If the remote debugging does not help, then I would try contacting Microsoft, because the error in the event log shows there SqlCommandGenerator's Debugger.Assert failed, which I guess it is out of your control.
0
 
LVL 1

Author Comment

by:clintfield
ID: 22714385
prairiedog:
Im getting that feeling that MS may need to be contacted for this.  I will get the debugger up in a day; then test and post back here the results.  Thanks for the help.
0
 
LVL 21

Expert Comment

by:silemone
ID: 22715136
same thing I stated...debugging will most likely work...surprised no was returned from the try/catch...just a question, I read and caught up to what you've been doing lately...did you ever make the try/catch  text writer where you could actually get information from it that may help?  Who's in charge of the the staging server?  have you contacted them to see if it's a configuration problem?  seriously, it has to be given that it works locally...if the code is not different then it must be the difference in the two machines configurations.  The database you're using:  is the db the same for local use as for staging?  
0
 
LVL 22

Accepted Solution

by:
prairiedog earned 500 total points
ID: 22715989
It's not an exception error, that is why try/catch block does not catch it. It seems to be SqlCommandGenerator's Debugger.Assert call gets triggered. Usually when Debugger.Assert fails, a window will popup asking for response (retry, abort, continue), for example, JIT debugger will load up for you to debug. Somehow, in ASP.NET 2.0, the window does not popup, so it just sits on the server waiting for response. That is what the event log shows.
0
 
LVL 1

Author Comment

by:clintfield
ID: 22730969
Well, the strangest thing...
I had the remote debugger enabled.  I then attached to the process and ran the page in question.  I was first greeted with this error message:
EXECUTE permission denied on object 'sp_sdidebug', database 'master', owner 'dbo'
I then stopped debugging and decided to try the page again without the debugger running.  The page in question worked fast and without any issues.  The hanging just seemed to have stopped.  Could something have happened when remote debugging was installed on the machine?  Im afraid I may run into the same issue when I move to production.  Any thoughts?
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22731052
No clue, sorry.
0
 
LVL 1

Author Closing Comment

by:clintfield
ID: 31407090
You provided the insight that helped pinpoint the problem.  Thanks!
0
 
LVL 1

Author Comment

by:clintfield
ID: 22731169

My problem is gone...but Im not sure why.  However, I think the help provided will allow me to attack the problem in the future if it occurs again.  The suggestion to read the log file was great, and that allowed us to narrow the issue to the SqlCommandGenerator's Debugger.Assert call.

Everybody provided great help, but prairiedog seemed to help pinpoint the problem most.  Thanks to the experts!
0
 
LVL 21

Expert Comment

by:silemone
ID: 22734934
I told you debug a long time ago because you probably were being timed out because you couldn't access db due to some security issue...i.e  no permission...read my post, clintfield...
0
 
LVL 21

Expert Comment

by:silemone
ID: 22734968
read my first 3 posts.
0
 
LVL 1

Author Comment

by:clintfield
ID: 22735026
silemone:
With all due respect, your post wasnt the key to the solution.  The fix is not a result of me debugging on the machine...that seems to beg the question.  Also, you posted this in your last post:
if the code is not different then it must be the difference in the two machines configurations.  
I stated multiple times that identical code is working on the staging machine.  This project seemed to be the only with an issue on the staging server.  I dont think its the configuration as a result.  It has to be something project specific.
The database you're using:  is the db the same for local use as for staging?
I stated multiple times that the db was the same for local and staging in previous posts.  
Your advice to debug is too general.  The real key to fixing this issue (which still comes and goes) is the root of the problem - SqlCommandGenerator's Debugger.Assert call.  The root of the problem is what will get me to the solution...not to debug remotely.  I dont see how debugging would be a fix.
I do appreciate your attempts though.  Thanks again.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 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

18 Experts available now in Live!

Get 1:1 Help Now