Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:
> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:
> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > I am writing a vb.net application that calls a stored procedure and need
> some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the
> only
> > way I can figure it out is to use @.@.RowCount, but can't get the right
> result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
> > I am writing a vb.net application that calls a stored procedure and need some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the only
> > way I can figure it out is to use @.@.RowCount, but can't get the right result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
> > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
of
> > the last operation... I have a feeling you really want:
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > update ...
> >
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
message
> > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > I am writing a vb.net application that calls a stored procedure and
need
> > some
> > > help.
> > >
> > > I am writting the procedure to check if multiple records exists and
the
> > only
> > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > result,
> > > please help.
> > >
> > > What I have now is
> > >
> > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > @.@.Rowcount = 1)
> > > update...
> > >
> > >
> > > I tried using the following but got an error in my vb application
becuase
> > > the Procedure was returning rows
> > >
> > > select c_driver from cartons where orderid = @.orderid
> > > if @.@.Rowcount = 1
> > > update...
> > >
> > > Is there any way to use the above query without returning rows to VB?
> > >
> > > Is there a way to write an exists to query @.@.Rowcount?
> > >
> > >
> > >
> >
> >
> >|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:
> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > I tried the count() but I don't get what I need
> >
> > what I need to do is determin is if a single driver is assigned to
> multiple
> > cartons within an order. for Instance say driver #1 was assigned to Carton
> > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> these
> > cartons are in Order #1000
> >
> > Order# Carton# Driver#
> > 1000 100 1
> > 1000 101 24
> > 1000 102 24
> >
> > What I need to get to is this without the select (becuase doing it this
> way
> > returns an error in my vb.net application because the select wants to
> return
> > rows)
> >
> > select c_driver from Cartons where orderid = @.orderid
> > if @.@.RowCount = 1
> > --Only one driver exists for this order
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> >
> > if @.@.RowCount > 1
> > -- Multiple Driver exists for this Order
> >
> >
> > "Adam Machanic" wrote:
> >
> > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> of
> > > the last operation... I have a feeling you really want:
> > >
> > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > update ...
> > >
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> message
> > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > I am writing a vb.net application that calls a stored procedure and
> need
> > > some
> > > > help.
> > > >
> > > > I am writting the procedure to check if multiple records exists and
> the
> > > only
> > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > result,
> > > > please help.
> > > >
> > > > What I have now is
> > > >
> > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > @.@.Rowcount = 1)
> > > > update...
> > > >
> > > >
> > > > I tried using the following but got an error in my vb application
> becuase
> > > > the Procedure was returning rows
> > > >
> > > > select c_driver from cartons where orderid = @.orderid
> > > > if @.@.Rowcount = 1
> > > > update...
> > > >
> > > > Is there any way to use the above query without returning rows to VB?
> > > >
> > > > Is there a way to write an exists to query @.@.Rowcount?
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
> > Again, the COUNT(*) should do exactly what you need here... can you tell me
> > why this won't work for you:
> >
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > BEGIN
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> > END
> > ELSE
> > BEGIN
> > -- do something else...
> > END
> >
> >
> > ... Given that, however, I should ask why you're updating your orders table
> > with the driver from the cartons table? Why denormalize your data like
> > that?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> > news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > > I tried the count() but I don't get what I need
> > >
> > > what I need to do is determin is if a single driver is assigned to
> > multiple
> > > cartons within an order. for Instance say driver #1 was assigned to Carton
> > > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> > these
> > > cartons are in Order #1000
> > >
> > > Order# Carton# Driver#
> > > 1000 100 1
> > > 1000 101 24
> > > 1000 102 24
> > >
> > > What I need to get to is this without the select (becuase doing it this
> > way
> > > returns an error in my vb.net application because the select wants to
> > return
> > > rows)
> > >
> > > select c_driver from Cartons where orderid = @.orderid
> > > if @.@.RowCount = 1
> > > --Only one driver exists for this order
> > > Update orders set oDriver = (select distinct c_driver from cartons
> > > where orderid = @.orderid)
> > >
> > > if @.@.RowCount > 1
> > > -- Multiple Driver exists for this Order
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> > of
> > > > the last operation... I have a feeling you really want:
> > > >
> > > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > > update ...
> > > >
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> > message
> > > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > > I am writing a vb.net application that calls a stored procedure and
> > need
> > > > some
> > > > > help.
> > > > >
> > > > > I am writting the procedure to check if multiple records exists and
> > the
> > > > only
> > > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > > result,
> > > > > please help.
> > > > >
> > > > > What I have now is
> > > > >
> > > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > > @.@.Rowcount = 1)
> > > > > update...
> > > > >
> > > > >
> > > > > I tried using the following but got an error in my vb application
> > becuase
> > > > > the Procedure was returning rows
> > > > >
> > > > > select c_driver from cartons where orderid = @.orderid
> > > > > if @.@.Rowcount = 1
> > > > > update...
> > > > >
> > > > > Is there any way to use the above query without returning rows to VB?
> > > > >
> > > > > Is there a way to write an exists to query @.@.Rowcount?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>