Re: how to get the id of the inserted datarow?
- From: "William Vaughn" <billvaNoSPAM@xxxxxxxxx>
- Date: Sat, 15 Sep 2007 17:54:28 -0700
Very true, but it's an unreliable global variable. We recommend use of SCOPE_IDENTITY() instead.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"Fatih Isikhan" <Fatih Isikhan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:6A86FA61-3351-414B-BBFD-EBEA4C8B65F5@xxxxxxxxxxxxxxxx
In SQL Server, if your table contains identity column, the identity value is
stored in @@IDENTITY and it is probably the unique id that you want.
"Norbert Pürringer" wrote:
Hello there,
after inserting a row, I want to return the id of the inserted row.
This id could be a number or a string (e.g. GUID values). The database
could be SQL Server, Oracle, MySQL or Access. Is there a database
independent way to return the ID of the inserted data row?
My way does only work, if the ID is a sequence value of an Oracle
database. In each other case the ID, which I want to return, has the
value DBNull:
public int InsertDataRow(string tableName, IDictionary columnValues,
string idName, string idGenerator, out object idValue)
{
idValue = 0;
AddProperIdValue(tableName, columnValues, idName,
idGenerator, true);
IDbConnection conn = GetConnection();
try
{
string fields = null;
foreach (DictionaryEntry item in columnValues)
if (!_factory.IsGeometryValue(item.Value))
fields += item.Key.ToString() + ",";
if (!columnValues.Contains(idName))
fields += idName;
if (fields.EndsWith(","))
fields = fields.Substring(0, fields.Length - 1);
string sql = "SELECT " + fields + " FROM " + tableName
+ " WHERE 0=1";
IDbCommand selectcmd = _factory.CreateCommand(sql,
conn);
selectcmd.Transaction = _transaction;
IDbCommand insertcmd = CreateInsertCommand
(tableName, columnValues, conn, true);
try
{
IDbDataAdapter dataAdapter =
_factory.CreateAdapter(selectcmd);
dataAdapter.InsertCommand = insertcmd;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
DataRow dataRow = dataTable.NewRow();
foreach (DictionaryEntry item in columnValues)
dataRow[item.Key.ToString()] = item.Value;
dataTable.Rows.Add(dataRow);
int affectedrows = dataAdapter.Update(dataSet);
idValue = dataRow[idName];
return affectedrows;
}
catch (Exception ex)
{
Rollback();
throw CreateDataException
(ex, "Error inserting data row!\n" +
insertcmd.CommandText,
tableName, columnValues, true);
}
}
finally
{
CloseConnection(conn);
}
}
The important line in the upper code is idValue = dataRow[idName];
This line returns in the most cases DBNull instead of giving me the
new ID value of the inserted datarow.
What can be done to solve my problem?
Regards,
Norbert
.
- References:
- how to get the id of the inserted datarow?
- From: Norbert Pürringer
- RE: how to get the id of the inserted datarow?
- From: Fatih Isikhan
- how to get the id of the inserted datarow?
- Prev by Date: Re: is there some per-process-limit on memory in .net processes? is there any way to increase it? i keep getting System.OutOfMemoryException when my box has 8 gigs of unused memory.
- Next by Date: Re: Select and update
- Previous by thread: RE: how to get the id of the inserted datarow?
- Next by thread: SqlException and Violation of PRIMARY KEY constraint
- Index(es):
Relevant Pages
|
