Hi,
I have an sqlCommand return the ID from the last inserted row. I use SELECT SCOPE_IDENTITY within the SQL.
When I call the function it returns a Decimal instead of an integer??
So I want to convert this to an integer, however this is not liked by the compiler since the overloaded method for the decimal conversion is obviously a decimal, but the ExecuteScalar parameter is of type object.
So I changed to this:
iReturnValue = decimal.ToInt32((decimal)_sqlCommand.ExecuteScalar());
Can someone tell me if this is the right way to do it? Seems messy code to me...
After doing an insert, I am using a System.Data.SqlClient.SqlDataReader to fetch the identity of the inserted row with the following code: dbCommand.CommandText = "SELECT @@IDENTITY As LogID SET NOCOUNT OFF"Dim dataReader As System.Data.SqlClient.SqlDataReader = dbCommand.ExecuteReaderdataReader.ReaddataReader.GetInt32(0)dataReader.CloseThe problem is that I'm getting an invalid cast exception. If I use dataReader.GetDataTypeName(0), it tells me that value is a decimal? This is odd because the identity column in the SQL table is an int (size: 4). When I use very similar code for another database on the same server, it works .Anybody have any ideas?
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Hi, Not too long ago I was looking to change a primary key in a table from one column to another, the standard accepted procedure to so procedure to do so (I was told) was: 1) drop primary key constraint from old_key_column 2)add primary key constraint to new_key_column 3) drop old_key_column (optional).
In order to carry out the first step, I needed the name of the primary key constraint, at which point I asked around for a command that would return that name (and would quite probably take as a parameter the name of the column that key is related to). The answer was :
Try this: select name 'constraintName' from sys.indexes where object_id=object_id('<tableName>') and is_primary_key=1
Unfortunately this command never worked for me. Probably because I don't have a table sys.indexes in my DB, but I can see a table sysindexes in the system tables folder, then again that table doesn't have an object_id column, but it has an id column, finally this column only contain obscur numbers.
So I am asking you all (again) for help, do you know about a command that returns every single constraint related to a column (or a table, or both).
One more thing, I am running SQL SERVER 2000, I do know that there are graphic ways to do those very operations (in the enterprise manager or the query analyzer) and I do know those ways (that's how i got past the problem mentioned earlier), I just think that knowing how to do it programmatically would be a plus.
PS: I am a total newbie to T-SQL (and a still a beginner in SQL, so please take it easy with me )
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
I would like to cast (convert) data type decimal(24,4) to decimal(21,4). I could not do this using standard casting function CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable) because of the following error: "Arithmetic overflow error converting numeric to data type numeric." Is that because of possible loss of the value?
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places. But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted. How can I solve or get around this problem? (with jet if it possible) iviczl
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g. ‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format. How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals 158203508540188236252EUR20BZK0030 Å“& 20060715 0001010100010101
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)
Hi I am using Visual Web Developer Express 2008. The site is configured to ASP.Net 2.0. The database is a MS Access database and is accessed using a DAL. The Query - "InsertNewOrder()" is INSERT INTO Orders (order_date, order_type, order_pay) VALUES (NOW(), 'P', 'none'); SELECT SCOPE_IDENTITY()The Code-behide is : Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Page.IsPostBack Then Dim NewOrder As New OrdersTableAdapter Dim CurrentOrderNumber As Integer = NewOrder.InsertNewOrder() End If End SubWhen debugging the following exemption occurs: "Characters found after end of SQL statement." Can anybody please help. If I take the ";" out then I get a "missing ; error" How do I fix this?ThanXRed
Hi, I want to capture the last inserted ID field for a table so that i can use it to display the last entered record on a formview. (ReadOnly Mode) This should happen straight away after that new record is inserted. I wrote the following code, but my results variable is always null. 1 protected void FormView1_ItemInserted(object sender, FormViewInsertedEventArgs e) 2 { 3 //Get the CarID for the newly created record 4 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString); 5 SqlCommand sqlcomm = new SqlCommand("SELECT SCOPE_IDENTITY()", conn); 6 conn.Open(); 7 string results = sqlcomm.ExecuteScalar().ToString(); 8 conn.Close(); 9 10 Session["id"] = results; 11 FormView1.ChangeMode(FormViewMode.ReadOnly); 12 }
