Add ID Column to DataTable - PLEASE HELP!

[faithful1]

Does anyone know if it is possible to add an id column to an existing datatable that autoincrements? I have tried, but the id column is null. I am filtering a cached datatable so the ids for the records will constantly change.
This is what I have tried...but NO LUCK! I get zero records, I would think I would get one. Thanks!

Sample code:

alphapagedt = CType(Session("AlphaResults&quot, DataTable)
Dim c1 As New DataColumn("alphaid", Type.GetType("System.Int32&quot)
c1.AutoIncrement = True
c1.AutoIncrementSeed = 1
alphapagedt = CType(Session("AlphaResults&quot, DataTable)
alphapagedt.Columns.Add(c1)
alphapagedt.DefaultView.RowFilter = "alphaid=1"

dgSearch.DataSource = alphapagedt.DefaultView
dgSearch.DataBind()

 

[chiph]

What is your database? Oracle? SQL Server? MS-Access?

Because it would have to be done at a database level.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[faithful1]

I am using SQL Server. The thing is I run an original query, say by location and then cache that. I do create an id at the database level for the original query. I store that in a datatable and cache it. I want to do a filter on the cached datatable, since I don't want to hit the database again. When filtering the datatable, the rows I select may have id's of 5,7,8,10....but I need to have an autoincremented id of 1,2,3,4.........

Any suggestions would help.

 

[chiph]

Sorry, I haven't used dataTables. Maybe someone else can help?

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[faithful1]

thanks for your reply... it is much appreciated! =)

 

[sunaj]

Hi,

You could of course loop through the datatable and set each number in your 'Autonumber' column.
You can also do it in your SQL statement. The most efficient is acutally to create a temporary table (#) with an identity column, insert the data into that table (SELECT INTO) and then select the data from there. If performance is not an issue you can also:

Select (select count(tblTestID) from tblTest T1 where T1.tblTestID < T2.tblTestID) as MyLocalID,MyField1, MyField2 From tblTest T2 order by T2.tblTestID


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[faithful1]

the first suggestion sounds like a better idea. Any tips on how to reference the 'autonumber' column and set it?
Thanks sunaj!

 

[sunaj]

Something like
--------------------------------------------------------
Dim DS As New DataSet()
Dim DA As New SqlClient.SqlDataAdapter(&quot;SELECT TestText from tblTest&quot;, Conn)
DA.Fill(DS, &quot;MyTable&quot
Dim DT As DataTable = DS.Tables(&quot;MyTable&quot
DT.Columns.Add(&quot;MyAutoNum&quot;, System.Type.GetType(&quot;System.Int32&quot)
Dim i As Int32
For i = 0 To DT.Rows.Count - 1
DT.Rows(i).Item(&quot;MyAutoNum&quot = i
Next
--------------------------------------------------------
or your could
--------------------------------------------------------
Dim DS As New DataSet()
Dim DA As New SqlClient.SqlDataAdapter(&quot;SELECT NULL as MyAutoNum,TestText from tblTest&quot;, Conn)
DA.Fill(DS, &quot;MyTable&quot
Dim DT As DataTable = DS.Tables(&quot;MyTable&quot
Dim i As Int32
For i = 0 To DT.Rows.Count - 1
DT.Rows(i).Item(&quot;MyAutoNum&quot = i
Next
--------------------------------------------------------
to get the autonumber column as the first.

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[faithful1]

Thanks...I will try!