Best way to generate a new unique identifier for a row insert?

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I still have some questions about generating a new unique identifier during
a row insert.

So far I got it only to work if I actually generate one like this prior to
the addrow/insert:

newrow["col1"] = "data";

newrow["col2ID"] = System.Guid.NewGuid()

Later doing:

myDataSet.Tables["myTable"].Rows.Add(newrow);

myAdapter.Update(myDataSet,"MyTable"); //doing an insert

I played around with MissingSchemaAction, AutoIncrement,
AutoIncrementStep,.. but no success so far.

Is there no way that the ID gets generated automaically during the insert?
 
AutoIncrement will work. Set the Autoincrement property to true of the
datacolumn you want to use. Then set the AutoIncremementSeed = 0 and the
AutoIncrementValue to -1. THen the values will all be negative. When you
submit the update to the db, it will correctly assign the values. If you
have multiple users, doing it other ways can be a real pain and this ensures
that your values will be legit. You can fire another Select statement after
the update (which is what the DataAdapter does via the configuration wizard
if you have the Refresh DataSet option set to true) and get the new value.
Once this value is returned and set in the datatable, if you have a
datarelation(s) set up then those values on the key will be updated too.

HTH,

Bill
 
Bill,

I same to have a problem with ordering the statements. I'm getting an
excpetion that I can't cahnge the column data.

DataSet myDataSet = new DataSet();

myAdapter.Fill(myDataSet , "myTable");

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrement = true;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementSeed = 0;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementStep = -1;

I'm getting an exception for one of the above three lines.

I created my table with the server explorer.

Alex

p.s. I don't think the issue is cuased by the spaces...

William Ryan eMVP said:
AutoIncrement will work. Set the Autoincrement property to true of the
datacolumn you want to use. Then set the AutoIncremementSeed = 0 and the
AutoIncrementValue to -1. THen the values will all be negative. When you
submit the update to the db, it will correctly assign the values. If you
have multiple users, doing it other ways can be a real pain and this ensures
that your values will be legit. You can fire another Select statement after
the update (which is what the DataAdapter does via the configuration wizard
if you have the Refresh DataSet option set to true) and get the new value.
Once this value is returned and set in the datatable, if you have a
datarelation(s) set up then those values on the key will be updated too.

HTH,

Bill
Alex said:
I still have some questions about generating a new unique identifier during
a row insert.

So far I got it only to work if I actually generate one like this prior to
the addrow/insert:

newrow["col1"] = "data";

newrow["col2ID"] = System.Guid.NewGuid()

Later doing:

myDataSet.Tables["myTable"].Rows.Add(newrow);

myAdapter.Update(myDataSet,"MyTable"); //doing an insert

I played around with MissingSchemaAction, AutoIncrement,
AutoIncrementStep,.. but no success so far.

Is there no way that the ID gets generated automaically during the insert?
Click to expand...
Click to expand...
 
More detailed:

I'm getting an exception on
myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrement = true;

Exception: "Cannot change DataType of a column once it has data."
However I'm unable to change this even for a new table/column in the server
explorer of VS.NET 2003

Alex said:
Bill,

I same to have a problem with ordering the statements. I'm getting an
excpetion that I can't cahnge the column data.

DataSet myDataSet = new DataSet();

myAdapter.Fill(myDataSet , "myTable");

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrement = true;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementSeed = 0;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementStep = -1;

I'm getting an exception for one of the above three lines.

I created my table with the server explorer.

Alex

p.s. I don't think the issue is cuased by the spaces...

William Ryan eMVP said:
AutoIncrement will work. Set the Autoincrement property to true of the
datacolumn you want to use. Then set the AutoIncremementSeed = 0 and the
AutoIncrementValue to -1. THen the values will all be negative. When you
submit the update to the db, it will correctly assign the values. If you
have multiple users, doing it other ways can be a real pain and this ensures
that your values will be legit. You can fire another Select statement after
the update (which is what the DataAdapter does via the configuration wizard
if you have the Refresh DataSet option set to true) and get the new value.
Once this value is returned and set in the datatable, if you have a
datarelation(s) set up then those values on the key will be updated too.

HTH,

Bill
Click to expand...
prior
to
the addrow/insert:

newrow["col1"] = "data";

newrow["col2ID"] = System.Guid.NewGuid()

Later doing:

myDataSet.Tables["myTable"].Rows.Add(newrow);

myAdapter.Update(myDataSet,"MyTable"); //doing an insert

I played around with MissingSchemaAction, AutoIncrement,
AutoIncrementStep,.. but no success so far.

Is there no way that the ID gets generated automaically during the insert?
Click to expand...
Click to expand...
Click to expand...
 
Thajeer:

Yep and identity will work but the problem he's having is client side.. the
solution I recommended takes this into account, but locally you need to set
the autoincrement property to true to get it to move client side and using
the negative number is the safest way to ensure that your Identity values
don't collide.
 
Alex:

What type of data is in this column? Do you have any spaces or any
non-numeric data?
Alex said:
More detailed:

I'm getting an exception on
myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrement = true;

Exception: "Cannot change DataType of a column once it has data."
However I'm unable to change this even for a new table/column in the server
explorer of VS.NET 2003

Alex said:
Bill,

I same to have a problem with ordering the statements. I'm getting an
excpetion that I can't cahnge the column data.

DataSet myDataSet = new DataSet();

myAdapter.Fill(myDataSet , "myTable");

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrement = true;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementSeed = 0;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementStep = -1;

I'm getting an exception for one of the above three lines.

I created my table with the server explorer.

Alex

p.s. I don't think the issue is cuased by the spaces...

William Ryan eMVP said:
AutoIncrement will work. Set the Autoincrement property to true of the
datacolumn you want to use. Then set the AutoIncremementSeed = 0 and the
AutoIncrementValue to -1. THen the values will all be negative. When you
submit the update to the db, it will correctly assign the values. If you
have multiple users, doing it other ways can be a real pain and this ensures
that your values will be legit. You can fire another Select statement after
the update (which is what the DataAdapter does via the configuration wizard
if you have the Refresh DataSet option set to true) and get the new value.
Once this value is returned and set in the datatable, if you have a
datarelation(s) set up then those values on the key will be updated too.

HTH,

Bill
I still have some questions about generating a new unique identifier
during
a row insert.

So far I got it only to work if I actually generate one like this
Click to expand...
prior
to
the addrow/insert:

newrow["col1"] = "data";

newrow["col2ID"] = System.Guid.NewGuid()

Later doing:

myDataSet.Tables["myTable"].Rows.Add(newrow);

myAdapter.Update(myDataSet,"MyTable"); //doing an insert

I played around with MissingSchemaAction, AutoIncrement,
AutoIncrementStep,.. but no success so far.

Is there no way that the ID gets generated automaically during the insert?
Click to expand...
Click to expand...
Click to expand...
 
For the following solution, the identity field of the table should have the
'uniqueidentifier' datatype.

In your Server Explorer, open the table in 'design' mode

I suggest setting the RowGUID property of the identity field to True, and
then setting the 'default value' of the column to (newid())

This will generate a new GUID for every row added to the dataset

HTH,
Evert
 
It has the uniqueidentifier datatype.
I set the default value to newID(). This works when manually adding a row in
the server explorer designer. However for programmatically adding a row it
doesn't work.

Alex

William Ryan eMVP said:
Alex:

What type of data is in this column? Do you have any spaces or any
non-numeric data?
Alex said:
More detailed:

I'm getting an exception on
myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrement = true;

Exception: "Cannot change DataType of a column once it has data."
However I'm unable to change this even for a new table/column in the server
explorer of VS.NET 2003

Alex said:
Bill,

I same to have a problem with ordering the statements. I'm getting an
excpetion that I can't cahnge the column data.

DataSet myDataSet = new DataSet();

myAdapter.Fill(myDataSet , "myTable");

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrement = true;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementSeed = 0;

myDataSet .Tables["myTable"].Columns["myIDCOL"].AutoIncrementStep = -1;

I'm getting an exception for one of the above three lines.

I created my table with the server explorer.

Alex

p.s. I don't think the issue is cuased by the spaces...

AutoIncrement will work. Set the Autoincrement property to true of the
datacolumn you want to use. Then set the AutoIncremementSeed = 0
Click to expand...
and
the
AutoIncrementValue to -1. THen the values will all be negative.
Click to expand...
When
you
submit the update to the db, it will correctly assign the values.
Click to expand...
If
you
have multiple users, doing it other ways can be a real pain and this
ensures
that your values will be legit. You can fire another Select statement
after
the update (which is what the DataAdapter does via the configuration
wizard
if you have the Refresh DataSet option set to true) and get the new value.
Once this value is returned and set in the datatable, if you have a
datarelation(s) set up then those values on the key will be updated too.

HTH,

Bill
I still have some questions about generating a new unique identifier
during
a row insert.

So far I got it only to work if I actually generate one like this prior
to
the addrow/insert:

newrow["col1"] = "data";

newrow["col2ID"] = System.Guid.NewGuid()

Later doing:

myDataSet.Tables["myTable"].Rows.Add(newrow);

myAdapter.Update(myDataSet,"MyTable"); //doing an insert

I played around with MissingSchemaAction, AutoIncrement,
AutoIncrementStep,.. but no success so far.

Is there no way that the ID gets generated automaically during the
insert?
Click to expand...
Click to expand...
Click to expand...
 
An other problem: the identity property for uniqueidentifier column is
grayed out in the server explorer designer.
 
Alex,

If you're going to use a Guid/uniqueidentifier as your primary key,
your best bet is to generate the new PK values as you create your DataRows.
To the best of my knowledge, there is no @@IDENTITY-equivalent for asking
SQL Server for the last uniqueidentifier generated.

At the end of this post, you'll find a small piece of sample code that
you could use as a basic approach. The code programmatically assigns a new
Guid value to the ID column, submits the change with very basic updating
logic (generated by a CommandBuilder) and then re-queries the table for the
row to verify that the row was created with the expected ID value.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.



//Create the schema for the DataTable
//Could also be done via DataAdapter.FillSchema
DataTable tbl = new DataTable();
DataColumn col = tbl.Columns.Add("ID", typeof(Guid));
tbl.PrimaryKey = new DataColumn[] {col};
tbl.Columns.Add("OtherColumn", typeof(string));


//Connect to database and create Guid table
//Call to CreateGuidTable can be removed after initial execution
string strConn = "Data Source=(local);Initial Catalog=Northwind;" +
"Trusted_Connection=Yes;";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
CreateGuidTable(cn);


//Create DataAdapter
string strSQL = "SELECT * FROM NewGuidId WHERE ID = @ID";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
SqlParameter p;
p = da.SelectCommand.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);


//Create new row
//Set ID to new Guid prior to adding to DataTable
DataRow row = tbl.NewRow();
Guid g = Guid.NewGuid();
row["ID"] = g;
row["OtherColumn"] = "First row";
tbl.Rows.Add(row);
Console.WriteLine(row["ID"]);


//Create updating logic
SqlCommandBuilder cb = new SqlCommandBuilder(da);


//Submit new row to database
da.Update(tbl);


//Clear table prior then query for row
// to verify row submitted successfully
tbl.Rows.Clear();

p.Value = g;
da.Fill(tbl);
Console.WriteLine(tbl.Rows[0]["ID"]);

cn.Close();



//Procedure to create the Guid table
public void CreateGuidTable(SqlConnection cn)
{
SqlCommand cmd = cn.CreateCommand();
try
{
cmd.CommandText = "DROP TABLE NewGuidID";
cmd.ExecuteNonQuery();
}
catch {}

string strSQL;
strSQL = "CREATE TABLE NewGuidId " +
"(ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NEWID())," +
" OtherColumn varchar(50))";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
}
 
Back
Top