Erwin Pombett

asked on 3/15/2017

i can not succeed to feed a Store Proc (with an identity column) with a Datatable from C# , what am i doing wrong ? .

Hello,

i'm stacked with the following problem:

From C# I'm trying to feed a store procedure MyStoreProc which has only one entry parameter that is a "TYPE AS TABLE"
my "TYPE AS TABLE" has id as first column which is declared as identity(1,1)

Now, in c# i've created a datatable which has the same number of columns (same header that columns name) that my "TYPE AS TABLE" including
the column for the id that i filled up with integers.
If I call my SP with the parameter which is the datatable , I receive the error:
INSERT into an identity column not allowed on table variables

If I remove the id column from the datatable and I call my SP I receive the error
Trying to pass a table-valued parameter with (X-1) column(s) where the corresponding user-defined table type requires (X) column(s).

how can i manage to call myStoreProc in order no to pass the id ?

thank you in advance for all your ideas, proposals.

toshi

Microsoft SQL Server

Last Comment

Erwin Pombett

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Nitin Sontakke

3/15/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Erwin Pombett

3/15/2017

ASKER

hello Nitin Sontakke,

thank you for your asnwer.
sorry for the explanation if they are not clear enough :/

actually i'm trying to pass unittest against the store proc. the store proc has it's adhoc type with the identity(1,1) as first column for the id.

is there a possibility to keep the table type (with it's id identity(1,1) as it is ?

thank you in advance for further help.

toshi

Nitin Sontakke

3/15/2017

If possible, please share precise location of problem area. The error you are getting is quite clear enough, no explanation require there.

INSERT into an identity column not allowed on table variables

Open in new window

.

So you definitely need to rectify it. The error (I think) is not related to table type. It is related to table variable, which is entirely different stuff. So wanted to see the relevant portion of the code.

Erwin Pombett

3/15/2017

ASKER

here we are:

------------------------------- call to the SP ----------------------------------------

 [TestMethod]
        public void InsertUsers__ThroughProcessUserSP__3People()
        {
            DataTable usersInDB = PeopleBuilderForInjection.GenerateDT_WithUsers();

            SqlCommand theCommand  = new SqlCommand();
            theCommand.Connection  = _theConnection; 
            theCommand.CommandType = CommandType.StoredProcedure;
            theCommand.CommandText = "[dbo].[User_Process]"; 

            SqlParameter paramTU = new SqlParameter();
                paramTU.ParameterName = "@allHFRUsers"; 
                paramTU.SqlDbType = SqlDbType.Structured;
                paramTU.TypeName = "[dbo].[UserType]";
                paramTU.Value = usersInDB; 

            theCommand.Parameters.Add(paramTU); 

            _theConnection.Open();

            SqlDataReader reader = theCommand.ExecuteReader();
.....

Open in new window

------------------------------------------------------------------------------------------------------
DataTable

---------------------- table type ------------------------------------------------------------------------------
note: i've removed the identiy(1,1) on the id

CREATE TYPE [sitel].[UserType] AS TABLE(
	[id] [int] NOT NULL,
	[EmployeeID] [nvarchar](50) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Gender] [nvarchar](15) NULL,
	[JobTitle] [nvarchar](255) NULL,
	[PreferredLanguage] [nvarchar](50) NULL,
	[Email] [nvarchar](255) NULL,
	[HomePhone] [nvarchar](50) NULL,
	[ExtensionAttribute5] [nvarchar](1024) NULL,
	[MobileNumber] [nvarchar](50) NULL,
	[StreetAddress] [nvarchar](1024) NULL,
	[City] [nvarchar](100) NULL,
	[ZipCode] [nvarchar](15) NULL,
	[State] [nvarchar](100) NULL,
	[Country] [nvarchar](50) NULL,
	[Active] [bit] NULL DEFAULT ((0)),
	[BusinessPhone] [nvarchar](50) NULL,
	[ExtensionAttribute1] [nvarchar](1024) NULL,
	[ExentsionAttribute2] [nvarchar](1024) NULL,
	[ExtensionAttribute27] [nvarchar](1024) NULL,
	[ExtensionAttribute3] [nvarchar](1024) NULL,
	[Province] [nvarchar](100) NULL,
	[Description] [nvarchar](max) NULL,
	PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Open in new window

if anything else ?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Nitin Sontakke

3/15/2017

The portion of sp where this parameter table is used.

Erwin Pombett

3/15/2017

ASKER

if i remove the identity(1,1)
my store proc has a prob with inserting then in the @currentUser....
error: id can not be null ....

ALTER PROCEDURE [sitel].[User_Process]
(
	@allHFRUsers [sitel].[UserType] readonly
)
AS


declare @ProcessResult as [sitel].[ProcessReportType]  /* pour les logs */
declare @ii int, @iiMax int   /* iterateurs limites */

declare @nbInserted int = 0,  @nbUpdated int = 0,  @nbDeleted int = 0
declare @nbErrors int = 0,  @nbWarnings int = 0



-- for current user received in entry
declare @CurrentUser as [sitel].[UserType]
-- for user
declare @HFRUCUser as [sitel].[UserType]

-- assigning index range for looping through users received.
select @ii=MIN(id), @iiMax =MAX(id) from @allHFRUsers

-- loop for controlling if it's an update, delete or a new person
	While @ii<=@iiMax
	BEGIN
		-- get an user - from received param - and set it in @CurrentUser
		delete @CurrentUser
		insert into @CurrentUser ([EmployeeID],[FirstName], [LastName], [Gender], [JobTitle], [PreferredLanguage] /*, ...  */ )
			select [EmployeeID],[FirstName], [LastName], [Gender], [JobTitle], [PreferredLanguage]
			from @allHFRUsers 
			where id=@ii
		
		-- get the Current user out of HFRUniversalConnector.User and assign to @HFRUCUser
		delete @HFRUCUser
		insert into @HFRUCUser ([EmployeeID],[FirstName], [LastName], [Gender], [JobTitle], [PreferredLanguage] /*, ...  */ )
			select [EmployeeID], [FirstName], [LastName] , [Gender], [JobTitle], [PreferredLanguage]   --....
			from [dbo].[User]
			where [EmployeeID] = (select top 1 [EmployeeID] from @CurrentUser)
.....

Open in new window

Nitin Sontakke

3/15/2017

I think then it is easy...(if I understand the problem correctly!)

Just code it as

insert into @CurrentUser (@ii, [EmployeeID],

Open in new window

and you are done. You want that value only isn't it?

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Nitin Sontakke

3/15/2017

Even this

where [EmployeeID] = (select top 1 [EmployeeID] from @CurrentUser)

Open in new window

can be changed to

where [EmployeeID] = @ii

Open in new window

Erwin Pombett

3/15/2017

ASKER

thank you ,
i'm going to try it out

toshi

Nitin Sontakke

3/15/2017

This interaction between us is an excellent example of how someone asking question should give complete information and explain scenario in detail.

We need to understand fully the scenario. You just mention the error, I just mention how to resolve it, doesn't work.

If we don't dig deeper, we will not be able to suggest which is the best way to resolve the issue.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Erwin Pombett

3/16/2017

ASKER

ok, i succed to work with my unittest and SP.

finally i had to remove the constraint on id which was identity(1,1) to manage it inside the SP.

thank you for your help.