• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Problems with returning @@identity via a stored procedure into a c# class

This one is starting to drive me mad, as I'm sure I've missed something simple.... I just can't see it!

I have a sql stored procedure on a sql2005 box called COM_add_batch :

----------------------------------------------------------- start of sp
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON;
go

-- Author:            John Clark
-- Create date: 4rd October 2007
-- Description:      Adds a new complaint batch and returns the @@identity as the reference
-- ===================================================================================
ALTER PROCEDURE [dbo].[COM_add_batch]
      
@in_customer_code varchar(10),
@in_customer_name varchar(255),
@in_description varchar(255),
@in_create_date      datetime,
@in_created_by varchar(70),
@in_last_addition_date datetime,
@in_prism_company varchar (50)

AS

insert into dbo.COM_BATCH_HEADER

(COM_BATCH_CUSTOMER_CODE,COM_BATCH_CUSTOMER_NAME,COM_BATCH_DESCRIPTION,COM_BATCH_CREATE_DATE,COM_BATCH_CREATED_BY,COM_BATCH_LAST_ADDITION_DATE,COM_BATCH_PRISM_COMPANY)

values

(@in_customer_code,@in_customer_name,@in_description,@in_create_date,@in_created_by,@in_last_addition_date,@in_prism_company)

select @@identity

----------------------------------------------------------- end of sp

It's used, quite obviously, for adding an entry into a table and returning the identity. Running it on the sql server (right click, execute) I have absolutely no problems at all - the identity is selected.

However.... when I'm calling it from a C# class, the data is entered into the table succesfully, but the identity doesn't appear to be selected and I end up with a casting error for trying to cast something that's null to an int...

The class is below:  remember that the data is being written to the server fine, so connectivity 'shouldn't' be an issue.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using PRISM_OBJECTS;



namespace COMPLAINTS_OBJECTS
{
    public class Complaint_batch
    {

        SqlConnection sqlcon = new SqlConnection();


       /* ~~~~~~~~~ CONSTRUCTORS */

        public Complaint_batch(string in_company) :base()
        {


            sqlcon.ConnectionString = ConfigurationManager.ConnectionStrings["ChimeraConnectionString"].ConnectionString;
                       
        }
       
        /* ~~~~~~~~~ Methods */

        public void save_batch()
        {
                       
            SqlCommand mysqlcom = new SqlCommand();
            mysqlcom.Connection = sqlcon;
            mysqlcom.CommandType = System.Data.CommandType.StoredProcedure;
            mysqlcom.CommandText = "COM_add_batch";

            mysqlcom.Parameters.AddWithValue("@in_customer_code", this.COM_BATCH_CUSTOMER_CODE);
            mysqlcom.Parameters.AddWithValue("@in_customer_name", this.COM_BATCH_CUSTOMER_NAME);
            mysqlcom.Parameters.AddWithValue("@in_description", this.COM_BATCH_DESCRIPTION);
            mysqlcom.Parameters.AddWithValue("@in_create_date", this.COM_BATCH_CREATE_DATE);
            mysqlcom.Parameters.AddWithValue("@in_created_by", this.COM_BATCH_CREATED_BY);
            mysqlcom.Parameters.AddWithValue("@in_last_addition_date", this.COM_BATCH_LAST_ADDITION_DATE);
            mysqlcom.Parameters.AddWithValue("@in_prism_company", this.COM_BATCH_PRISM_COMPANY);
                                   
            if (sqlcon.State != System.Data.ConnectionState.Open) { sqlcon.Open(); }

            this.COM_BATCH_ID = (int)mysqlcom.ExecuteScalar();
           

        }


       
        // Load Batch

        // Close Batch

        // Re-open Batch




        /* ~~~~~~~~~ Properties */

        private int c_COM_BATCH_ID;

        public int COM_BATCH_ID
        {
            get { return c_COM_BATCH_ID; }
            set { c_COM_BATCH_ID = value; }
        }

        private string c_COM_BATCH_CUSTOMER_CODE;

        public string COM_BATCH_CUSTOMER_CODE
        {
            get { return c_COM_BATCH_CUSTOMER_CODE; }
            set { c_COM_BATCH_CUSTOMER_CODE = value; }
        }

        private string c_COM_BATCH_CUSTOMER_NAME;

        public string COM_BATCH_CUSTOMER_NAME
        {
            get { return c_COM_BATCH_CUSTOMER_NAME; }
            set { c_COM_BATCH_CUSTOMER_NAME = value; }
        }
      

        private string c_COM_BATCH_DESCRIPTION;

        public string COM_BATCH_DESCRIPTION
        {
            get { return c_COM_BATCH_DESCRIPTION; }
            set { c_COM_BATCH_DESCRIPTION = value; }
        }

        private DateTime c_COM_BATCH_CREATE_DATE;

        public DateTime COM_BATCH_CREATE_DATE
        {
            get { return c_COM_BATCH_CREATE_DATE; }
            set { c_COM_BATCH_CREATE_DATE = value; }
        }

        private string c_COM_BATCH_CREATED_BY;

        public string COM_BATCH_CREATED_BY
        {
            get { return c_COM_BATCH_CREATED_BY; }
            set { c_COM_BATCH_CREATED_BY = value; }
        }

        private DateTime c_COM_BATCH_LAST_ADDITION_DATE;

        public DateTime COM_BATCH_LAST_ADDITION_DATE
        {
            get { return c_COM_BATCH_LAST_ADDITION_DATE; }
            set { c_COM_BATCH_LAST_ADDITION_DATE = value; }
        }


        private string c_COM_BATCH_PRISM_COMPANY;

        public string COM_BATCH_PRISM_COMPANY
        {
            get { return c_COM_BATCH_PRISM_COMPANY; }
            set { c_COM_BATCH_PRISM_COMPANY = value; }
        }

    }
}






0
misdevelopment
Asked:
misdevelopment
  • 10
  • 7
  • 2
1 Solution
 
Jai STech ArchCommented:
its preferrable to use SCOPE_IDENTITY() rather than @@identity
change you last line to
select SCOPE_IDENTITY()
0
 
Jai STech ArchCommented:
if that does not work
then just insert the record using the SP
and use SELECT SCOPE_IDENTITY as a separate statement with Execute Scalar

mysqlcom.ExecuteScalar();

mysqlcom.CommandText = "SELECT SCOPE_IDENTITY()";
this.COM_BATCH_ID = (int)mysqlcom.ExecuteScalar();
0
 
misdevelopmentAuthor Commented:
Thanks for that. All changed, unfortunately, still having the same issues. That was a seriously speedy response though!!!
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Jai STech ArchCommented:
did you try my second example ?
0
 
Jai STech ArchCommented:
just use like this...(for my first example)

select SCOPE_IDENTITY() as NewID;
0
 
misdevelopmentAuthor Commented:
Just tried the second example with exactly the same results - obviously needed to set the commandtype to text.

Also tried as per you ID 20083853, still exactly the same results... very peculiar. Must be something silly.
0
 
Jai STech ArchCommented:
thts strange...becos
object returnValue;
            try {
                returnValue = command.ExecuteScalar();
            }
            finally {
                if ((previousConnectionState == System.Data.ConnectionState.Closed)) {
                    command.Connection.Close();
                }
            }
this one works for me...can you try it out assigning it to a object and boxing to int,,,,,
0
 
Jai STech ArchCommented:
there is no reason that it should fail...

mysqlcom.CommandText = "SELECT SCOPE_IDENTITY() as col1";
this.COM_BATCH_ID = (int)mysqlcom.ExecuteScalar();
0
 
misdevelopmentAuthor Commented:
Putting it out to an object works !!! i.e:
----------------------

Object retval;
            retval = mysqlcom.ExecuteScalar();

-----------

The IDE shows retval as having a value of 16 (on the last run)

However, converting that object (retval)  to an int or Int32 still gets the casting error!!!! Very strange.

this.COM_BATCH_ID = (Int32) retval;

"Specified cast is not valid."
0
 
Jai STech ArchCommented:
use int for cast...it works all the time
(int)retval
0
 
misdevelopmentAuthor Commented:
In Locals, retval is shown as:

retval      16      object {decimal}
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
from books online

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, SQL Server 2005 introduces the following types of copy-only backups:
Copy-only full backups (all recovery models)

A copy-only full backup cannot serve as a differential base or differential backup and does not affect differential backups.

Copy-only log backups (full recovery model and bulk-logged recovery model only)

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore.
The transaction log is never truncated after a copy-only backup. Copy-only backups are recorded in the is_copy_only column of the backupset table.
0
 
misdevelopmentAuthor Commented:
as per my entry: 20083926

"int or Int32 still gets the casting error!!!!"

This is getting very bizarre...

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Sorry wrong post

0
 
Jai STech ArchCommented:
can you CAST it to a (decimal) then...
0
 
misdevelopmentAuthor Commented:
So it's true.. even Genii can post in the wrong place :-)

no problem.
0
 
misdevelopmentAuthor Commented:
Ok - we've got some success, but there's an awful lot more casting than I would have expected!!!!!

Object retval = mysqlcom.ExecuteScalar();
       
            Decimal mydec = (Decimal)retval;
                     
            this.COM_BATCH_ID = (int)mydec;


Many many thanks for your assistance on this one!!!!

JC
0
 
Jai STech ArchCommented:
you can also use like this
            object ret = mysqlcom.ExecuteScalar();
            int NoteID = Convert.ToInt32((decimal)ret);                        
0
 
Jai STech ArchCommented:
I owe a THANKS to you
your points brought me to the top 1000 !!!!!!!
:-)))))))))))
winning shot...it was a 50 day dream to me and it was acomplished in 51 days !!!! :-)))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 10
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now