Updating sql05 with Stored Procedure

[Chuck P]

I have a typical windows forms application that uses a DataGridView, a
dataset, a bindingsource, and a TableAdapter.

If I modify one row in the datagridview everything works fine.
If I modify more than one row only the first modified row gets
updated.

My Code to write to the db is

{
if (this.Validate())
{
this.lutCategoriesBindingSource.EndEdit();
if (this.dsLookUp.HasChanges())
this.lutCategoriesTableAdapter.Update(this.dsLookUp.lutCategories);
}
}

The tableAdapter uses Stored Procedures for the commands.


I have the same code going against an Access database without the
stored procedures and it works fine. When I switched to SQL05 and
stored procedures I got this behavior.

 

[Kevin Yu [MSFT]]

Hi Chuck,

The code you have provided seems to be fine. Since the same code works
alright on the Access DB, I think there might be something wrong with the
stored procedures that you're using to update the database. Please check
the following:

1. Have you provided stored procedures for select/insert/update/delete?
2. If the other rows are not uupdated to the database, what kind of
operation have you done with them? Are these rows modified or deleted or
newly added?
3. Could you please post all these stored procedure here?

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Chuck P]

Hi Chuck,

The code you have provided seems to be fine. Since the same code works
alright on the Access DB, I think there might be something wrong with the
stored procedures that you're using to update the database. Please check
the following:

1. Have you provided stored procedures for select/insert/update/delete? yes

2. If the other rows are not uupdated to the database, what kind of
operation have you done with them? Are these rows modified or deleted or
newly added?

I just updated 3 rows (added a character to a varchar)

3. Could you please post all these stored procedure here?

Here they are. They were all created by the VS dataset wizard with the
exception of the select procedure.

I also tried commenting out the final select statement in the Update
stored procedure, but the results didn't change.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SR].[lutCategories_Del]
(
@Original_CategoryID int,
@Original_RowVersion timestamp
)
AS
SET NOCOUNT OFF;
DELETE FROM [SR].[lutCategories] WHERE (([CategoryID] =
@Original_CategoryID) AND ([RowVersion] = @Original_RowVersion))


====================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SR].[lutCategories_Ins]
(
@Description varchar(50),
@IsActive bit
)
AS
SET NOCOUNT OFF;
INSERT INTO [SR].[lutCategories] ([Description], [IsActive]) VALUES
(@Description, @IsActive);

SELECT CategoryID, Description, IsActive, RowVersion FROM
SR.lutCategories WHERE (CategoryID = SCOPE_IDENTITY())

==================================================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SR].[lutCategories_Sel]
@GetActiveOnly bit

AS
SET NOCOUNT ON;
SELECT CategoryID, Description, IsActive, RowVersion
FROM SR.lutCategories
WHERE (0=@GetActiveOnly or Sr.lutCategories.IsActive=@GetActiveOnly)
ORDER BY Description

======================================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SR].[lutCategories_Upd]
(
@Description varchar(50),
@IsActive bit,
@Original_CategoryID int,
@Original_RowVersion timestamp,
@CategoryID int
)
AS
SET NOCOUNT OFF;
UPDATE [SR].[lutCategories] SET [Description] = @Description,
[IsActive] = @IsActive WHERE (([CategoryID] = @Original_CategoryID)
AND ([RowVersion] = @Original_RowVersion));

SELECT CategoryID, Description, IsActive, RowVersion FROM
SR.lutCategories WHERE (CategoryID = @CategoryID)

 

[Kevin Yu [MSFT]]

Hi Chuck,

The stored procedures seem to be fine to me. It is really a little bit
weird.

Would you please check more things?

1. Add a breakpoint in your code before calling TableAdapter.Update method.
In the watch window, check RowState property of each modified row in the
table. Are they all displayed as Modified?
2. If the above is true(all are modified), it will be helpful if we start
SQL Profiler for a trace. From the trace log, we can see how many rows have
been updated actually.

If the above still doesn't help, I might need more of your code on this
form for further research. Thank you!

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Chuck P]

Hi Chuck,

The stored procedures seem to be fine to me. It is really a little bit
weird.

Would you please check more things?

1. Add a breakpoint in your code before calling TableAdapter.Update method.
In the watch window, check RowState property of each modified row in the
table. Are they all displayed as Modified?

yes, both are modifed in the dataset

2. If the above is true(all are modified), it will be helpful if we start
SQL Profiler for a trace. From the trace log, we can see how many rows have
been updated actually.

I did a trace and it showed both rows were updated (attached).
The trace had the changes highlighted (added an 's' to the description
column in two rows).
I then looked at the table in SQL and the 2nd row was unchanged(SQL
Server 9.0.2047,sp1, standard edition)!

 

[Chuck P]

Hi Chuck,

The stored procedures seem to be fine to me. It is really a little bit
weird.

Would you please check more things?

1. Add a breakpoint in your code before calling TableAdapter.Update method.
In the watch window, check RowState property of each modified row in the
table. Are they all displayed as Modified?

yes, both are modifed in the dataset

2. If the above is true(all are modified), it will be helpful if we start
SQL Profiler for a trace. From the trace log, we can see how many rows have
been updated actually.

I did a trace and it showed both rows were updated (attached).
The trace had the changes highlighted (added an 's' to the description
column in two rows).
I then looked at the table in SQL and the 2nd row was unchanged(SQL
Server 9.0.2047,sp1, standard edition)!

 

[Kevin Yu [MSFT]]

Hi Chuck,

Thanks for your trace log. From the trace log, we can see both two records
are updated successfully. In this case, we can narrow down this issue to
database side, since all data need to be updated has been passed to the
database.

To find the root cause of this issue, can you do me a favour to perform
some more steps?

1. Copy and paste the 2 executions of SR.lutCategories_Upd completely to
Query Analyzer, like

exec SR.lutCategories_Upd
@Description='AbandonmentS',@IsActive=1,@Original_CategoryID=1001,@Original_
RowVersion=0x000000000665E8D6,@CategoryID=1001
exec SR.lutCategories_Upd
@Description='AttendanceS',@IsActive=1,@Original_CategoryID=1002,@Original_R
owVersion=0x000000000665E8D7,@CategoryID=1002

2. Execute these 2 lines with a single run.

Are the two records all changed?

If it stilly only changes the first record, it means that these is
something wrong with your database. Are there any others connecting to and
operating on this database? Are transactions involved?
If the two lines are all changed this time, I think the stored procedure
might have some problem, please use sp_recompile to recompile the stored
procedures.

Please try this and let me know the result, so that I can make further
research, if that still doesn't work.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Chuck]

Hi Chuck,

Thanks for your trace log. From the trace log, we can see both two records
are updated successfully. In this case, we can narrow down this issue to
database side, since all data need to be updated has been passed to the
database.

To find the root cause of this issue, can you do me a favour to perform
some more steps?

1. Copy and paste the 2 executions of SR.lutCategories_Upd completely to
Query Analyzer, like

exec SR.lutCategories_Upd
@Description='AbandonmentS',@IsActive=1,@Original_CategoryID=1001,@Original_
RowVersion=0x000000000665E8D6,@CategoryID=1001
exec SR.lutCategories_Upd
@Description='AttendanceS',@IsActive=1,@Original_CategoryID=1002,@Original_R
owVersion=0x000000000665E8D7,@CategoryID=1002

2. Execute these 2 lines with a single run.

Are the two records all changed?

No only the first one

If it stilly only changes the first record, it means that these is
something wrong with your database. Are there any others connecting to and
operating on this database? Are transactions involved?
If the two lines are all changed this time, I think the stored procedure
might have some problem, please use sp_recompile to recompile the stored
procedures.

No one else is connected to the database.
No transactions are being used.
I ran sp_recomplie, the result was :
Object '[SR].[lutCategories_Upd]' was successfully marked for
recompilation.

I reexecuted the SP and got the sames results.

This table has a trigger:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [SR].[lutCategories_AIU] ON [SR].[lutCategories]
FOR INSERT, UPDATE
AS
Update lutCategories
set
updatewho = SYSTEM_USER,
updatewhen = CURRENT_TIMESTAMP


I disabled the trigger but got the same results when rerunning the sp.

 

[Kevin Yu [MSFT]]

Hi Chuck,

For another trial, I have expand the stored procedure as follows:

UPDATE [SR].[lutCategories] SET [Description] = 'AbandonmentS',
[IsActive] = 1 WHERE (([CategoryID] = 1001)
AND ([RowVersion] = 0x000000000665E8D6));

UPDATE [SR].[lutCategories] SET [Description] = 'AttendanceS',
[IsActive] = 1 WHERE (([CategoryID] = 1002)
AND ([RowVersion] = 0x000000000665E8D7));

SELECT CategoryID, Description, IsActive, RowVersion FROM
SR.lutCategories WHERE (CategoryID = 1001)

SELECT CategoryID, Description, IsActive, RowVersion FROM
SR.lutCategories WHERE (CategoryID = 1002)

Please copy and paste the above 4 statements into Query Analyzer, and run
them together. Please check the result to see if the two lines are all
modified. If this time it still doesn't work, for better communication,
besides posting here, could you send me a email stating the problem? We'll
try other ways for debugging it. Remove 'online' from the no-spam alias is
my real email.

Since we can be sure that the problem resides in the SQL Server database,
if you can make a simple database with little data, which can reproduce the
problem, please also attach this in the email. It will be of great help if
I can repro it on my machine. Thank you for your cooperation!

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Chuck P]

Kevin,
Thanks, for the help. A poorly written trigger was firing that was
changing the timestamp for the second row. Fixed the trigger and all
works now.

 

[Kevin Yu [MSFT]]

Hi Chuck,

It was nice to know that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)