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 spInserttblDocu Lijn
@ID bigint output,
-- FK tblDocument.DOC ID
@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

"DraguVaso" <pi**********@h otmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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

[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
"DraguVaso" <pi**********@h otmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..
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.Exec uteNonQuery 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.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DraguVaso" <pi**********@h otmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. .. 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

[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

"DraguVaso" <pi**********@h otmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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

[Armin Zingler]

"DraguVaso" <pi**********@h otmail.com> schrieb

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.ExecuteNonQ uery()

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 SqlServerExcept ion class to find out what errors
occurred.

Try
.....
Catch ex as System.Data.Sql Exception
.... handle and/or report error
Finally
.... clean up
End Try
--
Mike

Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
When you call Update
"DraguVaso" <pi**********@h otmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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

[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]

Thanks guys!! works great!!

"Mike McIntyre [MVP]" <mi****@dotnets howandtell.com> wrote in message
news:uH******** *****@TK2MSFTNG P09.phx.gbl...

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.ExecuteNonQ uery()

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 SqlServerExcept ion class to find out what errors
occurred.

Try
....
Catch ex as System.Data.Sql Exception
... handle and/or report error
Finally
... clean up
End Try
--
Mike

Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
When you call Update
"DraguVaso" <pi**********@h otmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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

[DraguVaso]

Hehe hi Cor!

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

Pieter

"Cor Ligthert" <no**********@p lanet.nl> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..

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