Get SQL Error from .Net

I am sing SQL Procedures for Insert and Update. In the SQL Procedure i used try catch.  How can i get the SQL exception from .net.

11/10/2008 9:06:47 PM 29906 articles. 0 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 55

 //Execute your sql here.
Catch  (SqlException and sqlEx)
 //Do Something


Pushing out shapes, to a popular beat combo.
11/10/2008 10:26:40 PM

If you want to cause an error in the calling application you need to raise an error in the Catch Block.  If you just want to return the error information as a resultset, execute a select statement.  There are several variables, only available in the Catch Block, that you can use to return error information. 

Example from MSDN

        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;

More info from MSDN

 When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements. For more information about how to use RAISERROR with TRY…CATCH, see Using TRY...CATCH in Transact-SQL.

11/10/2008 10:31:52 PM

Here are some samples in my SP code to raise errors and return then to ASP.NET application


IF EXISTS (SELECT * FROM dbo.People WHERE FirstName = @FirstName AND LastName = @LastName AND DOB=@DOB

AND SiteID = (select SiteID from People where PersonID = @PersonID) and PersonID <> @PersonID)


RAISERROR (N'There is another user %s %s with the same name.',

16, -- Severity,

1, -- State,

@FirstName, @LastName)



And end of this procedure (I have try/catch block in it)




DECLARE @ErrorSeverity int, @ErrorNumber int,

@ErrorMessage nvarchar(4000), @ErrorState int,

@ErrorLine int, @ErrorProc nvarchar(200)

-- Grab error information from SQL functions

SET @ErrorSeverity = ERROR_SEVERITY()

SET @ErrorNumber = ERROR_NUMBER()

SET @ErrorMessage = ERROR_MESSAGE()

SET @ErrorState = ERROR_STATE()

SET @ErrorLine = ERROR_LINE()


SET @ErrorMessage = 'Problem updating person''s information.' + CHAR(13) +

'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) +

' in procedure: ' + @ErrorProc +

' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) +

' Error text: ' + @ErrorMessage

-- Not all errors generate an error state, to set to 1 if it's zero

IF @ErrorState = 0SET @ErrorState = 1


-- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback



--print 'Rollback transaction'



RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)





This code is written based on the article by Kevin Goff

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
11/11/2008 1:56:33 AM

Similar Artilces:

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Getting SQL Access error after updating to .NET 1.1 SPK1
Perhaps you guys can help me out! I an issue has cropped up that may have something to do with the .NET 1.1 SP1 update. I cannot get to any SQL server resources from my local test machine via Web Applications. I can connect and work with the SQL database in Visual Studio .NET 2003 without issue. The problem only exists when trying to browse a .NET web app in a web browser. This happens for EVERY app I have on my test machine. However, if I deploy the project to our DEV servers, things work fine issues. I don't think the DEV servers have the updates yet. Could this be rela...

A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005
 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.Pro...

Getting a SQL Database Error...any ideas SQL experts?
 I am trying to insert a value into a field in a database named ASPNETDB.MDF.  The table name is "profiles_BasicProperties" and the field name is "UserID".  I get an error when I attempt to do this.  See the code I am using to try to do this below...and then the error that I get which is further down in this post.  Note...both the code and the database are on my laptop.  I can connect to the database just fine using Server Explorer in MS VS 2005.  Thanks in advance for any help anybody can offer...  Here is the code I am using: &l...

DW.NET support for SQL Anywhere .NET provider
Hello, we are trying to get DW.NET (v. 2.5) working with Sybase SQL Anywhere .NET provider for database access. Does anybody know if that is possible, and if so, then how? The reason for this question is that we are evaluating DW.NET as a solution for document printing from our existing C# application which uses SQL Anywhere .NET provider. I would expect that these two products should work together, as they are both developed by Sybase and targeted for the same .NET platform. Linas ...

Strange difference between SQL from .Net and SQL within stored proc
Hi, I am trying to fill a DataGrid with random records from my database. It thought I had found a way to randomly select records using SQL. So I built a stored procedure in Access and sure enough, it returns a random set of records. Whe I tried to call this from my app, it kept returning the same set of records. So I copied and pasted the SQL into the app (code below) but it made no difference. Could anyone tell me why this wouldn't work? <%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="...

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

Sql Server Script File(.sql) Execution by Code
I have a problem. I m working on "light weight sql server"  project.   and i want to execute .sql file through code with the help of sqldmo library and sqlns namespace. but i donot know any method to directly execute the .sql file. i am successfully making the full script of select database of sql server.   please help me.... thanks.... Hi, You can use SQL-DMO object in your .net projects by referencing Sqldmo.dll  file which is in  C:\Program Files\Microsoft SQL Server\80\Tools\Binn  folder is default installation parameters are used. I...

T-SQL TRIGGER causes crash in .Net, Interactive SQL works...?
Hi All I am fairly new to Sybase databases. I have a database with a single table (AdressenStorage) to that table I added (T-SQL)triggers for INSERT, UPDATE and DELETE. Everything works fine with InteractiveSQL. But as soon as I execute the statements from my .Net application, it crashes. (The program hangs at cmd.ExecuteNonQuery() ). I removed all triggers and everything worked fine. Right now I have just the (reduced) INSERT trigger (which still crashes): CREATE TRIGGER "AdressenStorage_Insert" on DBA.AdressenStorage for insert as begin -- Handle RevIsCurrent ...

DateTime conversion error between SQL Server and .NET?
Not sure where best to post this because I am not sure where the error is occuring.... but....  I'm taking a DateTime in C#, and saving it to SQL Server 2005 as a DateTime.  When it is reloaded out of the DB back into C# (as a DateTime), I'm seeing between a few microseconds and so far up to 2 milliseconds in difference.  The process I'm using, takes a DateTime and saves it to SQL Server.  This DateTime is then converted ToBinary().ToString() and saved temporarily in in C#. Then I re-load the DateTime that was saved in SQL ...

Converting classic to .NET 2 code, and .NET gets an error back from ADS?
 For future-proofing, our Internal Phone Directory is being moved onto our AD servers, and obviously we're using LDAP for queries.To get the phoneboook queries working, I adapted the current classic ASP code to use LDAP, and that works fine. However, we're wanting to move these pages to .NET, and that's where the problem comes in. Using the same basic logic from the Classic code (90% of its unchanged) to produce an LDAP query, I get the error  155 Unknown error (0x80005000)This is using the same website, and so the same credentials  - unless aspx pages run und...

creating ssis packagte for sql server to sql serer using
Hi friendsdoes any one has code or urls which can help me to create the ssis pacakage which will transfer data from sql server to sql server databaseswith some manipulationsthanks so much Lad LaxmikantPune You are probably in the wrong forum.  This forum is for questions relating to ASP.NET Dynamic Data. thanks,David...

sql anywhere 10 .NET provider & sql anywhere 8 server
sql anywhere 10 .NET provider & sql anywhere 8 server are there any major known issues other than test for my scenarios or it is not compatible at all. i was able to connect, add, delete & update records on a simple table & dataset. Env: VS 2005 .NET 2.0 & sql anywhere 10 thanks vsv This configuration should work. But we do not do significant (if any) testing of this configuration, so you should ensure you do thorough testing with your application. -- Ian McHardy (iAnywhere Solutions) Please reply only to the newsgroup. Whitepapers, TechDo...

used a to converter and am now getting a syntax error.
ok I used a cool utility at to convert some to and it spit out a bunch of code which almost works but I am getting a syntax error on this line Imports (StreamReader sr = Shadows Function)() As StreamReader(objResponse.GetResponseStream()) I tried it that way and also like Imports (StreamReader sr = Shadows Function)() As StreamReader(objResponse.GetResponseStream()) but either way gave the same error Compiler Error Message: BC30035: Syntax error. Source Error: Line 26: Line ...

Web resources about - Get SQL Error from .Net -

Error - Wikipedia, the free encyclopedia
The word error entails different meanings and usages relative to how it is conceptually applied. The concrete meaning of the Latin word "error" ...

Google translated Russia to 'Mordor' in 'automated' error
Google releases a statement after one of its online tools translated "Russian Federation" to "Mordor".

Error 404: Security insights found
... are generally associated with client requests 500 - 599 are generally server related For the casual web user, an encounter with a 404 error ...

Google Translate ‘technical error’ labels Russia as evil kingdom of ‘Mordor’
... of the Rings’ fiery, evil kingdom of Mordor, and its foreign minister is a “sad little horse,” according to a Google Translate “technical error” ...

RANKED: The best and most ridiculous 404 error pages on presidential campaign websites
... machine gun . That may seem a tad unusual, but it's become commonplace this campaign season for candidates to put together priceless 404 error ...

Avoiding errors and issues at the hospital - Videos - CBS News
More than 35 million Americans a year spend at least one night in a hospital, and the system may seem complicated to navigate. Dr. Tara Narula ...

US sailors made 'navigational error' : Pentagon
The 10 U.S. sailors held by Iran before being released on Wednesday made a navigational mistake, the U.S. military said on Thursday.

Carter: 'Navigational error' led sailors to Iran waters -
... were within three miles of Iran's Farsi Island when the Iranians took them. "I think that this much is clear, there was a navigational error ...

Unlike Mozilla, Google anticipated SHA-1 errors from HTTPS traffic inspection systems
Earlier this week, Mozilla was forced to backpedal on banning new SHA-1 digital certificates because the move completely cut off some Firefox ...

Ted Cruz: Failure To Report Goldman Sachs Loan A 'Paperwork Error'
Old Tailgunner Ted feigned ignorance and had the nerve to play the poverty card when asked about the loan he "forgot to mention" to the FEC from ...

Resources last updated: 1/22/2016 1:15:17 AM