Hi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it.
The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false".
When the page is viewed, I insert a record into the dB:
Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)
conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;")
And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1".
The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function. Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT AS Insert INTO TM_TalentInvites ( TalentID ) Values ( @TalentID ) SELECT @@IDENTITY AS TalentInviteID RETURN @@IDENTITY
And here is my function - public static int addTalentInvite(int talentID) { // Initialize SPROCstring connectString = "Data Source=bla bla"; SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn); cmd.CommandType = CommandType.StoredProcedure; // Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID); conn.Open();int talentUnique = (int)cmd.ExecuteScalar(); conn.Close();return talentUnique; } Any help you can give me will be greatly appreciated thanks!
I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.
That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.
As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?
When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".
Hello all,I have a sqldatasource that inserts data to the database but I want to get the ID from an Identity field after I am done inserting the data. How can I best do that? I'm not using a stored procedure to do this. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:onepduConnectionString %>" InsertCommand="INSERT INTO [tblaccounts] ([fname],[lname], [address], [city], [state], [zip], ,[phone],[credits]) VALUES (@fName,@lname, @address, @city, @state, @zip, @email, @phone, @credits)> <InsertParameters> <asp:FormParameter Name="fname" FormField="FirstNameTextBox" Type="String" /> <asp:FormParameter Name="lname" FormField="LastNameTextBox" Type="String" /> <asp:FormParameter Name="address" FormField="AddressTextBox" Type="String" /> <asp:FormParameter FormField="cityTextBox" Name="city" Type="String" /> <asp:FormParameter FormField="stateTextBox" Name="state" Type="String" /> <asp:FormParameter FormField="zipTextBox" Name="zip" Type="String" /> <asp:FormParameter FormField="emailTextBox" Name="email" Type="string" /> <asp:FormParameter FormField="phoneTextBox" Name="phone" Type="string" /> <asp:Parameter Name="credits" DefaultValue=0 /> </InsertParameters> </asp:SqlDataSource>
I am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7
I am inserting a record by calling a stored procedure in my asp.net code. I need to return the identity field from the insert to use elsewhere in my code. I have found many things regarding this but nothing has worked.
Here is the last part of my stored procedure. BTW, I have added Scope_Identity to the end of my sp and when I open my sp up, it is not there...not sure what that is about. I guess my main problem is what command do I use in ASP.net to execute the sp AND hold my returned value...
Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot my sproc: CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO
class file public class Cotacoes { public int codigoCotacao; } public class CotacaoAtualiza { public Cotacoes cotacoes = new Cotacoes(); public CotacaoAtualiza() { } public void AdicionarCotacao( ... ) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]); SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection); myCommand.CommandType = CommandType.StoredProcedure; ... SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4); paramCodigo_cotacao.Direction = ParameterDirection.Output; myCommand.Parameters.Add(paramCodigo_cotacao); ... myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(); while(result.Read()) { this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"]; } myConnection.Close(); }
calling into code-behind file: CotacaoAtualiza ca = new CotacaoAtualiza(); Cotacoes cotacoes = ca.cotacoes; Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
I have a table tblnetwork on which identity property is defined on column networkid.
When I issue the following command new record is getting inserted into tblnetwork and identity column is getting incremented properly. But, I am not able to get the @@identity value It is returned as NULL.
I have a createStudent SProc. When this is called it calls a createContact SProc. This in turn calls a Create Address Stored procedure. Now when I create the address, an Identity column automatically creates a new Identity for me. I need to return this value back to the calling stored procedure. I know how to return a value and all, however, how to I get the identity just entered (the corresponding identity) reliably? I know that select MAX <Identity> could suffice in this case, but is flawed as if someone was to add another object near the same time we could get the wrong identity returned...
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.
Hello, I have a C# application that adds records to a SQL Server database using a query something like this one:
INSERT INTO table_name (first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ; SELECT SCOPE_IDENTITY() AS [Scope_Identity]
This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records.
(The table I'm adding to does have a autonumbered key field)
i recently found a little error in a stored procedure that was included in a project handed over to me....
the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows
IF @@ERROR>0 BEGIN SELECT @int_InterventionID = 0 RETURN @@ERROR END ELSE BEGIN SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned RETURN 0 END
i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)
so i changed to...
IF @@ERROR>0 BEGIN SELECT @int_InterventionID = 0 RETURN @@ERROR END ELSE BEGIN SELECT @int_InterventionIDReturned = @@IDENTITY SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned RETURN 0 END
it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??
how can i ensure that i always get the timestamp of the record it has just inserted
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I'm using SQL-MSDE and have a table defined with a 'identity seed' column that automatically gets assigned when a record is added (I do not load this value). This column is also my KEY to this table. I'm using INSERT to add a record. Is there a way to return this KEY value after doing the INSERT?
Hi,I've got a stored procedure that's inserting data into a sql database fine. The only problem is that I'm not sure how to read back the value of the auto increment field that was just generated by the insert (e.g the id field). Any help appreciated.
so I know how to use tsql to print a value in just the query, but when I insert a column, I want it to return the value of the ID that was just created. I don't know how to do this and send the value back to asp.net so that I can move on with that value. Can any body help me with this? this is what I have so far. thanks for the help!@title varchar(40) = null,
I am trying to insert a record to based on the source below, however the GUID of GiftOcc_ID is being returned as zero's so the first record can be added but as it is defined as the primary Key and uniqueidentifier the next record fails with a duplicate entry. Basically how do I ensure that the GUID is created and not nulls. As you can see I am trying to use Newid() which I have inserted as a default value but that does not work. Also as it is a unique identifier the "is identity" is not available
Protected Sub CreateGiftOccasion(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Try Dim null As New Nullable(Of Integer) Dim da As New DataSet2TableAdapters.Gift_OccasionTableAdapter Dim GiftOcc_ID As Guid da.Insert(newid(), Occ_Type_Text.Text, Occ_Desc_Text.Text, Calendar1.SelectedDate, Calendar2.SelectedDate, 1) Catch ex As Exception
Help! I am importing a large comma delimited text file into an existing table useing the BULK INSERT command. The table is 4 colums (char16, char16, varchar50, char1). The first 100 or so lines go in without an error. then, I recieve an error stateing that an entry is too long for the field in the database, and kicks me out. The entry is 50 characters, which is allowed. Any ideas why this would happen?
I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration. Thank you in advnace for any help on this! INSERTrequest ( emp_id, request_submit_date, request_start_date, request_end_date, request_duration, request_notes, time_off_id ) Select@emp_id, GETDATE(), @request_start_date, @request_end_date, 1 + DATEDIFF(day, @request_start_date, @request_end_date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date), @request_notes, @time_off_id
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob INsert into b values(1,1,2,2) select 0
(this is a cut up version of the sp just to show about the way it is formated) Please help.
PROBLEM: The SQL insert trigger code below is returning incorrect results. In some cases the results returned are from entirely different fields than those specified as the source field in the SET statement. For instance the value returne for the Price_BeforeAdj field does not = 20000000? It returns a NULL. See code below.
OFFENDING CODE:
ALTER TRIGGER [dbo].[xcti_WIPAdjustments_I] ON [dbo].[budxcWIPAdjustments] AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE budxcWIPAdjustments
I am experimenting with using CDC to track user changes in our application database. So far I've done the following:
-- ENABLE CDC ON DV_WRP_TEST USE dv_wrp_test GO EXEC sys.sp_cdc_enable_db GO
-- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TEST USE dv_wrp_test
[Code] ....
The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
if exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4) drop procedure dbo.spdv_AVAUpdate
[Code] ....
When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE USE dv_wrp_test GO SELECT * FROM cdc.dbo_AVA_CT GO
--RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4 --__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID --0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL --0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL --0x0031E84F00000098000ANULL0x0031E84F00000098000310x0F119Test36NULL --0x0031E84F00000098000ANULL0x0031E84F00000098000420x0F119Test46NULL
Why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
after i have inserted a row into the DB i am trying to get the last identity like this : Dim myCommand2 As New SqlCommand("SELECT @@IDENTITY AS 'Identity'", ) Dim myReader2 As SqlDataReader myReader2 = myCommand2.ExecuteReader() where myConnection2 is the connection objectafter i did this i dont underdsand how do i get the 'Identity' value?i tried myReader2 ('Identity') but no luckany 1 can helpthnaks in advancepeleg
Let me start off by saying I have posted this on: comp.databases.ms-sqlserver
My apologies, try not to do that again.
I have a table that I am trying to insert into with data from another table. Here is that Query:
INSERT INTO item (identifier, name, customlgtxt1, weight,mainprice, customnumber3, customnumber4) Select partno, name, description, weight,price, qtyprice, qty From Import2
This seems to work fine.
My dilemma has to do with an 'id' column in that item table. This is incrementally updated by one..ie Natural Key. I have set it to Identity and used Set @@IDENTITY AS 'id'...worked like a charm. But for reasons that have to do with a front end admin tool used by the home office I can't set this column to have an Indentity property. Screws up the insert done with the admin tool.
This is a item/price database by the way. Name of item, price, description, qty price..etc.
So I've tried to put this trigger on the item table CREATE TRIGGER auto_fill ON [dbo].[item] FOR INSERT, UPDATE AS BEGIN declare @maxc int set @maxc = (select Max(id) from item) set @maxc = @maxc +1 Select 'id' AS '@maxc', notorderable = '0', createdon = getdate(), createdat = getdate(), createdby = 'Steve', modifiedon = getdate(), modifiedat= getdate(), modifiedby = 'Steve'
END
But the Insert Into statement (see above) will not work...giving the error "Cannot insert the value NULL into column 'id', table 'new_Catalog1v1.dbo.item" Which I know...that's why I set it to Identity..but that cannot be.
So the question is how to set an autonumber (or natural key or I'm not sure of the name) that updated from the max(id)table when inserting from another table.
And then...one more.
I have to update a table named UNIQUEIDS with the lastest value of the id column (max(ID))..UNIQUEIDS keeps track of the latest value inserted into the id column for a number of tables. Here is another trigger I put on the item table to update the UNIQUEIDS table.
create trigger upUniqueIds_item on [dbo].[item] for update,Insert, delete as begin Select @@Identity as 'id' from inserted Update uniqueids set id = @@identity Where tablename = 'item' End
But of course this doesn't work if I can't set the columns to IDENTITY.
I hope someone can help and I hope my explanation had made sense. Need to increment the id field using max(id) and update another table with the last imported value of max(id). One occurs during insert..another after the insert..i think?
