DataRow InsertAt Database

  • Thread starter Thread starter A. Walker
  • Start date Start date
A

A. Walker

Hello,

I have a table with datarows 1, 2 and I want to add a new row x between
1 and 2. The result I want is 1, 2(formerly x), 3(formerly 2).

There is a method DataRowCollection.InsertAt which works well for the
dataset. But when I write the dataset to the database the new row will
be written as last row. (Result: 1, 2, x)

I assume this is a result of my primarykey column named ID (autonumber).

What do I have to change to get the desired result?

Thx for your help
 
A. Walker said:
Hello,

I have a table with datarows 1, 2 and I want to add a new row x between 1
and 2. The result I want is 1, 2(formerly x), 3(formerly 2).

There is a method DataRowCollection.InsertAt which works well for the
dataset. But when I write the dataset to the database the new row will be
written as last row. (Result: 1, 2, x)

I assume this is a result of my primarykey column named ID (autonumber).

What do I have to change to get the desired result?
Click to expand...

This is a result of the internal workings of your database and there is
nothing you can do about it from your client side code. If the table has a
clustered index, it always stores the data in the order of the clustered
index regardless of how you insert them. In Sql Server, the primary key
defaults to clustered unless you explicitly specified otheerwise, and if it
is autonumeric, this means that your records will always be kept in the
order in which you inserted them.

If the table doesn't have a clustered index, then there is no guarantee
about the order of the data since the database server will use any available
space to store new records, resulting in an apparent "random" order once it
has been operating for some time and you have inserted and removed several
records.

If you need the records in some specific order, the right thing to do
is to specify an "...order by..." clause when extracting the information.
There is nothing you can do when inserting (except creating a numeric column
to specify the order and using that column as the clustered index).
 
* A. Walker wrote, On 4-11-2009 9:19:
Hello,

I have a table with datarows 1, 2 and I want to add a new row x between
1 and 2. The result I want is 1, 2(formerly x), 3(formerly 2).

There is a method DataRowCollection.InsertAt which works well for the
dataset. But when I write the dataset to the database the new row will
be written as last row. (Result: 1, 2, x)

I assume this is a result of my primarykey column named ID (autonumber).

What do I have to change to get the desired result?

Thx for your help
Click to expand...

The only way to accomplish this is to add a column to your table in
which you specify the order (unless order can be deducted by ordering
the rows based on an existing column).

Then when loading the data, sort it based on the 'order' column using
the Order By clause.

Btw, this isn't really a .NET question, more a database question. You'll
no doubt get a more extensive explanation in the sqlserver.programming
newsgroup.


Jesse
 
Back
Top