Error trapping of datasource control

Hello,

I encountered an interesting situation. I have a gridview and a sqldatasource. It has delete function. When I delete a record an error of foreign key violation is raised. I would like to trap this error and give a user friendly message to the user.

If I use ADO.Net I can use Try/Catch, but it seems there is no way to do the same thing using datasource. Anyone knows?

Thank you,

J

void CustomersGridView_RowDeleted(Object sender, GridViewDeletedEventArgs e)
 {
// Display whether the delete operation succeeded.
if(e.Exception ==null)
 {
 Message.Text ="Row deleted successfully.";
 }
else
 {
 Message.Text ="An error occurredwhile attempting to delete the row.";
 e.ExceptionHandled =true;
 }
 }

Take a look@.MSDNhttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowdeleted.aspx

|||

Hosam,

Thank you for the reply. It helps, but I would like to get more specific message and give users a friendly message based on that. That is, how can I know the exception is raised due to foreign key violation specifically for example?

Or is there any way to trap the error message passed by a stored procedure?

Thank you,

J

|||

What about check forConstraintException

http://msdn2.microsoft.com/en-us/library/system.data(VS.71).aspx

http://msdn2.microsoft.com/en-us/library/system.data.constraintexception(VS.71).aspx

|||

That is a good suggestion. I will read them.

By the way, thinking for a little time your previous answer does not make sense. The exception is handled after row is ALREADY deleted?

|||

But note that if this exception was thrown nor record will be deleted as the constraint will prevent this.

So if the exception exists no records actually deleted.

|||

Thank you, Hosam.

I tested with detailsview's inserted event, but it did not work. However, what you were saying makes sense as well. I will look into details. If I find more new things, I wll post it here. Thank you.

J

Error Trapping

Hi all,
For the following script, I was wondering why it traps the
first error, but breaks on the second error without going
into the second error trapping.
Thanks in advance, Anna
----
use pubs
go
declare @.errcnt int
--insert datetime into varchar column
insert into authors (au_id)
values (getdate())
--first error trap
select @.errcnt = @.@.error
if @.errcnt <> 0
begin
print 'first error trap'
end
--insert varcahar into datetime
insert into sales
values (6380, 68709, 'wrong data for datetime', 5, 'Net
60', 'BU1032')
--second error trap
select @.errcnt = @.@.error
if @.errcnt <> 0
begin
print 'second error trap'
endAnna,
In the 2nd case you get syntax error. SQL Server checks whether it can cast
your expression to datetime - if it is not possible, thn you use wrong
syntax. Syntax checking occurs first, before compiling and executing the
expression. Because it was not executed, there was no error trapping.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Anna Linne" <lliming12@.yahoo.com> wrote in message
news:0a2f01c38ef2$d9938760$a301280a@.phx.gbl...
> Hi all,
> For the following script, I was wondering why it traps the
> first error, but breaks on the second error without going
> into the second error trapping.
> Thanks in advance, Anna
> ----
> use pubs
> go
> declare @.errcnt int
> --insert datetime into varchar column
> insert into authors (au_id)
> values (getdate())
> --first error trap
> select @.errcnt = @.@.error
> if @.errcnt <> 0
> begin
> print 'first error trap'
> end
> --insert varcahar into datetime
> insert into sales
> values (6380, 68709, 'wrong data for datetime', 5, 'Net
> 60', 'BU1032')
> --second error trap
> select @.errcnt = @.@.error
> if @.errcnt <> 0
> begin
> print 'second error trap'
> end

Error Trapping

I have a linked server setup in a SQL Server 2000 environment that links to
an Oracle database. I then have a Trigger on a SQL Server table, that on
insert, inserts a row of data into the Oracle table via the linked server.
All of this is driven by a front-end application written in VB.NET. I want
the front end to be oblivious to trigger. In other words, if the linked
server loses connection to the Oracle database I don't want the trigger to
execute. I have a "clean-up" stored proc that will insert any rows that
weren't inserted by the trigger. Any ideas on how I can accomplish this? I
am unable to trap the error before it actually occurs. I would like to be
able to somehow check the status of the linked server before executing the
trigger. If it is offline (or errors) then the trigger should not execute
and no errors should be sent to the VB.NET front end. Thanks for any advice
.
Marcocould you not just write a sProc that uses the below and execute it from .ne
t
as follows:
Private Function RunExecRoutines()
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password=" &
Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
Catch ex As Exception
sqlCnError = ("Error: Could not establish database connection")
End Try
cmd = New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = ProcToExec -- whatever sProc you wanna run
cmd.CommandTimeout = 9000
cmd.CommandType = CommandType.StoredProcedure
-- any params you want to pass or receive back...
cmd.Parameters.Add("@.pWho", UNameRoutine)
cmd.Parameters(0).Direction = ParameterDirection.Input
cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
cmd.Parameters(1).Direction = ParameterDirection.Output
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure"
)
MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try
cmd.Dispose()
cn.Close()
cn.Dispose()
end fuction
select srvid, srvstatus, srvname, srvproduct, providername, catalog from
master.dbo.sysservers where srvstatus & 32 <> 0
sp_helptext sp_sqlagent_get_perf_counters -- msdb
sp_helptext sp_enum_oledb_providers -- master
sp_helptext xp_enum_oledb_providers -- master|||Marc,
Thanks for the advice but I am working with a trigger that is executed in
SQL Server. The error occurs in SQL Server and is then reported to the app.
I had thought about the try...catch method but it does me no good since I am
working with a trigger that is idenpendent of .NET. Do you have any other
ideas? I would rather not reengineer my front-end app if at all possible.
Thanks,
Marco
"marcmc" wrote:

> could you not just write a sProc that uses the below and execute it from .
net
> as follows:
> Private Function RunExecRoutines()
> Try
> cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
&
> Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
> cn.Open()
> Catch ex As Exception
> sqlCnError = ("Error: Could not establish database connection")
> End Try
> cmd = New SqlClient.SqlCommand
> cmd.Connection = cn
> cmd.CommandText = ProcToExec -- whatever sProc you wanna run
> cmd.CommandTimeout = 9000
> cmd.CommandType = CommandType.StoredProcedure
> -- any params you want to pass or receive back...
> cmd.Parameters.Add("@.pWho", UNameRoutine)
> cmd.Parameters(0).Direction = ParameterDirection.Input
> cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
> cmd.Parameters(1).Direction = ParameterDirection.Output
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedur
e")
> MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
> MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
> End Try
> cmd.Dispose()
> cn.Close()
> cn.Dispose()
> end fuction
>
> select srvid, srvstatus, srvname, srvproduct, providername, catalog from
> master.dbo.sysservers where srvstatus & 32 <> 0
> sp_helptext sp_sqlagent_get_perf_counters -- msdb
> sp_helptext sp_enum_oledb_providers -- master
> sp_helptext xp_enum_oledb_providers -- master|||would this be any use?
if @.varName <= 0 goto trig_error
trig_error:
raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)|||Marc,
Thanks for the ideas. What I ended up doing was adding a status indicator
field to my table. In the front end I try to connect to the database. If I
cannot connect it sets this indicator field to a 1. If on insert that field
is set to one the Trigger then ignores the inserted row. This will also
allow me to monitor, a bit more closely, any connectivity issues I may have
with the Oracle database. thanks again for your help.
Marco
"marcmc" wrote:

> would this be any use?
> if @.varName <= 0 goto trig_error
> trig_error:
> raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)
>