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.Connection
Dim objCmd As ADODB.Command

Set objCnn = New ADODB.Connection
With objCnn
.ConnectionString = cnConn
.CursorLocation = adUseClient
.Open
End With
Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objCnn
With objCmd
.CommandText = "sp_UpdateJobItem"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Status", adInteger,
adParamInput, 4, Me.Status)
.Parameters.Append .CreateParameter("@JobID", adInteger,
adParamInput, 4, Me.iJobID)
.Execute
End With
Set objCnn = Nothing
Set objCmd = Nothing
End Sub
-----------------------------------------------------------------


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.sp_UpdateJobItem
@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_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[David Portas]

> UPDATE tbl_JobItems

SET Status = @status
WHERE JobID = @JobID

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]

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]

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]

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]

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

[David Portas]

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]

Sorry, I mean SET ROWCOUNT 0 as you had suggested

[David Portas]

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]

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]

EmbersFire (em*********@fastmail.fm) writes:

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
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:
.Execute

Make this

.Execute ,,adExecuteNoRecords

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[EmbersFire]

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]

EmbersFire (em*********@fastmail.fm) writes:

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.

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[EmbersFire]

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.