How to insert data to MS SQL , using FIFO ordering process...

[Andrevv]

Hi all! Im totaly new in programing, so i need some help if someone can help me...
Im programing in VS 2005, C# language and using MS SQL Server 2005 .

So, after i fill my table with 10 records, i would like to use First In First Out ordering process. The problem is, that after the DELETE statement, when i trigger the INSERT statement, the record is not placed in the last row, but replaces the row that i've deleted...
I have 2 columes...count er and data

For example:

counter data
1 a
2 a
3 a
4 a
.
.
.
DELETE FROM table1 WHERE counter = '" + c + "' "
INSERT INTO table1 values ('" + c + "','c')"

it does

1 c
2 a
3 a
4 a
.
.
.

This is how it use to be:

counter data
2 a
3 a
4 a
.
.
.
9 a
10 a
1 c



My code:


[PHP]static void Main()
{

int i;
int c;

// 1. Instantiate the connection
SqlConnection conn = new SqlConnection(
"Data Source=ADVMBRPP ;Initial Catalog=proba;I ntegrated Security=SSPI") ;

SqlDataReader rdr = null;

try
{
// Open the connection
conn.Open();

for (i = 1; i <= 10; i++)
{
// prepare command string
string insertString = @"
INSERT INTO table1 values ('"+i+"','a') ";

// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(inse rtString, conn);


// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQ uery();

}

i--;


if (i == 10)
{
for (c = 1; c < 2 ; c++ )
{
// prepare command string
string deleteString = @"DELETE FROM table1 WHERE counter = '" + c + "' ";


SqlCommand cmd2 = new SqlCommand(dele teString,conn);


string insertString = @"INSERT INTO table1 values ('" + c + "','c')";


SqlCommand cmd3 = new SqlCommand(inse rtString, conn);

cmd2.ExecuteNon Query();
cmd3.ExecuteNon Query();

}

}

}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
}

[/PHP]

Any help would be appreciated and sorry for my bad english

Tnx
Andrew

[Banfa]

Looks to me like you are retrieving/viewing the data sorted by counter.

If you want a specific order for your data you need to have a field that allows you to sort the data into that order.

In this case a simple auto incrementing id field (that you don't set the value of but let the database engine set the value of on inserts) would allow you to retrieve the rows in the order they have been inserted in to the database.

[Andrevv]

emmm, the records don't have to be in order. I just have to use only this 10 rows and in this table with this 10 lines i have to do 10000 writings.

counter.....dat a
1.............. ..a
2.............. ..a
3.............. ..a
4.............. ..a
5.............. ..a --> 10 inputs(first loop in the code)
6.............. ..a
7.............. ..a
8.............. ..a
9.............. ..a
10............. .a

2.............. ..a
3.............. ..a
4.............. ..a
5.............. ..a
6.............. ..a
7.............. ..a --> 11. input(second loop in the code)
8.............. ..a
9.............. ..a
10............. .a
1.............. ..a

3.............. ..a
4.............. ..a
5.............. ..a
6.............. ..a
7.............. ..a
8.............. ..a -->12. input(- | | -)
9.............. ..a
10............. . a
1.............. ..a
2.............. ..a

4.............. ..a
5.............. ..a
6.............. ..a
7.............. ..a
8.............. ..a -->13. input(- | | -)
9.............. ..a
10............. . a
1.............. ..a
2.............. ..a
3.............. ..a

and so on...to 10000 inputs

when i delete the first record, so 1..........a, and want to insert it at the end of the column, it just replaces the deleted one....i just don't know how to add it at the end of the column...

[Banfa]

emmm, the records don't have to be in order. I just have to use only this 10 rows and in this table with this 10 lines i have to do 10000 writings.

Clearly the records DO have to be in order since you are complaining that they are not in the right order.

A database is NOT a FIFO queue the order it stores the data in its tables may (and probably will) have nothing to do with the order you inserted it into the database unless you have added an index or primary key that creates that particular order. I repeat if you want the records is a specific order then you need to add a column/index that will maintain that order for you.

when i delete the first record, so 1..........a, and want to insert it at the end of the column, it just replaces the deleted one....i just don't know how to add it at the end of the column...

It does not replace the deleted one. You delete a row, then you insert a new row, then you retrieve the data the order that the data is returned from the retrieval is purely a function of how you are retrieving the data and has nothing to do with the insert delete process.

[Andrevv]

Thanks for everything....n ow i got it...everything works properly, i just didn't understood what u meant. Thanks again!!!