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,
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
"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...[vbcol=seagreen]
> 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
> 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
>

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...
> > 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
> >
> >
>|||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
>

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

Key in new recird with grid view

Inside my gridview, the user can key in new record, delete record and update record. but dont know why my insert function cant work out and i dunno why this is happen? Can somebody help me out with this?Thanks

My Code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="INSERT INTO [rest_info] ([resname], [menu], [price], [date]) VALUES (@.resname, @.menu, @.date, @.price)"
<InsertParameters>
<asp:Parameter Name="resname" Type="Char" />
<asp:Parameter Name="menu" Type="char" />
<asp:Parameter Name="price" Type="Decimal" />
<asp:Parameter Name="date" Type="datetime" />
</InsertParameters>
</asp:SqlDataSource>

What error do you get?|||no error, just that when i click at the new link inside the gridview which is use to insert data and nothing happen.|||

Hi zouve,

Based on the code you have provided, I didn't see anything wrong.

Could you please make another simple page with a simple GridView and DataSource to see if this issue can be reproduced? If so, plesae post code for the whole page here, and the database table, so that we can make a repro here.

Thanks!

KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS

Hi,
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
--
pmudINF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
AMB
"pmud" wrote:
> Hi,
> My sql table contains duplicate rows & I am trying to delete those but when
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a primary
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud|||hi Alejandro,
My table doesnt have a primary key...so this procedure doesnt fir here...any
other ways to accompliish this?
Thanks
"Alejandro Mesa" wrote:
> INF: How to Remove Duplicate Rows From a Table
> http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
>
> AMB
>
> "pmud" wrote:
> > Hi,
> >
> > My sql table contains duplicate rows & I am trying to delete those but when
> > i try to delete or when i try to edit & save the duplicate rows i get the
> > error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> > WERE AFFTECTE DBY UPDATE.
> >
> > how can i delete these dupliacte rows? I cant even make any column a primary
> > key coz there are duplicates...
> >
> > Plz help.
> >
> > Thanks
> > --
> > pmud|||You can alter the table and add a primary key column to it first
ALTER TABLE tablename
ADD id INT IDENTITY (1, 1)
"pmud" wrote:
> hi Alejandro,
> My table doesnt have a primary key...so this procedure doesnt fir here...any
> other ways to accompliish this?
> Thanks
> "Alejandro Mesa" wrote:
> > INF: How to Remove Duplicate Rows From a Table
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
> >
> >
> > AMB
> >
> >
> > "pmud" wrote:
> >
> > > Hi,
> > >
> > > My sql table contains duplicate rows & I am trying to delete those but when
> > > i try to delete or when i try to edit & save the duplicate rows i get the
> > > error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> > > WERE AFFTECTE DBY UPDATE.
> > >
> > > how can i delete these dupliacte rows? I cant even make any column a primary
> > > key coz there are duplicates...
> > >
> > > Plz help.
> > >
> > > Thanks
> > > --
> > > pmud

KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS

Hi,
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
pmud
INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...b;en-us;139444
AMB
"pmud" wrote:

> Hi,
> My sql table contains duplicate rows & I am trying to delete those but when
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a primary
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud

KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS

Hi,
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
--
pmudINF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...kb;en-us;139444
AMB
"pmud" wrote:

> Hi,
> My sql table contains duplicate rows & I am trying to delete those but whe
n
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROW
S
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a prima
ry
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud

Key column information is insufficient of incorrect....

I am trying to delete all records from a table and could
delete all but two of them. I noticed the two records
have identical info. I am getting the following message:
Key Column information is insufficient of incorrect. Too
many rows were affected by update.
Please help
in query analyzer issue a set rowcount 1, then delete the record.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Diane" <anonymous@.discussions.microsoft.com> wrote in message
news:1b72c01c44ff1$f8ab3920$a401280a@.phx.gbl...
> I am trying to delete all records from a table and could
> delete all but two of them. I noticed the two records
> have identical info. I am getting the following message:
> Key Column information is insufficient of incorrect. Too
> many rows were affected by update.
> Please help

Keeping the 10 newest records... in each GROUP ?

Ok, I give up.
I need to GROUP BY state... keep the newest 10 records in each state...
then delete all other records.
(Why do the simplest *SOUNDING* queries... always stump me?)
CREATE TABLE MyTable
(
MyKey Int
IDENTITY(1,1) PRIMARY KEY,
MySaveDate DateTime NOT NULL
DEFAULT GetDate(),
MyState VarChar(2) NOT NULL
)
GOHi
I'm not sure that understand your request
SELECT OrderID,CustomerId ,OrderDate
FROM Orders WHERE OrderDate IN (SELECT TOP 2 OrderDate FROM
Orders O WHERE O.CustomerId=Orders.CustomerId ORDER BY OrderDate DESC)
ORDER BY CustomerId
This is an example from NorthWind database. We get two newest OrderId made
by each Customer
Can you show us DDL+ sample data + expected result?
""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
news:e0VNlMdsFHA.276@.TK2MSFTNGP10.phx.gbl...
> Ok, I give up.
> I need to GROUP BY state... keep the newest 10 records in each state...
> then delete all other records.
> (Why do the simplest *SOUNDING* queries... always stump me?)
> CREATE TABLE MyTable
> (
> MyKey Int IDENTITY(1,1) PRIMARY KEY,
> MySaveDate DateTime NOT NULL DEFAULT
> GetDate(),
> MyState VarChar(2) NOT NULL
> )
> GO
>
>|||On Mon, 5 Sep 2005 00:34:43 -0400, "A_Michigan_User" wrote:

>Ok, I give up.
> I need to GROUP BY state... keep the newest 10 records in each state...
>then delete all other records.
>(Why do the simplest *SOUNDING* queries... always stump me?)
>CREATE TABLE MyTable
> (
> MyKey Int
>IDENTITY(1,1) PRIMARY KEY,
> MySaveDate DateTime NOT NULL
>DEFAULT GetDate(),
> MyState VarChar(2) NOT NULL
> )
> GO
Hi A_Michigan_User,
If you want to leave the table untouched but remove some rows from the
output, use the code Uri posted.
But if your requirement is to delete rows from the table, but keep only
the 10 most recent rows for each state, use the one below (note: this
version may retain more than 10 rows for a state if there are ties)
DELETE FROM MyTable
WHERE (SELECT COUNT(*)
FROM MyTable AS b
WHERE b.MyState = MyTable.MyState
AND b.MySaveDate > MyTable.MySaveDate) >= 10
Note: this code is untested. Test it on a backed-up database, inside a
transaction and ROLLBACK if the results are wrong.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Keep 1000 newest records... delete all the others

Ok... I'm trying to keep the newest 1000 records in my table,
and delete all the others.
There's got to be an easier/quicker way than this:
I create a derived table, sort it, looking at the 1000 newest
records... then ANOTHER derived table, sort it, looking at the oldest
of those 1000. Then using THAT output to delete all the records older
that record #1000 date.
This sample code keeps the 3 newest records... and deletes 2 older
records...
but the principle should be the same when I use it to keep 1000 newest
records...
and delete about 20000 older records... in my actual application.
DECLARE @.MyTable TABLE
(
MyDate DateTime
)
INSERT INTO @.MyTable VALUES('05-Jan-2005')
INSERT INTO @.MyTable VALUES('02-Jan-2005')
INSERT INTO @.MyTable VALUES('03-Jan-2005')
INSERT INTO @.MyTable VALUES('04-Jan-2005')
INSERT INTO @.MyTable VALUES('01-Jan-2005')
SELECT *
FROM @.MyTable
WHERE MyDate<
(
SELECT TOP 1 MyDate
FROM
(
SELECT TOP 3 MyDate
FROM @.MyTable
ORDER BY MyDate DESC
) AS t1
ORDER BY MyDate ASC
)--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I get the same results w/ this:
select top 2 * from @.mytable order by mydate asc
Just the order is different.
To me, newest means the latest dates, not the earliest dates. E.g.:
5-Jan-2005 is newer than 1-Jan-2005
If you want the newest dates (my definition) you'd use something like
this:
select top 1000 <column list>
from <table name>
order by date_column desc
If you want the oldest date first:
select * from
(select top 1000 <column list>
from <table name>
order by date_column desc) as a
order by date_column
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQlCRsYechKqOuFEgEQLfJACgkvk5KEhp6oZ0
1Cz2CpOmKDUlux0AmgNC
zAClof1qV9omRj72uLahmLPt
=Ug3Z
--END PGP SIGNATURE--
"A_Michigan_User" wrote:
> Ok... I'm trying to keep the newest 1000 records in my table,
> and delete all the others.
> There's got to be an easier/quicker way than this:
> I create a derived table, sort it, looking at the 1000 newest
> records... then ANOTHER derived table, sort it, looking at the oldest
> of those 1000. Then using THAT output to delete all the records older
> that record #1000 date.
> This sample code keeps the 3 newest records... and deletes 2 older
> records...
> but the principle should be the same when I use it to keep 1000 newest
> records...
> and delete about 20000 older records... in my actual application.
> DECLARE @.MyTable TABLE
> (
> MyDate DateTime
> )
> INSERT INTO @.MyTable VALUES('05-Jan-2005')
> INSERT INTO @.MyTable VALUES('02-Jan-2005')
> INSERT INTO @.MyTable VALUES('03-Jan-2005')
> INSERT INTO @.MyTable VALUES('04-Jan-2005')
> INSERT INTO @.MyTable VALUES('01-Jan-2005')
> SELECT *
> FROM @.MyTable
> WHERE MyDate<
> (
> SELECT TOP 1 MyDate
> FROM
> (
> SELECT TOP 3 MyDate
> FROM @.MyTable
> ORDER BY MyDate DESC
> ) AS t1
> ORDER BY MyDate ASC
> )
>|||So I guess you could
DELETE TableName WHERE YourKey NOT IN (SELECT TOP 1000 * FROM TableName
ORDER BY YourKey ASC (or) DESCc)
One curiosity of the TOP n is that it seems to behave differently during a
regular select clause than it does in the above!
Consider a simple table with an integer column. The values for the Column
are 1,2,3,3,3,4,5.
SELECT TOP 3 * returns 1,2,3
Whereas the above DELETE statement deletes 2 rows and you are left with
1,2,3,3,3
I must confess I don't know what is going on!!! - unless SQL is preventing a
Set overlap in which case depending on the distribution of your
Keys, you may or may not end up deleting everything but 1000 rows'
""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
news:ed6PD5KOFHA.164@.TK2MSFTNGP12.phx.gbl...
> Ok... I'm trying to keep the newest 1000 records in my table,
> and delete all the others.
> There's got to be an easier/quicker way than this:
> I create a derived table, sort it, looking at the 1000 newest
> records... then ANOTHER derived table, sort it, looking at the oldest
> of those 1000. Then using THAT output to delete all the records older
> that record #1000 date.
> This sample code keeps the 3 newest records... and deletes 2 older
> records...
> but the principle should be the same when I use it to keep 1000 newest
> records...
> and delete about 20000 older records... in my actual application.
> DECLARE @.MyTable TABLE
> (
> MyDate DateTime
> )
> INSERT INTO @.MyTable VALUES('05-Jan-2005')
> INSERT INTO @.MyTable VALUES('02-Jan-2005')
> INSERT INTO @.MyTable VALUES('03-Jan-2005')
> INSERT INTO @.MyTable VALUES('04-Jan-2005')
> INSERT INTO @.MyTable VALUES('01-Jan-2005')
> SELECT *
> FROM @.MyTable
> WHERE MyDate<
> (
> SELECT TOP 1 MyDate
> FROM
> (
> SELECT TOP 3 MyDate
> FROM @.MyTable
> ORDER BY MyDate DESC
> ) AS t1
> ORDER BY MyDate ASC
> )
>|||alex, i don't understand.. this does exacly what it's expected to do.
in your example, after the inner 'select top 3..' is executed, what you're
left with is something like this:
delete tablename where yourkey not in (1,2,3) --iow, in(4,5)
what you see as an anomaly is result of yourkey column not being unique.
dean
"Alex Potter" <apotter@.videotron.ca> wrote in message
news:e8KcVPMOFHA.1948@.TK2MSFTNGP14.phx.gbl...
> So I guess you could
> DELETE TableName WHERE YourKey NOT IN (SELECT TOP 1000 * FROM TableName
> ORDER BY YourKey ASC (or) DESCc)
> One curiosity of the TOP n is that it seems to behave differently during
> a
> regular select clause than it does in the above!
> Consider a simple table with an integer column. The values for the Column
> are 1,2,3,3,3,4,5.
> SELECT TOP 3 * returns 1,2,3
> Whereas the above DELETE statement deletes 2 rows and you are left with
> 1,2,3,3,3
> I must confess I don't know what is going on!!! - unless SQL is preventing
> a
> Set overlap in which case depending on the distribution of your
> Keys, you may or may not end up deleting everything but 1000 rows'
>
> ""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
> news:ed6PD5KOFHA.164@.TK2MSFTNGP12.phx.gbl...
>|||Why 1000 rows? Might this requirement not change if the volume of
business goes up or down? A more logical method (and much more
efficient) would seem to be to base this on a date rather than a fixed
value:
DELETE FROM YourTable
WHERE dt <= DATEADD(DAY,-28,CURRENT_TIMESTAMP)
You could schedule this as a regular job if you need to.
DISPLAYING only 1000 rows is a different matter. You don't need to
delete rows just because you don't want to display them.
David Portas
SQL Server MVP
--|||> Why 1000 rows? Might this requirement not change if the volume of
> business goes up or down? A more logical method (and much more
> efficient) would seem to be to base this on a date rather than a fixed
I need to keep the 1000 newest. (That's *MORE* than we'll really need. But
we *DO* want
to keep "a certain number"... not... "delete based by dates".)

> DISPLAYING only 1000 rows is a different matter. You don't need to
> delete rows just because you don't want to display them.
I need to "delete". (My example was written to "display"... just so I
could see what I was about to delete... before
actually deleting.)|||I also tried the "NOT IN" method... and it seemed to work... but I was
assuming that might be slower to execute.
I "think" that it's doing this... but it might not be:

> Get 1000 newest records... sort them... but return the 20000 older
> records instead.
> Make a massive list of 20000 comma separated dates. (Ugh)
> Delete them... 1-by-1.
> If the same date appears 5000 times... search for it each time... and
> delete it each time. (Double ugh.)
Maybe MS-SQL is smart enough to know NOT to do it the hard way... and is
optimizing it automatically.
I really don't know.
What I'd *REALLY* like to do... is find the date of record number 1001
(sorted by date, newest first)... and
just do a single, quick delete... based on that 1 date.
DELETE FROM myTable
WHERE MyDate<@.thatDate
Done.
But what's the easiest/quickest way to find the date of the 1001st record
(sorted by date, newest first)?
(Hopefully with a single SQL statement.)
Thanks for everyone's good ideas.

> DELETE TableName WHERE YourKey NOT IN (SELECT TOP 1000 * FROM TableName
> ORDER BY YourKey ASC (or) DESCc)
> One curiosity of the TOP n is that it seems to behave differently during
> a
> regular select clause than it does in the above!
> Consider a simple table with an integer column. The values for the Column
> are 1,2,3,3,3,4,5.
> SELECT TOP 3 * returns 1,2,3
> Whereas the above DELETE statement deletes 2 rows and you are left with
> 1,2,3,3,3
> I must confess I don't know what is going on!!! - unless SQL is preventing
> a
> Set overlap in which case depending on the distribution of your
> Keys, you may or may not end up deleting everything but 1000 rows'
>
> ""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
> news:ed6PD5KOFHA.164@.TK2MSFTNGP12.phx.gbl...
>|||""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
news:Oyd1hxVOFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I also tried the "NOT IN" method... and it seemed to work... but I was
>assuming that might be slower to execute.
> I "think" that it's doing this... but it might not be:
>
> Maybe MS-SQL is smart enough to know NOT to do it the hard way... and is
> optimizing it automatically.
> I really don't know.
> What I'd *REALLY* like to do... is find the date of record number 1001
> (sorted by date, newest first)... and
> just do a single, quick delete... based on that 1 date.
> DELETE FROM myTable
> WHERE MyDate<@.thatDate
> Done.
> But what's the easiest/quickest way to find the date of the 1001st record
> (sorted by date, newest first)?
> (Hopefully with a single SQL statement.)
> Thanks for everyone's good ideas.
something like this?
delete myTable
where myDate<(select min(myDate) from (select top 1001 myDate from myTable
order by myDate desc))
dean|||Dean, I couldn't get your example to work... unless I changed that SELECT
TOP x statement
to a derived table. Then it seems to do exactly what I need. Thanks.
DECLARE @.MyTable TABLE
(
MyDate DateTime
)
INSERT INTO @.MyTable VALUES('05-Jan-2005')
INSERT INTO @.MyTable VALUES('02-Jan-2005')
INSERT INTO @.MyTable VALUES('03-Jan-2005')
INSERT INTO @.MyTable VALUES('04-Jan-2005')
INSERT INTO @.MyTable VALUES('06-Jan-2005')
INSERT INTO @.MyTable VALUES('07-Jan-2005')
INSERT INTO @.MyTable VALUES('08-Jan-2005')
INSERT INTO @.MyTable VALUES('09-Jan-2005')
SELECT *
FROM @.MyTable
WHERE MyDate<
(
SELECT MIN(MyDate)
FROM
(
SELECT TOP 3 MyDate
FROM @.MyTable
ORDER BY MyDate DESC
) AS t
)
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:%23CfaC6VOFHA.2704@.TK2MSFTNGP15.phx.gbl...
> ""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
> news:Oyd1hxVOFHA.1476@.TK2MSFTNGP09.phx.gbl...
>
> something like this?
> delete myTable
> where myDate<(select min(myDate) from (select top 1001 myDate from myTable
> order by myDate desc))
> dean
>|||> I get the same results w/ this:
> select top 2 * from @.mytable order by mydate asc
> Just the order is different.
> To me, newest means the latest dates, not the earliest dates. E.g.:
> 5-Jan-2005 is newer than 1-Jan-2005
I agree. (But I've never heard anyone say "the socks I bought back in 1998
are newer than the
ones I just bought in 2005.)
Newer = more recently = 05-Jan-2005 = MAX(MyDate) = DESC sorted
Older = long ago = 01-Jan=1998 = MIN(MyDate) = ASC sorted
I hope I've got that correct. (But I can't find anything in my example...
or anyone's reply... that says otherwise.)
> If you want the newest dates (my definition) you'd use something like
> this:
> select top 1000 <column list>
> from <table name>
> order by date_column desc
> If you want the oldest date first:
> select * from
> (select top 1000 <column list>
> from <table name>
> order by date_column desc) as a
> order by date_column
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQlCRsYechKqOuFEgEQLfJACgkvk5KEhp6oZ0
1Cz2CpOmKDUlux0AmgNC
> zAClof1qV9omRj72uLahmLPt
> =Ug3Z
> --END PGP SIGNATURE--
>
> "A_Michigan_User" wrote: