Proper ADO Usage where Conflicts will arise

Hi everyone, I'm creating a ASP.NET 2.0 web application utilizing sql server 2000 as a database. My problem revolves around multiuser acces with long running processes. Some of the pages in the application have long running processes against large tables in the database, lets say that take 2minutes to complete. My problem is how do I utilize ado properly in the rest of my application to display a message to users who may try to access data associated with a table while in the midst of one of its long running processes? For instance I would like to notify the user that "Table X is currently locked, please try again in a few minutes".
Do I catch sqlException and examine the .Number property? Any insight is appreciated.

Thanks.

For Sql server 2005 you can use try /catch to Resolve deadlock:Using TRY/CATCH to Resolve Deadlocks in SQL Server 2005

For sql server 2000 you can take a look at:

Tips for Reducing SQL Server Locks

Troubleshooting Deadlocks

Hope it helps.

|||

Sorry I should add that I'm looking for a way to do this WITHOUT stored procedures. My client does not like using stored procedures (regardless of good idea or not), I'm looking for advice on solutions utilizing C# and ADO.NET set of libraries only.

Thanks

sql

PROGRAMTICALLY ACCESS SQLDATASOURCE !

I am trying to acces an SQLDatasource in the code page, I have the following code but get the error as below, any one help please

The SQLDataSource returns 1 value named [ShippingRegion], I think that has somethjing to do with it ??!!

dsShippingRegion.Select(DataSourceSelectArguments.Empty)

Dim myReaderAs Data.IDataReader =CType(dsShippingRegion.Select(DataSourceSelectArguments.Empty), Data.IDataReader)
If myReader.ReadThen
If Convert.IsDBNull(myReader("ShippingRegion"))Then
Beep()
Else
Beep()
EndIf
EndIf

System.InvalidCastException was unhandled by user code
Message="Unable to cast object of type 'System.Data.DataView' to type 'System.Data.IDataReader'."
Source="App_Web_rd5quiy1"
StackTrace:
at admin_administer_shop_productaddnew.Page_Load(Object sender, EventArgs e) in E:\Web Development\WebSites\AJAX_sirs2hers\admin\administer_shop\productaddnew.aspx.vb:line 25
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

dsShippingRegion.Select(DataSourceSelectArguments.Empty)

In the SqlDataSource markup, set DataSourceMode="DataReader". If the DataSourceMode is DataSet (or is not set, since DataSet is the default), a DataView object is returned from Select, which is what you're seeing. If the DataSourceMode is DataReader, an IDataReader object is returned. Seehttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.select.aspx for more details.

Hope that helps.

Aaron

|||

Aaron

this is the code for adding a product, what and where would i place to retrieve the returned value from the SPROC, insert is marked at the bottom with HERE IS THE INSERT

Thanks for the help, changed datasourcemode as above

Steve

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

If Trim(FCKeditor1.Value) =""Then

Dim AlertMSG ="Please enter product description"

ShrekGlobal.GlobalFunctions.CreateMessageAlert(Me, AlertMSG,"strKey1")

ExitSub

EndIf

Dim UploadImageAsBoolean =True

dsProduct.InsertParameters.Clear()

Dim ShopIDAsNew ControlParameter("ShopID","hfShopID","Value")

dsProduct.InsertParameters.Add(ShopID)

Dim CategoryIDAsNew ControlParameter("CategoryID","ddlCategories","SelectedValue")

dsProduct.InsertParameters.Add(CategoryID)

Dim ModelNumberAsNew ControlParameter("ModelNumber","txtModelNumber","Text")

dsProduct.InsertParameters.Add(ModelNumber)

Dim ModelNameAsNew ControlParameter("ModelName","txtModelName","Text")

dsProduct.InsertParameters.Add(ModelName)

Dim UnitCostAsNew ControlParameter("UnitCost","txtUnitCost","Text")

dsProduct.InsertParameters.Add(UnitCost)

Dim StockAsNew ControlParameter("Stock","txtStock","Text")

dsProduct.InsertParameters.Add(Stock)

Dim ProductDescriptionAsNew ControlParameter("ProductDescription","FCKeditor1","Value")

dsProduct.InsertParameters.Add(ProductDescription)

Dim ImageFilenameAsString

If ddlMainImage.SelectedIndex = 0Then

ImageFilename =""

Else

ImageFilename =String.Format("{0}.jpg", ddlMainImage.SelectedValue)

EndIf

dsProduct.InsertParameters.Add("ProductImage", ImageFilename)Dim ShippingIDUK1AsNew ControlParameter("ShippingID1","ddlUKShipping1","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK1)

Dim ShippingAmountUK1AsNew ControlParameter("ShippingAmount1","txtUKShipping1","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK1)

Dim ShippingIDUK2AsNew ControlParameter("ShippingID2","ddlUKShipping2","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK2)

Dim ShippingAmountUK2AsNew ControlParameter("ShippingAmount2","txtUKShipping2","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK2)

Dim ShippingIDUK3AsNew ControlParameter("ShippingID3","ddlUKShipping3","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK3)

Dim ShippingAmountUK3AsNew ControlParameter("ShippingAmount3","txtUKShipping3","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK3)

Dim ShippingIDUK4AsNew ControlParameter("ShippingID4","ddlUKShipping4","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK4)

Dim ShippingAmountUK4AsNew ControlParameter("ShippingAmount4","txtUKShipping4","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK4)

Dim ShippingIDUK5AsNew ControlParameter("ShippingID5","ddlUKShipping5","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK5)

Dim ShippingAmountUK5AsNew ControlParameter("ShippingAmount5","txtUKShipping5","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK5)

Dim ShippingIDInt1AsNew ControlParameter("ShippingID6","ddlIntShipping1","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt1)

Dim ShippingAmountInt1AsNew ControlParameter("ShippingAmount6","txtIntShipping1","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt1)

Dim ShippingIDInt2AsNew ControlParameter("ShippingID7","ddlIntShipping2","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt2)

Dim ShippingAmountInt2AsNew ControlParameter("ShippingAmount7","txtIntShipping2","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt2)

Dim ShippingIDInt3AsNew ControlParameter("ShippingID8","ddlIntShipping3","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt3)

Dim ShippingAmountInt3AsNew ControlParameter("ShippingAmount8","txtIntShipping3","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt3)

Dim ShippingIDInt4AsNew ControlParameter("ShippingID9","ddlIntShipping4","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt4)

Dim ShippingAmountInt4AsNew ControlParameter("ShippingAmount9","txtIntShipping4","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt4)

Dim ShippingIDInt5AsNew ControlParameter("ShippingID10","ddlIntShipping5","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt5)

Dim ShippingAmountInt5AsNew ControlParameter("ShippingAmount10","txtIntShipping5","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt5)

'Dim ProductImage As New ControlParameter("ProductImage", "siProductImage", "NewFileName")

'dsProduct.InsertParameters.Add(ProductImage)

Try

dsProduct.Insert() HERE IS THE INSERT

Dim xAsInteger

For x = 1To 8

'Dim SaveDirectory As String = String.Format("{0}\", Server.MapPath(TemporarySaveDirectory), x)

MsgBox(ddlCategories.SelectedValue)

Dim ProductIDAsInteger = 1

Dim SaveDirectoryAsString =String.Format("~\shopping_mall\images\shops\{0}\{1}\{2}\", Session("ShopID"), ddlCategories.SelectedValue, ProductID)

If ShrekGlobal.GlobalFunctions.FileMove(String.Format("{0}.jpg", x), Server.MapPath(TemporarySaveDirectory), Server.MapPath(SaveDirectory))Then

Beep()

Else

Beep()

EndIf

Next

'siProductImage.TemporarySaveDirectory = String.Format("~\shopping_mall\images\shops\{0}\{1}", hfShopID.Value, ddlCategories.SelectedValue)

'siProductImage.Upload()

ClearPage()

Catch exAs Exception

'UploadImage = False

DeleteImage(0)

MsgBox(String.Format("{0} ~ {1}","Error", ex.Message.ToString()))

'_lblInfo1.Text = sb.ToString

' Consider customizing the message for the EmailNotSentPanel in the ShowAds page.

'Beep()

'_lblInfo1.Text = Now + " :: " + sb.ToString

EndTry

'If UploadImage Then

'Dim TemporarySaveDirectory As String

'aveDirectory =

'End If

EndSub

|||

Try this article:http://aspnet.4guysfromrolla.com/articles/050207-1.aspx

Hope that helps.

Aaron