Retrieve Identity After Insert
Jun 1, 2006
I am a "newbie" and have been struggling with this for days! I have users enter their residence information and insert which generates houseid. I want to use/display that houseid on next page/step. I am VERY FRUSTRATED and would appreciate any assistance!
<script runat="server">
Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting
e.Command.Parameters("@house").Size = 5
End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Dim house = e.Command.Parameters("@house").Value
Response.Write(house)
End Sub
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs)
End Sub
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
End Sub
</script>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ic_registerConnectionString %>"
oninserted="SqlDataSource1_Inserted"
oninserting="SqlDataSource1_Inserting"
DeleteCommand="DELETE FROM [household] WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate"
InsertCommand="INSERT INTO [household] ([housenum], [streeraddr], [aptnum], [city], [state], [zipcode], [HHPhone], [timedate]) VALUES (@housenum, @streeraddr, @aptnum, @city, @state, @zipcode, @HHPhone, { fn NOW() }); SELECT @house = SCOPE_IDENTITY()"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [household]"
UpdateCommand="UPDATE [household] SET [housenum] = @housenum, [streeraddr] = @streeraddr, [aptnum] = @aptnum, [city] = @city, [state] = @state, [zipcode] = @zipcode, [HHPhone] = @HHPhone, [timedate] = @timedate WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate" OnSelecting="SqlDataSource1_Selecting">
<InsertParameters>
<asp:Parameter Name="housenum" Type="String" />
<asp:Parameter Name="streeraddr" Type="String" />
<asp:Parameter Name="aptnum" Type="String" />
<asp:Parameter Name="city" Type="String" />
<asp:Parameter Name="state" Type="String" />
<asp:Parameter Name="zipcode" Type="String" />
<asp:Parameter Name="HHPhone" Type="String" />
<asp:Parameter Type = "String" Name="house" Direction= "Output"/>
</InsertParameters>
Next question is this easier to do using a Wizard Control and DetailsView on a "step" or using seperate pages and FormView? Or does it matter?
View 2 Replies
ADVERTISEMENT
May 14, 2008
I have a table with an Identity column. Is there a way to get the automatically generated primary key after I insert something to the table? If so, what is the syntax?
View 5 Replies
View Related
Jan 10, 2006
Hello,
In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.
Is it possible to retreive the GUID column of this new record (without requerying the table) ?
I'm using EVC, Sql Mobile 3.0 and OLE DB interface.
Thanks in advance.
View 1 Replies
View Related
Jun 23, 2006
Hello
Using Visual Studio 2005 Prof and SQL Server everywhere.
How do get the identity column value after insert record.
With SQL Server 2005, its quite easy to get by creating and insert statement on the tabledapter ( Insert statement followed by a select statement where identitycolumn = scope_identity())
How do this is sql everywhere??
regards
View 1 Replies
View Related
Jul 9, 2006
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?
TIA,
Barkingdog
View 10 Replies
View Related
Jan 30, 2008
Ok I've been researching this for a day now and I'm not coming up with much. I want to store the auto-incrementing ID of the last inserted record in a session variable, so that I may put it in a foreign key column in another table, if the user wishes to fill out a form on another page. I think my stored procedure is correct. But don't know what code to add to my aspx page. Any help will be greatly appreciated.
Here is my VB ScriptProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim personalContactDataSource As New SqlDataSource()personalContactDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DataConnectionString1").ToString()
personalContactDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
personalContactDataSource.InsertCommand = "PersonalContactInsert"
personalContactDataSource.InsertParameters.Add("FirstName", FirstName.Text)
personalContactDataSource.InsertParameters.Add("LastName", LastName.Text)personalContactDataSource.InsertParameters.Add("KeyPerson", KeyPerson.Checked)
personalContactDataSource.InsertParameters.Add("DayPhone", DayPhone.Text)personalContactDataSource.InsertParameters.Add("EveningPhone", EveningPhone.Text)
personalContactDataSource.InsertParameters.Add("Fax", Fax.Text)personalContactDataSource.InsertParameters.Add("Email", Email.Text)
personalContactDataSource.InsertParameters.Add("HomeAddress", HomeAddress.Text)personalContactDataSource.InsertParameters.Add("City", City.Text)
personalContactDataSource.InsertParameters.Add("State", State.Text)personalContactDataSource.InsertParameters.Add("Zip", Zip.Text)
personalContactDataSource.InsertParameters.Add("ReqEffectDate", ReqEffectDate.Text)personalContactDataSource.InsertParameters.Add("MRID", MRID.Text)
personalContactDataSource.InsertParameters.Add("CurrentPremium", CurrentPremium.Text)personalContactDataSource.InsertParameters.Add("CurrentCarrier", CurrentCarrier.Text)
personalContactDataSource.InsertParameters.Add("CurrentDeductible", CurrentDeductible.Text)personalContactDataSource.InsertParameters.Add("CurrentCoins", CurrentCoins.Text)personalContactDataSource.InsertParameters.Add("ReasonForQuote", ReasonForQuote.Text)
End Sub
And here is my Stored ProcALTER PROCEDURE dbo.PersonalContactInsert
@FirstName varchar(30),@LastName varchar(30),
@DayPhone varchar(14),@EveningPhone varchar(14),
@Fax varchar(14),@Email varchar(60),
@HomeAddress varchar(80),@City varchar(30),
@State char(2),@Zip char(5),
@KeyPerson bit,@ReqEffectDate smalldatetime,
@CurrentCarrier varchar(30),@CurrentPremium smallmoney,
@CurrentDeductible smallmoney,@CurrentCoins smallmoney,
@ReasonForQuote varchar(150),@MRID int,
@ClientNumber int OUT
AS
INSERT INTO PersonalContact(FirstName, LastName, DayPhone, EveningPhone, Fax, Email, HomeAddress, City, State, Zip, KeyPerson, ReqEffectDate, CurrentCarrier, CurrentPremium, CurrentDeductible, CurrentCoins, ReasonForQuote, MRID, DateTimeStamp)
VALUES(@FirstName,@LastName,@DayPhone,@EveningPhone,@Fax,@Email,@HomeAddress,@City,@State,@Zip,@KeyPerson,@ReqEffectDate,@CurrentCarrier,@CurrentPremium,@CurrentDeductible,@CurrentCoins,@ReasonForQuote,@MRID, GetDate())
SET @ClientNumber = SCOPE_IDENTITY()
RETURN
View 8 Replies
View Related
Aug 21, 2014
Best way to retrieve the identity key of the record just inserted?This question is for discussion purposes; the business process that spurred the question is currently working.Using SQL Server 2008 R2, a record is inserted from a stored procedure. Let's say the sp has something like this:
Code:
BEGIN
BEGIN TRANSACTION
INSERT INTO tblTools
([Desc],CreateDate,Model,CreatedBy,Notes)
[code]....
In Access, when you add a new record to the recordset, the identity field comes "pre-populated" making it east to get the actual, correct identity value assigned to the record you are inserting. In SQL Server, I know options include:
Code:
IDENT_CURRENT('tblX')
SCOPE_IDENTITY
@@IDENTITY
among other methods.
Each has pros and cons, such as user privileges (IDENT_CURRENT requires the user to have Select privileges on the table, and catches records created by other things, such as users and triggers), and the other two give you the last key inserted and don't allow specifying the object (which is a problem if the insert added records to multiple tables, or you have multiple inserts).
View 2 Replies
View Related
Oct 13, 2007
Hello Everyone,
I'm using Nhibernate and SQL Server 2005.
I have two tables something like this :
Table : Account
AccountID
UserName
Password
Table : Address
AddressID
AccountID
Line1
Line2
State
My NHibernate mapping file looks like this :
For Table Account
<id name="ID" type="long" column="AccountID" access="property">
<generator class="identity"/>
</id>
<property name="Username" column="Username" type="String" />
<property name="Password" column="Password" type="String" />
For Table : Address
<id name="ID" type="long" column="AddressID" access="property">
<generator class="identity"/>
</id>
<many-to-one name="Account" column="AccountID" class="..., ...." fetch="select"/>
<property name="Line1" column="Line1" type="String" />
<property name="Line2" column="Line2" type="String" />
My Problem is it does entry in the Account table perfectly, but when it comes to do entry in Address Table it says AccountID can't be null, I'm wondering shouldn't it pick it up from the Account table as I'm passing in that object.
For the Workaround I was thinking maybe I can get the identity of the last entry saved and somehow save it to the other table myself.
I was hoping if someone can please help me.
View 1 Replies
View Related
Jun 30, 2006
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?
View 12 Replies
View Related
Aug 7, 2005
Is there a clever way of reteiving the identity of an INSERT query in SQLServer?
The example below is a the first insert (into the "parent" table with an identity column) of a heirarchical multi-table update.
sqlCommand.CommandText = "INSERT INTO Orders VALUES ( Date, Customer ) ; SELECT @@IDENTITY AS ID" ;
sqlCommand.ExecuteNonQuery() ;
The ExecuteNonQuery() method returns the number of rows inserted but I need the ID for the INSERT into the "child" table.
My options based on my current understanding appear to be:
a) use command parameters and set ID as an output param
b) use a stored procedure, use command parameters and set ID as an output param
c) issue the SELECT @@IDENTITY as a subsequent seperate command using ExecuteScalar()
Is there a more elegant way to get the ID using SQLServer's "batch" query mechanism and ADO.NET v1.1?
Thanks,
View 1 Replies
View Related
Jun 19, 2008
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
View 6 Replies
View Related
May 23, 2007
(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks.
Session["snCoDeptRowID"] = SqlDataSource1.Select();
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT Scope_Identity"
</asp:SqlDataSource>
View 4 Replies
View Related
Dec 10, 2011
I want to use sql server as a back end for my asp.net shopping cart using vb.net..i have created an "news.mdf" under app_data folder of my website in visual studio2010...but i tried to connect it to database using below command...but showing error...
sqlconnectionstring = "Data Source=.SQLEXPRESS;attachdbfilename=App_Datamyd atabase_data.mdf;Integrated Security=true;User Instance=true"
myconnection.ConnectionString = sqlconnectionstring
insertstring = "insert into News_table values(@N_head,@n_contents); "
mycommand = New SqlCommand(insertstring, myconnection)
[Code] ....
How to fix this error..and how to display this data in my site also
View 1 Replies
View Related
Jan 10, 2007
Hi everyone. Does anyone know if you can retrieve truncated data from a BULK INSERT operation?
We have a file that needs to be inserted into a SQL Server Database. There is a field that has a maximum of 8000 characters, but some times users submit files that have more than that. We need to be able to capture the truncated data. The BULK INSERT operation does not throw an error. The only way I can think of to get the data is if I bulk insert the data into a temporary table with a memo field and then copy it over, but that may really slow down the SP.
Has anyone encountered this situation before? I also have the option of parsing the file in .NET.
Thanks and take care,
Angel
View 9 Replies
View Related
Sep 16, 2009
I have a primary key named pk, name and surname fields. I need to insert to my table names and surnames.
INSERT INTO People (name,surname) VALUES ('john','black');
I'm not giving pks database gives is auto. But my problem is i need to know the pk that my database gave. Because i have lots of duplicate records. Is there any way to retrieve pk while inserting to table.
View 7 Replies
View Related
Aug 28, 2007
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
View 3 Replies
View Related
Mar 2, 2006
hello all
pls tell me
how can i insert, retrieve date from ASPX(vs2003) page in table in sql server 2000??
i m in trouble
pls help me
View 1 Replies
View Related
May 4, 2007
Hi,
I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
CREATE PROCEDURE dbo.InsertProduction
@DATEOUT datetime(8),
@DATEREQUIRED datetime(8),
@PREPAREDBY varchar,
@COMMENTID INteger,
@TOTALQUANTITY INteger,
@VENDORID INteger,
@WPO varchar,
@TCAPONUMBER INteger,
@APPROVEDBY varchar,
@Identity int OUT
AS
INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY)
SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update
'Get a reference to the Production table.
Dim dtProduction As DataTable = DS.Tables("Production")
Dim dtLineItem As DataTable = DS.Tables("LineItems")
' Create the SqlCommand to execute the stored procedure.
Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection)
Production.InsertCommand.CommandType = CommandType.StoredProcedure
' Add the parameter for the CategoryName. Specifying the
' ParameterDirection for an input parameter is not required.
'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName")
' Add the SqlParameter to retrieve the new identity value.
' Specify the ParameterDirection as Output.
Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID")
parameter.Direction = ParameterDirection.Output
' Create a new row with the same schema.
Dim dr As DataRow = dtProduction.NewRow()
'you need the ID from this to insert into the Production DB
' Set the value of all the columns.
dr("DateOut") = CDate(DateTimePicker1.Text)
dr("DateRequired") = CDate(DateTimePicker2.Text)
dr("VendorID") = CInt(vendorbox.SelectedValue)
dr("HomeAddress") = txtApproved.Text.ToString
dr("ApprovedBy") = txtPrepared.Text.ToString
dr("TCAPO") = CInt(txtTCAPO.Text.Trim)
dr("CommentID") = CommentID
dr("TotalCost") = CDec(txtTotals.Text)
dr("TotalQuantity") = CInt(txtQtyTotal.Text)
' Add to the Rows collection or table .
dtProduction.Rows.Add(dr)
'Update the Production Table and then retrieve the ID created in this case
Production.Update(dtProduction)
Dollarjunkie
View 2 Replies
View Related
Apr 18, 2008
Hello,
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.
Thanks.
View 8 Replies
View Related
Nov 26, 2005
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
View 1 Replies
View Related
May 6, 2002
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?
View 1 Replies
View Related
Aug 27, 2002
I need to insert into a table that has an identity insert set to on.
I need to know how to turn it off while I insert data into the table.
Here is the senerio
column 1
1
2
View 2 Replies
View Related
Aug 27, 2002
I need to insert into a table that has an identity insert set to on.
I need to know how to turn it off while I insert data into the table.
Here is the senerio
column 1
1
2
View 1 Replies
View Related
Feb 22, 2001
Help!
I need to be able to insert into an Identity column and tried putting
SET IDENTITY_INSERT table ON
in a stored procedure , but it didn't like this if not dbo, which will be the case.
Is there anyway of globally making this setting and leaving it on permenantly
KB
View 1 Replies
View Related
Dec 8, 2004
Is it possible to create a trigger that inserts the @@identity (primary key) from Table1 into a field in Table2?
If so, how? Thank you.
-D-
View 1 Replies
View Related
Nov 10, 2006
Hi
how can I insert @@identity at once (for MS SQL 2000 / 2500)
INSERT INTO Table1 (name) VALUES ('any')
DECLARE @ID int SELECT @@Identity
INSERT INTO Table2 (id_Table1) VALUES (@ID)
does not work
thank you
View 8 Replies
View Related
Sep 7, 2007
hi all,
i have a question:
i have a table that has an identity id column called pId and also a column called ParentPid.
now my question is that i want to insert the value of pId into the ParentPid when i'm adding a new Property to a table.
any idea?
thanks
View 6 Replies
View Related
Apr 2, 2007
Hi All,
I tried enabling the IDENTIY_INSERT ON for 2 tabls in a database. It says that I cannot have 2 tables in a database with IDENTITY INSERT ON.
Why is this so?
Thanks,
Prakash.P
View 8 Replies
View Related
Jun 11, 2007
If I have my table setup like so...
CREATE TABLE Customer
(SID integer IDENTITY(1,1) PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
How would I do an insert statement so that it auto increments and creates a unique number?
I keep getting errors.
View 4 Replies
View Related
Feb 28, 2008
I have twoo tables Table A (Col1 int, Col2 varchar(10)) and Table B (Col1 Identity(1,1), Col2 varchar(10))
Table A has
1 ABC
2 XYZ
NULL JIM
NULL KIM
10 min
I want to insert this in Table B
Well I seperated teh data per the TableA.Col1 where if Null, Table B will generate its Col1
But how will I do it in SSIS when TableA.Col1 in NOT NULL.
How would I set the IDENTITY INSERT ON on Table B while Inserting through SSIS?
Please advice
View 1 Replies
View Related
Nov 14, 2006
Hey, I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems. Thanks,Lang
View 2 Replies
View Related
Feb 29, 2008
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost)How do i obtain this value and how would I supply it to the second INSERT statement?
View 9 Replies
View Related
Apr 5, 2008
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;")
If Not IsPostBack Then
AddTrack()
End If
End Sub
Sub AddTrack()
Dim myCommand As SqlCommand
Dim insertTrack As String
insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn)
myCommand.Connection.Open()
Try
myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack
Catch ex As SqlException
tempTxt.Text = ex.Number.ToString()
End Try
myCommand.Connection.Close()
End Sub
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?
View 6 Replies
View Related
