Scope_Identity returning Decimal for an Integer field

[RobinS]

SQLServer2005, VS2005, C#.

I have a stored procedure that returns a SCOPE_IDENTITY after doing an
insert.

Here's the pertinent part of the SQL.

INSERT INTO PlanData
(PlanName, ...) VALUES (@PlanName, ...)
SELECT PlanID = SCOPE_IDENTITY()

PlanID is defined as an integer in the database and in my business object.

To get the data back, I am retrieving a datatable, which should have one
row and column. So to pull my identity column, I am doing this:

PlanId = (int)dt.Rows[0][cn_PlanID]; //cn_PlanID = "PlanID", the name
of the SQL column

I get an error "Cannot unbox 'dt.Rows[0][cn_PlanID]' as a 'int'"

When I do a dt.Rows[0][cn_PlanID].GetType();, it says it's a decimal.

I *can* do this:

PlanId = (int)(decimal)dt.Rows[0][cn_PlanID];

or this:

PlanId = (int)dt.Rows[0][cn_PlanID].ToString();

I tried changing it to use ExecuteScalar, and it does the same thing; my
SQL procedure returns a Decimal. I tried retrieving @@Identity, and it does
the same thing.

Any idea what I'm doing wrong or what I've missed? It seems stupid to have
to cast it as decimal and then recast it as int.

Thanks in advance,
Robin S.

 

[Guest]

In your SQL, try PlanID = CAST(SCOPE_IDENTITY() as int)

 

[RobinS]

Okay, I can do that. Why is it returning it as a decimal? Is that just what
SQLServer does? I don't have this problem in VB, even though I have Option
Strict On. It just happens to me in C#. Weird.
Robin S.

 

[Armin Zingler]

Okay, I can do that. Why is it returning it as a decimal? Is that
just what SQLServer does? I don't have this problem in VB, even
though I have Option Strict On. It just happens to me in C#. Weird.
Robin S.

I have the same problem. The field is an int field, but after INSERTing,
select @@identity returns a Decimal value (the value is correct, but the
type is wrong). (SQL server Express 2005)

Any idea?


Armin

 

[William Vaughn]

This I gotta see. Let's see your code (SQL and how you're calling the
routine). @@Identity is an INT or BIGINT.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Armin Zingler]

This I gotta see. Let's see your code (SQL and how you're calling the
routine). @@Identity is an INT or BIGINT.

Thanks for your reply. Here some excerpts from the code and the table
definition:


Private f_CMDSelectIdentity As SqlCommand

'...

'f_con is the SQLConnection
f_CMDSelectIdentity = New SqlCommand("select @@identity", f_Con)


'...

Private Function GetIdentity() As Integer

Dim dr As SqlDataReader

dr = f_CMDSelectIdentity.ExecuteReader
dr.Read()
'TODO: conversion is workaround
Return CInt(DirectCast(dr(0), Decimal)) '<----------------------
End Function

(ExecuteScalar behaves the same)

I call GetIdentity /immediatelly/ after executing this SQL:

INSERT INTO Documents (...) values (...)

(of course, the identity column is not included in the SQL)


Table Documents (from management studio express):

CREATE TABLE [dbo].[Documents](
[ID] [int] IDENTITY(1,1) NOT NULL,
....


As you see, ID is an int identity field. In Function GetIdentity, I was
previously using

Return Directcast(dr(0), Integer)

which lead to an exception. When examining dr(0) in debug mode, I found out
that it's type is Decimal, not Integer as expected. Though, the value is
always correct.


Armin

 

[William Vaughn]

Ah, why are you casting to a Decimal in the first place? I must have missed
something. This example uses a couple of different ways to execute SQL and
return a value. Using the DataReader for a single scalar value is the last
choice I would make.

Try
cn = New OleDbConnection(My.Settings.ConnectToJet)
cn.Open()
cmd = New OleDbCommand("INSERT INTO Shippers (CompanyName,
Phone) VALUES ('USPS', '(214) 555-1212')", cn)
cmd.ExecuteNonQuery()
cmd.CommandText = "SELECT @@Identity"
Dim objIdentity As Object
objIdentity = cmd.ExecuteScalar
Debug.Print(objIdentity.ToString)
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader
If dr.HasRows Then
dr.Read()
Dim objID As Object
objID = dr.GetValue(0)
Debug.Print(objID.ToString)
End If

cn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

This I gotta see. Let's see your code (SQL and how you're calling the
routine). @@Identity is an INT or BIGINT.

Thanks for your reply. Here some excerpts from the code and the table
definition:


Private f_CMDSelectIdentity As SqlCommand

'...

'f_con is the SQLConnection
f_CMDSelectIdentity = New SqlCommand("select @@identity", f_Con)


'...

Private Function GetIdentity() As Integer

Dim dr As SqlDataReader

dr = f_CMDSelectIdentity.ExecuteReader
dr.Read()
'TODO: conversion is workaround
Return CInt(DirectCast(dr(0), Decimal)) '<----------------------
End Function

(ExecuteScalar behaves the same)

I call GetIdentity /immediatelly/ after executing this SQL:

INSERT INTO Documents (...) values (...)

(of course, the identity column is not included in the SQL)


Table Documents (from management studio express):

CREATE TABLE [dbo].[Documents](
[ID] [int] IDENTITY(1,1) NOT NULL,
....


As you see, ID is an int identity field. In Function GetIdentity, I was
previously using

Return Directcast(dr(0), Integer)

which lead to an exception. When examining dr(0) in debug mode, I found
out
that it's type is Decimal, not Integer as expected. Though, the value is
always correct.


Armin

 

[Armin Zingler]

Ah, why are you casting to a Decimal in the first place?

Because it /is/ a Decimal value. Casting to an Integer throws an exception
if it is a Decimal.

I must have
missed something. This example uses a couple of different ways to
execute SQL and return a value. Using the DataReader for a single
scalar value is the last choice I would make.

I know how to get the value, though I don't know why the type is Decimal,
not Integer.

Try
cn = New OleDbConnection(My.Settings.ConnectToJet)

Be aware of the fact that I am using an SqlConnection, not an
OleDBConnection.

cn.Open()
cmd = New OleDbCommand("INSERT INTO Shippers
(CompanyName, Phone) VALUES ('USPS', '(214) 555-1212')", cn)
cmd.ExecuteNonQuery()
cmd.CommandText = "SELECT @@Identity"
Dim objIdentity As Object
objIdentity = cmd.ExecuteScalar
Debug.Print(objIdentity.ToString)
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader
If dr.HasRows Then
dr.Read()
Dim objID As Object
objID = dr.GetValue(0)

Insert this line here:

MsgBox(dr.GetValue(0).GetType.FullName())

Which type name is displayed?

Debug.Print(objID.ToString)
End If

cn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights. __________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book: Hitchhiker's Guide to Visual Studio and SQL Server (7th
Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

This I gotta see. Let's see your code (SQL and how you're
calling the routine). @@Identity is an INT or BIGINT.

Thanks for your reply. Here some excerpts from the code and the
table definition:


Private f_CMDSelectIdentity As SqlCommand

'...

'f_con is the SQLConnection
f_CMDSelectIdentity = New SqlCommand("select @@identity", f_Con)


'...

Private Function GetIdentity() As Integer

Dim dr As SqlDataReader

dr = f_CMDSelectIdentity.ExecuteReader
dr.Read()
'TODO: conversion is workaround
Return CInt(DirectCast(dr(0), Decimal))
'<---------------------- End Function

(ExecuteScalar behaves the same)

I call GetIdentity /immediatelly/ after executing this SQL:

INSERT INTO Documents (...) values (...)

(of course, the identity column is not included in the SQL)


Table Documents (from management studio express):

CREATE TABLE [dbo].[Documents](
[ID] [int] IDENTITY(1,1) NOT NULL,
....


As you see, ID is an int identity field. In Function GetIdentity,
I was previously using

Return Directcast(dr(0), Integer)

which lead to an exception. When examining dr(0) in debug mode, I
found out
that it's type is Decimal, not Integer as expected. Though, the
value is always correct.


Armin

 

[William Vaughn]

Okay, let's go back two steps. What DBMS engine are you using? JET? SQL
Server? Something else?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Ah, why are you casting to a Decimal in the first place?

Because it /is/ a Decimal value. Casting to an Integer throws an exception
if it is a Decimal.

I must have
missed something. This example uses a couple of different ways to
execute SQL and return a value. Using the DataReader for a single
scalar value is the last choice I would make.

I know how to get the value, though I don't know why the type is Decimal,
not Integer.

Try
cn = New OleDbConnection(My.Settings.ConnectToJet)

Be aware of the fact that I am using an SqlConnection, not an
OleDBConnection.

cn.Open()
cmd = New OleDbCommand("INSERT INTO Shippers
(CompanyName, Phone) VALUES ('USPS', '(214) 555-1212')", cn)
cmd.ExecuteNonQuery()
cmd.CommandText = "SELECT @@Identity"
Dim objIdentity As Object
objIdentity = cmd.ExecuteScalar
Debug.Print(objIdentity.ToString)
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader
If dr.HasRows Then
dr.Read()
Dim objID As Object
objID = dr.GetValue(0)

Insert this line here:

MsgBox(dr.GetValue(0).GetType.FullName())

Which type name is displayed?

Debug.Print(objID.ToString)
End If

cn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights. __________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book: Hitchhiker's Guide to Visual Studio and SQL Server (7th
Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

This I gotta see. Let's see your code (SQL and how you're
calling the routine). @@Identity is an INT or BIGINT.

Thanks for your reply. Here some excerpts from the code and the
table definition:


Private f_CMDSelectIdentity As SqlCommand

'...

'f_con is the SQLConnection
f_CMDSelectIdentity = New SqlCommand("select @@identity", f_Con)


'...

Private Function GetIdentity() As Integer

Dim dr As SqlDataReader

dr = f_CMDSelectIdentity.ExecuteReader
dr.Read()
'TODO: conversion is workaround
Return CInt(DirectCast(dr(0), Decimal))
'<---------------------- End Function

(ExecuteScalar behaves the same)

I call GetIdentity /immediatelly/ after executing this SQL:

INSERT INTO Documents (...) values (...)

(of course, the identity column is not included in the SQL)


Table Documents (from management studio express):

CREATE TABLE [dbo].[Documents](
[ID] [int] IDENTITY(1,1) NOT NULL,
....


As you see, ID is an int identity field. In Function GetIdentity,
I was previously using

Return Directcast(dr(0), Integer)

which lead to an exception. When examining dr(0) in debug mode, I
found out
that it's type is Decimal, not Integer as expected. Though, the
value is always correct.


Armin

 

[Armin Zingler]

Well, what do you think as I use an SqlConnection in the example and as I
wrote "SQL server Express 2005" in my first post?

Armin

Okay, let's go back two steps. What DBMS engine are you using? JET? SQL
Server? Something else?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Ah, why are you casting to a Decimal in the first place?

Because it /is/ a Decimal value. Casting to an Integer throws an
exception
if it is a Decimal.

I must have
missed something. This example uses a couple of different ways to
execute SQL and return a value. Using the DataReader for a single
scalar value is the last choice I would make.

I know how to get the value, though I don't know why the type is Decimal,
not Integer.

Try
cn = New OleDbConnection(My.Settings.ConnectToJet)

Be aware of the fact that I am using an SqlConnection, not an
OleDBConnection.

cn.Open()
cmd = New OleDbCommand("INSERT INTO Shippers
(CompanyName, Phone) VALUES ('USPS', '(214) 555-1212')", cn)
cmd.ExecuteNonQuery()
cmd.CommandText = "SELECT @@Identity"
Dim objIdentity As Object
objIdentity = cmd.ExecuteScalar
Debug.Print(objIdentity.ToString)
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader
If dr.HasRows Then
dr.Read()
Dim objID As Object
objID = dr.GetValue(0)

Insert this line here:

MsgBox(dr.GetValue(0).GetType.FullName())

Which type name is displayed?

Debug.Print(objID.ToString)
End If

cn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights. __________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book: Hitchhiker's Guide to Visual Studio and SQL Server (7th
Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

This I gotta see. Let's see your code (SQL and how you're
calling the routine). @@Identity is an INT or BIGINT.

Thanks for your reply. Here some excerpts from the code and the
table definition:


Private f_CMDSelectIdentity As SqlCommand

'...

'f_con is the SQLConnection
f_CMDSelectIdentity = New SqlCommand("select @@identity", f_Con)


'...

Private Function GetIdentity() As Integer

Dim dr As SqlDataReader

dr = f_CMDSelectIdentity.ExecuteReader
dr.Read()
'TODO: conversion is workaround
Return CInt(DirectCast(dr(0), Decimal))
'<---------------------- End Function

(ExecuteScalar behaves the same)

I call GetIdentity /immediatelly/ after executing this SQL:

INSERT INTO Documents (...) values (...)

(of course, the identity column is not included in the SQL)


Table Documents (from management studio express):

CREATE TABLE [dbo].[Documents](
[ID] [int] IDENTITY(1,1) NOT NULL,
....


As you see, ID is an int identity field. In Function GetIdentity,
I was previously using

Return Directcast(dr(0), Integer)

which lead to an exception. When examining dr(0) in debug mode, I
found out
that it's type is Decimal, not Integer as expected. Though, the
value is always correct.


Armin

 

[William Vaughn]

Ok, then I'm absolutely sure that the @@IDENTITY returns a BigInt. In this
case I suspect your query is passing back another number somehow. Actually,
you should be using the function SCOPE_IDENTITY() instead of @@IDENTITY.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Well, what do you think as I use an SqlConnection in the example and as I
wrote "SQL server Express 2005" in my first post?

Armin

Okay, let's go back two steps. What DBMS engine are you using? JET? SQL
Server? Something else?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Ah, why are you casting to a Decimal in the first place?

Because it /is/ a Decimal value. Casting to an Integer throws an
exception
if it is a Decimal.

I must have
missed something. This example uses a couple of different ways to
execute SQL and return a value. Using the DataReader for a single
scalar value is the last choice I would make.

I know how to get the value, though I don't know why the type is
Decimal,
not Integer.


Try
cn = New OleDbConnection(My.Settings.ConnectToJet)


Be aware of the fact that I am using an SqlConnection, not an
OleDBConnection.


cn.Open()
cmd = New OleDbCommand("INSERT INTO Shippers
(CompanyName, Phone) VALUES ('USPS', '(214) 555-1212')", cn)
cmd.ExecuteNonQuery()
cmd.CommandText = "SELECT @@Identity"
Dim objIdentity As Object
objIdentity = cmd.ExecuteScalar
Debug.Print(objIdentity.ToString)
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader
If dr.HasRows Then
dr.Read()
Dim objID As Object
objID = dr.GetValue(0)

Insert this line here:

MsgBox(dr.GetValue(0).GetType.FullName())

Which type name is displayed?


Debug.Print(objID.ToString)
End If

cn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights. __________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book: Hitchhiker's Guide to Visual Studio and SQL Server (7th
Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

This I gotta see. Let's see your code (SQL and how you're
calling the routine). @@Identity is an INT or BIGINT.

Thanks for your reply. Here some excerpts from the code and the
table definition:


Private f_CMDSelectIdentity As SqlCommand

'...

'f_con is the SQLConnection
f_CMDSelectIdentity = New SqlCommand("select @@identity", f_Con)


'...

Private Function GetIdentity() As Integer

Dim dr As SqlDataReader

dr = f_CMDSelectIdentity.ExecuteReader
dr.Read()
'TODO: conversion is workaround
Return CInt(DirectCast(dr(0), Decimal))
'<---------------------- End Function

(ExecuteScalar behaves the same)

I call GetIdentity /immediatelly/ after executing this SQL:

INSERT INTO Documents (...) values (...)

(of course, the identity column is not included in the SQL)


Table Documents (from management studio express):

CREATE TABLE [dbo].[Documents](
[ID] [int] IDENTITY(1,1) NOT NULL,
....


As you see, ID is an int identity field. In Function GetIdentity,
I was previously using

Return Directcast(dr(0), Integer)

which lead to an exception. When examining dr(0) in debug mode, I
found out
that it's type is Decimal, not Integer as expected. Though, the
value is always correct.


Armin

 

[Armin Zingler]

Ok, then I'm absolutely sure that the @@IDENTITY returns a BigInt.
In this case I suspect your query is passing back another number
somehow. Actually, you should be using the function
SCOPE_IDENTITY() instead of @@IDENTITY.

Why don't you believe me? @@IDENTITY definitely returns a Decimal.
SCOPE_IDENTITY returns DBNull.Value. I changed the SQL to show you:


Dim dr As SqlDataReader

cmd = New SqlCommand("select SCOPE_IDENTITY(), @@IDENTITY", f_Con)
dr = cmd.ExecuteReader
dr.Read()

After dr.Read in the immediate window:

?dr(0)
{System.DBNull}
[System.DBNull]: {System.DBNull}

?dr(1)
129D {Decimal}
[Decimal]: 129D


The value 129 is correct, though the type mustn't be Decimal. It must be
Integer (Int32) because the type of the identity column is 'int'. The whole
database only has two tables. The tables have only int, nvarchar and
datetime fields.


Armin

 

[tdd123]

I'm seeing it too...

I noticed the return issue too. In my stored procedure, after an record insert on a table with an int as PK, I do a:
SELECT SCOPE_IDENTITY()

In my VB code, it kept blowing up when I did the following:
Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()

If rdr.Read Then

intReturn = rdr.GetInt32(0)

End If

I either had to cast the SCOPE_IDENTITY() as an int or do a rdr.GetDecimal(0) to get it to work...

 

[Mary Chipman [MSFT]]

What's likely going on is that SCOPE_IDENTITY returns a sql_variant
and @@IDENTITY returns a numeric (decimal) data type, not the
underlying data type defined in the table. So these values are what
SqlClient is getting back from the server -- it doesn't know anything
about the underlying table schema. SqlClient is performing a direct
cast to the larger CLR type without performing any narrowing
conversions (which it doesn't have sufficient metadata to perform
anyway). That's my take on it

-mary

Ok, then I'm absolutely sure that the @@IDENTITY returns a BigInt.
In this case I suspect your query is passing back another number
somehow. Actually, you should be using the function
SCOPE_IDENTITY() instead of @@IDENTITY.

Why don't you believe me? @@IDENTITY definitely returns a Decimal.
SCOPE_IDENTITY returns DBNull.Value. I changed the SQL to show you:


Dim dr As SqlDataReader

cmd = New SqlCommand("select SCOPE_IDENTITY(), @@IDENTITY", f_Con)
dr = cmd.ExecuteReader
dr.Read()

After dr.Read in the immediate window:

?dr(0)
{System.DBNull}
[System.DBNull]: {System.DBNull}

?dr(1)
129D {Decimal}
[Decimal]: 129D


The value 129 is correct, though the type mustn't be Decimal. It must be
Integer (Int32) because the type of the identity column is 'int'. The whole
database only has two tables. The tables have only int, nvarchar and
datetime fields.


Armin

 

[Armin Zingler]

What's likely going on is that SCOPE_IDENTITY returns a sql_variant
and @@IDENTITY returns a numeric (decimal) data type, not the
underlying data type defined in the table. So these values are what
SqlClient is getting back from the server -- it doesn't know
anything about the underlying table schema. SqlClient is performing
a direct cast to the larger CLR type without performing any
narrowing
conversions (which it doesn't have sufficient metadata to perform
anyway). That's my take on it

-mary

Thanks for your reply. I don't understand why it returns a Decimal although
the latest identity value is an int, but I will have to put up with it. I'll
ask in the SQL server group. Thx again.


Armin

 

[RobinS]

Thanks for your reply. I don't understand why it returns a Decimal
although
the latest identity value is an int, but I will have to put up with it.
I'll ask in the SQL server group. Thx again.


Armin

If you get an answer, let us know. I had to change mine to cast from a
decimal to the field I was using, too.

Robin S. (OP)

 

[Mary Chipman [MSFT]]

It returns the int wrapped in a decimal. It does that by design, there
is no mystery. See SQL Server Books Online for the SCOPE_IDENTITY
function where it says "Return types: numeric" Numeric is a synonym
for decimal. http://msdn2.microsoft.com/en-us/library/ms190315.aspx.
You can narrow a conversion but you can't widen one that is too narrow
to begin with.

--Mary