Filtering specifics dates then renewing ID

Question

Hi,

I have a little bit complex logic I need to implemente in a module of a simple module I developping... What I need?

1. I have on my form a dateTimePicker, datagridview and a table with two columns (ID, Date) 

2. In this table I have many records that some have diferents dates 

3. I need that after doing a filter for a couple a date with the same year (I repeat with the same year ONLY). Its should re-numbered the ID column from 1,2,3,4 and so on. In another word even this table already have 1000 records well numbered from ID. But after filtering the date its cannot start counting from 1001. Its should re-numbered from 1 until ... (But the latest record must be 1000 and the next will be 1 and so one).

 

Then this is what I wrote as codes just to give you a guide line. Because I cant develop this logic as well I can. Many thanx. 

 

///////////////// The filter start from here

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select [id],[data] from livro1 where data between @StartDate and @EndDate";

var param1 = new SqlParameter("@StartDate", SqlDbType.Date);

var param2 = new SqlParameter("@EndDate", SqlDbType.Date);

if (dateStarting.Value <= dateFinal.Value)

{

cmd.Parameters.Add(param1);

cmd.Parameters.Add(param2);

}

else

{

cmd.Parameters.Add(param2);

cmd.Parameters.Add(param1);

}

cmd.ExecuteNonQuery();

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

conn.Close();

dgvTest.DataSource = dt;

 

/////////////////////////Recreate ID coluna deleted from Data Base

 

conn.Open();

SqlCommand cmd2 = new SqlCommand();

// insert the name column into line's code

cmd2 = new SqlCommand("ALTER TABLE DBTest ADD ID int IDENTITY(1,1)", conn);

cmd2.ExecuteNonQuery();

conn.Close();