known, expected or weird behavior ?

Hi all,
Please have a look at a script below:
USE tempdb
DECLARE @.t TABLE (c1 uniqueidentifier, c2 uniqueidentifier)
INSERT @.t (c1, c2)
SELECT y.id, NULL
FROM ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5
) x (id)
CROSS JOIN
( SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID()
) y (id)
UPDATE t
SET c2 = y.c2
FROM @.t t,
( SELECT c1, NEWID()
FROM ( SELECT DISTINCT c1
FROM @.t
) x (c1)
) y (c1, c2)
WHERE t.c1 = y.c1
SELECT * FROM @.T
I would expect c2 column value to be the same accross all records where c1
column value is the same.
But in fact c2 column is unique accross the table. I looked at plan and can
see what it's doing and I can rewrite it in a proper way but question
remains, - why is that? Can comeone explain that behavior?
Thank a lot in advance
AlexThat's the way functions work. They are executed for each row of the final
result.
What are you trying to do? If you need uniqueidentifier values for each
integer value, insert them into a temporary table (i.e. table variable)
before issuing the update.
ML
http://milambda.blogspot.com/|||my colleague came accros this piece of code. She reworked it with use of
temp table, and I could find a workaround with derived table. But why the
function is executed in the final set? The code imlies newid() should be
called inside derived table y, and then derived table y joins the table var?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BBDCFC04-63E4-4408-B153-26A1C5A9EB2B@.microsoft.com...
> That's the way functions work. They are executed for each row of the final
> result.
> What are you trying to do? If you need uniqueidentifier values for each
> integer value, insert them into a temporary table (i.e. table variable)
> before issuing the update.
>
> ML
> --
> http://milambda.blogspot.com/|||We'll know this for sure as soon as we find another system function that
produces as random results as NEWID(). :)
Have you tried using a user-defined function that returns a random result?
ML
http://milambda.blogspot.com/|||Interesting, I used RAND() and float instead of NEWID() and
uniqueidentifier, and in this case RAND() was applied to the final result
set too, but the difference seems to be that RAND() was called only once
since ALL records have the same float value.
No, I didn't used UDF yet, maybe later today when I have time.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
> We'll know this for sure as soon as we find another system function that
> produces as random results as NEWID(). :)
> Have you tried using a user-defined function that returns a random result?
>
> ML
> --
> http://milambda.blogspot.com/|||NEWID is special. It is called for every row in a query. All other function
(the I know of) are
called only once in a query. Hence the difference between RAND and NEWID.
USE northwind
SELECT
NEWID() AS myNEWID
,RAND() AS myRand
,CURRENT_TIMESTAMP AS myTS
FROM "Order Details"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <alex_remove_this_mak@.telus.net> wrote in message news:dB6Ef.153384$6K2.43614@.edtnps
90...
> Interesting, I used RAND() and float instead of NEWID() and uniqueidentifi
er, and in this case
> RAND() was applied to the final result set too, but the difference seems t
o be that RAND() was
> called only once since ALL records have the same float value.
> No, I didn't used UDF yet, maybe later today when I have time.
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
>|||This is the expected behaviour of RAND.
ML
http://milambda.blogspot.com/|||I was wondering whether getdate() is called for each row or for the set...
Would it show on a big enough set?
ML
http://milambda.blogspot.com/|||thanks guys a lot
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D44C180C-8F26-461D-A552-9365B4DA27C9@.microsoft.com...
> This is the expected behaviour of RAND.
>
> ML
> --
> http://milambda.blogspot.com/|||Getdate() is normally only called once.
However, Itzik Ben-Gan came up with a really clever workaround for both
rand() and getdate().
Normally, you can't put getdate() or rand() in a User Defined Function, but
you can put them in a view, and then have your function select from the
view. You can then put your function in the select list, to have the
getdate() or rand() regenerated for each row.
Note that it might not look like getdate() is called for every single row,
because of the precision of the datatype. The function might be called
repeatedly more quickly than the getdate() value changes. But if you have
enough rows, you'll see that they aren't ALL the same, even though there
could be duplication.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ML" <ML@.discussions.microsoft.com> wrote in message
news:30C35A84-706B-48F3-8C41-69ECB598DA13@.microsoft.com...
>I was wondering whether getdate() is called for each row or for the set...
> Would it show on a big enough set?
>
> ML
> --
> http://milambda.blogspot.com/
>

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

Know list database and table in SQL Server

I use this macro to list in A column of sheet the name of sql server on
my lan.
Is possible to "scan server by server" and insert into column B the
name of database and related table?
Tks.
Sub test_sql()
Dim TEST As String
Dim RIGA As String

Set sqlApp = CreateObject("SQLDMO.Application")

Set serverList = sqlApp.ListAvailableSQLServers

numServers = serverList.Count
RIGA = 2

For I = 1 To numServers

TEST = serverList(I)

Range("A" + RIGA) = TEST
RIGA = RIGA + 1
Next

Set sqlApp = Nothing

End Subsorry for UP...|||Give this a try - the code assumes that you can connect to each server via Windows Authentication and that you have appropriate permissions to list the databases/tables. I've added Error handling for the connection to each server - if there is a problem listing the databases/tables, the code will stop with an error. Please note that I haven't performed any extensive testing of the code.

Sub SQLAudit()
'Clear sheet
Cells.ClearContents
Cells.ClearFormats

Const DisplaySystemDatabases = False ' Change this if you want to view system databases
Const DisplaySystemTables = False ' change this if you want to view system tables
Dim objSQLApp, objSQLServer, objSQLDatabase, objSQLTable
Dim strSQLServer
Dim i As Integer
i = 1

Set objSQLApp = CreateObject("SQLDMO.Application")

' Enumerate list of available SQL Servers
For Each strSQLServer In objSQLApp.ListAvailableSQLServers
' Server Header (Remove if header not required)
Range("A" & i).Value = strSQLServer
Range("A" & i & ":C" & i).Merge
Range("A" & i & ":C" & i).Font.Bold = True
Range("A" & i & ":C" & i).Font.Size = 14
Range("A" & i & ":C" & i).HorizontalAlignment = xlCenter
i = i + 1
' ***********************
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True ' Connect using Windows Authentication
On Error Resume Next
' Connect to the server (will fail if user does not have logon for server)
objSQLServer.Connect strSQLServer
If Err.Number <> 0 Then
' Display a message if unable to connect to the server
MsgBox ("Failed to connect to: " & strSQLServer & vbCrLf & Err.Description)
Err.Clear
Else
On Error GoTo 0 ' Turn off resume next error handling (throw exception if error occurs)
' Enumerate databases on server
For Each objSQLDatabase In objSQLServer.Databases
If (Not objSQLDatabase.SystemObject) Or DisplaySystemDatabases Then
' Database Header (Remove if header not requied)
Range("B" & i).Value = objSQLDatabase.Name
Range("B" & i & ":C" & i).Merge
Range("B" & i & ":C" & i).Font.Bold = True
Range("B" & i & ":C" & i).HorizontalAlignment = xlCenter
i = i + 1
' ***********************
'Enumerate tables in database
For Each objSQLTable In objSQLDatabase.Tables
If (Not objSQLTable.SystemObject) Or DisplaySystemTables Then
Range("A" & i).Value = objSQLServer.Name
Range("B" & i).Value = objSQLDatabase.Name
Range("C" & i).Value = objSQLTable.Name
i = i + 1
End If
Next
End If
Next
End If
Next

Set objSQLApp = Nothing
Set objSQLServer = Nothing
Set objSQLDatabase = Nothing
Set objSQLTable = Nothing
End Sub

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!