Stored Proc not updating multiple rows

[stephen2005]

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

 

[stephen2005]

No, I'm connected to the right database and it is actually doing an update correctly. It's just that it still doesn't do it for all the rows for a given jobID.

I've added in objCmd.namedParameters = true as well.

 

[JamesLean]

Out of interest, what happens if you don't use the SP and just execute a SQL string?

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

objCnn.Execute(strSQL)

--James

 

[stephen2005]

The same thing happened when I executed an sql string. Strange, it must not be a stored proceedure problem so.

 

[hmckillop]

could it be a caching problem

try dbcc dropcleanbuffers or freeproccache


"I'm living so far beyond my income that we may almost be said to be living apart

 

[stephen2005]

I tried that as well. Same thing again unfortunately.

 

[manjarikosaraju]

Is it possible that the table tbl_JobItems is present under two different users in the same database? (i.e., one with your login.Table and ApplicationLogin.Table)??