[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-16
19
Medium Priority
?
228 Views
Last Modified: 2013-12-17
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
Comment
Question by:misdevelopment
  • 10
  • 7
  • 2
19 Comments
 
LVL 14

Expert Comment

by:Jai S
ID: 20083836
its preferrable to use SCOPE_IDENTITY() rather than @@identity
change you last line to
select SCOPE_IDENTITY()
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20083842
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
 

Author Comment

by:misdevelopment
ID: 20083845
Thanks for that. All changed, unfortunately, still having the same issues. That was a seriously speedy response though!!!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Jai S
ID: 20083850
did you try my second example ?
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20083853
just use like this...(for my first example)

select SCOPE_IDENTITY() as NewID;
0
 

Author Comment

by:misdevelopment
ID: 20083868
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20083885
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20083888
there is no reason that it should fail...

mysqlcom.CommandText = "SELECT SCOPE_IDENTITY() as col1";
this.COM_BATCH_ID = (int)mysqlcom.ExecuteScalar();
0
 

Author Comment

by:misdevelopment
ID: 20083926
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20083931
use int for cast...it works all the time
(int)retval
0
 

Author Comment

by:misdevelopment
ID: 20083932
In Locals, retval is shown as:

retval      16      object {decimal}
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20083938
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
 

Author Comment

by:misdevelopment
ID: 20083941
as per my entry: 20083926

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

This is getting very bizarre...

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20083948
Sorry wrong post

0
 
LVL 14

Accepted Solution

by:
Jai S earned 2000 total points
ID: 20083955
can you CAST it to a (decimal) then...
0
 

Author Comment

by:misdevelopment
ID: 20083956
So it's true.. even Genii can post in the wrong place :-)

no problem.
0
 

Author Comment

by:misdevelopment
ID: 20083992
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20084000
you can also use like this
            object ret = mysqlcom.ExecuteScalar();
            int NoteID = Convert.ToInt32((decimal)ret);                        
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20084011
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question