After Insert how to get the newid()?

Question

After Insert how to get the newid()?

Helder Rodrigues

Old Hand

Points: 335
More actions
November 30, 2005 at 7:45 pm

#70028

Hello, I need to get the newid generated by sqlce when inserting a row in a table.
The columm that has the newid() running its an uniqueidentifier type.
I follow this guide from msn, but it didnt work
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclassparseexacttopic.asp
Well this is my code:
SqlCeDataAdapter catDA = new SqlCeDataAdapter("SELECT * FROM Encomendas",Sessao.conn);
catDA.InsertCommand = new SqlCeCommand("INSERT INTO Encomendas(moradaEntrega) VALUES(?)", Sessao.conn);
catDA.InsertCommand.CommandType = CommandType.Text;
catDA.InsertCommand.Parameters.Add("@moradaEntrega", SqlDbType.NText, 30, "moradaEntrega");
// Fill the DataSet.
DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");
// Add a new row.
DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["desconto"] = textBoxDesconto.Text;
//newRow["moradaEntrega"] = textBoxMoradaEntrega.Text;
catDS.Tables["Categories"].Rows.Add(newRow);
// Include an event to fill in the Autonumber value.
catDA.RowUpdated += new SqlCeRowUpdatedEventHandler(OnRowUpdated);
// Update the DataSet.
catDA.Update(catDS, "Categories");
protected static void OnRowUpdated(object sender, SqlCeRowUpdatedEventArgs args)
{
SqlCeCommand idCMD = new SqlCeCommand("SELECT @@IDENTITY AS \"@@IDENTITY\" FROM Encomendas", Sessao.conn);
if (args.StatementType == StatementType.Insert)
{
object newID = idCMD.ExecuteScalar();
MessageBox.Show(newID.GetType().ToString());
}
}
The messagebox shows: System.DBNull
I really need to get the newID.
Any tips?
Thank you a lot!
helder

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

Your issue is that you are requesting the @@IDENTITY which is not the UniqueIdentifier (NEWID()) so returns NULL, as you do not have a column in the table with an Identity attribute.

Change:

"SELECT @@IDENTITY AS \"@@IDENTITY\" FROM Encomendas"

To:

"SELECT <your uniqueidentifier column name> FROM Encomendas WHERE desconto = '" & textBoxDesconto.Text & "'"

Substitute your Encomendas table's field name (with the NEWID() default constraint) for <your uniqueidentifier column name>.

Note this is dependant on the desconto column's value being unique.

Otherwise you should create another parameter, generate a GUID or NEWID() for this parameter and add it to your Insert command.

Andy

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Part of the beauty of GUIDs is that you can generate the value knowing that it will be unique - so why not generate it in your client code and insert the value yourself?

nigel. SSChampion Points: 11636 More actions · Answer 3

You don't need to select anything.

In your updated event handler do this :

object newid = args.Row["<your id column name here>"] ;

HTH

Nigel
How to post questions