Hi,
I need to pass the ID of a newly inserted product in my products db to a wizard step so that I can achieve the following:
Wizard Step 1: Enter product details
Wizard Step 2: Choose product image and upload to file system, then write to DB.
All help is appreciated!
Thanks,
Chris
I'm using an Access2K front end on a SQL Server2K backend.I use Scope_Identity() in a lot of stored procedures to pass thenewwly inserted record's unique ID to the next select statement in thesame stored procedure.What I'm wondering is how I can pass the Scope_Identity back to thecalling application.I'm hoping someone can show me the SP code and the aceess code neededto accomplish the following:I insert a new record in a table with a stored procedure. The SPpasses the uniqueID (Scope_Identity) back to MS Access, then MS Accessuses the uniqueID when calling another function...thanks
View 1 Replies View RelatedI have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.
If I do the INSERT followed by a "SELECT @@IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @@IDENTITY.
I guess what I have is satisfactory but this was frustrating and I want to know why.
This is on SQL Server 2000 Standard edition with version SP3a + hot fixes
hi to all
who is the best use among IDENT_CURRENT and @@IDENTITY and SCOPE_IDENTITY when i wnat to get last inserted id from a table.
and also give the reason why because i am little bit confuse for useing these..
thanks in advance.
arvind
Dear all,
I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @@IDENTITY.
The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),
Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection) Try NewTagID = CInt(command2.ExecuteScalar().ToString)
However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @@IDENTITY", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)
Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @@IDENTITY would be the better option.
Thankful in advance for advice.
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
Is there no way to pass identity info back through SqlDataSource? You can only do that with ADO.NET code?
In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it?
I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource. I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control. But there doesn't seem to be any facility for Identity in there. I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything.
Thanks!
is it possible to pass a variable to an identity funtion
example
declare @max_note int
select @max_note = max(key ) from notes
select m_key = identity( int, @max_note, 1),
name
into #prod_note
from prod_note
Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
I am using Visual Web Developer 2005 Express Edition, ASP.NET 2.0 and SQL Server 2005 Express Edition.I'm using a DetailsView control with the default set to "Insert". (DetailsView1)I'm using a table adapter for the datasource. (ordersDS) I
have a table with the first column as an identity integer. When using
the DetailsView control (default is set to insert) and I click the insert button to insert a new
record, how can I get the identity integers value of the newly inserted
record??
Thank you in advance
hello, i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.it works like:insert into table() values()select @id = (newly created id number) from tableinsert into table1() values(@id) something like that.
View 2 Replies View RelatedHowdie y'all,
I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one....
CREATE PROCEDURE [dbo].[spAddUser]@UserEmail VARCHAR(255),@UserPassword VARCHAR(16),@UserName VARCHAR(32)ASINSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)VALUES (@UserEmail, @UserPassword, @UserName)GO
I actually would like to get value of the UserId column for the newly added record.
Can anyone of you folks help me with this?
Cheers,
Wes
I am trying to execute three dts packages from a Job that I created. When creating the steps I am setting the Type To: Operating System Command(CmdExec).
The Command I am using is: dtsrun /S servername /E /dtsPackageName
For some reason whenever I try to run thes jobs it fails. Within this Job I have three steps. For each step I am using the same job but with a different package name. I have a trusted connection so my command should work just fine. What am I doing wrong. Can some please explain this process.
Thanks
I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me.And now I have to do various calculations that involve making use of both these type of columns.
Rajesh writes "Hi
I have a table. I need to see the newly inserted row on the day. i.e. newly inserted data alone. I dont have any date field in the table."
Hi
I had DTS the Northwind sample database from SqlServer 2000 to 2005. It's went ok and no errors. Then, I created a SP named upGetCustomer, bascially it queries the Customers table and list some of it's fields and order by CustomerId,CustomerName, Country decrementally. SP is so simple and has no errors.
Then, I created a SqlServer project in VS2005 using C#. Add store procedure class as below,
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Customers(string customerid)
{
using (SqlConnection sqlConn =
new SqlConnection("context connection=true"))
{
sqlConn.Open();
SqlPipe sqlPipe = SqlContext.Pipe;
SqlParameter param = new SqlParameter("@custId", SqlDbType.VarChar, 5);
param.Value = customerid;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "upGetCustomer";
sqlCmd.Parameters.Add(param);
SqlDataReader rdr = sqlCmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
}
}
I had taken method of created SQLCLR from source in Microsoft website, but coding is mine, and hopes it is correct. I used SqlConnection string as 'context connection=true' which I still not quite sure what does it means, hopes it mean current connection I am using on my Windows authentication.
The project did compiled & deployed OK on the VS2005 side.
The problem is that when I tried to locate the assembly on the Sql Server 2005, but can't find it anywhere on Sql Server.
I did try complied and deloyed again, it keeps saying there is an error in deployment as customers assembly is already exist on the database. I then, tried to remove this assembly on the database using SQL script, drop assembly customers in the Northwind database but got error saying it does not exist. So where is it???
Please help...
Thank you
Hi There
Can anyone tell me how an identity column decides what number to give which row.
I always thought that it was in order of the clustered index.
But i have found the following:
If you have Table A with a clustered index(not unique) say on the first 3 columns.
If the table is empty and you add an identity column, then afterward load it with data the value of the identity column seems to match the clustered index.
But if the table has no identity column and it is filled with data, and then you add an identity column the number for the identity column seems to be all over the place.
So basically how does a newly added identity column know what number to give each row, what if the clustered index is not unique? Is it random ?
Thanx
Using scope_identity
I am using SQL2005 and I need to insert a record and return ID. I am using scope_identity() in the stored procedure to return the ID for the record just inserted.
Do you see any problem with this when it comes to multi-user and multi-threaded environment.
Hi, i need the DiagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement. I was told to use scope_identity(), but i'm not sure how to implement it. Below is my code behind in vb.net. pls help. Dim cmd1 As New SqlCommand("insert into Diagnosis(TypeID, SeverityID, UniBilateral, PatientID, StaffID) values ('" & typevalue & "','" & severityvalue & "','" & unibivalue & "','" & Session("PatientID") & "','" & Session("StaffID") & "')", conn) cmd1.ExecuteNonQuery() Dim i As Integer For i = 0 To hearingarray.Count - 1 Dim li As New ListItem li = hearingarray(i) Dim cmd As New SqlCommand("insert into DiagnosisManagement(ManagementID) values ('" & li.Value & "')", conn) //i need the DIagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement here cmd.ExecuteNonQuery() Next
View 1 Replies View RelatedHi All,
I'm trying to return the last id entered via the following code, and I'm only getting '0' back.
1 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))
2 {
3 connection.Open();
4 using (SqlCommand command = new SqlCommand("REG_S_CustomerIDFromPhoneNumber", connection))
5 {
6 command.CommandType = CommandType.StoredProcedure;
7 command.Parameters.AddWithValue("@Mobile_Telephone", MobileNumber);
8
9 int test = Convert.ToInt32(command.ExecuteScalar());
10
11 Response.Write(test.ToString());
12
13
14 }
15 } My SP is as follows (I'm trying to use one that's already been written for me) 1 SET QUOTED_IDENTIFIER ON
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7
8 ALTER PROCEDURE [dbo].[REG_I_CreateBlankCustomer]
9 @Mobile_Telephone varchar(255),
10 @CustomerID int OUTPUT
11
12 AS
13
14 INSERT INTO Customer (Mobile_Telephone)
15 VALUES (@Mobile_Telephone)
16
17 --SET @CustomerID = @@Identity
18 SELECT SCOPE_IDENTITY();
19
20
21 GO
22 SET QUOTED_IDENTIFIER OFF
23 GO
24 SET ANSI_NULLS ON
25 GO
26
27
28
when I'm running this via Query Analyser, I get the ID returned correctly, however as mentioned when ran via that code above - I get a 0 outputted to me. What am I doing wrong?
Thanks!
i have such a stored procedure.
but i dont know where to put that scope_identity to retrieve a value.
SELECT SCOPE_IDENTITY() AS [@Car_id]
GO
ALTER procedure [dbo].[insertuser](
@Make nchar(10),
@Model nchar(10),
@SellerID varchar(50),
@MileAge nchar(10),
@Year_Model int,
@Price money,
@Date_added datetime,
@Thumb_ID varchar(50),
@Image_id varchar(50),
@Car_id int
)
AS
INSERT INTO
dbo.tbcar
VALUES(@Make,@Model,@SellerID,@MileAge,@Year_Model,@Price,@Date_added);
INSERT INTO
dbo.tbimages
values
(@Thumb_ID,@Image_id,@Car_id)
I was wondering if its possible to use this function if your using a
SqlDataReader where I just run a stored procedure that just inserts a
row?
Hello altogether, my problem ist that I get following error message:
Create Stored ProcedureUnable to chances to the stored procedure.Error Details:'Scope_Identity' is not a recognized function name.
This is my Stored Procedure:
CREATE PROCEDURE sp_HyperSoftCustomer @Name varchar(25), @Adress varchar(250)as insert into HyperSoftCustomer(Name, Adress, Date) values (@Name, @Adress, GetDate()) Select SCOPE_IDENTITY() GO
I am using MSDE - MSSQLServer
I hope there is anybody who can help me?
Thanks, mexx
I have seen plenty of messages about using scope_index by creating parameters using HTML but I would like to do it from my .aspx.vb page.
Does anybody know if this is possible? I have got as far as the code below and get stuck when trying to add a new parameter with direction of output.
Any help would be much appreciated, cheers,
Doug.
Dim NewProperty As SqlDataSource = New SqlDataSource
NewProperty.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectIt").ToString()
NewProperty.InsertCommand = "INSERT INTO Test (Name) VALUES (@Name); SET @NewID=SCOPE_IDENTITY()"
NewProperty.InsertParameters.Add(NewID, id)
NewProperty.Insert()
I have four tables:
1- customer details
2- bank details
3- car details
4- contract details
All these tables are linked with the contract ID which is the primary key in table 4 and foriegn key in the rest. When a new customer inputs all the above data from the VB front, I want table 4 to give contract ID with a autonumber, which should be sent to the other tables, so that the contract in all tables are the same so that it is linked properly.....
I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager.....
Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both
THANKS
Hi All,
In SQL Server stored procedure
SCOPE_IDENTITY() will return the IDENTITY value inserted in Table, which was the last INSERT that occurred in the same scope.
can any one give me the syntax in Oracle ?
Here's what I have (OrderID and CustID are PK's and autoincrement):
tblOrders__________tblCustomers
---------__________------------
OrderID____________CustID
CustID_____________Name
Just a simple application I created in ASP.NET and C# with those tables in an SQL database. The user enters their name, clicks Submit, and their information is put into Customers. After that, I want a new Order to be created with the CustID from the Customer just created.
I know I'm supposed to SCOPE_IDENTITY() to create it, but I'm not sure how to use it. I've been told to use a stored procedure, but I'm not sure how to do that either. Here's my code:
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("INSERT INTO tblCustomers(Name)VALUES('"+TextBox1.Text+"');", conn);
//cmd2 with SCOPE_IDENTITY() inserting into tblOrders
conn.Open();
cmd.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
conn.Close();
I am seeing a problem with an ASP application, where I have 2 tables.In the first table, the ASP inserts just 1 row and retrieves theprimary key of the new row using SCOPE_IDENTITY. It then uses thatprimary key in the column of a second table (foreign key) to insertmany rows.What I'm seeing is an intermittent problem where the foreign key in thesecond table is not what it should be. I think the problem may be dueto the fact that the insert into the first table and the calling ofSCOPE_IDENTITY are done in 2 separate ASP statements with some ASP codein between.Is it possible that 2 users may be calling my ASP page at the same timeand causing a concurrency problem due to the INSERT and theSCOPE_IDENTITY being done in 2 different SQL statements? I read thatSCOPE_IDENTITY always returns the last identity value generated from"the current connection", so I thought that would mean that it wouldn'tget messed up by another ASP request. But now I'm thinking thatperhaps ASP uses connection pooling which could mean that 2 users couldbe sharing the same connection which would cause this concurrencyissue.Does anyone know if my theory of what's wrong is plausible?
View 5 Replies View RelatedI have an ASP front end on SQL 2000 database. I have a form that submits toan insert query. The entry field is an "identity" and the primary key. Ihave used scope_identity() to display the entry# of the record just enteredon the confirmation page. Now I need to insert the entry into anothertable. This is my query:SET NOCOUNT ONINSERT wo_main(site_id, customer, po_number)VALUES ('::site_id::', '::customer::', '::po_number::')SELECT scope_identity() AS entryINSERT INTO wo_combo_body(entry) VALUES ('::entry::')SET nocount offThis query displays the entry number of the record just entered, but insertsa 0 in to entry field of the 2nd table. Any help would be great.Thanks,Darren
View 4 Replies View Relatedis there an sql mobile equivalent of SCOPE_IDENTITY()?
View 4 Replies View Related
hi
what is difference between thos two's
SCOPE_IDENTITY()
and
@@IDENTITY
thanx
Hi,
I am trying to return the id of the newly added row after the insert
statement in my stored procedure. I was told to "RETURN
SCOPE_IDENTITY()", which i did. The problem is i do not know how to get
this value in asp.net?
I added this code below in my business layer. myDAL refers to an object
of my DAL layer. In my DAL layer, i have a addPara() method which will
help me dynamically add as many parameters. Someone please advise me on
what to do. Thanks
myDAL.addPara("@RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue)
Stored Procedure:
CREATE PROCEDURE [ADDPROMOTION]
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100)
)
As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)
-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()
GO
I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.