Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1017
  • Last Modified:

ASP.Net page hangs on DB calls

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
clintfield
Asked:
clintfield
  • 16
  • 8
  • 5
  • +1
1 Solution
 
silemoneCommented:
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
 
clintfieldAuthor Commented:
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
 
silemoneCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
clintfieldAuthor Commented:
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
 
clintfieldAuthor Commented:
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
 
prairiedogCommented:
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
 
clintfieldAuthor Commented:
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
 
Anurag ThakurTechnical ManagerCommented:
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
 
prairiedogCommented:
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
 
clintfieldAuthor Commented:
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
 
Anurag ThakurTechnical ManagerCommented:
in you application log the exception clearly says Cannot write to a closed TextWriter.
did you try my suggestion ID:22706304
0
 
clintfieldAuthor Commented:
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
 
Anurag ThakurTechnical ManagerCommented:
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
 
clintfieldAuthor Commented:
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
 
prairiedogCommented:
Hi clintfield,
How do you handle exception error in your application?
0
 
clintfieldAuthor Commented:
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
 
clintfieldAuthor Commented:
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
 
prairiedogCommented:
It seems that somewhere Debugger.Assert call failed and waited for reply.
0
 
clintfieldAuthor Commented:
prairiedog:
Any ideas on what this could be?
0
 
prairiedogCommented:
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
 
clintfieldAuthor Commented:
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
 
prairiedogCommented:
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
 
clintfieldAuthor Commented:
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
 
silemoneCommented:
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
 
prairiedogCommented:
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
 
clintfieldAuthor Commented:
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
 
prairiedogCommented:
No clue, sorry.
0
 
clintfieldAuthor Commented:
You provided the insight that helped pinpoint the problem.  Thanks!
0
 
clintfieldAuthor Commented:

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
 
silemoneCommented:
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
 
silemoneCommented:
read my first 3 posts.
0
 
clintfieldAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 8
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now