Why Stored Procedure throws exception during transaction?

[_Mmesa1984]

Hello everyone?

I will post in english but I am an spanish speaker!! So let's try to do this.....

I am stocked with an issue working with ado.net and sql server2009.

the problem is when i call a Store procedure that try to insert data into a column with a check

constrait. the insert is into a transaction block, but when i call the sp via ado.net y expect to receive 

the return value so that i can manage the process behaviour and send appropriate message.

but instead, after the sql command> executenonquery() method it throws the check column exception from sql server.

as it is into a transaction, i was expecting not to have an axception, and can filter via my return value.

with this exception, users receive very confidential information from the database structure, and even i can not indicate that the problem was just incorrect data format for that column value.

does anyone knows how should i configure sql server or app server with ado.net in order to not receive

(or throw...) that check constraint exception??

i have to add that this is an academy proyect, so that i am mandatory to use some architectures and technologies, but as i am stocked, i want to hear new options.

thanks in advance!!

[#2]

Hi @_Mmesa1984,

 the answer is in your description: attend to the user input and output only the error description -if you have to- and not the exception details to the end-user. Exceptions should be logged for the developer and not for end-users. Instead, try to inform the user on the wrong input or use proper input controls to validate the user input which has always been the Achilles heel of every application 🙂

Also, don't forget to put your code inside try-catch-finally blocks to eliminate the application exceptions and hanging of your app.

HTH,
Vassilis

[#3]

BTW there is no "sql server2009" 😉

[#4]

@vassilis-MSFT 

Thanks man!!

But you're wrong!! Yes there is....

[#5]

Hi @_Mmesa1984

I'm not wrong 🙂

The instance you are showing is version 10.50.1600 => SQL Server 2008 R2. I hope it makes sense to you, now.

If not check here for a comprehensive list of all SQL Server builds and versions available.

Vassilis