1

I am creating a web page to store data which is read from a Microsoft Excel worksheet.

I am passing the data to a stored procedure in SQL Server 2008.

Here is my C# code: 

SqlConnection conn = new SqlConnection(AppDB);
conn.Open();

SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_ins_TaskDetails_from_Excel";

command.Parameters.AddWithValue("@TaskDetails", dtTaskDetailsFromExcel);

string sReturnValue = command.ExecuteNonQuery().ToString();

The stored procedure has one user defined table data type as parameter.

Here is my stored procedure:

ALTER PROCEDURE dbo.sp_ins_TaskDetails_from_Excel
(
@TaskDetails TypeInsertTaskFromExcel11 readonly
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE 
    @ProjectID int,
    @ProjectTeamID int,
    @TeamLeaderUserID int,
    @TaskCategoryName varchar(max),
    @TaskDescription varchar(max),
    @TeamMemberUserID int,
    @TaskPriorityName varchar(10),
    @PlanDuration float,
    @PlanStartDate datetime,
    @PlanEndDate datetime,
    @TaskTypeName varchar(30),
    @TaskStatusName varchar(30),
    @TaskAllotName varchar(10),
    @CreatedBy varchar(30),
    @CreatedDate datetime,
    @ISMailSend bit,
    @Isvisible bit,
    @UniqueID int

DECLARE TMSTaskDetailFromExcelCursor CURSOR FOR SELECT

    ProjectID,
    ProjectTeamID,
    TeamLeaderUserID,
    TaskCategoryName,
    TaskDescription,
    TeamMemberUserID,
    TaskPriorityName,
    PlanDuration,
    PlanStartDate,
    PlanEndDate,
    TaskTypeName,
    TaskStatusName,
    TaskAllotName,
    CreatedBy,
    CreatedDate,
    ISMailSend,
    Isvisible,
    UniqueID

FROM @TaskDetails
OPEN TMSTaskDetailFromExcelCursor
FETCH NEXT FROM TMSTaskDetailFromExcelCursor INTO

    @ProjectID,
    @ProjectTeamID,
    @TeamLeaderUserID,
    @TaskCategoryName,
    @TaskDescription,
    @TeamMemberUserID,
    @TaskPriorityName,
    @PlanDuration,
    @PlanStartDate,
    @PlanEndDate,
    @TaskTypeName,
    @TaskStatusName,
    @TaskAllotName,
    @CreatedBy,
    @CreatedDate,
    @ISMailSend,
    @Isvisible,
    @UniqueID;
WHILE @@FETCH_STATUS=0 BEGIN

-- Insert statements for procedure here
INSERT INTO ManageTasks (ProjectID, ProjectTeamID, TeamLeaderUserID, 
    TaskCategoryName,TaskDescription, TeamMemberUserID, TaskPriorityName, 
    PlanDuration, PlanStartDate, PlanEndDate, TaskTypeName, 
    TaskStatusName, TaskAllotName, CreatedBy, CreatedDate, ISMailSend, Isvisible,UniqueID)
VALUES (@ProjectID, @ProjectTeamID, @TeamLeaderUserID,  @TaskCategoryName,
    @TaskDescription, @TeamMemberUserID, @TaskPriorityName, @PlanDuration,
    @PlanStartDate, @PlanEndDate, @TaskTypeName, @TaskStatusName,
    @TaskAllotName, @CreatedBy, @CreatedDate, @ISMailSend,@Isvisible,@UniqueID);

FETCH NEXT FROM TMSTaskDetailFromExcelCursor INTO
        @ProjectID,
    @ProjectTeamID,
    @TeamLeaderUserID,

    @TaskCategoryName,
    @TaskDescription,
    @TeamMemberUserID,

    @TaskPriorityName,
    @PlanDuration,
    @PlanStartDate,

    @PlanEndDate,
    @TaskTypeName,
    @TaskStatusName,

    @TaskAllotName,
    @CreatedBy,
    @CreatedDate,

    @ISMailSend,
    @Isvisible,
    @UniqueID;
END;

CLOSE TMSTaskDetailFromExcelCursor;
DEALLOCATE TMSTaskDetailFromExcelCursor;
END

It receives the data and using a cursor in the same stored procedure each row will be inserted using an Insert statement in the same procedure.

While executing, all the data from Excel is passed to the stored procedure as exactly mentioned in the user defined table type.

But, the values are not stored in database and the stored procedure returns -1.

All the values are in correct format and order. I don't know what is going wrong.

Is there something I should change in the stored procedures?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
matheen
  • 35
  • 1
  • 1
  • 6
  • 3
    why not use a simple insert .. select .. from @TaskDetails statement? – Aleksandr Fedorenko Sep 21 '13 at 12:37
  • 1
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Sep 21 '13 at 13:43
  • @Marc_s Thank you for your information. It would definitely save my time a lot. – matheen Sep 21 '13 at 13:58
  • Also see this discussion about [SET NOCOUNT ON usage](http://stackoverflow.com/questions/1483732/set-nocount-on-usage) in stored procedures; specifically, [this](http://stackoverflow.com/a/1483737/347805) answer – Sarin Sep 21 '13 at 14:05

0 Answers0