Retrieve ID Of Last Insert (Scope_Identity)
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
ADVERTISEMENT
Mar 14, 2007
I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening. SqlConnection conn = new SqlConnection(@"Server=.SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True");
conn.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
// delete all rows
cmd.CommandText = "DELETE FROM Person";
cmd.ExecuteNonQuery();
Response.Write("start... <br><br>");
// ad-hoc insert
cmd.CommandText = "SET IDENTITY_INSERT Person ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Response.Write("ID = ");
Response.Write(cmd.ExecuteScalar());
Response.Write("<br>");
cmd.CommandText = "SET IDENTITY_INSERT Person OFF";
cmd.ExecuteNonQuery();
// parameter insert
cmd.CommandText = "SET IDENTITY_INSERT Person ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@PersonID, @PersonName, @PersonAge)";
p = new SqlParameter("@PersonID", 11);
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);
p = new SqlParameter("@PersonName", "Jon Doe2");
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);
p = new SqlParameter("@PersonAge", 21);
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Response.Write("ID = ");
Response.Write(cmd.ExecuteScalar());
Response.Write("<br>");
cmd.CommandText = "SET IDENTITY_INSERT Person OFF";
cmd.ExecuteNonQuery();
Response.Write("<br>end.");
}
finally
{
conn.Close();
}
I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing...
Thank you.
View 4 Replies
View Related
Jan 21, 2008
Hello,
I have the following stored procedure that inserts records and updates the new record. The parameter @rpt_id has a value of -1 when entering the procedure. It needs to be updated with the new record if (identity) once the record is inserted, bu sometimes the update does not happen. The new records ends up with -1 in the rpt_id column.
I have included the stored procedure. I will appreciate any ideas?
ALTER PROCEDURE [dbo].[thhill_InsertReport]
(
@RPT_ID int,
@REPORT_DATE datetime,
@COMMIT_DATE datetime,
@JOB_NUMBER nvarchar(50),
@TECH_NAME nvarchar(75),
@CLIENT_NAME nvarchar(255),
@CLIENT_CONTACT nvarchar(75),
@CITY nvarchar(75),
@STATE nvarchar(75),
@COUNTRY nvarchar(75),
@SUPPLIER nvarchar(75),
@WORK_ORDER nvarchar(75),
@RIG_NAME nvarchar(75),
@WELL_NAME nvarchar(75),
@OCSG_NUMBER nvarchar(75),
@AFE_NUMBER nvarchar(75),
@MGMT varchar(6000),
@Complete varchar(3),
@newReportId int output,
@changeCommitDate bit = 1
)
AS
SET NOCOUNT ON;
if @changeCommitDate = 1
set @COMMIT_DATE = getdate()
INSERT INTO [reports] ([RPT_ID], [REPORT_DATE], [COMMIT_DATE], [JOB_NUMBER], [TECH_NAME], [CLIENT_NAME], [CLIENT_CONTACT], [CITY], [STATE], [COUNTRY], [SUPPLIER], [WORK_ORDER], [RIG_NAME], [WELL_NAME], [OCSG_NUMBER], [AFE_NUMBER], [MGMT], [isReportComplete])
VALUES (@RPT_ID, @REPORT_DATE, @COMMIT_DATE, @JOB_NUMBER, @TECH_NAME, @CLIENT_NAME, @CLIENT_CONTACT, @CITY, @STATE, @COUNTRY, @SUPPLIER, @WORK_ORDER, @RIG_NAME, @WELL_NAME, @OCSG_NUMBER, @AFE_NUMBER, @MGMT, @Complete );
declare @reference int, @tmpReportId int
set @tmpReportId = SCOPE_IDENTITY()
set @reference = @tmpReportId * -1
SET NOCOUNT Off;
Update [reports] set [RPT_ID] = @reference where id = @tmpReportId
select @newReportId = RPT_ID
FROM REPORTS
WHERE (ID = @tmpReportId)
View 3 Replies
View Related
May 24, 2007
What C# code would capture the Scope_Identity value (CoDeptRowID) output by the code below? Do I even need to capture it or is it already available as a C# variable CoDeptRowID ? I can't seem to get my hands on it!
SqlDataSource1.Insert();<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="INSERT INTO [CompanyDepartment] ([User_Name], [FirstName], [LastName]) VALUES (@User_Name, @FirstName, @LastName);
SELECT @CoDeptRowID = SCOPE_IDENTITY()"
<insertparameters>
<asp:sessionparameter Name="User_Name" Type="String" SessionField ="LoginName"/>
<asp:controlparameter Name="FirstName" Type="String" ControlID="TextBox1" PropertyName ="text"/>
<asp:controlparameter Name="LastName" Type="String" ControlID ="TextBox2" PropertyName ="text"/>
<asp:Parameter Direction =Output Name ="CoDeptRowID" Type ="Int32" DefaultValue = "0" />
</insertparameters>
</asp:SqlDataSource>
View 5 Replies
View Related
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
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
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, 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
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
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
Sep 15, 2007
Hi there,
I have inherited a databse and am building a new website to go wiht it.
There is a file upload page which will upload images to a directory. I need to insert into the database retrieve the id just added then upload the image renaming it in the format locID(QueryString)_ImageID(retrieved from database).jpg
The page has a file upload control and a button.
I am trying to write my code behind so that when the button is clicked it inserts location id into the images table retrieves Image id. Renames the file and uploads it to the images folder.
II think i need to call the routine from another routine for the button click but the signatures are different, where am i going wrong? or for that matter have i been pissing into the wind for the last 4 hours?
CODE BEHIND
Imports System.Data
Imports System.Data.SqlClientPartial Class admin_Add_Images
Inherits System.Web.UI.PageProtected Sub UploadImage(ByVal Sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim LocationId As String = Request.QueryString(ID)
' create a new SqlConnectionDim NewConn As New SqlConnection
NewConn = New SqlConnection("server=desktopsqlexpress;uid=xxxxxx;pwd=xxxxxxx;database=MYLOCDEV") 'OleDbConnection i
' open the connection
NewConn.Open()Dim MyInsert = New SqlCommand("INSERT into image([LocationID]) VALUES (@LocationID); SET @NewId = Scope_Identity()")
NewConn.Close()
If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
'RENAME THE FILEDim newid As Integer = e.Command.Parameters("@NewId").Value
Dim fn As String = (LocationId & "_" & newid & ".jpg")Dim SaveLocation As String = Server.MapPath("oicImages") & "" & fn
Try
File1.PostedFile.SaveAs(SaveLocation)Response.Write("The file has been uploaded.")
Catch Exc As ExceptionResponse.Write("Error: " & Exc.Message)
End Try
ElseResponse.Write("Please select a file to upload.")
End If
End SubProtected Sub Submit1_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Submit1.Click
UploadImage()End Sub
End Class
View 2 Replies
View Related
Oct 20, 2007
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.
View 6 Replies
View Related
Jan 3, 2008
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 Related
May 19, 2008
Hi 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!
View 9 Replies
View Related
Jun 9, 2008
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)
View 5 Replies
View Related
Mar 30, 2005
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?
View 2 Replies
View Related
Mar 31, 2006
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
View 3 Replies
View Related
Jun 3, 2006
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()
View 3 Replies
View Related
Aug 23, 2004
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
View 11 Replies
View Related
Aug 31, 2004
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 ?
View 4 Replies
View Related
Jan 10, 2008
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();
View 18 Replies
View Related
Jul 23, 2005
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 Related
Jul 20, 2005
I 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 Related
Apr 6, 2006
is there an sql mobile equivalent of SCOPE_IDENTITY()?
View 4 Replies
View Related
Sep 11, 2007
hi
what is difference between thos two's
SCOPE_IDENTITY()
and
@@IDENTITY
thanx
View 5 Replies
View Related
Dec 28, 2006
have a detailsView control with an SqlDataSource whose insert statement looks like this:
InsertCommand="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@NAME, @CITY, @ST, @ZIPCODE, @NAICS, @NAICSDESCRIPTION); SELECT RETURN_VALUE = SCOPE_IDENTITY()"
also played with the same insert but used ...;Select SCOPE_IDENTITY()
my question is how do i get the last record inserted into tblCompaniesNewSetRaw after the insert is run. ie I read that the Select Scope_identity() would return the value but how do i access the return value from within the code behind page, iusing VB.
some things i tried in the detailsView_ItemInserted(...
Dim row As DetailsViewRow
For Each row In DetailsView3.Rows
x = row.Cells.Item(0).Text
Next
in the VS debugger x is just "" and not the last record inserted in that table.
probably way off base on this, clues appreciated, tc
View 3 Replies
View Related
Jun 29, 2007
Hi folks;I'm having trouble retrieving the SCOPE_IDENTITY() with an SqlDataSource, it returns a number that has nothing to do with the real identity.It was always returning 102 or 137 when the real identities were something in between 5 to 10
Here is my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [POD] WHERE [PODID] = @PODID" InsertCommand="INSERT INTO [POD] ([CustomerID], [Airbill], [Shipper], [Consignee], [POD], [Date], [Time], [Pieces], [Weight], [Comments]) VALUES (@CustomerID, @Airbill, @Shipper, @Consignee, @POD, @Date, @Time, @Pieces, @Weight, @Comments); SELECT SCOPE_IDENTITY() AS @newID"
SelectCommand="SELECT * FROM [POD] ORDER BY [POD]" UpdateCommand="UPDATE [POD] SET [CustomerID] = @CustomerID, [Airbill] = @Airbill, [Shipper] = @Shipper, [Consignee] = @Consignee, [POD] = @POD, [Date] = @Date, [Time] = @Time, [Pieces] = @Pieces, [Weight] = @Weight, [Comments] = @Comments WHERE [PODID] = @PODID">
<DeleteParameters>
<asp:Parameter Name="PODID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:Parameter Name="Airbill" Type="String" />
<asp:Parameter Name="Shipper" Type="String" />
<asp:Parameter Name="Consignee" Type="String" />
<asp:Parameter Name="POD" Type="String" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Pieces" Type="Int32" />
<asp:Parameter Name="Weight" Type="Decimal" />
<asp:Parameter Name="Comments" Type="String" />
<asp:Parameter Name="PODID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="newID" Type="Int32" Direction="ReturnValue" />
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:Parameter Name="Airbill" Type="String" DefaultValue=" " />
<asp:Parameter Name="Shipper" Type="String" DefaultValue=" " />
<asp:Parameter Name="Consignee" Type="String" DefaultValue=" " />
<asp:Parameter Name="POD" Type="String" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Pieces" Type="Int32" DefaultValue="0" />
<asp:Parameter Name="Weight" Type="Decimal" DefaultValue="0" />
<asp:Parameter Name="Comments" Type="String" DefaultValue=" " />
</InsertParameters>
</asp:SqlDataSource>
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
' ** get the id and redirect to details page
Dim id As Integer = e.Command.Parameters("@newID").Value
Response.Redirect("pod_details.aspx?id=" & ID)
End Sub
Does anybody know what am i doing wrong in here?
View 2 Replies
View Related
Oct 12, 2007
I have a app that is inserting data into a SQL 2005 database and I would like to return the UniqueID of the inserted record. I am using
Dim queryString As String = "INSERT INTO dbo.DATATABLE (FIELD) VALUES (@FIELD);SELECT Scope_Identity()"
Dim sID As String = comSQL.ExecuteScalar()
This isn't working - it says the value returned is DBNull...
Any ideas on how to make this work?
View 4 Replies
View Related
Oct 28, 2007
I have a stored procedure that does three INSERTS each needing to use the primary key from the previous. There are three INSERTS in the procedure.
Is this ok? my reason for asking is that it will get the first @IDPrimary but not the second @IDSecondary
For example;
INSERT (1)
Set @IDPrimary = SCOPE_IDENTITY()
INSERT(2)
Set @IDSecondary = SCOPE_IDENTITY()
INSERT(3)
View 6 Replies
View Related
