Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Somehow it is not clear what exactly you are trying to achieve. As a first step (I think) remove the identity column from table type parameter.
What you actually pass from C# to SQL Server is data and nothing else. Agreed?
You can implement increasing column value by 1 logic in C#.
What you actually pass from C# to SQL Server is data and nothing else. Agreed?
You can implement increasing column value by 1 logic in C#.
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
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
If possible, please share precise location of problem area. The error you are getting is quite clear enough, no explanation require there.
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.
INSERT into an identity column not allowed on table variables
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.
here we are:
--------------------------
DataTable
---------------------- table type --------------------------
note: i've removed the identiy(1,1) on the id
if anything else ?
--------------------------
[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();
.....
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
if anything else ?
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 ....
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)
.....
I think then it is easy...(if I understand the problem correctly!)
Just code it as
and you are done. You want that value only isn't it?
Just code it as
insert into @CurrentUser (@ii, [EmployeeID],
and you are done. You want that value only isn't it?
Even this
can be changed to
where [EmployeeID] = (select top 1 [EmployeeID] from @CurrentUser)
can be changed to
where [EmployeeID] = @ii
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial