Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need som
e
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 thes
e
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...
> some
> only
> result,
>
>|||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 thes
e
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:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the ro
w
> 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:
>|||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[vbcol=seagreen]
> 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:
>
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]|||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 m
e
> 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 tab
le
> 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...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> 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 th
e
> 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:
>|||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.
>|||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 s
ome
> help.
> I am writting the procedure to check if multiple records exists and the on
ly
> way I can figure it out is to use @.@.RowCount, but can't get the right resu
lt,
> 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?
>
>