Retrieving Scope_Entity Or Identity From An SQL Insert

Oct 2, 2007

The following code inserts a record into a table.  I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other  inserts.  Can someone offer assistance in handling this.... I tried several alternatives that I found on the internet but none seem to work...

 Thanks!

Dim objConn3 As SqlConnection
Dim mySettings3 As New NameValueCollection
mySettings3 = AppSettings
Dim strConn3 As String
strConn3 = mySettings3("connString")
objConn3 = New SqlConnection(strConn3)
Dim strInsertPatient As String
Dim cmdInsert As SqlCommand
Dim strddlSex As String
Dim strddlPatientState As String
Dim rowsAffected As Integer

strddlSex = ddlSex.SelectedItem.Text
strddlPatientState = ddlPatientState.SelectedItem.Text

strInsertPatient = "Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _
"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _
"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _
"Today_Date_Year) Values (@UserID, @Accession, @FirstName, @MI, @LastName, @MedRecord, " & _
"'" & strddlSex & "', @DOB, @Address1, @Address2, @City, @Suite , '" & strddlPatientState & "', " & _
"@ZIP, @HomeTelephone, @OutsideNYC, @ClinicalImpression, @Today_Date_Month, @Today_Date_Day, " & _
"@Today_Date_Year)SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

cmdInsert = New SqlCommand(strInsertPatient, objConn3)

cmdInsert.Parameters.Add("@UserID", "Joe For Now")
cmdInsert.Parameters.Add("@Accession", Accession.Text)
cmdInsert.Parameters.Add("@LastName", LastName.Text)
cmdInsert.Parameters.Add("@MI", MI.Text)
cmdInsert.Parameters.Add("@FirstName", FirstName.Text)
cmdInsert.Parameters.Add("@MedRecord", MedRecord.Text)
cmdInsert.Parameters.Add("@ddlSex", strddlSex)
cmdInsert.Parameters.Add("@DOB", DOB.Text)
cmdInsert.Parameters.Add("@Address1", Address1.Text)
cmdInsert.Parameters.Add("@Address2", Address2.Text)
cmdInsert.Parameters.Add("@City", City.Text)
cmdInsert.Parameters.Add("@Suite", Suite.Text)
cmdInsert.Parameters.Add("@strddlPatientState", strddlPatientState)
cmdInsert.Parameters.Add("@ZIP", zip.Text)
cmdInsert.Parameters.Add("@HomeTelephone", Phone.Text)
cmdInsert.Parameters.Add("@OutsideNYC", OutsideNYC.Text)
cmdInsert.Parameters.Add("@ClinicalImpression", ClinicalImpression.Text)
cmdInsert.Parameters.Add("@Today_Date_Month", Today_Date_Month.Text)
cmdInsert.Parameters.Add("@Today_Date_Day", Today_Date_Day.Text)
cmdInsert.Parameters.Add("@Today_Date_Year", Today_Date_Year.Text)

objConn3.Open()
cmdInsert.ExecuteNonQuery()
objConn3.Close()

View 1 Replies


ADVERTISEMENT

Retrieving Identity After Insert

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

Retrieving Identity Field From Table On INSERT

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 3 Replies View Related

Retrieving The BigInt Value From The Identity Column After Inserting

Jul 26, 2007

I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.
How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure. 
Waht is the way to achive this please?
Marc

View 2 Replies View Related

Problem Retrieving @@Identity When Inside A Transaction.

Aug 22, 2005

I'm using transactions with my SqlConnection (  sqlConnection.BeginTransaction() ).

I do an Insert into my User table, and then subsequently use the
generated identity ( via @@identity ) to make an insert into another
table. The foreign key relationship is dependant on the generated
identity. For some reason with transactions, I can't retrieve the
insert identity (it always returns as 1).  However, I need the
inserts to be under a transaction so that one cannot succeed while the
other fails. Does anyone know a way around this problem?

So heres a simplefied explanation of what I'm trying to do:

Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @@Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.

Because of the transaction, userID is 1, therefore an insert cannot be
made into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??

View 5 Replies View Related

Problem With Stored Procedure And Retrieving Inserted Identity Value

Jul 11, 2006

Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne

View 3 Replies View Related

SQL Server CE 3.5, Typed Dataset, Retrieving @@IDENTITY Of Inserts?

Mar 19, 2008

I'm at loss how I'm supposed to work with typed datasets and Sql Server Compact 3.5, when inserting records and I need to update my datatables with the primary key of newly inserted rows.

I've tried adding a RowUpdated handler to all tableadapters that look like this:





Code Snippet
void Adapter_RowUpdated(object sender, System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs e) {

if(e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) {

if(e.Row.Table.PrimaryKey.Length > 0) {
SqlCeCommand selectIdentityCommand = new SqlCeCommand("SELECT @@IDENTITY",e.Command.Connection);
if(e.Command.Connection.State == ConnectionState.Open) {

e.Row[e.Row.Table.PrimaryKey[0].Ordinal] = (int)selectIdentityCommand.ExecuteScalar();

}
}
}






I've previously used this type of approach when working with an OleDbDatabase, which works just fine. But it doesn't work with Sql Server CE 3.5, and since it doesn't support stored procedures I can't fix it that way either. And it doesn't support commands in a batch (i.e appending the Insert command of the adapter with ";SELECT @@IDENTITY") so that doesn't work either...

So how are we supposed to use Sql Server CE 3.5? It's impossible together with datasets? Or am I missing something obvious?

Any hints would be greatly appreciated! Thanks!

Cheers!

View 3 Replies View Related

SQL Server 2008 :: Retrieving Same Identity Column Value By Multiple Users

Mar 11, 2015

I am using following queries in a stored procedure.This stored procedure is executed through a dot net application.

DECLARE @DEPTNBR BIGINT
SELECT @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS= 1
UPDATE DEPARTMENT_DETAILS SET STATUS= 0 WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC FROM DEPARTMENT_DETAILS WHERE DEPTNBR = @DEPTNBR ​

From my queries,I am providing a available department information.Each user needs to get unique available department information.But when more number of users using the application concurrently, multiple users getting same department information.How to solve my problem?I always wants to get unique department information even though multiple users using the application concurrently.

View 6 Replies View Related

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

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

Insert Row In Table With Identity Field, And Get New Identity Back

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

Retrieving ID After Insert

Jul 22, 2004

Hi, I have a Stored procedure doind an INSERT which then returns @@Identity.

I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..

Not sure where I am going wrong...

Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?

Many Thanks

View 3 Replies View Related

Retrieving PK To Insert Into Another Table

Mar 7, 2006

The query below should be inserting unique records in the PropertyItem table which only has propertyitem, propertyid, and itemid columns, all of which are PK's in other tables. I'm not doing the insert correctly b/c it's inserting 72 records instead of 24. I really just want to automatically insert the values once I've inserted in the other tables but I'm not sure how. Maybe On Update Cascade?


--PROPERTYITEM
INSERT INTO [USCONDEX_Production].[dbo].[propertyItem]( [propertyId], [ItemId])
SELECT Property.propertyId, ITEM.ItemID
FROM ITEM RIGHT OUTER JOIN
miamiherald ON ITEM.StartDate = miamiherald.FirstInsertDate AND ITEM.Price = miamiherald.PropertyPrice AND ITEM.Classified = convert(int,miamiherald.AdNumber) LEFT OUTER JOIN
Property ON property.adprintid = miamiherald.adprintid
WHERE validAD=1

View 1 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

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

Retrieving Output Paramater After Insert

Sep 14, 2006

Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control.  The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later.  I'm having trouble getting to this return value via the SQLDataSource .  Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?

View 1 Replies View Related

Retrieving GUID From INSERT Query -- HELP

Aug 27, 2007

I have tried many code sample but I am very stuck

The primary key of my database (SQL server 2005) table is a uniqueidentifier.


I am using the following code to insert a row into my table:

myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command
myCommand.Connection = this.dbConnection; //add the database connection to the command
myCommand.ExecuteNonQuery(); //execute the insert query


I need to retrieve the GUID that is automatically generated when the insert command is executed.

Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using

string _id = (string)myCommand.ExecuteScalar();

and I am still stuck. I will really appreciate it if someone can refer me to some code sample.


HELP

View 1 Replies View Related

Retrieving ID After INSERT Behind The Scenes - Not Using A Grid View

Jun 3, 2006

Hi there. I looked through many other posts describing scope_identity but I am trying to achieve the same thing from the code behind. i.e. I need to some how call a method to execute the insert command and then return the ID so I can update other tables with this value.
I was going down the road of something like:
addnew as sqldatasource = new sqldatasource
addnew.insertcommand = "Insert into....; def @NewID as scope_identity"
addnew.insert()
 
The problem is I don't know how to add a output parameter using VB or how to retrieve it.
Any help would be much appreciated, this is doing my head in....
Doug.

View 1 Replies View Related

How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor

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

Using Identity To Get The Last Insert

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

Need Insert Cannot Use Identity

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

IDENTITY INSERT

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

IDENTITY INSERT

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

IDENTITY - Need To Be Able To Insert

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

@@ Identity Insert

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

INSERT @@identity At Once

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

Insert Identity Id

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

Identity Insert

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

Insert When Using IDENTITY(1,1)

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

IDENTITY INSERT ON

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

How To Get An Identity Value From An INSERT Statement

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

Returning The Identity Value After Insert?

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

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 View Related

How Do You Use A SP To Do An Insert And Return The Identity To ADO

Apr 12, 1999

I would like to use ADO in a new project but I need to find out how to return the identity field value to ADO out of a stored procedure. I have not done alot with ADO or with SQL 7.0 so if anyone has an example of the SQL and the ADO code that I would need I would greatly appriate it.

Thanks

View 1 Replies View Related

Turn Identity Off On Insert

Jun 3, 2005

Hi,

I'm nesting a bunch queries, the parent being a select, and the children being inserts. I'd like to retain the auto generated IDs from the original table, and insert them into the new tables (into identity fields). I believe that there is a command that I can use to temporarily turn identity auto numbering off for the current query - can anyone help me with this?

Cheers,

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved