Creating an identity column in a dataset using VB.NET

  • Thread starter Thread starter Omavlana
  • Start date Start date
O

Omavlana

Hi,

I have created a datatable, dataadapter, added the table to a dataset
and filling the table.

Here I want to add an identity column to the above datatable and Later
I will use this identity colum for printing the records using print
document.

How to create an identity column that increments its value when each
row added to it.

Here is the code snippet... Pls help.

myCommand = New SqlClient.SqlCommand(mySql, gSQLConnection)
UsrAdapter = New SqlClient.SqlDataAdapter(myCommand)
myDataSet = New DataSet

Dim myDataTable As New DataTable("ShareHoldersAuditReport")
myDataSet.Tables.Add("ShareHoldersAuditReport")
Dim aColumn As DataColumn
aColumn = myconstraint
aColumn = New DataColumn("aaaa")
myDataSet.Tables("ShareHoldersAuditReport").Columns.Add(aColumn)
aColumn = New DataColumn("bbbb")
myDataSet.Tables("ShareHoldersAuditReport").Columns.Add(aColumn)
aColumn = New DataColumn("cccc")
myDataSet.Tables("ShareHoldersAuditReport").Columns.Add(aColumn)
UsrAdapter.Fill(myDataSet, "ShareHoldersAuditReport")
myTable = myDataSet.Tables("ShareHoldersAuditReport")
gSQLConnection.Close()



Regards.
 
Take a look at DataColumn AutoIncrement, AutoIncrementSeed and
AutoIncrementStep. Good example in the sdk.
 
look for datacolumn's properties

you have to set some properties to make identity column.

like,
Dim aColumn As DataColumn
aColumn = myconstraint
aColumn = New DataColumn("id")
Click to expand...

add
acolumn.autoincrement = true
acolumn.autoincrementseed = 1
acolumn.autoincrementstep = 1

and then
myDataSet.Tables("ShareHoldersAuditReport").Columns.Add(aColumn)
Click to expand...
 
set both to -1 if you want to synch up with a Database...why -1? So you do
not create an id that may already exist in Sql Server.
 
I find it interesting that everyone always recommends using -1 as the
initial value.
That assumes the original developers assigned +1 as the first Identity
value.
But if they did that then they cut their Domain of values in half before
even beginning the project.
I always set my starting values in the database to the smallest possible
negative value and increment by 1.
This gives twice as many values.

If you do that then your datasets should set the "place holder" identity to
the highest possible positive number instead of to -1. This should avoid the
collision problem too.
 
Good point. A better reply would be "be careful of collisions with the
id's in your dataset when you are trying to do Updates on your database"
 
Back
Top