By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,978 Members | 1,362 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,978 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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

"DraguVaso" <pi**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.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

Nov 20 '05 #2

P: n/a
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**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.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
Nov 20 '05 #3

P: n/a
> - 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.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DraguVaso" <pi**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.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

Nov 20 '05 #4

P: n/a
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**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.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

Nov 20 '05 #5

P: n/a
"DraguVaso" <pi**********@hotmail.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

Nov 20 '05 #6

P: n/a
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" <pi**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.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

Nov 20 '05 #7

P: n/a
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
Nov 20 '05 #8

P: n/a
Thanks guys!! works great!!

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:uH*************@TK2MSFTNGP09.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.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" <pi**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.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


Nov 20 '05 #9

P: n/a
Hehe hi Cor!

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

Pieter

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP11.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

Nov 20 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.