how can sqldataobject error be traped

My database has two tables that are joined by a relation (Item category and the item). My relation will not permit the deletion of item categories if there are item referencing it.

How can that error be trapped to then show a message such as. Cannot delete item category that has item associated with it?

This is a asp.net web form with VB

this is the error I get

Server Error in '/TWGSalesInfo' Application.

---

The DELETE statement conflicted with the REFERENCE constraint "FK_PurchaseVendors_PurchaseItems". The conflict occurred in database "CustSegmentation", table "dbo.PurchaseVendors", column 'VendorPurchaseItemID'.
The statement has been terminated.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_PurchaseVendors_PurchaseItems". The conflict occurred in database "CustSegmentation", table "dbo.PurchaseVendors", column 'VendorPurchaseItemID'.
The statement has been terminated.

Source Error:

Line 1904: }Line 1905: try {Line 1906: int returnValue = this.Adapter.DeleteCommand.ExecuteNonQuery();Line 1907: return returnValue;Line 1908: }

Thanks

Dj

Simply add a try/catch block

try

{

int returnValue = this.Adapter.DeleteCommand.ExecuteNonQuery();

return returnValue

}

catch(SqlException)

{

/// throw your custom exception here

throw new MyCustomDeleteException("Unable to delete record...");

}

and in the upper level catch only exceptions of your custom type

how can i use COUNT in my stored procedure?

hello,

i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums

How can i use the COUNT function in my stored procedure to take only first n Categories?

I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...

here is my stored procedure (i use it for two nested Repeaters)

ALTER PROCEDURESubCategoriiInCategorii

CategoryIDint)

AS

SELECTCategoryID,Name, DescriptionFROMCategoriesWHERECategoryID = @.CategoryID

ORDER BYName

SELECTp.SubCategoryID, p.Name,p.CategoryIDFROMSubCategorii p

ORDER BYp.Name

i hope you understand what i mean, thank you

Hi,

Have a look at this threadhttp://forums.asp.net/t/1138145.aspx it will tell you how to get N first rows or in otherwords N top rows.

Hope it will help you out.

Thanks and best regards,

|||

if i use the stored procedures from there i get error from the nested repeater...

i tryied this, but i get an error: invalid column name RowNumber ...why the alias don't work?

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategorii

WHEREDepartamentID = @.DepartamentIDANDRowNumber = 3

|||

Hi,

Actually I don't exactly get what are you trying to do in the above stored procedure.

Thanks and best regards,

|||

if i execute the stored procedure without the "AND" part, in the RowNumber i have values from 1 to 9 (counts how many CategoriyID's i have)

i think it work, try to execute the procedure

thank you

i just resolved it, here is the stored procedure

SELECT*FROM(

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategoriiWHEREDepartamentID = @.DepartamentID

)ASMyTable

WHERERowNumber <=5

i think is good for what i need, thank you again for help