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.
3  6990 
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_Ad dNewRec
(
@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("MyS toredProc_AddNe wRec", myConnection)
With cmdInsert
.Parameters.Add (New SqlParameter("@ ColumnValue", SqlDbType.Int)
.Parameters("@C olumnValue").Va lue = SomeInteger
.Parameters.Add (New SqlParameter("@ IDAssigned", SqlDbType.Int)
.Parameters("@I DAssigned", SqlDbType.Int, 4, ParameterDirect ion.Output)
' the connection has to be open already
.ExecuteNonQuer y
intTheNewID = CInt(.Parameter s("@IDAssigned" ).Value)
End With
Alan
"Jason L James" <ja***@no-spam.dive-master.org> wrote in message
news:40******** ******@news.new net.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.
CORRECTION (I hit send before I fixed a typo):
TYPO:
.Parameters.Add (New SqlParameter("@ IDAssigned", SqlDbType.Int)
.Parameters("@I DAssigned", SqlDbType.Int, 4, ParameterDirect ion.Output)
SHOULD BE:
.Parameters.Add (New SqlParameter("@ IDAssigned", SqlDbType.Int, 4,
ParameterDirect ion.Output))
"J. Alan Rueckgauer" <vo**@dev.nul > wrote in message
news:uY******** ******@TK2MSFTN GP12.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_Ad dNewRec
(
@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("MyS toredProc_AddNe wRec", myConnection)
With cmdInsert
.Parameters.Add (New SqlParameter("@ ColumnValue", SqlDbType.Int)
.Parameters("@C olumnValue").Va lue = SomeInteger
.Parameters.Add (New SqlParameter("@ IDAssigned", SqlDbType.Int)
.Parameters("@I DAssigned", SqlDbType.Int, 4,
ParameterDirect ion.Output) ' the connection has to be open already
.ExecuteNonQuer y
intTheNewID = CInt(.Parameter s("@IDAssigned" ).Value)
End With
Alan
"Jason L James" <ja***@no-spam.dive-master.org> wrote in message
news:40******** ******@news.new net.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.
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("@I DAssigned", SqlDbType.Int, 4, ParameterDirect ion.Output)
SHOULD BE:
.Parameters.Add (New SqlParameter("@ IDAssigned", SqlDbType.Int, 4,
ParameterDirec tion.Output))
"J. Alan Rueckgauer" <vo**@dev.nul > wrote in message
news:uY******* *******@TK2MSFT NGP12.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_Ad dNewRec
(
@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("MyS toredProc_AddNe wRec", myConnection)
With cmdInsert
.Parameters.Add (New SqlParameter("@ ColumnValue", SqlDbType.Int)
.Parameters("@C olumnValue").Va lue = SomeInteger
.Parameters.Add (New SqlParameter("@ IDAssigned", SqlDbType.Int)
.Parameters("@I DAssigned", SqlDbType.Int, 4,
ParameterDirec tion.Output) ' the connection has to be open already
.ExecuteNonQuer y
intTheNewID = CInt(.Parameter s("@IDAssigned" ).Value)
End With
Alan
"Jason L James" <ja***@no-spam.dive-master.org> wrote in message
news:40******** ******@news.new net.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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Stephen Ritchie |
last post by:
I have a dataset that contains two tables - Parent and Child. The key of the
Parent is called ParentID and is an identity column in SQL Server. The key of
the Child is two columns called ParentID - foreign key defined as INT to
Parent table - and ChildID that in itself is an identity column. I have used
the designer to create a relationship within the dataset for the two tables.
I have set the dataset so the ParentID column starts at -1...
|
by: Shahar |
last post by:
Hi
I need to get a field name 'ID'(that is an auto-number field) right
after I add a new row to table, it's work like that:
myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels
int iId = (int)myCommand.ExecuteScalar();
|
by: WhiteEagl |
last post by:
Hello,
I would need some help with this identity column problem in
SQLServer.
I have a database with two tables. The Parent table has an Identity
column.
Parent (ParentID IDENTITY, Name)
Child (ChildID, Name, ParentID)
|
by: dusty |
last post by:
Hi, I'll try to simplify the problem:
I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work connection-less, so I made my dataset where I populated a table with the data from the TestTable. When I insert a new row with the .NewRow() method, the identity column value is the next value available. For instance: if the last row had the value of 105,...
|
by: Richard |
last post by:
In normal asp i used
---------
objRS.Open "tbl_Nieuws", objConn, 1, 3
objRS.AddNew
objRS.Fields("N_Datum") = FormatDateTime(Now(),2)
objRS.Fields("N_Title") = ReplaceHTML(Upload.Form("title"))
objRS.Fields("N_Intro") = ReplaceHTML(Upload.Form("intro"))
objRS.Fields("N_Body") = ReplaceHTML(Upload.Form("body"))
| | |
by: gemel |
last post by:
I am developing an application that uses SQL 2000 as my source of
images. I have successfully created the code to load the images onto
the SQL Server and also to retrieve the images into a dataset. I
tested the application by populating a Dataset with the images from
SQL Server and rendered just one of the images by using a bitmap and
inserting the resulting stream into the response stream as a Jpeg
format.
My next move was to bind this...
|
by: WhiskyRomeo |
last post by:
Since I can't get answer from the author, can someone address this?
In this article . . .
http://msdn.microsoft.com/msdnmag/issues/04/05/DataPoints/default.aspx
In the "The Transaction and Sequence" paragraph, the author makes this
statement:
"The row is left with different original and current values so that when I
merge the delta DataSet with the originating DataSet, they will sync with
|
by: Neeraj |
last post by:
Hi all.
I have stuck at a strange point.in Database some master table have
identity column and some havenot. Then How can i know at runtimes that
which table have identity column.
According to this i have to write query string.
I got dataset from table.
and
write
ds.Tables.Columns.AutoIncrement
|
by: Curtis Rutland |
last post by:
OK, here's what I'm doing. I have a SQL Server 2005 table with an auto-number Identity column that I will be inserting to. I used the DataSet designer to add a table adapter to my DataSet to which I want to add an insert command.
Now, I want to know if there is some way to retrieve the number that was inserted into the Identity field. I know that if I were writing the update command manually I could set a parameter to SCOPE_IDENTITY(), but...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
