Add Autoincrement Column to a databound dataset

[Rattlesnake]

Hi

I have a master detail form (in ASP.net) that displays Purchase Order (PO) header and its details (Items).

 

I load a dataset with the PO detail rows of the Purchase ORder (from a SQL database) and assign it as a datasource to a datagrid.

 

For programming purposes (to track changes to each row) I add an Integer colum "ID" (autoincrement=true) to this Dataset AFTER I load it with the PO detail rows . (note:this column is not from the database table , but manually added )

 

The problem I face is that this column (ID) has NULL value for already existing rows (i.e. retrieved from the database). The field starts from 1 only for rows that are added after the page is displayed.

 

Is there a way to have this ID column have values for already exisitng rows plus the newly added rows.

 

Thanks

[HJB417]

there are 2 ways of going about doing this

1) modify the sql such.

"SELECT @id:=0;SELECT @id:=@id+1 AS id, po.* FROM po"

it's a mysql query but it adds a prepends a column to the resultset of an incrementing integer starting at zero.

 

2) using ado.net. I'm trying to figure this one out myself so I'll have to get back to you on that.

[HJB417]

ok, here's code of how to do it using a data adapter.

using(GenericDbConnection conn = new GenericDbConnection(Database.ConnectionInfo))
{
using(GenericDbDataAdapter da = new GenericDbDataAdapter(Database.ConnectionInfo))
{
	using(da.SelectCommand = conn.CreateCommand())
	{
		da.SelectCommand.CommandText = "SELECT * FROM personal";
		using(DataTable dt = new DataTable("personal"))
		{
			dt.Columns.Add("id", typeof(int)).AutoIncrement = true;
			da.Fill(dt);
		}
	}
}
}

[Rattlesnake]

Thanks. Worked excellently.