how to get the primary key autoincrement id inserted in ADO.NET?

Hi all!

I have a great problem in my C# application. I'm using ADO.NET with ASA9 
..NET Data Provider.
I need to execute an insert statement of one row in a table of my database 
and I need to get the autoincrement primary key id just inserted.

I don't know if there is a good design patter to do that, but i try to get 
this in this manner.....

The table is defined as follows:

CREATE TABLE Utenti
(
   id_utente       SMALLINT NOT NULL DEFAULT AUTOINCREMENT,
   utente            VARCHAR(16) NOT NULL,
   password      VARCHAR(16) NULL,
   nome            VARCHAR(30) NULL,
   cognome       VARCHAR(30) NULL,
   note              VARCHAR(255) NULL,
   PRIMARY KEY (id_utente)
);

And, using an AsaCommand.ExecuteScalar method, I exec this statement:
    INSERT INTO Utenti(utente, password, nome, cognome, note) 
VALUES(?,?,?,?,?);SELECT @@IDENTITY;

When I call the AsaCommand.ExecuteScalar function I get an AsaException 
defined as follows:
    NativeError -807 "Host variables may not be used within a batch"

Where am I wrong? What does the exception mean? Is this method, the correct 
way to get what I want? If not, what is the correct one?

This is the code
------------------------------------------------------------------------------------------------------------------
public void Insert()
{
      string commandString = "INSERT INTO Utenti(utente, password, nome, 
cognome, note) " +
            "VALUES(?,?,?,?,?);SELECT @@IDENTITY";
      using( AsaConnection con = new 
AsaConnection(Helper.GetConnectinString(m_Connection)) )
      {
            using( AsaCommand insertCmd = new AsaCommand(commandString, 
con) )
            {
                  insertCmd.Parameters.Add("@Utente", AsaDbType.VarChar, 
16);
                  insertCmd.Parameters.Add("@Password", AsaDbType.VarChar, 
16);
                  insertCmd.Parameters.Add("@Nome", AsaDbType.VarChar, 30);
                  insertCmd.Parameters.Add("@Cognome", AsaDbType.VarChar, 
30);
                  insertCmd.Parameters.Add("@Note", AsaDbType.VarChar, 255);

                 con.Open();

                  foreach( IUser user in this )
                  {
                        insertCmd.Parameters["@Utente"].Value = 
user.UserName;
                        insertCmd.Parameters["@Password"].Value = 
user.Password;
                        insertCmd.Parameters["@Nome"].Value = user.Nome;
                        insertCmd.Parameters["@Cognome"].Value = 
user.Cognome;
                        insertCmd.Parameters["@Note"].Value = user.Note;
                        object o = insertCmd.ExecuteScalar();            // 
<= this generates an AsaException
                        user.ID = (short)(int)o;
                  }
            }
     }
}
------------------------------------------------------------------------------------------------------------------

Thanks all!

Magallo 


0
Matteo
3/9/2006 10:46:16 AM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

6 Replies
4628 Views

Similar Articles

[PageSpeed] 6

Matteo Galletti wrote:

> I have a great problem in my C# application. I'm using ADO.NET with ASA9 
> .NET Data Provider.
> I need to execute an insert statement of one row in a table of my database 
> and I need to get the autoincrement primary key id just inserted.

Execute select @@identity after the insert. This method is documented 
and works like a charm :).

> And, using an AsaCommand.ExecuteScalar method, I exec this statement:
>     INSERT INTO Utenti(utente, password, nome, cognome, note) 
> VALUES(?,?,?,?,?);SELECT @@IDENTITY;

Execute queries one-by-one, don't stuff 2 queries into the single request.

Dmitri.
0
Dmitri
3/9/2006 10:56:21 AM
> Dmitri wrote:

> Execute select @@identity after the insert. This method is documented and 
> works like a charm :).

Yes, it works, if I exec it inside Interactive SQL for example, it works 
fine, but it doesn't work in a ASACommand.ExecuteScalar ADO.NET call.

> Dmitri wrote:
> Execute queries one-by-one, don't stuff 2 queries into the single request.


No, I can't! If I exec the INSERT using the ASACommand.ExecuteNonQuery() 
method and THEN, I exec e SELECT @@IDENTITY I can't be sure that the 
@@IDENTITY value returned by the select is the correct one or it comes from 
a different insert of the same or even another table. This is not the 
correct way. Is for that reason that I use the tow concatenated statements 
INSERT and SELECT.


Magallo 


0
Matteo
3/9/2006 11:51:33 AM
Matteo,

There is a function Get_Identity() which will return a reserved identity 
value that you can use in your Insert.  I'm not using ADO.Net, but I've used 
this SQL Anywhere function for a couple of years now without any problems. 
This function is connection specific.

Regards,

Michael Gould



"Matteo Galletti" <m.galletti@eldes.it> wrote in message 
news:441016c5$1@forums-1-dub...
>> Dmitri wrote:
>
>> Execute select @@identity after the insert. This method is documented and 
>> works like a charm :).
>
> Yes, it works, if I exec it inside Interactive SQL for example, it works 
> fine, but it doesn't work in a ASACommand.ExecuteScalar ADO.NET call.
>
>> Dmitri wrote:
>> Execute queries one-by-one, don't stuff 2 queries into the single 
>> request.
>
>
> No, I can't! If I exec the INSERT using the ASACommand.ExecuteNonQuery() 
> method and THEN, I exec e SELECT @@IDENTITY I can't be sure that the 
> @@IDENTITY value returned by the select is the correct one or it comes 
> from a different insert of the same or even another table. This is not the 
> correct way. Is for that reason that I use the tow concatenated statements 
> INSERT and SELECT.
>
>
> Magallo
> 


0
Michael
3/9/2006 12:14:22 PM
Michael Gould wrote:

> There is a function Get_Identity() which will return a reserved identity 
> value that you can use in your Insert.  I'm not using ADO.Net, but I've used 
> this SQL Anywhere function for a couple of years now without any problems. 
> This function is connection specific.

@@identity _is_ connection-specific, too.

Dmitri.
0
Dmitri
3/9/2006 1:06:46 PM
Matteo Galletti wrote:

>> Execute queries one-by-one, don't stuff 2 queries into the single request.
> No, I can't! If I exec the INSERT using the ASACommand.ExecuteNonQuery() 
> method and THEN, I exec e SELECT @@IDENTITY I can't be sure that the 
> @@IDENTITY value returned by the select is the correct one or it comes from 
> a different insert of the same or even another table. This is not the 
> correct way.

It _is_ the correct way.

> Is for that reason that I use the tow concatenated statements INSERT and SELECT.

Concatenation of statements isn't going to change anything - statements 
are executed serially anyway.

Dmitri.
0
Dmitri
3/9/2006 1:08:04 PM
Matteo Galletti wrote:
> 
> No, I can't! If I exec the INSERT using the ASACommand.ExecuteNonQuery() 
> method and THEN, I exec e SELECT @@IDENTITY I can't be sure that the 
> @@IDENTITY value returned by the select is the correct one or it comes from 
> a different insert of the same or even another table.

Re-read the documentation for @@IDENTITY.  It is guaranteed to be the 
value of the last autoincrement used for the current connection.

Using two separate but subsequent database calls (one for INSERT, the 
next for SELECT @@IDENTITY), guarantees that you are getting the value 
used for the INSERT.

greg.fenton
-- 
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
0
Greg
3/9/2006 3:56:48 PM
Reply:

Similar Artilces:

ASA9: how to get the primary key autoincrement id inserted in ADO.NET?
Hi all! I have a great problem in my C# application. I'm using ADO.NET with ASA9 ..NET Data Provider. I need to execute an insert statement of one row in a table of my database and I need to get the autoincrement primary key id just inserted. I don't know if there is a good design patter to do that, but i try to get this in this manner..... The table is defined as follows: CREATE TABLE Utenti ( id_utente SMALLINT NOT NULL DEFAULT AUTOINCREMENT, utente VARCHAR(16) NOT NULL, password VARCHAR(16) NULL, nome VARCHAR(30) NULL...

Duplicate primary key insertion by adding a second to time ASP.NET + ADO.NET + c#
Hi  I am reading records from SQL Server Database and inserting into Oracle Table. Just now it is working good as explained in below algorithm.  Read records from SQL Server Insert into Oracle Table  I am inserting two columns(name, DOB) from SQL Server into Oracle Table(name, DOB) where those two columns are composite primary key in Oracle DB. I do get duplicate records from SQL Server DB as explained below: First record: name: billgates DOB: 21/09/08 12:10:10 AM Second record:  name: billgates DOB: 21/09/08 12:10:10 AM  When I try to inse...

Get value of primary key after INSERT statement using Web Matrix (VB.NET)
I am using the following function developed using Web Matrix to insert a record into a table when the user clicks the submit button.  How can i get the "primary key" or value of the inserted record back to the application.  The following is my insert statement:Sub btnSubmit_Click(sender As Object, e As EventArgs)      CreateMyRecord("98", "UU1")       '==> need the primary key of the inserted record here.......End SubFunction CreateMyRecord(ByVal streetNumber As Integer, ByVal authorityRef As String) As Integer&nbs...

LINQ- how to get the ID(primary key) of the object has been Inserted or Updated without using store procedure
Hi all, I have tried to insert and update objects to database. I use same method named "Save" for both of them in the Data Access Layer. In Save method, I intend to use variable "Result" to hold the return value which is ID of the object has been updated or inserted. However, I do not know how to get this value if I do not use store procedure. I have 2 questions:   + Does anyone has any idea about this problem? I set auto-increase for SQL server to generate primary key + Is the way I implement the Insert and Update operation alright? (I thin...

Get Primary Key after Insert
Hello, I have a small database used to helpdesk ticketing. The Primary Key in the DB is the "Ticket Number." When users submit an online form, I'd like to Insert the information and retrieve the ID of the record that was just written. I'm assuming the INSERT programming is pretty standard, but can anyone help me with how I could get the new ID back? Thanks!! Jason return @@IDENTITY at the endChris Paterra Take a look @ HOW TO: Retrieve an Identity Value from a Newly Inserted Record from SQL Server by Using Visual Basic .NET HTHSushila Bowalekar PatelVisual ASP/A...

Insert : how to get the primary key
Hi, I'm currently writing a Perl script which insert lots of data into a database. When I insert a row in a table, I need to get the PRIMARY KEY which has been affected to this table, because I will use it to reference this record in an other table. Currently, I issue a INSERT, and then a SELECT statement to find the Primary Key. But since the script is converting a 20000 records database, it's very slow. My question is : is there a way, when issuing a INSERT statement (using prepare and then execute) to directly get the Primary Key affected to the newly created row withou...

autoincrement database key-id OR manually generated key-id
some of my friends suggested me not to use autoincrement id as primary key value but rather generate the id manually using a formula. since it seems like you guys in sybase doesn't use autoincrement id too, I am wondering which alternative is better, using autoincrement id or generating the id manually. what are the pros and cons of each? best regards, rony Well first and foremost, the purpose of a key is to be a unique identifier. So this is the main thing we must accomplish. When we use autoincrement, we are counting on the database itself to ensure that we receiv...

Getting Primary key after insert a query
I would like to get the id after insert a record in the vb.net  Dim addTagMSQL As SqlCommand  addTagSQL = New SqlCommand("insert into Tabs (TabOrder, PortalID, TabName, IsVisible, ParentID, Level, DisabledLink, Title, IsDeleted, TabPath) values (3,0,'newsletter','true', 36,1,'False','Newsletter sent','False', '//Home//newsletter') return @@identity", DBConn)  addTagSQL.ExecuteScalar()  Dim param As SqlParameter = addTagSQL.Parameters.Add("RETURN_VALUE", SqlDbType.Int)  Response.Write(param.Value)  I run the page and the system said i cannot...

SqlCommand Get the Inserted Primary Key
Hi, I would like to know how I can retrieve the ID (Primary key) of the row I just inserted with a sqlcommand(text not stored procedure). Thx Loads of ways of doing that here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=54 Regards Mike [MVP - ASP/ASP.NET]My site Here's an example for you: ASPX <%@ page autoeventwireup="true" codefile="RetrieveLastIdentityFromSqlQueryUsingADO.aspx.cs" inherits="HowTo_RetrieveLastIdentityFromSqlQueryUsingADO" language="C#" masterpagefile="~/MasterPages/Default.master" ...

Getting autoincrement primary key value
I have a window with 2 DWs (dw_1 & dw_2) in it. I enter information into both DWs and then save the records. The first DW (dw_1) has an autoincrement value set as default in the table which is the primary key. dw_2 also has a long primary key which should be in sync with dw_1 when it saves. I update and commit dw_1 successfully. Then I attempt to get the primary key value from dw_1 from the value in the cloumn. It returns NULL and I my update and commit for dw_2 fails because I need a non NULL value. I noticed on an earlier question in this group that the column shoul...

getting the primary key of the newly inserted row
i have a nested gridview that inserts in record in two table (ORIGINALESTIMATE(CostingID PK), ESTIMATE DETAILS(Details PK, CostingID FK) now my problem is that it succesfully inserts record to originalestimate table and it stops there. it does not go to the second insert statement and does not get the value of the primary of the newly inserted row.. can please look at my code below and tell me how can i do this... protected void BtnSave_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBSystemConnection...

Getting a primary key from a DB just inserted into
Heya, i've tried to do some searching on this problem, but it's kinda vague so it's hard to find a good way to search for it, so any info would be greatly appreciated... The concept is that i've got a form page with registration information from the user, but the registration has to be approved by another person. The user puts in all of their info, and it goes into the database with a specific registration number (regno) that is generated as the primary key. That's all fine and good... The question is, how can I pull up that regno and send it via email to the person that is to appro...

Insert a record and get the primary key returned
I would like to insert a record and have the primary key that is generated by the database returned to me. My code looks like this <code> Sql = “insert into table (stuff) values (stuff)” cnCapMaster.Open() sqlcmdMaster.CommandText = sql sqlcmdMaster.ExecuteNonQuery() cnCapMaster.Close() <code/> The database is sql server and it is automatically creating the primary key. I want to insert a row into the master table then using the primary key inset recorders into the detail table. Thanks, Bryan Sql = “insert into table (stuff) values (stuff);...

get primary key of last inserted record
 Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement  INSERTINTO products     ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT  class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo  FROM productsWHERE ...

Web resources about - how to get the primary key autoincrement id inserted in ADO.NET? - sybase.sqlanywhere.general

→ FCModel proposal: remove AUTOINCREMENT support
Do you use FCModel, my alternative to Core Data? I’m proposing a substantial feature removal, and I’d love for you to join the discussion on ...

FCModel proposal: remove AUTOINCREMENT support →
Do you use FCModel, my alternative to Core Data? I’m proposing a substantial feature removal, and I’d love for you to join the discussion on ...

PDP-8 - Wikipedia, the free encyclopedia
A PDP-8 on display at the Smithsonian 's National Museum of American History in Washington, D.C. . This example is from the first generation ...

The TDD loop for Android Content Providers
... db ) { db. execSQL ( "CREATE TABLE " + CREDENTIALS_TABLE_NAME + " (" + CredentialsColumns. CREDENTIAL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," ...

xssinterface and Google Gears
I mentioned the cross domain library, xssinterface , created by Malte Ubl on Ajaxian the other day. His library abstracts on top of postMessage() ...

Saving to a SQLite database in your Android application
This is the fourth post in my series about saving data in Android applications. Here are the other posts : Introduction : How to save data in ...

Archives: October 2008
Dabble has always given you great ways to get your data out via our computer readable exports, but it hasn't been as easy as we'd like for you ...

Microsoft Dynamics NAV Community – Support, How To, and Help
Find technical support for Microsoft Dynamics NAV, including: forums, blogs, downloads, training, documentation, and Microsoft assisted support. ...

Android Mobile: Following In the Windows Footsteps
FireEye discovered an email spam campaign, currently ongoing, which is dropping the well-known Android malware Android FakeDefender. Looking ...

Recent Links Tagged With "autoincrement" - JabberTags
JabberTags Discover the best the web has to offer H ome L og In A bout C ontact Us popular public links > > autoincrement Create GUID for used ...

Resources last updated: 1/18/2016 2:09:32 PM