(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.
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();
// 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.
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?
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>
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?
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...
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?
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.
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.
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'
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())
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?
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)
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
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
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?
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)
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
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.
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
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?
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
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
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?
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)
