Stored Proc not updating multiple rows

[EmbersFire]

I'm using a stored proceedure which should update a number of rows in a
table depending on a key value supplied (in this case 'JobID'). But
what's happening is when I call the proc from within the program, only
one row gets updated.

So

When I call the proc from Query Analyser, all rows get updated.
When I call the proc from within the program, only one row gets updated

Any ideas as to why this is happening??

JobID Description Price Status
----------------------------------------------
73412 Documents:Item 3 .00 0
73412 Documents:Item 5 .00 0
73412 Documents:Item 2 .00 0
73412 Documents:Item 4 .00 0
73412 Documents:Item 1 .00 0

^^^^Only one record gets updated, so the table ends up being...

JobID Description Price Status
----------------------------------------------
73412 Documents:Item 3 .00 4
73412 Documents:Item 5 .00 0
73412 Documents:Item 2 .00 0
73412 Documents:Item 4 .00 0
73412 Documents:Item 1 .00 0


Public Sub UpdateAllItems( ) As Boolean
Dim objCnn As ADODB.Connectio n
Dim objCmd As ADODB.Command

Set objCnn = New ADODB.Connectio n
With objCnn
.ConnectionStri ng = cnConn
.CursorLocation = adUseClient
.Open
End With
Set objCmd = New ADODB.Command
Set objCmd.ActiveCo nnection = objCnn
With objCmd
.CommandText = "sp_UpdateJobIt em"
.CommandType = adCmdStoredProc
.Parameters.App end .CreateParamete r("@Status", adInteger,
adParamInput, 4, Me.Status)
.Parameters.App end .CreateParamete r("@JobID", adInteger,
adParamInput, 4, Me.iJobID)
.Execute
End With
Set objCnn = Nothing
Set objCmd = Nothing
End Sub


-----------------------------------------------------------------




SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.sp_UpdateJo bItem
@JobID As int
, @Status As int
AS

--
=============== =============== =============== =============== =============== =============== ===
SET XACT_ABORT OFF -- Allow procedure to continue after
error
DECLARE @error integer -- Local variable to capture the
error OnHoldAction.
--
=============== =============== =============== =============== =============== =============== ===

BEGIN TRANSACTION

UPDATE tbl_JobItems
SET Status = @status
WHERE JobID = @JobID

--
=============== =============== =============== =============== =============== =============== ===
-- Check for errors
--
=============== =============== =============== =============== =============== =============== ===
SELECT @error = @ERROR

If @error > 0
BEGIN
ROLLBACK TRANSACTION
END
Else
BEGIN
COMMIT TRANSACTION
END



GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

[David Portas]

Re: Stored Proc not updating multiple rows

> UPDATE tbl_JobItems[color=blue]
> SET Status = @status
> WHERE JobID = @JobID[/color]

As the WHERE clause is based only on JobID this UPDATE statement will
update every row with that JobID. How do you intend to determine which
row to update? What is the key of this table?

--
David Portas
SQL Server MVP
--

[EmbersFire]

Re: Stored Proc not updating multiple rows

I'm using the jobID field to determine which records to update. That's
the key. So for a given jobid of 73412 like in the example, all rows
with that id should be updated.

[David Portas]

Re: Stored Proc not updating multiple rows

Have you used SET ROWCOUNT 1 somewhere? To be sure, try putting SET
ROWCOUNT 0 in the proc just before the UPDATE.

--
David Portas
SQL Server MVP
--

[David Portas]

Re: Stored Proc not updating multiple rows

Also, you should NOT be using SP_ as a prefix for user procs. SP_ is
reserved for system procs. That's probably not the cause of your
present problem but there's a chance it *could* be: if you've
inadvertently created a system proc of the same name in master then you
might be executing some code other than what you see here. Believe it
or not that can happen.

--
David Portas
SQL Server MVP
--

[EmbersFire]

Re: Stored Proc not updating multiple rows

I set ROWCOUNT to 1. But no luck I'm afraid.

[David Portas]

Re: Stored Proc not updating multiple rows

Do you mean you tried SET ROWCOUNT 0? You need to have ROWCOUNT set to
0 to be sure all rows can be updated.

--
David Portas
SQL Server MVP
--

[EmbersFire]

Re: Stored Proc not updating multiple rows

Sorry, I mean SET ROWCOUNT 0 as you had suggested

[David Portas]

Re: Stored Proc not updating multiple rows

OK. If it's not something that you can reproduce in QA then set up a
trace in SQL Profiler to capture the actual statements executed by your
code. That should give you enough to reproduce and identify the
problem.

--
David Portas
SQL Server MVP
--

[EmbersFire]

Re: Stored Proc not updating multiple rows

I've used the profiler. It's feeding in the correct parameters and no
errors are occurring. Interestingly enough, I tried a version of the
code which executes an sql string and the same thing is happening with
that as well.

strSQL = "UPDATE tbl_jobitems SET status = " & Me.Status & " WHERE
jobid = " & Me.iJobID

objCnn.Execute( strSQL)

[Erland Sommarskog]

Re: Stored Proc not updating multiple rows

EmbersFire (embers_fire@fa stmail.fm) writes:[color=blue]
> I'm using a stored proceedure which should update a number of rows in a
> table depending on a key value supplied (in this case 'JobID'). But
> what's happening is when I call the proc from within the program, only
> one row gets updated.
>
> So
>
> When I call the proc from Query Analyser, all rows get updated.
> When I call the proc from within the program, only one row gets updated[/color]

That's funny. A SET ROWCOUNT in effect sounds like the most like, but
it appears that you have already ruled this out.

Could there be trigger on the table? (Although that would be a strange
trigger.)

One thing, though:
[color=blue]
> .Execute[/color]

Make this

.Execute ,,adExecuteNoRe cords

this can save you from a few funny surprises in other situations. Use
this option whenever you run a command that does not generate any result
set.

And have you double-checked that you run the VB-app against the same
database as you run QA? And have you made sure that there is only one
instance of the table and the stored procedure?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[EmbersFire]

Re: Stored Proc not updating multiple rows

There are no triggers operating on the table.
I've checked that it's running against the correct database
I was told that it could be a problem with the index for the table,
although when I checked, there were non specified for it.

[Erland Sommarskog]

Re: Stored Proc not updating multiple rows

EmbersFire (embers_fire@fa stmail.fm) writes:[color=blue]
> There are no triggers operating on the table.
> I've checked that it's running against the correct database
> I was told that it could be a problem with the index for the table,
> although when I checked, there were non specified for it.[/color]

There should at least be a primary key, one would hope. You could try
DBCC CHECKTABLE to see if it uncovers any corruption, but I would not
really expect it.

Without a reproducible scenario, and no access to your system, it's very
difficult to diagnose.

Another things to try is to create a new table and a new stored procedure
to go with it, and load the table with same data, and see if this behaves
in the same way.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[EmbersFire]

Re: Stored Proc not updating multiple rows

I ran the DBCC CHECKTABLE command and if brought up no errors.
The JobID field is a foreign key that points to the JobID field in
another table called tbl_Jobs.
I may try creating a new table as you suggested and see what happens
with that.