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


ADVERTISEMENT

How To Get The Latest Identity Value From The Previous Insert Statement?

Oct 10, 2005

Hi all,I have 2 tables: Applicant_Table, Applicant_Resume_Table
In my web application, after applicant click "submit" for his job application, the information will be saved to Applicant_table in SQL Server. But in a SQL statement, how to get his Applicant_ID, so that I can save his resume to another table on his Applicant_ID?Thank you.

View 2 Replies View Related

SELECT @@IDENTITY During INSERT STATEMENT Error

Mar 9, 2008

The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:


"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"

Compact 3.5 doesnt like the SELECT statement claiming that:

There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]


Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?

I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.

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

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

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage

Apr 21, 2008

An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
 
thanks

View 7 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement

Jan 14, 2008


This problem is being seen on SQL 2005 SP2 + cumulative update 4

I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable

I now need to add an "instead of insert" trigger to the table that is the subject of the insert.

As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows

Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table

Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.

To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row

I need the behaviour to be correct when the trigger is present

Any thoughts would be much appreciated

aero1


/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)

GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted

END

/************************************************
3) - Do the insert
************************************************/

DECLARE @my_insert TABLE( my_table_id bigint )

declare @forename VARCHAR(100)
declare @surname VARCHAR(50)

set @forename = N'john'
set @surname = N'smith'

INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)

select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger

/************************************************
4) - Drop the trigger
************************************************/

drop trigger [dbo].[trig_my_table__instead_insert]
go

/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK

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

Select Statement With A New Identity Field

Mar 25, 2008

Hello,
 Is it possible to generate a identityfield dynamically upon select, like this:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC
I want the output to be:
1 - Name12 - Name23 - Name3
The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.
Like:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC
I want the output to be:
1 - Name32 - Name23 - Name1
 
Patrick

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

Case Statement Error In An Insert Statement

May 26, 2006

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View 10 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

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

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

How To Insert Identity Values

Oct 3, 2005

hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it

View 3 Replies View Related

How To Insert Into Identity Values

Oct 3, 2005

hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it
bye

View 1 Replies View Related

Insert Into A Table With Identity

Aug 31, 2006

I have 2 tables with same structure.only difference is, TableA has key colum which is declared as identity starting from 1.

I want to insert data from TableB into TableA where TableB.Key is all null with TableA.Key starting from 9000001

when i try to do that iam getting an error
"An explicit value for the identity column in table 'lEADlOAN' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
even when I turned IDENTITY_INSERT ON for TableA

View 4 Replies View Related







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