How To Get The Primary Key From The Field Of The Row I've Just Inserted
I need to insert a row of data and return the value of the primary key id of the row.
I thought that something like this would work
int Key = (int)command.ExecuteScalar();
where command is SqlCommand object.
It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.
View Complete Forum Thread with Replies
Related Forum Messages:
Inserted Records Missing In Sql Table Yet Tables' Primary Key Field Has Been Incremented.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails. I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record. And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View Replies !
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 product_ID = @productID
View Replies !
Setting Primary Key Of New Row Being Inserted
Not sure if this is the right place for this post or not, but I'll give it a try... If I am inserting a record into a table that uses a Primary Key, but not an Identity column, how do I insert the new primary key value? I started out with SQL just letting the Identity column increment itself for primary keys, but I cannot do that for this particular situation. If 'AddressID' is the primary key in the 'Address' table, could I do something like this: INSERT INTO Address (AddressID, Field1, Field2, ...) VALUES (**max value of primary key in Address table, plus 1**, 'field1data', 'field2data', ...); If that works, how would I make this part of the insert query: **max value of primary key in Address table, plus 1** ? I hope this question is clear and makes sense. If it's not, please let me know.
View Replies !
How To Prevent A Second Entry Being Inserted With Primary Key Value? (C#)
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database? My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e) 2 { 3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text; 4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue; 5 try 6 { 7 srcAddSkill.Insert(); 8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score."; 9 lblErrorMessage.Visible = true; 10 txtSkillName.Text = ""; 11 } 12 catch (Exception ex) 13 { 14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message; 15 lblErrorMessage.Visible = true; 16 }
View Replies !
Get The Last Inserted Uniqueidentifier Field
As you know we can get the last identity by using these functions:@@IDENTITY, IDENT_CURRENT( '[Table_Name]' ) And More.But if I set my primary key datatype to uniqueidentifier, I Can't get the last GUID inserted by SQL.Anyone have any idea?Reach me at my blog: http://ramezanpour.spaces.live.com
View Replies !
Clipping Data Inserted Into NText Field
I'm having a major problem. I'm executing an sql statement to insert data into an nText field. It's clipping the text though. I haven't seen any patterns yet. Example "Welcome to the show.", it would save "Welcome to t". And it's not all the time. Please Help. Thanks for your time, Randy
View Replies !
Trigger Problem With "inserted" And "deleted" On Text Field
I use SQL Server 6.5 I have create a trigger on UPDATE for history. This history send the old value and the new value in a table to follow all the modifications. Those values are "text datatype". When I execute the trigger (When I make a modification in the table), the two values are the same (the new value). If somone have a idea ... CREATE TABLE dbo.HELPDB ( ALMID int NOT NULL , MEMO text NULL , FIRSTAID text NULL ) GO CREATE TRIGGER trg_UpdateHelpDB ON dbo.HELPDB FOR UPDATE AS DECLARE @Id integer DECLARE @alm integer DECLARE @user varchar(20) DECLARE @almname varchar(24) DECLARE @usergroupproprid integer DECLARE @usergroupproprname varchar(30) DECLARE @oldFirstAid varchar(255) DECLARE @newFirstAid varchar(255) if(UPDATE(FIRSTAID)) begin select @alm = ALMID, @oldFirstAid = CONVERT(varchar(255), FIRSTAID) from deleted select @newFirstAid = CONVERT(varchar(255), FIRSTAID) from inserted Exec AMX.dbo.SpGetNewId 'HISTALM', @Id OUTPUT select @user = "testuser" select @almname = "testalmname" select @usergroupproprname = "testusergroupproprname" INSERT HISTALM (ID, ALMID, DATETIME, USERLOG, STATUS, ALMNAME, USERGRPPROPR,USERGRPOWNER, EVENTTYPE, OLDVALUE, NEWVALUE) VALUES(@Id, @alm, getdate(), @user, "First Aid", @almname, @usergroupproprname, "/", 0, @oldFirstAid, @newFirstAid) END GO CREATE TABLE dbo.HISTALM ( ID int NOT NULL , ALMID int NOT NULL , DATETIME datetime NOT NULL , USERLOG varchar (30) NOT NULL , STATUS varchar (25) NOT NULL , ALMNAME varchar (24) NOT NULL , USERGRPPROPR varchar (30) NOT NULL , USERGRPOWNER varchar (30) NOT NULL , EVENTTYPE int NOT NULL , OLDVALUE varchar (255) NULL , NEWVALUE varchar (255) NULL ) GO
View Replies !
Primary Key Field
Hi, I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc. I tried to find it out but it seems to be very complicated. Could anybody help me in this regards? Thanks in advance. Prasanna.
View Replies !
Bit Field As Primary Key
Hey guys. I just wanted to ask you a question? we are having a field declared as BIT and we want that field to be unique..We are not able to declare as a primary key..Is there anyway to declare as unique from the database point of view?..please help us out in this issue and i'd appreciate that. Andrew.
View Replies !
New Primary Key Field
Does anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
View Replies !
How To Get A New Value For A Primary Key Field
Hello, I have an app that is connected to SQL 2005 express edition. In the DB there is a table that has an auto incremental primary key What I want is, I want to get a new value for that auto incremental field before sending the new record's date to the DB to be stored. So I can display the new record's ID in my app's interface So is there something in SQL server like SEQUENCE in oracle. Thanks in advance.
View Replies !
Multi-field Primary Key
I have a table (table1) that has a bunch of fields....[field1][field2][field3][field4][field5]None of these fields are unique, but if I combine them, then they areunique.I know there is a way to make multi-field primary keys, but when I tryI get an that field1 is not unique, which I already know,How can I make a multi-field primary key?thanks
View Replies !
Using An Identity Field As Primary Key
I want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.
View Replies !
How To Auto Increment A Primary ID Field?
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
View Replies !
Duplicate Entry In A Primary Key Field
Hi everybody couldn't get through with saving my data on the table with two primary keys... my table structure is this pubidintUnchecked (primary key) pubchar(1)Unchecked publchar(1)Unchecked pubcodechar(2)Unchecked (primary key) a sample data is here pubid pub publ pubcode 1 a b ab 1 b b bb 2 a b ab 2 b b bb when i save this table modifying the pubid and pubcode as primary keys the following error displays... Unable to create index 'PK_PUBS3'. CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'. Could not create constraint. See previous errors. The statement has been terminated. what i understand is that on the primary key duplicates are not allowed how could i allow it? thanks
View Replies !
Automating Incrementation In Primary Key ID Field
I'm really puzzled with this. I am new to using MS SQL and need help with the primary key. When I was using Access, I would open the database table and just enter values for the various columns, each time a new column was begun, the computer would insert a unique number there. OK, so now I am using visual studio to work on a SQL database. I created a new table and the first field I specified was "ID", I then chose it as the primary key. For default value, I tried both auto number and auto increment. These don't seem to work, as when I type values into fields and try to move to the next row, having left the ID field blank, I get an error which reads "String or Binary data may be truncated". What is going on, what do I need to do so the field automatically populates with an ID number... Some research sort of led me to the concept of a "trigger" to do this. Is this the approach. I was able to r-click on the table and I saw an option for "create trigger" or "new trigger"... How do I learn to do this, is there a generic trigger to do an auto increment of a primary key ID field?
View Replies !
Insert Values In Primary Key Field
Hi, Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5. Thanks in advance for any reply.
View Replies !
Problem With Coping Primary Key Into Other Field In The Same Table
is there any way that i can copy primary key(PK) value to another field in the same table .. say my PK is MemberID i want to replicate that into other field say SortID at the same time when the primary key is incrementing. I'm a newbie in this field please pardon me if something is wrong in the way i'm asking ...please help me friends i'm struggling since a long time ... Thanks in advance .. savvy
View Replies !
Identity Primary Field In Merge Replication
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity. What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e On Server 1 - Values start from 1 then 2,3,4 etc etc Server 2 - 24001, 24002, 24003 etc etc Server 3 - 46001, 46002, 46003 etc etc Server 4 - 68001, 68002, 68003 My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc? Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app. cheers
View Replies !
Chosen Datatype For Primary Key Field And Performance Questions??
Hi there, I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system. One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype. An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes. Many tables aren't that big, so the values will fit in an int datatype. 1. Is iot a good option to change the decimals columns to an int column ? 2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ? 3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page? Thanks in advance, Greetz, Patrick de Jong
View Replies !
Autogenerating Numbers For A Primary Key Field, &&"studyId,&&" In A Table--but With A Few Twists.
I have a question on autogenerating numbers for a primary key field, "studyID," in a table€”but with a few twists. We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001). Here€™s the range of values we want our StudyIDs to be (this is to be our validation rule as well): 10001-19999 for Site 1 20001-29999 for Site 2 Your suggestions are VERY VERY WELCOME! THANKS!
View Replies !
Convert Composite Primary Key Into Simple Primary Key
Uma writes "Hi Dear, I have A Table , Which Primary key consists of 6 columns. total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist. may i convert Composite Primary key into simple primary key in thr table like this. Thanks, Uma"
View Replies !
Auto Incremented Integer Primary Keys Vs Varchar Primary Keys
Hi, I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key. For example: id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null] isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime. Regards Mike
View Replies !
4 Key Primary Key Vs 1 Key 'artificial' Primary Key
Hi all I have the following table CREATE TABLE [dbo].[property_instance] ( [property_instance_id] [int] IDENTITY (1, 1) NOT NULL , [application_id] [int] NOT NULL , [owner_id] [nvarchar] (100) NOT NULL , [property_id] [int] NOT NULL , [owner_type_id] [int] NOT NULL , [property_value] [ntext] NOT NULL , [date_created] [datetime] NOT NULL , [date_modified] [datetime] NULL ) I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id In this specific instance - property_instance_id will never be a foreign key into another table - queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row - Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns. What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table? Thanks Matt
View Replies !
Orw Is Not Inserted
while executing this command locally, ita working fine Insert into tblorderDetails (OrderID,ProductID,SofaPackageID,Quantity,UnitPrice,TotalOrd,ItemStatus ) values(1, 3915, 0, 1, 2049.00, 2049.00, 'PO') but when executing online, giving me following msg: (1 row(s) affected) Msg 207, Level 16, State 1, Line 1 Invalid column name 'Location16FreeStock'. and also data is not inserted in table.
View Replies !
Nothing Is Being Inserted
I will paste my code below. Â I inserted a breakpoint but nothing is being sent to the database and nothing came up when I ran it with the breakpoint. Â Can anyone tell me how to fix this?Protected Sub btn_addfriend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_addfriend.Click Dim add_friend_source As New SqlDataSource add_friend_source.InsertCommand = "INSERT INTO [Friends] ([UserName], [UserID], [IP], [AddedOn], [FriendName]) VALUES (@UserName, @UserID, @IP, @AddedOn, @FriendName)" detailsview_addfriend.DataSource = add_friend_source add_friend_source.DataBind() add_friend_complete.Text = "Success!" End SubThe "Success!" text is the only thing that seems to work properly...
View Replies !
Why Are "&" Inserted As "_"
I have a program to insert rows into a SQL Server table from an ASCII file. There appears to be some data mapping going on. For example: "J & J Smith" is imported as "J _J Smith". This doesn't happen on all servers. I can run it against my database and get the desired results. I looked into the Server Configuation. Under the Security tab there are Mapping options. It appears that you can map _ to something else, but I don't see where you can map & to anything.
View Replies !
Getting Last Inserted Value
hi, this is my spc: select top 1 u.userid, u.user_name, u.password, c.code_description as role_code, u.expiry_date, u.created_date, u.active, convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date, v.operation,h.user_name as updatedby from [usermaster] u inner join [codeMaster] c on 'sp'=c.code inner join [HRUser_developerlog] v on u.userid=v.inserted_id and v.operation='update' inner join [usermaster] h on v.userid=h.userid where u.userid = '3' order by v. user_date if i use it gives the v.user_date as fist modified date it is not giving last modified date. select * from HRUser_developerlog user_date operat userid ion 2007-01-25 14:28:17.000insert1 2007-01-24 13:02:18.093insert4 2007-03-03 11:30:29.310update2 2007-03-03 11:30:55.373insert3 2007-03-03 11:31:31.717insert26 2007-01-25 14:28:17.000insert3 2007-03-03 11:43:39.733update26 2007-03-03 11:48:04.543delete3 2007-03-03 14:26:22.420update3 2007-03-03 14:27:00.280update3 2007-03-03 14:27:12.013update2 2007-03-03 14:27:35.763update1 2007-02-08 14:28:17.030update2 2007-03-03 14:27:55.967update3 2007-03-03 14:29:18.827update3 2007-03-03 14:30:52.983update3 so it has to show 2007-03-03 14:30:52.983 but it shows the first updated id only 2007-03-03 14:26:22.420 please help me to get my need
View Replies !
How Return Inserted ID
Hi! I would like to get the last inserted ID in a sql 2005 table to a variable.My code is: SqlDataSource ds = new SqlDataSource();ds.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();ds.InsertCommandType = SqlDataSourceCommandType.Text;ds.InsertCommand = "INSERT INTO Products(SellerID, Price) OUTPUT INSERTED.ProductID VALUES(@SellerID,@Price)"; ds.InsertParameters.Add("SellerID", "Sony"); ds.InsertParameters.Add("Price", "123");string output = ds.Insert().ToString(); // it's return rowsAffected but I need productID (SCOPE_IDENTITY or @@IDENTITY )Thank you for your time and help :)
View Replies !
Need To Get The ID Of The Last Record That Was Inserted
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation. I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work. How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#. As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
View Replies !
How Do I Retreive Id Of Just Inserted Row?
I'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table. Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form. TIA Steve
View Replies !
Getting The Id Of The Row That I Inserted With SQL 2005
Hi,I use a Stored Procedure who works very well....INSERT INTO Computers (CategoryID, SubCategoryID, ......VALUES (@CategoryID, @SubCategoryID, .........But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.How can i do that? I code in VB.Thanks
View Replies !
Return Inserted Row
how do i insert a record, then return the inserted record back to VS (ASP + VB) to display? maybe just the ID will be enough. then i will do a select
View Replies !
DataSet - Inserted Row ID
I have a dataset that uses generated stored procedures to do its select, insert, update, delete. I am inserting a row to that dataset, and after the update, using the ID of newly created row. This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0) Any idea what happened / what I have to do to fix this? Thnx!
View Replies !
Return ID Of The Last Inserted Row
Hello, I want to return the ID of the last inserted row. I am using an ObjectDataSource and a FormView to perform the insert operation. Here is my presentation code: <asp:ObjectDataSource ID="odsOrders" runat="server" DataObjectTypeName="Auction.Info"InsertMethod="InsertOrder" SelectMethod="GetOrders" OnInserted="ReturnNewOrderID" OldValuesParameterFormatString="original_{0}" TypeName="Auction.Controller"> <SelectParameters> <asp:Parameter DefaultValue="00" Name="ModuleId" Type="Int32" /> </SelectParameters></asp:ObjectDataSource> <asp:FormView ID="fvAddOrder" runat="server" DataSourceID="odsOrders" DefaultMode="Insert" HorizontalAlign="Center" Width="100%"> <InsertItemTemplate>.... and my VB code: Protected Sub ReturnNewOrderId(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue) Response.Write("Test") End Sub and my Stored Procedure: ALTER PROCEDURE [dbo].[Auction_InsertOrder] (@ModuleID int,@FirstName nvarchar(50),@LastName nvarchar(50),@Email nvarchar(250),@Phone nvarchar(20),@Fax nvarchar(20),@DelAddress1 nvarchar(250),@DelAddress2 nvarchar(250),@DelTown nvarchar(50),@DelPostCode nvarchar(20),@DelCountry nvarchar(50),@InvAddress1 nvarchar(250),@InvAddress2 nvarchar(250),@InvTown nvarchar(50),@InvPostCode nvarchar(20),@InvCountry nvarchar(50),@AuctionName nvarchar(50),@Quantity int,@Price decimal(18,2),@PostCosts decimal(18,2))ASINSERT INTO Auction_Orders(ModuleID, FirstName, LastName, Email, Phone, Fax, DelAddress1, DelAddress2, DelTown, DelPostCode, DelCountry, InvAddress1, InvAddress2, InvTown, InvPostCode, InvCountry, AuctionName, Quantity, Price, PostCosts, DateEntered)VALUES (@ModuleID, @FirstName, @LastName, @Email, @Phone, @Fax, @DelAddress1, @DelAddress2, @DelTown, @DelPostCode, @DelCountry, @InvAddress1, @InvAddress2, @InvTown, @InvPostCode, @InvCountry, @AuctionName, @Quantity, @Price, @PostCosts, getdate())RETURN SELECT @@IDENTITY As NewOrderID I can insert the record into the database but the e.ReturnValue does not return anything. Thank you for your help/ Many thanks, Vincent
View Replies !
|
TRACKER
