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

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
--

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.

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
--

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
--

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

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
--

Sorry, I mean SET ROWCOUNT 0 as you had suggested

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
--

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)