catching exception while deleting calling a store procedure

Here arre my events for deleting.  I am trying to make sure I have my basis covered, but I am running into a potential problem.  The events work but if I call my store procedure and for some reason by chance the name of my table changes in the store procedure, it doesnt catch an invalid object.  How do I correct my code to catch it?   For an example, if my current table in the store procedure is called portingDetails  and for some reason I accidently renamed it to portingDetails1 but the table has never been created, it doesnt catch an error?  What would you suggest? 

 

    Protected Sub FormViewLrn_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewDeleteEventArgs) Handles FormViewLrn.ItemDeleting
        ' Get the Primary Key Id
        Dim refId As Integer = FormViewLrn.DataKey.Value
 
        Dim conn As New SqlConnection(GetConnectionString())

        Dim comm As New SqlCommand()
        comm.CommandText = "sp_User_Delete_ByRefId"
        comm.CommandType = CommandType.StoredProcedure
        'Create a SqlParameter object to hold the input parameter value
        comm.Parameters.Add("@Pass_lrnpclRefId", SqlDbType.Int).Value = Int32.Parse(refId)
        'Create a SqlParameter object to hold the output parameter value
        Dim Flag_Id As New SqlParameter("@Flag_Id_Out", SqlDbType.Int)
        'IMPORTANT - must set Direction as ReturnValue
        Flag_Id.Direction = ParameterDirection.Output
        'Finally, add the parameter to the Command's Parameters collection
        comm.Parameters.Add(Flag_Id)

        Try
            conn.Open()
            comm.Connection = conn
            comm.ExecuteNonQuery()
            Dim Check_Flag As Integer = Convert.ToInt16(Flag_Id.Value)
            ' Count how many records are associated with the Id
            If Check_Flag = 1 Then
                'Cancel Delete action, if the count is more than 1
                dbErrorMessage.Text = "This record cannot be deleted."
                dbErrorMessage.Text += "<br />The record selected indicates dependency."
                ' Cancel Event
                e.Cancel = True
            End If
        Catch ee As SqlException
            dbErrorMessage.Text = ee.Message
        Finally
            comm.Dispose()
            conn.Close()
            conn.Dispose()
        End Try
    End Sub
    Protected Sub FormViewLrn_ItemDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewDeletedEventArgs) Handles FormViewLrn.ItemDeleted

        ' Use the Exception property to determine whether an exception
        ' occurred during the insert operation.
        If e.Exception Is Nothing Then

            ' Use the AffectedRows property to determine whether the
            ' record was inserted. Sometimes an error might occur that 
            ' does not raise an exception, but prevents the insert
            ' operation from completing.
            If e.AffectedRows = 1 Then
                Dim label1 As Label = DirectCast(FormViewLrn.FindControl("label1"), Label)
                Dim label2 As Label = DirectCast(FormViewLrn.FindControl("label2"), Label)
                Dim label3 As Label = DirectCast(FormViewLrn.FindControl("label3"), Label)
                Dim label4 As Label = DirectCast(FormViewLrn.FindControl("label4"), Label)
                Dim label5 As Label = DirectCast(FormViewLrn.FindControl("label5"), Label)

                Dim passContent As String = passLrnProfileOriginal("Delete", label1.Text.ToString(), label2.Text.ToString(), label3.Text.ToString(), label4.Text.ToString(), label5.Text.ToString())
                sendEmailNotification(passContent, "Number")

                BindLrnListGrid()
                dbErrorMessage.Text = "The record has been deleted."
                ' If only one record total, select it by default
                If GridViewLrnListByProfileOcn.Rows.Count = 1 Then
                    GridViewLrnListByProfileOcn.SelectedIndex = 0
                    GridViewLrnListByProfileOcn.Rows(0).RowState = DataControlRowState.Selected
                    FormViewLrn.ChangeMode(FormViewMode.ReadOnly)
                End If
            Else
                ' Use the KeepInInsertMode property to remain in insert mode
                ' when an error occurs during the insert operation.

                'dbErrorMessage.Text = e.Exception.Message
                dbErrorMessage.Text = "There seems to be a problem with the current operation.<br />"
                dbErrorMessage.Text += "If this continues, please contact the Administrator."

                ' Use the ExceptionHandled property to indicate that the 
                ' exception has already been handled.
                e.ExceptionHandled = True

                ' Keep Mode
                'e.KeepInEditMode = True
            End If

        Else
            ' Use the KeepInInsertMode property to remain in insert mode
            ' when an error occurs during the insert operation.

            'dbErrorMessage.Text = e.Exception.Message
            dbErrorMessage.Text = "There seems to be a problem with the current operation.<br />"
            dbErrorMessage.Text += "If this continues, please contact the Administrator."

            ' Use the ExceptionHandled property to indicate that the 
            ' exception has already been handled.
            e.ExceptionHandled = True

            ' Keep Mode
            'e.KeepInEditMode = True

        End If
    End Sub
STORE PROCEDURE:
 
USE [database_name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_User_Delete_ByRefId]
-- Add the parameters for the stored procedure here

@Pass_lrnpclRefId int,
@Flag_Id_Out int Output

AS
BEGIN

Declare @Check_Flag_Id int

-- Check if there is a dependency
If(Exists(SELECT pdRefId, FKocnId,
FKuserId, endUserIdentifier,
FKscRefId, FKlrnpclRefId
FROM portingDetails
LEFT OUTER JOIN lrnToPortInit
ON pdRefId = lrnToPortInit.FKpiRefId
WHERE (FKlrnpclRefId = @Pass_lrnpclRefId) AND (FKscRefId = 3 OR
FKscRefId = 4)))
Begin
Set @Check_Flag_Id = 1
End
Else
-- Okay To Delete
Begin
Set @Check_Flag_Id = 0
End

Set
@Flag_Id_Out = @Check_Flag_Id

END
  

Please remember to click Mark as Answer if my post was helpful.
0
imchaz
5/4/2009 4:31:00 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

0 Replies
739 Views

Similar Articles

[PageSpeed] 11

Reply:

Similar Artilces:

Deleting data by calling the stored procedure in the .NET
Hi, does anyone know how to delete data from the SQL database by calling the stored procedure in the Visual Basic.NET? Because I did the Delete hyperlink bounded inside a datagrid. I have already displayed the appointment date, time in the datagrid so I do not have to input any values inside it. These are my stored procedures code for deleting: ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9)) AS BEGIN IF NOT EXISTS     (SELECT MemNRIC, AppDate, AppTime     FROM DasAppointment     WHERE (Me...

Calling a .NET dll from a SQL Server 2005 Stored Procedure
Setup:  I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states.  There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem:  I have not yet figured out how to call the dll from the stored procedure.  Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help.  So you're seeking for using CLR Integration in SQL2005. You can also take a look at t...

Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')  but I also have a second stored procedure that needs to make reference to&n...

calling a stored procedure from a stored procedure
Hi Group! I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL. I have a sproc which breaks a given string-parameter into pieces. It's result set contains the pieces, e.g. last line of sproc: "select item from mytable;". This sproc works fine. Now I need to call this procedure from another procedure which has a variable result set. I tried the following: <snip... set ll_retval = 0; call sp_breakstring(as_bzp,','); BreakLoop: while ll_retval <> 100 and not ll_retval < 0 loop set li_zaehler = li_zaehler + 1; fetch next sp_breakstrin...

Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...

Calling stored procedure from another stored procedure
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath Yes, you can. Just use EXEC usp_secondStoredProc @params inside your first SP.Nick...

Call as stored procedure from another stored procedure
I am using a stored procedure (SP_A) to insert rows to a table. This is done by the cursor within the procedure. Value for one of the column is provided by SP_B. My problem is that when i call SP_B in SP_A it does not return any value (rather i do not know how to capture it from SP_A). If executed individually it returns a value. Please help with example if something specific needs to be taken care. Suresh wrote: > > Please help with example if something specific needs to be > taken care. Please always post the version and build number of ASA that you are using (e....

Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...

Calling Stored Procedure fromanother Stored Procedure
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames I believe you want to use 'EXEC' abad...

how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------ Inside your first Stored Procedure: exec <name of SP to run> <Eventual Parameters this SP requires> Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se--------------------------------...

Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - Verify if protocol exists and fetch protocol id - add activity - add document - add activity protocol reeturn document id */ select id from protocols where protocols.protname = :protocol into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

calling another stored procedure within a stored procedure?
Hi, I have a while loop stored procedure, I need to send email for each item in the loop using a sendemail stored procedure. I have two question .. 1) I use EXEC PRODUCTION.DBO.SENDMAIL 'email@hotmail.com', 'Start', 'Job Start' in side the while loop stored procedure, but i didn't get any email or error msg. Why? 2) I try to move a file .. how do I find out if the moving is completed successfully? -- MOVE FILES  SET @CMD = 'MOVE /Y ' + '"' + @ORIGINAL_FILE + '"' + ' "' + @MOVE_FILE + '"' EXEC master.dbo.xp_cmdshell @CMD   Thanks for your reply in advance. 1. I hav...

Web resources about - catching exception while deleting calling a store procedure - asp.net.sql-datasource

United States free speech exceptions - Wikipedia, the free encyclopedia
Exceptions to free speech in the United States are limitations on the First Amendment's guarantee of free speech and expression as recognized ...

Podcast #42 – It’s The Exception That Proves The Rule
... Language and Usage , which was frustrated by the high number of certain types of questions that were coming in. Is this podcast the exception ...

Paul Ryan - Mother's health exception to abortion is "Mack truck" sized loophole - YouTube
April 5, 2000 - During the debate on a partial-birth abortion ban, Paul Ryan says that "the [mother's] health exception is a loophole wide enough ...

Queensland the exception as Labor to preference Greens before all in Senate
Labor's national executive has ordered the party to preference the Greens ahead of all others in every state except Queensland.

Exception to Origin rule
Exception to Origin rule

Peter FitzSimons: Sydney siege met with courage, wisdom and one exception
Senator David Leyonhjelm's response to the Sydney siege was gobsmacking.

Women film directors: still the exception
... behind the camera than ever before but recognition is slow to catch up. Women film directors are still talked about in terms of exception - ...

Men Need to Admit That Jian Ghomeshi Is No Exception
That men like Jian Ghomeshi are a terrible exception and if we as men simply stand up and say we are not as bad as that, or that we are not rapists ...

No exception in punishing the corrupt: People's Daily
No exception in punishing the corrupt: People's Daily People's Daily Online BEIJING, June 30 People's Daily , the flagship newspaper of the ...

No exception allowed in graft crackdown: People's Daily
No exception allowed in graft crackdown: People's Daily - Ecns.cn ecns The expulsion of Ling Jihua from the Communist Party of China (CPC) ...

Resources last updated: 11/26/2015 11:26:44 AM