Problem getting SCOPE_IDENTITY()

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have a function which takes a table and calls DA.Update with that table. I
can't seem to get my new ids returned back to me.
My second problem is I don't like using the select * for the SelectCommand.
I was thinking of adding where '1' = '2' but think that will definitly not
return any records back to me.

public void UpdateTable(DataTable dt)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = sqlConnection1.CreateCommand();
SqlCommandBuilder cb = new SqlCommandBuilder();

cmd.CommandText = string.Format("select * from {0}", dt.TableName);
da.SelectCommand = cmd;
cb.DataAdapter = da;

DataColumn dc = null;

foreach (DataColumn c in dt.Columns)
{
if (c.AutoIncrement == true)
{
dc = c;
break;
}
}

da.InsertCommand = cb.GetInsertCommand();

if (dc != null)
da.InsertCommand.CommandText += " SELECT SCOPE_IDENTITY() As " +
dc.ColumnName;

da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();

da.Update(dt);
}
 
Take a look at my article on the CommandBuilder. See www.betav.com/articles
("Weaning developers from the CommandBuilder").
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top