I have a table I insert a record into to give access to a user. It uses
primary keys so duplicates are not allowed, so trying to add a record
for a user more than once is not allowed.
In my .NET programs, sometimes it's easier to let the user select a
group of people to give access to eben if some of them may already have
it.
Of course this throws an exception an an error message. Now I could
catch and ignore the message in .NET for this operation but then I'm
stuck if something is genuinely wrong.
So is there a way to do this? :
In my stored procedure determine if an error occured because of a
duplicate key and somehow not cause an exception to be returned to
ADO.NET in that case? 5 2854
Can't you just change the INSERT statement so that it won't insert duplicate
rows? For Example:
INSERT INTO foo (user, ...)
SELECT 'Smith', ...
WHERE NOT EXISTS
(SELECT *
FROM foo
WHERE user = 'Smith') ;
or
INSERT INTO foo (user, ...)
SELECT user, ...
FROM bar
LEFT JOIN foo
ON foo.user = bar.user
WHERE foo.user IS NULL
AND ... ;
--
David Portas
SQL Server MVP
--
Yes, I could check for the records existance before hand. But I wanted
to know if you can detect when an error occurs in T-SQL, and handle it
w/o causing an exception to be thrown in ADO.NET.
<wa********@yah oo.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com... I have a table I insert a record into to give access to a user. It uses primary keys so duplicates are not allowed, so trying to add a record for a user more than once is not allowed.
In my .NET programs, sometimes it's easier to let the user select a group of people to give access to eben if some of them may already have it.
Of course this throws an exception an an error message. Now I could catch and ignore the message in .NET for this operation but then I'm stuck if something is genuinely wrong.
So is there a way to do this? : In my stored procedure determine if an error occured because of a duplicate key and somehow not cause an exception to be returned to ADO.NET in that case?
Unfortunately, error handling in MSSQL (at least up to version 2000) is
somewhat limited - see these articles for more details: http://www.sommarskog.se/error-handling-I.html http://www.sommarskog.se/error-handling-II.html
These sections in particular may be useful for you: http://www.sommarskog.se/error-handl...ml#client-code http://www.sommarskog.se/error-handling-I.html#ADO.Net
Simon
> Yes, I could check for the records existance before hand.
Not beforehand - in the INSERT statement itself. I wanted to know if you can detect when an error occurs in T-SQL, and handle it w/o causing an exception to be thrown in ADO.NET.
See the articles that Simon posted but IMO a stored procedure that requires
you to ignore an error for correct inputs is not a good stored procedure -
it won't fail safe and real problems may go undetected.
--
David Portas
SQL Server MVP
--
(wa********@yah oo.com) writes: I have a table I insert a record into to give access to a user. It uses primary keys so duplicates are not allowed, so trying to add a record for a user more than once is not allowed.
In my .NET programs, sometimes it's easier to let the user select a group of people to give access to eben if some of them may already have it.
Of course this throws an exception an an error message. Now I could catch and ignore the message in .NET for this operation but then I'm stuck if something is genuinely wrong.
So is there a way to do this? : In my stored procedure determine if an error occured because of a duplicate key and somehow not cause an exception to be returned to ADO.NET in that case?
In SQL 2000, no. In the next version of SQL Server, SQL 2005 currently
in beta, yes.
But there is really not that big difference between catching the error in
SQL or in .Net. In the .Net excrption you ignore if the error number 2627
or else you rethrow. But admittedly, it's nicer to do this in the SQL
code, since you keep the error-handling logic closer to the test.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jen S |
last post by:
I feel like I'm missing something obvious here, but I'm stumped...
I have a stored procedure with code that looks like:
INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('An error occurred in the stored proc.', 16, 1);
|
by: xAvailx |
last post by:
I have a requirement that requires detection of rows deleted/updated by
other processes. My business objects call stored procedures to create,
read, update, delete data in a SQL Server 2000 data store. I've done
a fair amount of research on concurrency handling in newsgroups and
other resources. Below is what I've come up as a standard for
handling concurrency thru stored procedures. I am sharing with everyone
so I can get some comments...
|
by: Nyul |
last post by:
Gurus,
I have a verb big problem which I'm unable to explain.
We have a DB2 V6.1.0 on AIX 4.3
I want to make a C stored procedure which at the end will be called by a PHP
script.
The development server was an UDB V6.1.0 on W2K.
Everything went well. I was able to call the stored proc from C and from
Delphi.
|
by: Dave Sisk |
last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table.
(Note this is an external trigger defined with the ADDPFTRG CL command, not
a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined
a SQL stored proc, and the trigger is set to call this SP. I've posted the
simplified source below. I can manually call the stored proc, and the
external trigger is created without any errors. However, when I do...
|
by: Rhino |
last post by:
I am getting an sqlcode of -927 when I execute SQL within a COBOL stored
procedure in DB2 OS/390 Version 6 on OS/390. I have looked at the error
message for that condition and tried everything I could think of to resolve
the problem but nothing works.
The stored proc is running in the DB2 Stored Procedures Address Space and
both the client and the proc have DSNELI linked into their load modules. The
client and proc are running in TSO via...
| |
by: Eric Land |
last post by:
Help! I'm trying to call a parameterized stored proc in ASP.NET in VB. I am
creating a command object and creating a parametr list, and assigning a value
from a session variable (this is working) so that I can sen in the value as a
parameter. When I try to use the adapter's fill command to fill the data set,
it get a error tellig me that I am not giving the stored procedure the
parameter that it expects, even though I am! I've looked...
|
by: ravindrag |
last post by:
Hi,
I am getting error SQL1131N during sqlj.install_jar(...). There is no useful message in the diag.log (even with diag level 4). I am giving the diag.log entries at the end of this posting (would have been ideal if there was an option to attach the file).
command:
db2 call sqlj.install_jar('file:/home/xyz/abc.jar','def.abc')
response:
SQL1131N DARI (Stored Procedure) process has been terminated abnormally.
SQLSTATE=38503
|
by: db2user24 |
last post by:
I'm trying to invoke a DB2 stored procedure. The stored proc is coded
in C and compiled to a shared library, which has been placed in the
<DB2 dir>/functions directory. The platform is Linux (using 64 bit DB2 UDB).
From the debug log it seems that the stored procedure can't be found,
although I don't know why. I can see that the procedure name is defined in the database, however it can not be invoked.
Can anyone shed any light on what could...
|
by: mirandacascade |
last post by:
Questions toward the bottom of the post.
Situation is this:
1) Access 97
2) SQL Server 2000
3) The Access app:
a) sets up pass-thru query
b) .SQL property of querydef is a string, the contents of which
comprise the call to a stored proc
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |