By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,333 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,333 IT Pros & Developers. It's quick & easy.

Getting the identity column from my dataset

P: n/a
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.
Nov 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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/de...anidcrisis.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
"Jason L James" <ja***@no-spam.dive-master.org> wrote in message
news:40**************@news.newnet.co.uk...
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.

Nov 20 '05 #2

P: n/a
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))

"J. Alan Rueckgauer" <vo**@dev.nul> wrote in message
news:uY**************@TK2MSFTNGP12.phx.gbl...
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/de...anidcrisis.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
"Jason L James" <ja***@no-spam.dive-master.org> wrote in message
news:40**************@news.newnet.co.uk...
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.


Nov 20 '05 #3

P: n/a
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.

On Fri, 16 Jul 2004 11:01:55 -0400, "J. Alan Rueckgauer"
<vo**@dev.nul> wrote:
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))

"J. Alan Rueckgauer" <vo**@dev.nul> wrote in message
news:uY**************@TK2MSFTNGP12.phx.gbl...
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/de...anidcrisis.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
"Jason L James" <ja***@no-spam.dive-master.org> wrote in message
news:40**************@news.newnet.co.uk...
> 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.




Nov 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.