Re: IDENT_CURRENT question
Create an output parm to store the identity
Code:
strSQL = "INSERT INTO MyTable (blah blah) ... set @output = @@identity"
ExecuteNonQuery (strSQL)
'TODO: get identity value from output parm
Re: IDENT_CURRENT question
Thanks wild_bill - in doing my own research on this since my post, I did see that there is an OUTPUT option to the INSERT statement as of SQL 2005, and it can be useful for this purpose.
However, I'm thinking that it is the SCOPE_IDENTITY function that I want to use (as it will return the last inserted value in a given connection/scope), as opposed to IDENT_CURRENT (which gets the last inserted value of a table regardless of scope).
As a general rule, I always do each SQL statement in its own DB connection (i.e.: get in - get it (or do it) - get out). So I'm thinking it would be best to this as follows:
CONNECT TO DB
EXECUTE THE INSERT
DO A "SELECT SCOPE_IDENTITY() AS TheNewID"
EXECUTE DATAREADER (and save the value)
CLOSE CONNECTION
Do you think that is appropriate?
Re: IDENT_CURRENT question
Not quite appropriate. Don't use a separate DataReader. Do use SCOPE_IDENTITY but either return the ID via an output parameter on the same Command or, if using a DataAdapter, just SELECT the ID straight back into the same DataRow.
Re: IDENT_CURRENT question
Thanks JM. With your remarks in mind, I refined my search, then found code suitable to adapt to my function "ExecuteIdentityInsert".
Coding style differences aside (as I recall from years back you are not a big fan of Hungarian), the idea of using a single command object has been implemented here.
The function would be called as follows:
Code:
Dim strSQL As String _
= "INSERT INTO MyTable(EmpFName, EmpLName) OUTPUT Inserted.EmpID VALUES('John', 'Doe')"
Dim intNewID As Integer = ExecuteIdentityInsert(strSQL, "EmpID")
The function itself, which uses a single SqlCommand object, is as follows:
Code:
Public Function ExecuteIdentityInsert(ByVal pstrSQL As String, ByVal pstrIDFieldName As String) As Integer
Dim objConn As New SqlConnection(DB_CONN_STRING)
Dim objCommand As New SqlCommand
objCommand.CommandType = CommandType.Text
objCommand.CommandText = pstrSQL
objCommand.Connection = objConn
Dim objParm As SqlParameter = objCommand.CreateParameter
objParm.SqlDbType = SqlDbType.Int
objParm.ParameterName = pstrIDFieldName
objParm.Direction = ParameterDirection.Output
objCommand.Parameters.Insert(0, objParm)
objConn.Open()
Dim objRdr As SqlDataReader = objCommand.ExecuteReader
objRdr.Read()
Dim intNewID As Integer = CInt(objRdr(pstrIDFieldName))
If objCommand.Connection.State = ConnectionState.Open Then objCommand.Connection.Close()
If objConn.State = ConnectionState.Open Then objConn.Close()
Return intNewID
End Function