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

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,
PieterThe 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" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.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
>|||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" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.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|||> - 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" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.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
>|||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" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.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
>|||"DraguVaso" <pietercoucke@.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|||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" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.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
>|||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|||Thanks guys!! works great!!
"Mike McIntyre [MVP]" <mikemc@.dotnetshowandtell.com> wrote in message
news:uHVvStGKEHA.892@.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" <pietercoucke@.hotmail.com> wrote in message
> news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> whole
be[vbcol=seagreen]
> there
>|||Hehe hi Cor!
It was indeed a nice conference here in this topic with everybody all
together :-)
Pieter
"Cor Ligthert" <notfirstname@.planet.nl> wrote in message
news:%23TvQDHHKEHA.1000@.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
>sql