Table Join Getting Old Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Join Getting Old Values

Author

Topic

Wylbur
Starting Member

29 Posts

Posted - 2009-02-15 : 21:07:26

Hi all;

I'm using stored procedures with ASP.NET 3.5 (ADO.NET) to update
and access a pair of tables. Upon update, the records are updated
as expected. On access - using a left join operation - the result
set has the old values for the record that is on the right side
of the join operation.

I then open SQL Server Management Studio to examine the tables.

When I step through my code with the Visual Studio debugger
- stopping at the point just after the stored procedure with the
join operation has executed - I can examine the records for the
table on the right side of the join to verify that they were indeed
updated.

When I execute the stored procedure that performs the join operation
(with Management Studio), the result set contains the old values
for the records before the update.

Can anyone give this poor fool a clue as to what I need to do in
order to get the join operation to use the new values?

THANKS!!!

Wylbur
========================

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 21:17:53

without some sample data we cant understand your problem, can you please post some sample data and expected output and explain problem you're facing?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-15 : 21:46:24

How does your update statement look like?

Wylbur
Starting Member

29 Posts

Posted - 2009-02-15 : 22:24:04

quote:
Originally posted by visakh16

without some sample data we cant understand your problem, can you please post some sample data and expected output and explain problem you're facing?

Hi visakh16;

I don't see how having any data will do you any good.

As I mentioned before: My table is being updated as expected,
but the join operation is yielding a result set with the old values
that were in the table before the update.

THANKS for the response!!!

Wylbur
========================

Wylbur
Starting Member

29 Posts

Posted - 2009-02-15 : 22:27:03

quote:
Originally posted by sodeep

How does your update statement look like?

Hi sodeep;

Perhaps this is where I'm going wrong.

Am I missing anything here?:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

ALTER PROCEDURE [dbo].[update_sales_details]
( @description_ref INTEGER,
@owner_code INTEGER,
@invoice_code INTEGER,
@quantity_sold INTEGER )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT ON;

-- SET NOCOUNT OFF added to prevent the rows affected count
-- returned to be zero, which the DataAdapter interprets
-- as a concurrency conflict. In this event,
-- a DBConcurrencyException will be thrown.
SET NOCOUNT OFF;

UPDATE sales_details
SET
DB_quantity_sold = @quantity_sold
WHERE
DB_description_ref = @description_ref
AND DB_owner_code = @owner_code
AND DB_invoice_code = @invoice_code;

END
GO

SET ANSI_PADDING OFF
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

THANKS for the response!!!

Wylbur
========================

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 23:19:42

quote:
Originally posted by Wylbur

quote:
Originally posted by visakh16

without some sample data we cant understand your problem, can you please post some sample data and expected output and explain problem you're facing?

Hi visakh16;

I don't see how having any data will do you any good.

As I mentioned before: My table is being updated as expected,
but the join operation is yielding a result set with the old values
that were in the table before the update.

THANKS for the response!!!

Wylbur
========================

are you using join in same batch? also are you using NOLOCK hint?

Wylbur
Starting Member

29 Posts

Posted - 2009-02-15 : 23:31:56

quote:
Originally posted by visakh16

quote:
Originally posted by Wylbur

quote:
Originally posted by visakh16

without some sample data we cant understand your problem, can you please post some sample data and expected output and explain problem you're facing?

Hi visakh16;

I don't see how having any data will do you any good.

As I mentioned before: My table is being updated as expected,
but the join operation is yielding a result set with the old values
that were in the table before the update.

THANKS for the response!!!

Wylbur
========================

are you using join in same batch? also are you using NOLOCK hint?

Hi visakh16;

quote:
Originally posted by visakh16
are you using join in same batch?

No - the join operation is in a stored procedure that is executed
after the update.

quote:
Originally posted by visakh16
also are you using NOLOCK hint?

I'm not sure what you mean.

Perhaps this?: SET NOLOCK ON

THANKS for the response!!!

Wylbur
========================

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 23:37:17

yup. have you set it to on?

Wylbur
Starting Member

29 Posts

Posted - 2009-02-16 : 00:35:14

quote:
Originally posted by visakh16

yup. have you set it to on?

Hi visakh16;

When I tried to execute the script to alter the procedure,
I got the following error message:

"NOLOCK" is not a recognized SET option

I tried again using "LOCK" instead - and similarly got the
following error message:

"LOCK" is not a recognized SET option

It doesn't look like I can do that.

THANKS for the response!!!

Wylbur
========================

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 00:42:46

nope..i was asking whether you were using it. using it causes uncommited values to be returned.

Wylbur
Starting Member

29 Posts

Posted - 2009-02-16 : 01:12:39

quote:
Originally posted by visakh16

nope..i was asking whether you were using it. using it causes uncommited values to be returned.

Hi visakh16;

I'm not sure how to use it.

Can you give me a suggestion?

THANKS for the response!!!

Wylbur
========================

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 01:53:10

you dont have to use it. i was asking whether you were using it. are you sure by the time you do join update is completed?

Wylbur
Starting Member

29 Posts

Posted - 2009-02-16 : 03:42:23

quote:
Originally posted by visakh16

you dont have to use it. i was asking whether you were using it. are you sure by the time you do join update is completed?

Hi visakh16;

Now THAT is a good question.

I need to figure out some way to make sure that it is indeed completed
before the procedure for the join is called. I'm not sure how I'm
going to do that right at the moment - I'll have to put some thought
into it.

I'll have to get to that later though. I've got other pressing matters
to attend to for now.

THANKS for the assistance!!!

Wylbur
========================

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 05:29:33

Ok. first find out if update is completed by the time you do join and then we can take it from there

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources