knowing the 'result' of a, INSERT/UPDATE/DELETE

[DraguVaso]

Hi,

I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.

for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.

Is there any possiblity of doing this?

Thanks a lot,

Pieter

 

[EricJ]

The return parameter id will tell you the autonumber id created here and if
it executed

/* Stored Procedure Insert tblDocuLijn*/
CREATE PROCEDURE spInserttblDocuLijn
@ID bigint output,
-- FK tblDocument.DOCID
@doclDOCID int,
@doclInhoud varchar(2000),
@doclPrijs float
As Insert INTO tblDocuLijn
(doclDOCID,
doclInhoud,
doclPrijs
)
VALUES
(
@doclDOCID,
@doclInhoud,
@doclPrijs

)
SET @ID = SCOPE_IDENTITY()
GO

for your update you will need a double stored proc
first select @output = count(*) from blabla where your condition
then your update

delete the same

hope it helps

eric

 

[Tom Moreau]

Check out @@ROWCOUNT and @@ERROR in the BOL.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


Hi,

I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.

for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.

Is there any possiblity of doing this?

Thanks a lot,

Pieter

 

[Dan Guzman]

- after an INSERT: knowing if this happened well or not

- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.

The SqlCommand.ExecuteNonQuery method will return the number of rows
affected by an INSERT, UPDATE or DELETE. If execution fails, a
SqlException is thrown and you can catch it as desired.

 

[CJ Taylor]

Don't forget that both SQLCommand Objects and SqlDataAdapters have a variety
of events that will let you know all sorts of status of queries etc...

-CJ

 

[Armin Zingler]

I'm writing a VB.NET application who has to insert/update and delete
a whole bunch of records from a File into a Sql Server Database. But
I want to be able to knwo the 'result' of my ctions.

for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if
there were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.

Is there any possiblity of doing this?

The ExecuteNonQuery method is a function returning the number of affected
records.


--
Armin

How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

 

[Mike McIntyre [MVP]]

When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
number of rows affected is returned. Here is an example that captures the
number of rows affected into a variable.

Dim recordsAffected As Integer = cmd.ExecuteNonQuery()

As far as knowing if "things went well" - exceptions will be raised. To
handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
Try..Catch block and the SqlServerException class to find out what errors
occurred.

Try
.....
Catch ex as System.Data.SqlException
.... handle and/or report error
Finally
.... clean up
End Try


--
Mike

Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com


When you call Update

 

[DraguVaso]

Thanks guys!! works great!!

When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
number of rows affected is returned. Here is an example that captures the
number of rows affected into a variable.

Dim recordsAffected As Integer = cmd.ExecuteNonQuery()

As far as knowing if "things went well" - exceptions will be raised. To
handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
Try..Catch block and the SqlServerException class to find out what errors
occurred.

Try
....
Catch ex as System.Data.SqlException
... handle and/or report error
Finally
... clean up
End Try


--
Mike

Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com


When you call Update

 

[Cor Ligthert]

Hi Pieter,

I find it nice to have my name too in this nice group of people.

If you need more answer, feel free to ask.

Now we wait all for Herfried.

))))

Cor

 

[DraguVaso]

Hehe hi Cor!

It was indeed a nice conference here in this topic with everybody all
together

Pieter