[bz]

Hi,

I think I will post this here instead to the Access group.

I have a invoice table with a row number as the primary key. Looking at the
source code, the row# is used to tell the program to display the record in
that row in a grid.

1. order#9
2. order#7
3. order#8

etc.

If I add an empty row in between row 1 and row 2 to the grid, existing row#2
and row#3 will become row#3 and row#4. (they will move down one row)

1. order#9
3. order#7
4. order#8

If I add more rows, the row# will continue to move down and row# in the
database will continue to inscrease 1.

1. order#9
4. order#7
5. order#8

When the user right-click row 2 and click "Insert Row", I have a query in
the function that looks like this:

Connect1.Execute "Update Invoice Set Row = Row + 1 Where Row >= 2"

Of course the query does not work.

Is there any way (or trick) to run this query without getting a key
violation error from the database? or I am screwed. heading to a major
overhaul to the source code?


Thanks!







--
> There is no answer.
> There has not been an answer.
> There will not be an answer.
> That IS the answer!
> And I am screwed.
> Deadline was due yesterday.
>
> There is no point to life.
> THAT IS THE POINT.
> And we are screwed.
> We will run out of oil soon.

Archived from group: microsoft>public>vb>enterprise

[Andrew D. Newbould]

In message , bz
writes
>Hi,
>
>I think I will post this here instead to the Access group.
>
>I have a invoice table with a row number as the primary key. Looking at the
>source code, the row# is used to tell the program to display the record in
>that row in a grid.
>
>1. order#9
>2. order#7
>3. order#8
>
>etc.
>
>If I add an empty row in between row 1 and row 2 to the grid, existing row#2
>and row#3 will become row#3 and row#4. (they will move down one row)
>
>1. order#9
>3. order#7
>4. order#8
>
>If I add more rows, the row# will continue to move down and row# in the
>database will continue to inscrease 1.
>
>1. order#9
>4. order#7
>5. order#8
>
>When the user right-click row 2 and click "Insert Row", I have a query in
>the function that looks like this:
>
>Connect1.Execute "Update Invoice Set Row = Row + 1 Where Row >= 2"
>
>Of course the query does not work.
>
>Is there any way (or trick) to run this query without getting a key
>violation error from the database? or I am screwed. heading to a major
>overhaul to the source code?
>
>
>Thanks!

The immediate thing that springs to mind is to create a Stored
Prodcedure that uses a Cursor to walk the Invoice of interest backwards
(ie: ORDER BY ROW DESC). In otherwords start at Row 5, then 4 etc.

Kind Regards,

--
Andrew D. Newbould E-Mail: NO-usenet-SPAM@zadsoft.com

ZAD Software Systems Web : www.zadsoft.com