KDC Problem: update

Showing posts with label update. Show all posts

Wednesday, March 28, 2012

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

Kinda new to SQL, have a few questions

I have to update my database format to msSQL (any version) and the specific version I will be using I guess is unknown.

1. Security wise is there anything I should have installed? Can databases be accessed remotely through anything other than serverside scripts or software?
2. Backups will be important and so does msSQL allow you to do this easily? Is there a function in the program that will allow automatic backups? Or, I have autobackup software that basically zips files, would this work for msSQL?

Will probably think of some more questions :P

Thanks a lotIn answer to your second question (2) : Yes MSSQL allows scheduled backups from within the management studio interface. You can set up a scheduled job and get it to run on a daily basis. You need to set up a backup device (file area). Usually advisable to have a seperate drive for this (although a partition MIGHT suffice, except in disk failure scenarios).|||Thanks, when you say a separate drive is this due to performance or loss of data due to disk failure etc?
Is this a feature of all versions?

Thanks again|||ummmmm....do you have sql server client tools installed?

Start there, then get back to us

If you don't have access to a sql server or the tools, this will be very difficult to help you|||This was just general research before I go out and buy anything expensive|||Depending upon your requirements you could start with SQL Server Express, which is very cheap (free, I think).|||yes, it's free. has similar limitations as MSDE, like no more than 4gb database size, etc.|||Ok then thanks, did not know there was a free version.
What else should I add to my checklist of things to install?

sql server client tools?|||there is a free client to go along with the free server. client is SQL Server Management Studio Express, server is SQL Server Express.

you can get both here: http://msdn.microsoft.com/vstudio/express/sql/download/

Monday, March 19, 2012

key/value pair table update query

I'm working with a table that I've created called Config which contains key/value pairs used to get and set site-wide settings. I'm now trying to create a web form which updates the table but I'm not sure how to create the most effective UPDATE query.

Table of course takes this form key | value
----------
config_setting1 | value1
config_setting2 | value2

I'm working with a System.Collections.Specialized.StringDictionary Class object which contains all of the pairs from my webform... anybody have a creative way to build an UPDATE string using this object?

Thanks for any help and suggestions,
ecolner@.yahoo.comI suggest you to create static method to your database class that updates the configuration. The idea is simple.

1) Create parameterized SqlCommand for updates (update conf set value=@.value where setting=@.setting).
2) Start database transaction.
3) Iterate through you string dictionary and on every turn evaluate @.value and @.setting parameters again and then execute the command.
4) If there is errors then roll back database changes, otherwise commit the changes
5) Dispose the update command

Monday, March 12, 2012

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 column information is insufficient or incorrect. Too many...

Hi,

Im trying to Update a table called Contratos in the field Cliente from the table Clientes with the field Cliente.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clientes ON dbo.Clientes
FOR UPDATE
AS
update Contratos
set Cliente = inserted.Cliente
from Clientes
inner join inserted on Clientes.Cliente = inserted.Cliente

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

What is happen...Can somebody help me?

Leonardo AlmeidaOriginally posted by vectords
Hi,

Im trying to Update a table called Contratos in the field Cliente from the table Clientes with the field Cliente.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clientes ON dbo.Clientes
FOR UPDATE
AS
update Contratos
set Cliente = inserted.Cliente
from Clientes
inner join inserted on Clientes.Cliente = inserted.Cliente

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

What is happen...Can somebody help me?

Leonardo Almeida

Where is relation between Contratos and Clientes?|||This is the case... I would like to learn the statement that make the relation between these tables.
Why? Cos these are separated in two different databases and if a user make an update in a table from database X these changes must to be applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida|||Try this one:

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from inserted on Contratos.ID_Client = inserted.ID_Client

--------
Only because of Pele|||Originally posted by vectords
This is the case... I would like to learn the statement that make the relation between these tables.
Why? Cos these are separated in two different databases and if a user make an update in a table from database X these changes must to be applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

Do every table have a primary key? If no - create primary keys...|||Yes ... each table has the primary key

ID_Client for Clients
ID_Con for Contratos|||This is working:

create table table1(id int primary key,uname varchar(10))
create table table2(id int primary key,id_2 int,uname varchar(10))
go
create trigger i_table1 on table1
for update
as
update table2
set uname=inserted.uname
from inserted where table2.id_2=inserted.id
go
insert table1 select 1,'test'
insert table2 select 1,1,'test'
go
update table1 set uname='test2' where id=1
select * from table1
select * from table2

Try to find error in your script.|||Thank you very much...
Can you send me your e-mail to leonardoalmeida2004@.yahoo.com.br to me give back a return from whats happen to my database I will put this struture you send me in my database and tables

THANK YOU VERY MUCH.

Leonardo Almeida

Friday, March 9, 2012

Keeping users out while updating data

I have a flat file with data that I need to use to update existing records
in my database with. I wrote a Stored Proc to do this, but I want to run it
when everyone is out of the database. My question is, how do I keep users
from accessing the database while my update is running? I thought about
taking it offline but BOL said that while it's offline it can not be
modified. Does this mean the data or the structure or both?
Thanks
MikeYou could set it to single_user temporarily...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"M Smith" <msmith@.avma.org> wrote in message
news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> I have a flat file with data that I need to use to update existing records
> in my database with. I wrote a Stored Proc to do this, but I want to run
it
> when everyone is out of the database. My question is, how do I keep users
> from accessing the database while my update is running? I thought about
> taking it offline but BOL said that while it's offline it can not be
> modified. Does this mean the data or the structure or both?
> Thanks
> Mike
>|||OK, I set the database to start up in single_user mode. The problem is when
I try to open query analyzer to execute my stored proc it won't let me in.
It gives me a log in failure because SQL Server is in single user mode. How
can I execute my stored proc?
Mike
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> You could set it to single_user temporarily...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> > I have a flat file with data that I need to use to update existing
records
> > in my database with. I wrote a Stored Proc to do this, but I want to
run
> it
> > when everyone is out of the database. My question is, how do I keep
users
> > from accessing the database while my update is running? I thought about
> > taking it offline but BOL said that while it's offline it can not be
> > modified. Does this mean the data or the structure or both?
> >
> > Thanks
> > Mike
> >
> >
>|||Setting it to restricted_user might be better assuming the normal users do
not have elevated privileges - with single_user the risk is someone getting
the connection before you which sounds like what is happening
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M Smith" <msmith@.avma.org> wrote in message
news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> OK, I set the database to start up in single_user mode. The problem is
when
> I try to open query analyzer to execute my stored proc it won't let me in.
> It gives me a log in failure because SQL Server is in single user mode.
How
> can I execute my stored proc?
> Mike
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> > You could set it to single_user temporarily...
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "M Smith" <msmith@.avma.org> wrote in message
> > news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> > > I have a flat file with data that I need to use to update existing
> records
> > > in my database with. I wrote a Stored Proc to do this, but I want to
> run
> > it
> > > when everyone is out of the database. My question is, how do I keep
> users
> > > from accessing the database while my update is running? I thought
about
> > > taking it offline but BOL said that while it's offline it can not be
> > > modified. Does this mean the data or the structure or both?
> > >
> > > Thanks
> > > Mike
> > >
> > >
> >
> >
>|||How about just using a locking hint for a more restrictive lock. If you used the holdlock locking hint:
SELECT * FROM [TABLE] (HOLDLOCK) it would be as if you were briefly the only user of that table
There are other locks less restrictive than that like tablock and UPDLOCK, which will let others read the data.|||Hi,
I do agree with Jaspers suggestion, set the database to restricted user and
using the same connection (inside query analyzer) try to execute the
procedure
Alter database northwind set RESTRICTED_USER with rollback immediate
go
exec procedures_name
go
Alter database northwind set MULTI_USER
Thanks
Hari
MCDBA
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uH2v#LdOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Setting it to restricted_user might be better assuming the normal users do
> not have elevated privileges - with single_user the risk is someone
getting
> the connection before you which sounds like what is happening
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> > OK, I set the database to start up in single_user mode. The problem is
> when
> > I try to open query analyzer to execute my stored proc it won't let me
in.
> > It gives me a log in failure because SQL Server is in single user mode.
> How
> > can I execute my stored proc?
> >
> > Mike
> >
> > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> > > You could set it to single_user temporarily...
> > >
> > > --
> > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.aspfaq.com/
> > >
> > >
> > >
> > >
> > > "M Smith" <msmith@.avma.org> wrote in message
> > > news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> > > > I have a flat file with data that I need to use to update existing
> > records
> > > > in my database with. I wrote a Stored Proc to do this, but I want
to
> > run
> > > it
> > > > when everyone is out of the database. My question is, how do I keep
> > users
> > > > from accessing the database while my update is running? I thought
> about
> > > > taking it offline but BOL said that while it's offline it can not be
> > > > modified. Does this mean the data or the structure or both?
> > > >
> > > > Thanks
> > > > Mike
> > > >
> > > >
> > >
> > >
> >
> >
>

Wednesday, March 7, 2012

keeping a lock on table or row

I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?You can do it with optimizer hints:

HOLDLOCK, UPDLOCK, PAGLOCK, TABLOCK, TABLOCKX|||thanks for your reply!

Would you elborate for me how to do that, I'm not familiar with SQL Server.

I would like to be able to do this in a single statement if possible, perhaps something to append to a regular update statement to hold the lock? (I tried appending 'holdlock' to the statement but that dosen't appear to be correct syntax)

thanks|||Not True

BEGIN TRAN

Some code....

won't COMMIT until you COMMIT the TRAN with COMMIT TRAN...

Sure in SQL you have to start the transaction..

and in Oracle that's a setting that you can change...

Also, I really hope this is not for an application your building and just in SQL+

In any event getting in and out as FAST as possible should be the way to think...regardless of what you're doing...|||begin tran
select * from your_table (TABLOCK) where 1=2
update your_table set...where...
commit tran

Keep track of data base versions

We have several data bases and we update them every once in a while.

We want to mark each current state witha version in order to be able to track the changes made between 2 versions.

Any recommended tool?

Thanks

Avi harush

Check in Books Online about using Extended Properties.

Topics:

sp_addextendedproperty

sp_updateextendedproperty

fn_listextendedproperty

Code Snippet

EXECUTE sp_updateextendedproperty 'Version', 'v 1.0'
SELECT * FROM ::fn_listextendedproperty
( 'Version', default, default, default, default, default, default )
EXECUTE sp_updateextendedproperty 'Version', 'v 1.1'
SELECT * FROM ::fn_listextendedproperty
( 'Version', default, default, default, default, default, default )

|||

Thanks

Isn't there a product for doing this?

Avi

|||

I don't know of a third party tool to manage this.

But it would be very simple to create a couple of stored procedures (or script files) that can be used by your Upgrade process to check the version and either inform or take appropriate action.

Friday, February 24, 2012

Keep getting update

I keep getting a critical update for SQL Server 2000 Desktop Engine(Windows)
in Window Server 2003(KB829358), I install the update and it keeps
redownloading the update. Can anyone help me with this issueHi Lloyd
Check the version you are currently on and see if it is higher than
8.00.0884, if it is then you may not need the update and might be able to
remove it by using the custom install and checking the don't show this update
option.
If you want to install it manual it can be download from
http://www.microsoft.com/downloads/details.aspx?familyid=814c230b-5c53-453f-810a-b9505acf1392&displaylang=en.
Also make sure that your account has enough permissions to install the
update and update the registry.
John
"Lloyd Pratt" wrote:
> I keep getting a critical update for SQL Server 2000 Desktop Engine(Windows)
> in Window Server 2003(KB829358), I install the update and it keeps
> redownloading the update. Can anyone help me with this issue
>

Keep getting update

I keep getting a critical update for SQL Server 2000 Desktop Engine(Windows)
in Window Server 2003(KB829358), I install the update and it keeps
redownloading the update. Can anyone help me with this issue
Hi Lloyd
Check the version you are currently on and see if it is higher than
8.00.0884, if it is then you may not need the update and might be able to
remove it by using the custom install and checking the don't show this update
option.
If you want to install it manual it can be download from
http://www.microsoft.com/downloads/d...isplaylang=en.
Also make sure that your account has enough permissions to install the
update and update the registry.
John
"Lloyd Pratt" wrote:

> I keep getting a critical update for SQL Server 2000 Desktop Engine(Windows)
> in Window Server 2003(KB829358), I install the update and it keeps
> redownloading the update. Can anyone help me with this issue
>

Keep getting update

I keep getting a critical update for SQL Server 2000 Desktop Engine(Windows)
in Window Server 2003(KB829358), I install the update and it keeps
redownloading the update. Can anyone help me with this issueHi Lloyd
Check the version you are currently on and see if it is higher than
8.00.0884, if it is then you may not need the update and might be able to
remove it by using the custom install and checking the don't show this updat
e
option.
If you want to install it manual it can be download from
http://www.microsoft.com/downloads/...displaylang=en.
Also make sure that your account has enough permissions to install the
update and update the registry.
John
"Lloyd Pratt" wrote:

> I keep getting a critical update for SQL Server 2000 Desktop Engine(Window
s)
> in Window Server 2003(KB829358), I install the update and it keeps
> redownloading the update. Can anyone help me with this issue
>

Wednesday, March 28, 2012

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

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

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

Kinda new to SQL, have a few questions

Monday, March 19, 2012

key/value pair table update query

Monday, March 12, 2012

Key in new recird with grid view

Key column information is insufficient or incorrect. Too many...

Friday, March 9, 2012

Keeping users out while updating data

Keeping users out while updating data

Keeping users out while updating data

Wednesday, March 7, 2012

keeping a lock on table or row

Keep track of data base versions

Friday, February 24, 2012

Keep getting update

Keep getting update

Keep getting update

KDC Problem

Blog Archive

About Me