Help Using Try Catch Exception Please

Mar 25, 2008

  Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs)
Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label)
Try

Catch sqlEx As SqlClient.SqlException
If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then
label2.Visible = True
label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it"
e.Cancel = True
End If
End Try
End Sub Hi, Im using vb.net sql2005 and visual studio 2005

I have 2 tables which have a foregin key relationship.  When i try to delete information from within one of my aspx pages it rightly comes up with an application errror, something along the lines of

 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_callScore_agentType'. The conflict occurred in database 'Merlin_####', table 'callScores', column 'typeID'.
The statement has been terminated.

I have looked around and can see people talking about using a try catch excpetion however i need to know how id implement this using the detailsview1_itemdeleting event. Ive never used this before and havent found a decent tutorial to help.

So far i have this code but im stuck as im not sure that this is correct but more importantly what i put in the try method. Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs)
Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label)
Try
(WHAT GOES HERE)
Catch sqlEx As SqlClient.SqlException
If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then
label2.Visible = True
label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it"
e.Cancel = True
End If
End Try
End Sub Your help would be greatly appreciated

View 12 Replies


ADVERTISEMENT

Try Catch Does Not Catch Exception

May 15, 2007

hi all,



All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement



my problem catch does not catch the error. There is no way to find out teh what is causing this error



SqlCeConnection sqlcon = new SqlCeConnection("

Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"

);

SqlCeCommand sqlcmd = new SqlCeCommand();

sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');

sqlcmd.Connection = sqlcon;

SqlCeDataReader sqldr = null;

try

{

sqlcon.Open();

//use nonquery if result is not needed

sqlcmd.ExecuteNonQuery(); // application crashes here



}

catch (Exception e)

{

base.myErrorMsg = e.ToString();



}

finally

{

if (sqlcon != null)

{

if (sqlcon.State != System.Data.ConnectionState.Closed)

sqlcon.Close();

sqlcon.Dispose();

}

}//end of finlally

View 4 Replies View Related

How To Catch An Exception?

Jul 20, 2005

Is there something like exception handling in T-SQL?For example, how to catch an error of convertion at thissample:CREATE PROCEDURE SP@param VARCHAR(50)AS BEGINDELCARE @var INT-- try {SET @var = CONVERT( int, @param)-- } catch (error#245) {-- handle an error right here-- }ENDIt must be invisible for a caller of SP if something wrong inside SP.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related

Clearing Exception In Catch Block

Sep 3, 2004

Is there any way to clear out the exception from a previous Try/Catch block if I am nesting another Try/Catch block within it. I am executing a SQL Command and based on the Error number coming back decide whether or not to execute various other Sql commands. Now the Outer exception seems to be taking precedence over the Inner try block, and even though the code is stepped through for the inner try block it is never executed due to the Parent Exception. Is there any way to clear the exception received from the outer Try block? Here is a snippet of code:


Try

Cmd = New SqlCommand(Sql, Con)

Cmd.ExecuteNonQuery()

Catch t as SqlException

if t.Number = "2601" then

sql_upd = "<Text>"

Try

Cmd_upd = New SqlCommand(Sql_upd, Con)

Cmd_upd.ExecuteNonQuery()

Catch b as Exception

response.write("<Text>")

End Try

End If

End Try
Thanks,

View 3 Replies View Related

Null Reference Exception During Synch (can't Trap With Try-catch)

Mar 12, 2007

Dear all,

I am running into a null reference exception on replication with SQL Server 2005 Compact Edition. The problem occurs at repl.synchronize(). Even though the statement is inside a try-catch block, the exception is not caught, and the application quits.

Device O/S: Windows Mobile 5.0 v 5.1.195 Build 14957.2.3.1
Hardware: Dell Axim X51v, Intel PXA270
Database: SQL 2005 SP2 (no post SP2-patches)

Exception shows as "an unexpected error has occured in AppName.exe. Select Quit and then restart this program, or select Details for more information." Details:
.. at NullReferenceException at AppName.Form1.Synch()
.. at .... (various lines)
.. at AppName.Form1.Main()

The application is based on the tutorial (Creating a Mobile Application with SQL Server Compact Edition) at http://msdn2.microsoft.com/en-us/library/ms171908.aspx.

The application was successfully working initially but then stopped working on replication after some minor modifications which were not related to the replication code, e.g. adding some buttons to the form.

Three things are puzzling me here..

1. The fact that this is a NullReferenceException and not a SqlCeException
2. Why the try-catch block doesn't work
3. Why the exception occurs

Is this a bug or am I doing something wrong here?

Any help would be appreciated.



Code sample below:

Sub Synch()
Try
LogMsg("Deleting db...")
DeleteDB()
LogMsg("Initializing repl...")
Dim repl As New SqlCeReplication
repl.InternetUrl = http://192.168.0.100/SQLMobile/sqlcesa30.dll
repl.Publisher = "TRKSRV"
repl.PublisherDatabase = "trk_db"
repl.PublisherSecurityMode = SecurityType.NTAuthentication
repl.Publication = "Trk"
repl.Subscriber = "Trk"
repl.SubscriberConnectionString = "Data Source=""Program FilesTrk2007DefectTrk.sdf"";Max Database Size=128;Default Lock Escalation =100;"
LogMsg("Adding subscription...")
repl.AddSubscription(AddOption.CreateDatabase)
LogMsg("Synch...")
repl.Synchronize()
LogMsg("Cleanup...")
repl = Nothing
Catch err As SqlCeException
MessageBox.Show(err.ToString & " " & err.HResult & " " & err.NativeError & " " & err.Message & " " & err.InnerException.ToString)
Catch err2 As Exception
MessageBox.Show(err2.ToString & " " & err2.Message.ToString & " " & err2.InnerException.ToString)
End Try
End Sub

View 1 Replies View Related

Throwing Exception In Stored Proc To Catch In C# And Rollback The Transaction

Sep 27, 2007




Hi,

I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction.
Here is how i wnated to do ( in sequence )

C#
=====
Open a connection
Begin the transaction
Execute the command

In the Stored Proc
===========
do multiple operations one by one
if error
stop processing further
Throw the error
C#
========
if exception
rollback the transaction
else
commit the transaction

I have tried using raise error in stored proc but never thrown exception

Can any one let me know how to achieve this scenario??

~Mohan Babu

View 5 Replies View Related

Try Catch Doesn't Catch Errors Inside A Data Flow Transformation Script Component

Feb 15, 2007

Hi,

I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:

a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?

Hope someone could help.

View 3 Replies View Related

'((System.Exception)($exception)).Message' Threw An Exception Of Type 'System.NotSupportedException'

Jan 16, 2008

Greetings everyone, I am attempting to build my first application using Microsofts Sql databases. It is a Windows Mobile application so I am using Sql Server Compact 3.5 with Visual Studio 2008 Beta 2. When I try and insert a new row into one of my tables, the app throws the error message shown in the title of this topic.
'((System.Exception)($exception)).Message' threw an exception of type 'System.NotSupportedException'



My table has 4 columns (i have since changed my FavoriteAccount datatype from bit to Integer)
http://i85.photobucket.com/albums/k71/Scionwest/table.jpg

Account type will either be "Checking" or "Savings" when a new row is added, the user will select what they want from a combo box.

Next is a snap shot of my startup form.
http://i85.photobucket.com/albums/k71/Scionwest/form.jpg



Where it says "Favorite Account: None" in the top panel, I am using a link label. When a user clicks "None" it will go to a account creation wizard, and set the first account as it's primary/favorite. As more accounts are added the user can select which will be his/her primary/favorite. For now I am just creating a sample account when the label is clicked in an attempt to get something working. Below is the code used.


private void lnkFavoriteAccount_Click(object sender, EventArgs e)

{

FinancesDataSet.BankAccountRow account = this.financesDataSet.BankAccount.NewBankAccountRow();

account.Name = "MyBank Checking Account";

account.AccountType = "Checking";

account.Balance = Convert.ToDecimal("15.03");

account.FavoriteAccount = 1;//datatype is an integer, I have changed it since I took the screenshot.

financesDataSet.BankAccount.Rows.Add(account);
//The next three lines where added while I was trying to get this to work.
//I don't know if I really need them or not, I receive the error regardless if these are here or not.



this.bankAccountTableAdapter1.Update(financesDataSet);

this.financesDataSet.AcceptChanges();

refreshDatabase();

}


the refreshDatabase() code is here:


private void refreshDatabase()

{

this.bankAccountTableAdapter1.Fill(this.financesDataSet.BankAccount);

//Aquire a count of accounts the user has

int numAccounts = financesDataSet.BankAccount.Count;

//Loop through each account and see which one is the primary.

for (int num = 0; num != numAccounts; num++)

{
//Works ok in frmMain_Load, but when my lnkFavoriteAccount_click calls this, it throws the error.

if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)

{
//Display the primary account on our home page. User can click the link label & be taken to their account register.

this.lnkFavoriteAccount.Text = this.financesDataSet.BankAccount[num].Name.ToString();

this.lnkFavoriteFunds.Text = this.financesDataSet.BankAccount[num].Balance.ToString();

break;

}

}

}


and my form_load code

private void frmMain_Load(object sender, EventArgs e)

{

refreshDatabase();

}


So, when I click on the lnkFavoriteAccount label, and my new row gets added, the app stops at the following line in my DataSet.Designer

[global:ystem.Diagnostics.DebuggerNonUserCodeAttribute()]

public byte FavoriteAccount {

get {

try {

return ((byte)(this[this.tableBankAccount.FavoriteAccountColumn]));

}

catch (global:ystem.InvalidCastException e) {
//Stops at the following line, this error was caused by 'if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)'

throw new global:ystem.Data.StrongTypingException("The value for column 'FavoriteAccount' in table 'BankAccount' is DBNull.", e);

}

}

set {

this[this.tableBankAccount.FavoriteAccountColumn] = value;

}

}


I have no idea what I am doing wrong, all of the code I used I retreived from Microsofts help documentation included with VS2008. I have tried used my TableAdapter.Insert() method and it still failed when it got to

if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)

in my refreshDatabase() method it still failed.

When I look, the data has been added into the database, it's just when I try to retreive it now, it bails on me. Am I retreiving the information wrong?

Thanks for any help you guys can offer.

Johnathon

View 1 Replies View Related

The Script Threw An Exception: Exception Of Type 'System.OutOfMemoryException' Was Thrown.

Jan 31, 2007

Hi,

I got an strange problem with one of my packages.

When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.

Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?

Regards,

Jan

View 2 Replies View Related

Try Catch

Jan 15, 2007

Hi

for MS SQL 2000
how can I do ?:


INSERT INTO [Users] (Name)
SELECT Names FROM OtherUsers


I am having an UNIQUE INDEX on [Users].Name

how can I avoid an error ?
if the [Users].Name allready exists I want to jump over the error and keep on inserting

thank you for helping

View 14 Replies View Related

TRY CATCH

Apr 22, 2008

Is it any better than using @@ERROR?

It doesn't seem so

Any opinions?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View 4 Replies View Related

Try Catch

May 13, 2008

Hi!

I'm trying to use Try Catch together with transaction handling but it seems like it doesn't do Rollback although I produce an error.

It looks something like this:


DECLARE @soID INT
BEGIN TRY
BEGIN TRANSACTION
UPDATE Serviceobjekt
SET ServiceobjektstypID = 13
WHERE ServiceobjektID = 26555

UPDATE .... 2 more tables

INSERT INTO Serviceobjekt (
Namn...)
VALUES ('XXXX')

SET @soID = @@IDENTITY
INSERT INTO Atgard (
Namn, ServiceobjektID)
VALUES ('sssss',@soID)

COMMIT TRANSACTION
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH


I get after changing some id's in the where clause which I know is wrong I get a result like this:

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

Shouldn't it be 0 rows affected on all if just one update or insert statement is wrong?

/Magnus

View 4 Replies View Related

Try And Catch

May 22, 2008

Hi I use Try catch and transactions in one of my stored procedures.

Inside I make a call to another stored procedure.

Is it possible to roll back the outer sp?

Please advise.

View 1 Replies View Related

Catch Me If You Can / Bug In The Bug?

Oct 7, 2007

Hello!
Try this (-:

BEGIN TRY

SELECT *

FROM NonExistentTable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can' ;

END CATCH ;

/*

results in:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'NonExistentTable'.

statement not printet */

BEGIN TRY

BEGIN TRY

SELECT *

FROM NonExistentTable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can' ;

END CATCH ;

END TRY

BEGIN CATCH

PRINT 'catched?'

END CATCH ;

-- same behaviour

CREATE PROC catchme

AS

BEGIN

BEGIN TRY

SELECT *

FROM nonexistingtable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can'

END CATCH ;

END ;

BEGIN TRY

EXEC catchme ;

END TRY

BEGIN CATCH

PRINT 'catched' ;

END CATCH ;

-- prints "catched"

BEGIN TRY

EXEC catchnonexistingproc ;

END TRY

BEGIN CATCH

PRINT 'catched again' ;

SELECT error_state()

END CATCH ;

-- prints "catched again"





BEGIN TRY

RAISERROR ('catch this' , 10 , 1) ;

END TRY

BEGIN CATCH

PRINT 'hello' ;

END CATCH ;

-- no hello

BEGIN TRY

RAISERROR ('catch this' , 11 , 1) ;

END TRY

BEGIN CATCH

PRINT 'hello' ;

END CATCH ;

-- prints hello



CREATE PROC catchme2

AS

BEGIN

BEGIN TRY

RAISERROR ('catch this' , 10 , 1) ;

END TRY

BEGIN CATCH

PRINT 'CATCH ME IF YOU CAN' ;

END CATCH ;

END ;



BEGIN TRY

EXEC catchme2 ;

END TRY

BEGIN CATCH

PRINT 'gotcha?' ;

END CATCH ;

-- prints only "catch this"





CREATE PROC catchme3

AS

BEGIN

BEGIN TRY

RAISERROR ('catch this' , 20 , 1) ;

END TRY

BEGIN CATCH

PRINT 'CATCH ME IF YOU CAN' ;

END CATCH ;

END ;

BEGIN TRY

EXEC catchme3 ;

END TRY

BEGIN CATCH

PRINT 'gotcha?' ;

END CATCH ;

-- prints "CATCH ME IF YOU CAN" ;-)

See also
-- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm

The above behaviour is as described in BOL, but
comparing to other languages such as Java, C#, PL/SQL
I would expect that catch always catch unless you take the power from the machine, special
in the case for "catchnonexistingproc". Remark the different behaviour compared to "nonexistingtable"
Documentation shows not example for non existing proc!
That catch doesn't catch "is as bug". So is the catch of non existing proc a bug in the bug? ;^)
I hope not, because it is the behaviour we want...

Greetings




View 7 Replies View Related

Try And Catch Problem

Feb 26, 2007

I want to use a try/catch to catch any error with this datareader so if no data then exit sub, eventually I will create custom error pagebut for now I just want to exit sub so following sql statements are not executed. I get the following problem  when I try to close connection.this was not a problem before using try and catch, do I have in right place? Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        'declare vars        Dim iPreqid As String = CStr(Session("iPreqid"))                Dim sql As String = "select  *  from Vrequest where req_id = '" & iPreqid & "'"        'Connection String  value        Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString        'Create a SqlConnection instance        Using myConnection As New SqlConnection(conn)            Dim myCommand As New SqlCommand(sql, myConnection)            'declare sqldatareader            Dim dbReader As SqlDataReader            Try                myConnection.Open()                myCommand.ExecuteNonQuery()                dbReader = myCommand.ExecuteReader                'Initialize reader                dbReader.Read()                'assign reader index values using column names to controls                Label8.Text = CStr(dbReader("User_Name"))                Label11.Text = CStr(dbReader("NHI"))                Label9.Text = CStr(dbReader("Event_Number"))                Label10.Text = CStr(dbReader("ACC_Number"))                Label7.Text = CStr(dbReader("Required_Date"))                Label1.Text = CStr(dbReader("Title"))                Label3.Text = CStr(dbReader("address1"))                Label4.Text = CStr(dbReader("Birth_date"))                Label2.Text = CStr(dbReader("surname"))                Label5.Text = CStr(dbReader("home_phone"))                Label21.Text = CStr(dbReader("firstname"))                Label20.Text = CStr(dbReader("delivery"))                Label22.Text = CStr(dbReader("ward"))                Label23.Text = CStr(dbReader("request_date"))            Catch ex As Exception                Session("exception") = ex                exit sub            End Try        End Using        'if connection not closed then close        If (Not conn Is Nothing) Then            myConnection.Close()<---------------------------------------->Name 'myconnection is not declared'            myConnection.Dispose()<---------------------------------------->Name 'myconnection is not declared'        End If

View 4 Replies View Related

Try,Catch And Response

Jul 2, 2007

Hi,
Here is the code:Try
If DS.Tables(0).Rows.Count = 0 ThenResponse.Write("Cannot delete an unexist row!")
Response.End()
End If
DS.Tables(0).Rows(0).Delete()
Catch BUG as Exception
Response.Write(Bug.Message)
End Try
I removed all the connection stuff..
What I don't understand is why if ds.table(0).rows.count is zero I still get Bug.Message? I did Response.End ...
Thank you.

View 11 Replies View Related

SP Catch Error

Aug 21, 2007

Hi,

I have the following stored procedure which is added to the windows scheduler. When it is run I'm getting an "Invalid use of null error", therefore I need to capture and any errors but I'm not sure how to do this. Can someone help?
Thanks.


Code:

CREATE PROCEDURE [dbo].[usp_Reminders] AS

DECLARE @ReminderSent datetime
DECLARE @getRecords CURSOR
DECLARE @err int

SELECT E.[RL Staff No], E.Forename, E.Surname, C.CourseName, V.CourseDate , E.Email, V.ReminderSent
FROM empdetails.dbo.v_Employee E
INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No]
INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode
WHERE V.Completed Is Null AND V.ReminderSent Is Null AND V.CourseDate <= dateadd(dd, -3, getdate())
order by e.[rl staff no]

SET @getRecords = CURSOR FOR

SELECT V.ReminderSent
FROM empdetails.dbo.v_Employee E
INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No]
INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode
WHERE V.Completed IS NULL AND V.ReminderSent IS NULL AND V.CourseDate <= dateadd(dd, -3, getdate())
ORDER BY e.[rl staff no]

OPEN @getRecords
FETCH NEXT FROM @getRecords INTO @ReminderSent
WHILE @@FETCH_STATUS= 0
BEGIN
UPDATE Validation
SET ReminderSent = GetDate()
WHERE CURRENT OF @getRecords
FETCH NEXT FROM @getRecords INTO @ReminderSent
END

CLOSE @getRecords
DEALLOCATE @getRecords
GO

View 1 Replies View Related

Try.. Catch Block

Dec 11, 2006

HI,
i'm trying to execute some sql using the Try.. Catch blocks.

Following code does not execute in Catch Block

Begin
begin try
insert into dbo.Test values (1,'aaa')
-- here we are inserting int value in identity field...
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO


Whereas the following block works fine and the Catch block executes.

Begin
begin try
Select 1/0
--This causes an error.
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO





Any idea why is it so?

View 5 Replies View Related

Try..catch Statement

Apr 13, 2008

can someone help me how to corect the query,


BEGIN TRY
INSERT INTO ORDERS VALUES( 1122,'BAC123' );
INSERT INTO ORDLINE VALUES( 1122,991,1 );
PRINT 'VERY GOOD'
END TRY
BEGIN CATCH
PRINT 'FALSE'
INSERT INTO LOGTAB ('BAC123',1122,991,1, ERROR_NUMBER,ERROR_MESSAGE);
END CATCH


i am getting error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'ERROR_NUMBER'.

View 2 Replies View Related

Try Catch Syntax

Mar 14, 2007

hi there i have 2 questions about the TRY/CATCH statement that i have written below. any advice from more experienced persons would be highly appreciated. thankyou.

1. re the @ErrorMessage variable, i have seen this declared with a datatype of varchar(MAX), nvarchar(2048) and nvarchar(4000). which is the correct one for me to use?

2. do i need the statement 'RETURN @ErrorCode' at the end or is this redundant?

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

DECLARE
@ErrorCode int,
@ErrorMessage nvarchar(4000),
@ErrorSeverity int

SELECT
@ErrorCode = @@ERROR,
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY()

IF EXISTS (SELECT *FROM Users WHERE Username = @Username)
RAISERROR('The username already exists.', 15, 1)
ELSE IF EXISTS (SELECT * FROM Users WHERE Email = @Email)
RAISERROR('The email already exists.', 15, 1)
ELSE
RAISERROR(@ErrorMessage, @ErrorSeverity, 1)

RETURN @ErrorCode

END CATCH

END

View 2 Replies View Related

How To Try-catch 'sp_addrolemember'?

Dec 27, 2006

how to try-catch sp_addrolemember?

View 4 Replies View Related

Error Catch In SQL

Jul 20, 2005

Hi everyone, I am using an SQL extended stored procedure to send emails in aDTS package using a cursor that goes through each row in a table.Email sending code below======================exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'======================Fetch Next From EmailCursor ...Now the problem I have is that if an individual email address in invalidthen an error occurs and the whole DTS package falls over. What I would liketo be able to do is "catch the error", something like this (C# code used asexample)try{exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'} catch {exec master.dbo.xp_smtp_sendmail@FROM = "arealaddress@mybusiness.com",@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'}Is this possible??? Normally I would do all the email validation before theemail is entered into the database but unfortunately, I do not have accessto the application code so I am stuck doing it this way.Thanks in advanceMark

View 2 Replies View Related

Use Of TRY/CATCH: Architecture

Oct 20, 2007

Hi there,
My question is more "architectural" than technical.

One of our standard is to systematically enclose the code of our stored proc within a TRY/CATCH block.

What is your point of view?
Should a TRY/CATCH be included even for simple operations?
Are there any drawbacks?

Thanks!

EDIT: Thanks for your input.

View 4 Replies View Related

Try...catch With Transactions

Apr 28, 2008

I have 3 procedures where the first one calls the second one and the second one calls the third one. All procedures have try..catch blocks and transactions. In the third procedure I declare an INT variable and try to give it the value 'A', to create an error. This makes the code in the catch block to run. In the catch block I rollback the transaction and return 1. It then returns to the 2nd procedure, but here I get the exception "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 0. Is there I way to keep running the code in the second procedure without getting this error? I take care of the return value from the 3rd procedure which is 1 so I know that there was an error in the third procedure but I want to run the code below the procedure call to the 3rd procedure in the second procedure, is there I way to do this?

I tried to have a transaction only in the first procedure but then I get this error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

What shoule I do to continue running the code in procedure 2 and procedure 1 after the procedure calls? In real life I have another scenario but the code below is just an easy example that creates the same error that my real procedures create.

Procedure 1:





Code Snippet
ALTER PROCEDURE [dbo].[ProcFirst]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

DECLARE @return int
BEGIN TRANSACTION




EXEC @return = [dbo].[ProcSecond]
IF @return <> 0
BEGIN

SELECT 'return value <> 0 i ProcFirst'
RETURN 1
END
IF (XACT_STATE()) = 1 COMMIT TRANSACTION
RETURN 0

END TRY
BEGIN CATCH -- Error-handling statements


-- End Transaction
IF @@TRANCOUNT > 0 BEGIN

IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
END


SELECT 'catch ProcFirst', @ErrorMessage


RETURN 1

END CATCH
END






Procedure 2:




Code Snippet
ALTER PROCEDURE [dbo].[ProcSecond]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

DECLARE @return int
BEGIN TRANSACTION




EXEC @return = [dbo].[ProcThird]




IF @return <> 0
BEGIN

-- End Transaction
IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1
BEGIN

SELECT 'RollBack transaction'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN

Select 'commit transaction'
COMMIT TRANSACTION;
END
END
SELECT 'return value <> 0 i ProcSecond'
RETURN 1
END



IF (XACT_STATE()) = 1 COMMIT TRANSACTION
RETURN 0
END TRY
BEGIN CATCH -- Error-handling statements

-- End Transaction
IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
END



SELECT 'catch ProcSecond', @ErrorMessage



RETURN 1
END CATCH
END






3rd procedure:




Code Snippet
ALTER PROCEDURE [dbo].[ProcThird]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

DECLARE @tal int
BEGIN TRANSACTION




SET @Tal = 'a'
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
RETURN 0
END TRY
BEGIN CATCH -- Error-handling statements
--End Transaction

IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1
BEGIN

SELECT 'RollBack transaction ProcThird'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN

Select 'commit transaction ProcThird'
COMMIT TRANSACTION;
END
END



RETURN 1
END CATCH
END






Please help me!

//Emelia

View 4 Replies View Related

On Update Catch 22

Aug 15, 2006

How do you make an update cascade opperation if you want to set the PK to an excisting one?
I have a table where to post are the same only the PK differs, now I want to delete one but transfer it's dependecies. i.e redirect its references to the other post.
How can I do that using on update cascade/delete. I see a catch 22 coming up!

/Jonneponne

View 1 Replies View Related

Try Catch Block

Apr 12, 2006

Hi:

one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated.
I am unable to understand as to why the CATCH block is not executed when an INSERT is made
On table T3 which is dropped after the first transaction.

The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2
Invalid object name 't3'.

BOL says TRY€¦CATCH constructs do not trap the following conditions:
Warnings or informational messages with a severity of 10 or lower.
Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.


Here is the script.




use tempdb
go

create table t1 (a int)
create table t2 (b int)
create table t3 (c int)


Begin tran

Insert into t1 values (1)
Insert into t2 values (2)
Insert into t3 values (3)

IF @@error <> 0
Rollback tran
else
commit tran
-------------------------------------------------------------

Select * from t1
Select * from t2
Select * from t3
-------------------------------------------------------------

Drop table t3

-------------------------------------------------------------

Set xact_abort on
Begin try
Begin tran insertNow

Insert into t1 values (1)
Insert into t2 values (2)

save tran insertNow

Insert into t3 values (3)
commit tran insertNow
End try

Begin Catch

IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION insertNow
END;

-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION insertNow
END

End Catch



View 9 Replies View Related

Using TRY...CATCH With RAISERROR

Jun 1, 2006



Hi, I am trying to write a trigger in SQL 2005 and am running into a lot of issues. Here is my code



BEGIN

BEGIN TRY

DECLARE @count int

--check for timeon which is greater than timeoff

SET @count=0

SELECT @count=count(*)

FROM IVRCONFIG.dbo.C_GROUP_HOURS a WITH (NOLOCK),inserted i

WHERE a.XFER_GROUP = i.XFER_GROUP

AND a.DAY_OF_WEEK = i.DAY_OF_WEEK

AND a.TIMEON = i.TIMEON

AND a.TIMEOFF < a.TIMEON

IF @count<>0 RAISERROR (50001,16,1);

END TRY

BEGIN CATCH

RAISERROR

END CATCH

END

The error I specify is one I added to sys.messages. Can anyone tell me where I am going wrong??

View 1 Replies View Related

Error Handling With Try/catch

Feb 6, 2008

Hey,Here's yet another question for you more knowledgeable than me Up to this point I have been using a try/catch statement when dealing with SQL, for exampleint result = 0;try{result = Int32.Parse(command.ExecuteScalar().ToString());}catch{result = 0;}But I read up one some error handling and I have no idea how to solve this anymore. Since I'll be using the catch block to catch exceptions, something like thiscatch (SqlException){throw;}So I was wondering what is the good, standard practice for dealing with this since I need the catch statement to set result to zero or I would end up with an error.Thanks in advance,Sixten 

View 2 Replies View Related

Sqlexpress Catch All Trafic

May 9, 2008

Hi,
I am looking for a way to record all sql server (express) activities.
is there a script or a way to do this,
thanks
Cemal

View 3 Replies View Related

Catch Raiserror From ExecuteReader

Feb 27, 2004

Hi. I am executing a stored procedure. The stored procedure raises an error and all I need is to catch this error. Pretty simple, but it only works with an ExecuteNonQuery and not with an Executereader statement. Can anybody explain to me why this happens?

Here's the sp:

CREATE PROCEDURE dbo.rel_test
AS
select 1
raiserror ('My error.', 11, 2)
return
GO

Here's the ASP.Net page:

<% @Page Language="VB" debug="True" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public Function RunSP(ByVal strSP As String) As SqlDataReader
Dim o_conn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
AddHandler o_conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

o_conn.Open

Dim cmd As New SqlCommand(strSP, o_conn)
cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim rdr as SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rdr.Close()
cmd.Dispose()

Response.Write(o_conn.State)


End Function

Private Sub OnInfoMessage(sender as Object, args as SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
Response.Write(String.Format("The {0} has received a severity {1}, state {2} error number {3}" & _
"on line {4} of procedure {5} on server {6}:{7}", _
err.Source, err.Class, err.State, err.Number, err.LineNumber, _
err.Procedure, err.Server, err.Message))
Next
End Sub

Sub Page_Load(sender as Object, e as EventArgs)
RunSP("rel_test")
End Sub
</script>

View 2 Replies View Related

What The Catch With SqlParameterCollection.Add Constructors

Oct 24, 2005

Hi!
I'm doing a class for working with MSDE.
My goal (as you understood) is to separate business and data layers.
I'm facing the following problem:
I have to use something like cmd.Parameters.Add("@Name", value) in my business layer to avoid using
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 16, value) where you can see that 'SqlDbType.NVarChar' doesn't respect the 'rules' for separating Data/Business layer.

Does it cost a lot to use a 'light' constructor (more job for the SQL engine to convert datas)?

Do I have to do a special function in my 'Data class' to convert an Int32 to a SqlDbType.Int for example?

I could see that Parameters.Add is deprecated and I have to use Parameters.AddWithValue in .Net 2 when you have only 2 parameters.
Is there a better way to handle that?

Thanks for your replies ;)

View 16 Replies View Related

Catch MSSQL Triggers In VB6

Jul 29, 2004

Hi

Is there a way to run VB code when a trigger is executed ? Maybe to define a VB event that will occur when a trigger is executed ??

I've tried googling...

Thanks,

Inon.

View 1 Replies View Related

Which Event Can Catch Web Application Name?

Dec 12, 2007

Hello, everyone:

I want to know if there is an event in profiler can catch web application name. If yes, which one? For example, there is SP named sp_storedprocedure that is called by www.web.com and www.web.com/forumdisplay.php?f=23. I want create a profiler trace that can catch www.web.com or www.web.com/forumdisplay.php?f=23 calling sp_storedprocedure. Any suggestion is great appreciated.

Thanks

ZYT

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved