Getting the identity column from my dataset

[Jason L James]

Hi all,

I recently wrote a vb.net app using oledb to
an access database. When I inserted new
rows in my datatable the identity column
was automatically created. This app used
an un-typed dataset.

My current app is using sqlClient and a typed
dataset that I created by exporting an xsd
file from a small app that loaded the db schema.
This application does not automatically provide
me with the identity column, so I can't write the
record do db and use its primary key without
first updating the datasource and repopulating
the dataset!!!

How can I get the identity of the newly created
record into the dataset so that I can manipulate
the record via its PK?

Anyone have any thoughts on this?

Thanks,

Jason.

 

[J. Alan Rueckgauer]

If I'm understanding you correctly, you are trying to get the IDENTITY
assigned to a just-INSERTed row. There's an article in the KB you might
find helpful:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp

(make sure it's all one line if you have to copy/paste it into your browser)

If you're using stored procedures to do your inserts, you can declare an
output parameter for your command object that captures the result:

CREATE PROCEDURE MyStoredProc_AddNewRec
(
@ColumnValue int,
@IDAssigned int OUTPUT
)
AS
INSERT INTO MyTable
( some_column )
VALUES
(@ColumnValue)

SELECT @IDAssigned = SCOPE_IDENTITY()
GO

And your command object would be something like

cmdInsert = New SqlCommand("MyStoredProc_AddNewRec", myConnection)
With cmdInsert
.Parameters.Add(New SqlParameter("@ColumnValue", SqlDbType.Int)
.Parameters("@ColumnValue").Value = SomeInteger
.Parameters.Add(New SqlParameter("@IDAssigned", SqlDbType.Int)
.Parameters("@IDAssigned", SqlDbType.Int, 4, ParameterDirection.Output)
' the connection has to be open already
.ExecuteNonQuery
intTheNewID = CInt(.Parameters("@IDAssigned").Value)
End With



Alan

 

[J. Alan Rueckgauer]

CORRECTION (I hit send before I fixed a typo):

TYPO:
.Parameters.Add(New SqlParameter("@IDAssigned", SqlDbType.Int)
.Parameters("@IDAssigned", SqlDbType.Int, 4, ParameterDirection.Output)

SHOULD BE:
.Parameters.Add(New SqlParameter("@IDAssigned", SqlDbType.Int, 4,
ParameterDirection.Output))

 

[Jason L James]

Alan,

thanks for the info.

These seems to work fine. I can then insert the
returned value into the PK field in the dataset
so that I can reference the row. Once I have
inserted the returned value into the PK the row
is considered updated by the data adapter!

Do I need to add an update SP to record this action?

The updated ds is bound to a datagrid. How can
I refresh the grid to include the new record based on
the sort order of the data table?

Thanks,

Jason.