DataSource Controls :: Obtain Inserted Table Row Identity With An Auto Increment Key
Feb 18, 2010
I am using LINQ to store and retrieve data from tables with a clustered index key. However, I also need to know how to obtain an inserted table row identity with an auto-increment Primary key. The "Go To Definition" feature for my data context of dataClassesDataContext provides the following information:
[System.Data.Linq.Mapping.DatabaseAttribute(Name="EFMDB")]
public partial class DataClassesDataContext : System.Data.Linq.DataContext
I am asking for the C# code that is needed in a button click event handler that will insert a row of data in a table named "Fields". It only consists of an auto-incrementing primary key column (FieldID), a string column (Phrase) and a null defaulted variable column.
i have a question, suppose i have a table 'tblImage' which have 3 rows of data and my auto increment is not +1.
imageId imageName 1 a.gif 2 b.gif 4 c.gif
i want to query '2' using LINQ C# to the table in MS SQL and show the record which i have done easily BUT now i want to get the next auto incremented value to make Next button on the sane web form, which is in this case is '4' and i also want the previous imageId as well.
I have an SQL Server table named "Fields" with an autoincrement identity column "FieldID" as the primary key, an nchar column "Phrase" that textbox entries are to be stored in from a web page, and another column that is defaulted to null. The following is the recommended example on how to use Scope_Identity() to get a newly inserted table row identity value:
//C# string query = "Insert Into Categories (CategoryName) Values (@CategoryName);" + "Select Scope_Identity()"; int ID; string connect = @"Server=.SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" + "Database=Northwind;Trusted_Connection=Yes;"; using (SqlConnection conn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@CategoryName", Category.Text); conn.Open(); ID = (int)cmd.ExecuteScalar(); } }
Obviously, this example uses a Categories Table in a Northwind database, which I do not have. So I am confused by how to adapt this example for my application. The following is my data context:
DataClassesDataContext db = new DataClassesDataContext();
Could you explain how I can adapt the example for my "Fields" table in my "EFMDB" database?
I'm trying to get an IDENTITY column to auto-increment with each new row inserted, so that the field (userid in this case) can be used as a unique identifier.
However, I'm getting this error:
[Code]....
My code looks like this:
[Code]....
I don't understand - if I have to explicitly insert something into my auto-increment column, how do I know what to insert? Isn't the point of an auto-increment column that it's... auto?
I have a detailsView that I use for Insert. Once a record is Inserted, I need to get the new idenetity value.
Is there some function that grabs the inserted identity value? Or do I need to query it somehow?
By the way, the reason I'm doing this is the user is inserting a record into a 'general' list that is being applied to a 'specific' list - example, an employee can select a vendor from a list, but the vendor isn't listed so the employee adds the vendor in the vedor list and while the new vendor is inserted, the vendor info is also applied to the new order record.
I want to insert a complete row with automatic increment and the PartsNo should be the same as the PartsNo from the PartsMaster table.
The medialink should be the PartsNo + '-2.jpg' The mediadescription is for example 'image2' The CatalogCode should be 'catalog' and the sorting code should be '0'
From The partsMaster table I Just need the PartNo So I can add this to the PartMedia Table. The PartNo is the foreign key in the PartMedia table.
The following I got so far but no luck
insert into dbo.PartsMedia (PartNo,MediaLink,MediaDescription,CatalogCode, SortCode) values (dbo.PartsMaster.PartNo, PartsMaster.PartNo+'-2.jpg','image2', 'catalog','0')
I have this image upload code...that works....but I need a code to get the Id of the image that i just uploaded..
Protected Sub ImageButton_AddImage_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton_AddImage.Click Dim IntLength As Integer Dim ArrContent As Byte()
I am trying to get the @@Identity from a table by using a stored procedure called by my asp.net website. I am using a datareader to do this but get an error as it tells me it cannot convert the returned value to getint. Here is my code. What should I be using instead of getint?
Dim myReader = cmd.ExecuteReader() While (myReader.Read()) intID = myReader.GetInt32(0) End While myReader.Close()
This would seem to be quite a trivial task, however I am having trouble retrieving the identity of the record inserted using the detailsview.insert() in the code behind. It would seem that detailsview.DataKey.Value would contain this value, but it is null after the insert is performed.
I have an ID column (int) in my table which also needs to be a unique key.
last time I worked with a database (BD2), I set this as an autoIncrement feild and it would automaticaly 'deal with it' (ie, maintain the last used value somewhere and automaticaly used that value (after incrementing it) for any new row on the table).
How do I handle this in ASP.NET. Especially, as I try to add a new line based on form content when the user presses on a button (C#).
i am using one text box in New registration page. when ever user clicks on New registration button, The text box of record number should be auto incremented.
I use SQL Server and when I create a new table I make a specific field an auto increment primary key. The problem is some people told me making the field an auto increment for the primary key means when deleting any record (they don't care about the auto increment field number) the field increases so at some point - if the type of my field is integer for example - the range of integer will be consumed totally and i will be in trouble.
So they tell me not to use this feature any more.The best solution is making this through the code by getting the max of my primary key then if the value does not exist the max will be 1 other wise max + 1.Any suggestions about this problem? Can I use the auto increment feature?I want also to know the cases which are not preferable to use auto increment ..and the alternatives...note :: this question is general not specific to any DBMS , i wanna to know is this true also for DBMSs like
i was given a form in which empid is varchar and i m supposed to auto increment it using a function and calling it ....i have never done this. plz help me out the way .
