Error while trying to add an auto-incrementable column to a datagridview

Question

I’m trying to add a column self-adjusting in a datagridview but I am not able to figure out what is missing to work properly. My program reads the data from a database table and displays in a datagridview. I need to add a column self-adjusting called 'Legend', because the table values are also represented in a graph and the column 'Legend' exists to facilitate the interpretation of the data by the user.

Follows part of the code responsible for this. The column called 'Caption' appears in datagridview, but the lines are blank.

Conexao ca = new Conexao();
string sql = "";
sql += " Select ";
sql += " CausaDef, Incidencia ";
sql += " From ";
sql += " Relat ";

ca.Conectar();
OleDbDataAdapter da = new OleDbDataAdapter(sql, ca.cx);
DataSet ds = new DataSet();
da.Fill(ds, "DetGraf");

object sumObject;
sumObject = ds.Tables["DetGraf"].Compute("Sum(Incidencia)", "");

ds.Tables["DetGraf"].Rows.Add("TOTAL", sumObject.ToString());

DataColumn Legenda = new DataColumn();
Legenda.ColumnName = "Legenda";
Legenda.DataType = typeof(int);
Legenda.AutoIncrement = true;
Legenda.AutoIncrementSeed = 1;
Legenda.AutoIncrementStep = 1;

ds.Tables["DetGraf"].Columns.Add(Legenda);

dgvDetGraf.DataSource = ds.Tables["DetGraf"];

ca.Desconectar();

Answer 1

I was able to find a solution as follows:

ds.Tables["DetGraf"].Columns.Add(Legenda);

int index = 0;
foreach (DataRow row in ds.Tables["DetGraf"].Rows)
row.SetField(Legenda, ++index);

dgvDetGraf.DataSource = ds.Tables["DetGraf"];

Abs!

Answer 2

It tries to modify the code, instead of creating this "auto-increment" column, it tries to bring the number of the database row, through the ROW_NUMBER function that if it is SQL SERVER. So just apply the data source to the datagrid.

Conexao ca = new Conexao();
string sql = "";
sql += " Select ";
sql += " ROW_NUMBER() OVER(ORDER BY CausaDef) AS Legenda,";
sql += " CausaDef, Incidencia ";
sql += " From ";
sql += " Relat ";

ca.Conectar();
OleDbDataAdapter da = new OleDbDataAdapter(sql, ca.cx);
DataSet ds = new DataSet();
da.Fill(ds, "DetGraf");

object sumObject;
sumObject = ds.Tables["DetGraf"].Compute("Sum(Incidencia)", "");

ds.Tables["DetGraf"].Rows.Add("TOTAL", sumObject.ToString());

dgvDetGraf.DataSource = ds.Tables["DetGraf"];

ca.Desconectar();