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.

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

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.
----------------------------
"Travis" <Travis (AT) discussions (DOT) microsoft.com> wrote

"RobinS" <RobinS (AT) NoSpam (DOT) yah.none> schrieb
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

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" <az.nospam (AT) freenet (DOT) de> wrote

"William Vaughn" <billvaNoSPAM (AT) betav (DOT) com> schrieb
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

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)
-----------------------------------------------------------------------------------------------------------------------

"Armin Zingler" <az.nospam (AT) freenet (DOT) de> wrote

"William Vaughn" <billvaNoSPAM (AT) betav (DOT) com> schrieb
Because it /is/ a Decimal value. Casting to an Integer throws an exception
if it is a Decimal.

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



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


Insert this line here:

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

Which type name is displayed?

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)
-----------------------------------------------------------------------------------------------------------------------

"Armin Zingler" <az.nospam (AT) freenet (DOT) de> wrote

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