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

 

[hmckillop]

I think what you need is to check for proper errors, as the line

SELECT @error = @ERROR

should actually be

SELECT @error = @@ERROR

Other suggestion is to check the parameters are actually being set properly, best way is via SQL Profiler.
If you run this it should show exact call to DB.


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

 

[vongrunt]

Hm... SET ROWCOUNT somewhere? Weird trigger?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[JamesLean]

I'm not sure if it will have any bearing but try removing the CursorLocation line when you open the connection?

Also, your explicit transaction seems unnecessary (unless there is more code in the SP which you have not shown). Try just having the single UPDATE statement in your SP.

--James

 

[stephen2005]

Thanks for replying guys

All the code in the stored proc is shown

I'm not using SET ROWCOUNT

I removed the CursorLocation bit and took out the transaction handling code

It still only updates one row.

 

[vongrunt]

Somehow this reminds me on Hello world example, Master Programmer's version (link) - no any criticism intended, just funny observation. .

There must be something else... better check with profiler as hmckillop suggested.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[JamesLean]

Are you sure that if you go to QA and do the following:

SELECT *
FROM tbl_jobitems
WHERE jobid = 73412

UPDATE tbl_jobitems
SET status = 4
WHERE jobid = 73412

SELECT *
FROM tbl_jobitems
WHERE jobid = 73412

then you get a status of 0 in the first select and a status of 4 for all the rows in the second select?

--James

 

[stephen2005]

Yes, that's what I get and when I run the stored proc through Query Analyser, it updates all rows with a given job id as well. It's just not doing it through vb code for some reason.

 

[JamesLean]

After you've run the SP from your app, have you checked the rows directly from the db thru QA or is your app displaying the updated rows?

If the latter, try checking them directly - it may be that the app is just not refreshing it's view of the rows properly.

--James

 

[stephen2005]

Yes, I've checked them directly through QA.

 

[stephen2005]

I tried adding SET ROWCOUNT 0 as well. but that didn't make a difference either.

It's a strange problem. Thanks for your help anyways guys.

 

[vongrunt]

Again: have you checked for triggers? A bit stretched I know, but still worth to check IMO.

Or maybe client app (VB, right?) also executes something else after this stored procedure? That's why we insisted on using profiler.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[hmckillop]

Have you looked at Profiler and see what is actually being called from the application.
The other thing to do, is the only differences between ADO connecting from your app and you executing in QA are a few settings which you can change in QA options.

The only other time I have seen something similiar to this was when I was connecting to Sybase database through a legacy driver. There was a setting on the driver ini file which allowed a definition of the maximum amount of rows in resultsets, but since you are using ADO this shouldnt be a problem.



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

 

[stephen2005]

I ran profiler and I can see the store proc being executed: 'exec sp_UpdateJobItem 4, 73417'. 4 -> the status and 73417 is the job id. There doesn't appear to be anything unusual in there. I don't think the table is being affected by triggers either

 

[vongrunt]

Isn't order of arguments different (@JobID, @Status)?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[JamesLean]

Hang on, just noticed that in your SP code the parameters are declared in the order JobID, Status but in your VB code you are adding them in the order Status, JobID (the names you give them in VB makes no difference, they are assigned in order) - this is backed up by the EXEC call from the profiler trace.

What that trace call would do is update all items for JobID 4 to Status of 73417.

Try reversing the order in which you add the parameters in your VB code?

--James

 

[stephen2005]

I reversed the order of the parameters, but that didn't make a difference either.

>>What that trace call would do is update all items for JobID 4 to Status of 73417.

It was always doing the update correctly, but only in the case of one of the records.

 

[vongrunt]

As far as I know, ADODB.Command object builds procedure call string according to physical order of parameters in .Parameters collection. So if sproc code and VB code were the same as in original post, I don't have any ideas how it worked before at all.

To avoid further confusions, use Set objCmd.namedParameters = true.


------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[JamesLean]

I agree with vongrunt - as you had it, it shouldn't have been updating anything!

Another long shot - can you check your connection string and make sure you're connecting to the right server/database? Maybe you're updating a table on another server and there is a trigger on that table which is updating the single row in the "correct" table?

(and if that sorts it I'll eat my hat... ;-))

--James

 

[vongrunt]

More precisely, it could never work from VB but should still work from QA - depending on parameters supplied.

Minor detail: Set before objCmd.namedParameters is wrong (this is boolean, not an object). Too many languages today .

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba