SCOPE_IDENTITY Returns Decimal From SQL Command
Oct 31, 2007
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.
iReturnVal = decimal.ToInt32(_sqlCommand.ExecuteScalar()); <--- compiler complains....
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...
View 3 Replies
ADVERTISEMENT
Aug 9, 2005
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?
View 4 Replies
View Related
May 23, 2006
Guys,
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?
Thanks
PJ
View 4 Replies
View Related
May 14, 2008
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 )
View 6 Replies
View Related
Sep 26, 2007
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)
View 5 Replies
View Related
Dec 8, 2013
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?
View 2 Replies
View Related
Apr 29, 2008
Hello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
View 5 Replies
View Related
Jul 24, 2006
Hello!
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?
Thanks for giving me any advice,
Ziga
View 6 Replies
View Related
Sep 19, 2007
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
View 4 Replies
View Related
Jul 23, 2005
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?
View 6 Replies
View Related
Nov 30, 2007
Hi all,
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.
Any help would be much appreciated
Thanks!
View 5 Replies
View Related
Jun 18, 2007
I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.
I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.
It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).
Is the decimal limited to scale 4 ???
Thank you for your help!
Anna
View 3 Replies
View Related
Jun 4, 2007
Hi,
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
Please help!
Thanks
Mirudhu
View 4 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
Apr 14, 2008
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
View 10 Replies
View Related
Jun 7, 2008
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 }
View 2 Replies
View Related
