db errors

[Guest]

Hi all, my asp.net web app will a couple of times a day throw the following
error. The user closes out, goes back in and all is good. Any ideas on where
I can start debugging?

System.Web.HttpException: Error executing child request for
home_risk.aspx. ---> System.Web.HttpUnhandledException: Exception of type
System.Web.HttpUnhandledException was thrown. --->
SterlingWare.Objects.Web.GenericException: Transaction (Process ID 94) was
deadlocked on lock resources with another process and has been chosen as the
deadlock victim. Rerun the transaction. at
SterlingWare.Objects.Web.Database.DBClass.GetDataSet(String sqlstr) at
SterlingWare.Objects.Web.Database.DBClass.GetUserHomePage(String username,
Int16 type) at ASP.home_risk_aspx.loaddata() in D:\website
files\moses\home_risk.aspx:line 73 at ASP.home_risk_aspx.page_load(Object
sender, EventArgs e) in D:\website files\moses\home_risk.aspx:line 51 at
System.Web.UI.Control.OnLoad(EventArgs e) at
System.Web.UI.Control.LoadRecursive() at
System.Web.UI.Page.ProcessRequestMain() --- End of inner exception stack
trace --- at System.Web.UI.Page.HandleError(Exception e) at
System.Web.UI.Page.ProcessRequestMain() at
System.Web.UI.Page.ProcessRequest() at
System.Web.UI.Page.ProcessRequest(HttpContext context) at
System.Web.HttpServerUtility.ExecuteInternal(String path, TextWriter writer,
Boolean preserveForm) --- End of inner exception stack trace --- at
System.Web.HttpServerUtility.ExecuteInternal(String path, TextWriter writer,
Boolean preserveForm) at System.Web.HttpServerUtility.Transfer(String path,
Boolean preserveForm) at System.Web.HttpServerUtility.Transfer(String path)
at ASP.home_aspx.CheckUserName() in D:\website files\moses\home.aspx:line 48
at ASP.home_aspx.page_load(Object sender, EventArgs e) in D:\website
files\moses\home.aspx:line 21 at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive() at
System.Web.UI.Page.ProcessRequestMain() at
System.Web.UI.Page.ProcessRequest() at
System.Web.UI.Page.ProcessRequest(HttpContext context) at
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
completedSynchronously)

TIA!

 

[Kevin Yu [MSFT]]

Hi,

Based on the stack trace, we can see the db exceptions were thrown from a
third party assembly. Since we cannot debug on the 3rd party component, you
can try to contact them directly for a resolution.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

The 3rd party assembly was written in-house. Any ideas what I can be looking
for?

TIA!

 

[Kevin Yu [MSFT]]

Hi,

You can try to set a breakpoint and step into
SterlingWare.Objects.Web.Database.DBClass.GetDataSet(String sqlstr) to find
out what is throwing the exception.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

How would I set a breakpoint in a production environment? It is only
happening there and I cannot duplicate in my dev/test environment.

TIA!

 

[Kevin Yu [MSFT]]

Hi,

It's hard for us to debug on this issue without getting a repro. Please
check the security setting and impersonation on the production server.
Also, as far as I can see, this is an ASP.NET problem since it is thrown
with an HttpException. Something is wrong with the HTTP requestion, I think.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

All the security settings look OK to me. I am not running impersonation and
the asp.net web app uses sql authentication to connect to the database. Am I
exceeding the connections in the connection pool? It appears to happen
during heavy load times.

Thanks

 

[Kevin Yu [MSFT]]

Hi,

To debug, you can try to enlarge the maximum number of connections in pool.
If that's not the case, can you post your code of GetDataSet method?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Here is the code:-

Public Class DBClass
Implements IDisposable

Public strdsn as string
Private objConnect as SqlConnection

Public Sub New
strdsn = ConfigHelper.GetValueFromConfigFile("dsn")
if len(strdsn) = 0 Then
throw new genericexception("No dsn string configured in config file.", 1)
end if

Try
objConnect = new SqlConnection(strdsn)
objConnect.Open
Catch objException as InvalidOperationException
throw new genericexception("Connection to database cannot be opened: no
server specified OR connection is already open.")
Catch objException as SQLException
throw new genericexception("Connection to database could not be
established. The server maybe down.")
End Try
End Sub

Public Sub Dispose() Implements System.IDisposable.Dispose
objConnect.close()
objConnect.dispose()
End Sub

Sub Destruct()
End Sub

Public Function GetDataSet(byval sqlstr as string) as Dataset
Dim ds as new dataset
Try
Dim objDataAdapter as New SqlDataAdapter(sqlstr, objConnect)
objDataAdapter.Fill(ds)
objDataAdapter.dispose()
objConnect.Close()
Catch objException As SqlException
throw new GenericException(objException.Message, objException.Number)
End Try

return ds
End Function

End Class

 

[Kevin Yu [MSFT]]

Hi,

In the GetDataSet method, you needn't call objConnect.Close(), because if
the connection is closed before Fill, it will be close automatically. If
you need to call it anyway, you have to check the state of connection
before closing.

If objConnect.State = ConnectionState.Open Then objConnect.Close()

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Is there any down-side to opening the connection in the New() constructor of
the class? i.e. lets say I have client code that looks like:-

Dim db as new DBClass
db.GetDataSet....
db.GetDataReader
db.ExecuteString...
db.dispose()

Will that cause any performance issues?

 

[Guest]

Also, I should be getting a different error message if it had something to
do with closing a connection that is already closed right?

 

[Kevin Yu [MSFT]]

Sorry, I might have provided incorrect information in my last post. Calling
SqlConnection.Close() multiple times will not caus e an exception. Instead,
you can try to remove objDataAdapter.dispose(). If that's still not the
case, please step into the code and see which line throws the exception.

Yes, as you can see, opening a connection in the constructor of the class
will cause the connection occupied. Just open it whenever you need to use
it. And close it immediately when you finished using.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."