Catch Duplicate records

I have a table that I am inserting records to via VB6 using an ADO command.
If I try to insert a duplicate record I get the error message
-2147217873 Access Primary Key violation etc
This error doesn't jump to my error handler so how can I catch it? is there
something I can do from with SQL? or am I missing something in VB
ThanksThe best way to handle the error is to prevent it from happening in the firs
t
place. That can be done by, for example, checking to see if the value exists
before you try to insert it.
You can certainly catch this error in VB and I would propose that is the bes
t
place to do it.
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:%239gwgADTFHA.3544@.TK2MSFTNGP10.phx.gbl...
>I have a table that I am inserting records to via VB6 using an ADO command.
> If I try to insert a duplicate record I get the error message
> -2147217873 Access Primary Key violation etc
> This error doesn't jump to my error handler so how can I catch it? is the
re
> something I can do from with SQL? or am I missing something in VB
> Thanks
>|||If u use stored procedure
u can return value from it
e.g
....
.....
...
INSERT INTO ....
......
RETURN @.@.ERROR
in VB u must check the return value if return value != 0
then an error occuerd. In case everything ok zero will returnd
Message posted via http://www.webservertalk.com|||Thanks, I have tried this
declare @.Stockcode char(30)
declare @.Barcode char(20)
declare @.Qty int
set @.Stockcode = '123456'
set @.Barcode = '1234567890123'
set @.Qty = 12
INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
RETURN @.@.ERROR
GO
but I get the error
A RETURN statement with a return value cannot be used in this context.
What am I doing wrong?
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
> If u use stored procedure
> u can return value from it
> e.g
> ....
> .....
> ...
> INSERT INTO ....
> ......
> RETURN @.@.ERROR
>
> in VB u must check the return value if return value != 0
> then an error occuerd. In case everything ok zero will returnd
> --
> Message posted via http://www.webservertalk.com|||Again, prevent the error from happening by checking for the value. Given tha
t
you did not specifiy the PK I'll use the Force and guess:
Create Table dbo.OuterBarCodes
(
StockCode Char(30)
, BarCode Char(20)
, Quantity Int
, Constraint PK_OuterBarCodes Primary Key (StockCode, BarCode)
)
If Not Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
If you really want to know whether the value exists then do this:
If Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Begin
Raiserror('Duplicate StockCode and BarCode', 16, 1)
Return
End
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:ODBIBhDTFHA.560@.TK2MSFTNGP10.phx.gbl...
> Thanks, I have tried this
> declare @.Stockcode char(30)
> declare @.Barcode char(20)
> declare @.Qty int
> set @.Stockcode = '123456'
> set @.Barcode = '1234567890123'
> set @.Qty = 12
>
> INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
> RETURN @.@.ERROR
> GO
> but I get the error
> A RETURN statement with a return value cannot be used in this context.
> What am I doing wrong?
> "E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
> news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
>