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

  • Thread starter Thread starter DraguVaso
  • Start date Start date
D

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
 
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
 
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
 
- 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.
Click to expand...

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.
 
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 said:
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?
Click to expand...

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
 
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
 
Thanks guys!! works great!!

Mike McIntyre said:
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
Click to expand...
 
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
 
Hehe hi Cor!

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

Pieter
 
Back
Top