How can you prevent the data changes message during editing a

Hi David,
I think you are right that this is not a server's job. I assumed that the
server has the original record available in the Deleted version of the recor
d
in a trigger - but this is probably not the record state the user first read
into a form.
My problem is that I have a big application (a mail-order processing system)
written in Access 2000 FE (.mdb) file with lots of forms with bound ODBC
server tables/queries. I have 850 queries and a lot of code. Up to now I use
d
pessimistic locking but this is not possible anymore (I think) because if I
link via ODBC to SQL server I do not have the option to lock pessimisticly
anymore.
In principle I could use the optimistic locking but how can I deal with data
changes in a record made by other users/jobs. If I cannot lock, a job or
program could "get caught out" as well and how would the program then know
what to do (from the choices: copy the data to the clipboard, overwrite or
loose it). I really think Microsoft should solve this problem by changing th
e
bound forms so that only the changed fields get updated and overwrite by
default - may with an option to switch this behaviour on and off...
It should never get to that stage, it must be prevented via short time locks
that a record gets changed during a read and update transaction.
It is okay to assume that if a user field change clashes with another user
field change the last writing user "wins" provided only the changed fields
will be updated.
If I could I would like to leave everything in ODBC or DAO and convert
slowly to stored procedures and use ADO, ... otherwise it would be a mega jo
b
to convert all in one go. I might have to do this if there is no way around
the locking problem.
Do you or anybody else know a way around it - it would be like light in the
dark tunnel for me!
Thanks.
Oliver
"David Gugick" wrote:

> Oliver wrote:
> I don't agree with your assessment. This is an application programming
> issue and not one for the database to manage on its own. You're
> suggesting that the server update a row that has been updated in the
> interim by another process just because the columns being updated are
> not the same. I would argue that there's no way for SQL Server to know
> if changing a single column value in a row would somehow affect business
> rules and know whether or not the proposed row changes are valid.
> Many applications can deal with this scenario by assuming the last
> update should be the most current. In that scenario, there is no
> additional programming required. If you need to manage concurrency and
> changes to a row by another session to avoid overwriting those changes,
> you should use a timestamp. You pass the timestamp value with the other
> column values to your stored procedure and the procedure does the work.
> The application should be able to handle the condition where the row was
> not updated because an error was raised and then requery the data and
> inform the end-user. All your DML should be done in stored procedures.
> In the case where there is no end-user, your application code should log
> an error condition that can be managed manually in an interactive
> fashion or it could requery the data, assuming this is something it can
> work around.
> For a nightly batch process, you could lock the entire table within your
> transaction.
>
> I'm not sure what you are describing here. A change is a change.
> Presumably, you have a stored procedure to manage each type of change to
> your data. Maybe you can elaborate on this part a little more.
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>Oliver wrote:
> Hi David,
> I think you are right that this is not a server's job. I assumed that
> the server has the original record available in the Deleted version
> of the record in a trigger - but this is probably not the record
> state the user first read into a form.
> Oliver
Can't you use either the dbOptimistic or dbOptimisticValue LockEdit
parameter on the Recordset? I think dbOptimisticValue does a column by
column value comparison before updating. It's been a long time since
I've looked at DAO, so this might be a better question for an Access
newsgroup.

> I really think Microsoft
> should solve this problem by changing the bound forms so that only
> the changed fields get updated and overwrite by default - may with an
> option to switch this behaviour on and off...
I think this is a bit of a risk for those users programming using bound
forms (not recommended in my opinion for any important application - but
that's another topic). The point I tried to make in my last post was
that it's too much of a risk to update some columns and not others -
there's no way to know if the relationship between them is important in
some manner.
David Gugick - SQL Server MVP
Quest Software

How can I use SELECT DISTINCT but maintain the original order

Say I have this result set with two fields (numbers and letters):
1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?

When I try select distinct Number from MyResultSet it will reorder the new result set by the Number field and return
1
2
3

However, I'd like maintain the Letter order and return
1
3
2

hmm.. tricky one. I think this is not exactly what you were hoping for but it's the only thing i could come up with that works for sure.

Remember that database theory thinks or records as collection in a mathematical sense, and these do not have an order.

Good luck,

John

Code Snippet

declare @.KeepOrder table (Num int, Ord int)
declare @.Num int,
@.Ord int

declare c cursor fast_forward for

-- change this query to your table
select Num from Test
order by Let

open c
fetch next from c into @.num
set @.Ord = 1

while @.@.fetch_status = 0
begin
if not exists(select num from @.Keeporder where Num = @.Num)
begin
insert into @.Keeporder(num, ord) values(@.num, @.Ord)
set @.Ord = @.Ord + 1
end
fetch next from c into @.num
end

close c
deallocate c

select num
from @.Keeporder
order by ord

|||

Try:

create table dbo.t1 (

c1 int not null,

c2 char(1) not null

)

go

insert into dbo.t1 values(1, 'A')

insert into dbo.t1 values(3, 'A')

insert into dbo.t1 values(1, 'B')

insert into dbo.t1 values(2, 'B')

go

;with cte

as

(

select

c1,

c2,

row_number() over(partition by c1 order by c2) as rn

from

dbo.t1

)

select

c1

from

cte

where

rn = 1

order by

c2

go

drop table dbo.t1

go

AMB

|||

You could try something along these lines... Bruce

declare @.t1 table (ID int NULL, CD varchar(1) NULL)

insert into @.t1 (ID, CD) select 1, 'A'

insert into @.t1 (ID, CD) select 3, 'A'

insert into @.t1 (ID, CD) select 1, 'B'

insert into @.t1 (ID, CD) select 2, 'B'

select * from @.t1

;

WITH work_tbl AS

(

SELECT ROW_NUMBER() OVER (ORDER BY CD) AS 'RowNumber',

ID, CD

FROM @.t1

)

SELECT min(RowNumber), ID

FROM work_tbl

GROUP BY ID

ORDER BY 1

|||I really have to start looking into these new 2005 functions....|||great, thanks Bruce and HunchBack! that's exactly what I was looking for.

Is the WITH clause new for 2005? I haven't seen it before, but it's a lot easier than creating and dropping temporary tables.|||Yes, I believe the WITH is new.. it's nice for clearer code when first making a derived table.. and then referencing it multiple times in the main query. Note that you must put the semi-colon just before the WITH, strange... It won't work without that semi-colon... Bruce

How can I use SELECT DISTINCT and maintain the original order

Say I have a result set with two fields numbers and letters.

1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?

When I try

select distinct Number from MyResultSetit will reorder the new result set by the Number field and return

1
2
3

However, I'd like maintain the Letter order and return

1
3
2

try this

create

table #test(nnint,llchar(1))

insert

into #test

values

(1,'A')

insert

into #test

values

(3,'A')

insert

into #test

values

(1,'B')

insert

into #test

values

(2,'B')

select

nnfrom(select nn,min(ll)llfrom #test

group

by nn

)

aa

order

by ll

drop

table #test