Procedure or function has too many argument specified

Question

I am trying to upload a .mdb file from my upload event from c# using stored procedure. but that mdb file is not loading data in tables of my sql server . when i debug the whole code i got above exception. My Stored Procedure parameter is:-(from stored procedure I m showing only its starting as it is sensitive)

  USE [Demo]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[UploadToDatabase] 
        @UserId bigint,
        @ClientMachineIP nvarchar(15),
        @LoadType nvarchar(25) ='Upload',
        @InstId bigint = null, 
        @bIsIgnoreErrors bit = 'False'
AS
BEGIN
    SET NOCOUNT ON;

    .
            declare @srcConnection varchar(MAX) 
        set @srcConnection = 'Data Source=PAth Of my .mdb file;Provider=Microsoft.Jet.OLEDB.4.0;'


            declare @ssispath varchar(1000)
            declare @cmd varchar(1000)
            set @ssispath = 'Package Path\Package.dtsx'
            set @cmd = @cmd + 'Package.Variables["SourceConnectionString"].Value = @srcConnection' 
            set @cmd = 'dtexec /F "' + @ssispath + '"' 
            print @cmd
            exec master..xp_cmdshell @cmd

It is after that loading data into my tables that code i m not showing at here.

I am using Linq to SQL for accessing sql server in the code hence for the stored procedure my designer.cs file code is

[Function(Name="dbo.UploadToDatabase")]
        public int UploadToDatabase([Parameter(Name="UserId", DbType="BigInt")] System.Nullable<long> userId, [Parameter(Name="ClientMachineIP", DbType="NVarChar(15)")] string clientMachineIP, [Parameter(Name="LoadType", DbType="NVarChar(25)")] string loadType, [Parameter(Name="InstId", DbType="BigInt")] System.Nullable<long> instId, [Parameter(DbType="Bit")] System.Nullable<bool> bIsIgnoreErrors, [Parameter(DbType="Int")] ref System.Nullable<int> rcout)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), userId, clientMachineIP, loadType, instId, bIsIgnoreErrors, rcout);
            rcout = ((System.Nullable<int>)(result.GetParameterValue(5)));
            return ((int)(result.ReturnValue));
        }

As I have layered architecture stored procedure calling on upload button is as-

DAL -Access.cs file

Public int? RunDTSxProcess(long userID, string clientMachineIP, string loadType, long instID, bool ignoreErrors)
        {
            try
            {
                int? result = 0;
                var run = Demo.UploadToDatabase(userID, clientMachineIP, loadType, instID, ignoreErrors,ref result);
                return result;
            }
            catch (Exception)
            {

                throw;
            }
        }   

Logic.cs

 public int? ExcuteDTSxProcess(long userID, string clientMachineIP, string loadType, long instID, bool ignoreErrors)
    {
        try

        {
            return new DAL.access().RunDTSxProcess( userID,  clientMachineIP,  loadType,  instID,  ignoreErrors);
        }
        catch (Exception)
        {
            throw;
        }
    }

On button click :-

protected void btnUpload_Click(object sender, EventArgs e)
        {
            pnlUploadData.Style.Value = "display:none;";
            pnlModify.Style.Value = "display:none;";
            if (fileUpload1.HasFile)
            {
                try
                {


                    string savePath = string.Empty;
                    savePath = ConfigurationManager.AppSettings["UploadDBFilePath"];



                    Server.ScriptTimeout = 100000000;

                    fileUpload1.SaveAs(Server.MapPath(savePath + fileUpload1.FileName));

                    try
                    {
                        //Call Upload procedure
                        **int? result = this.UploadModifyDatabase(savePath +
                              fileUploadEspace.FileName, "Upload");**
                        if(result == 0)
                            lblUploadSuccess.Text = "Database Upload successful";
                        else
                            lblUploadSuccess.Text = "Error while uploading the Database. Please try again.";

                    }
                    catch (Exception)
                    {
                        lblUploadSuccess.Text = "Error in append process. Please try again.";

                    }

                    lblUploadSuccess.Text = "File uploaded successfully.";
 catch (Exception ex)
                {
                    lblUploadSuccess.Text = "ERROR: " + ex.Message.ToString();
                }
            }
            else
            {
                lblUploadSuccess.Text = "You have not specified a file.";
            }

UploadModifyDatabase mehtod is:-

   protected int? UploadModifyDatabase(string fileName, string loadType)
            {
                try
                {
                    long userID = 0;
                    if (null != Session["UserID"])
                        userID = long.Parse(Session["UserID"].ToString());
                    long instID = 0;
                    if (null != Session["InstID"])
                        instID = long.Parse(Session["InstID"].ToString());
                    string clientMachineIP = null;

                    bool ignoreErrors = ckhIgnoreErrors.Checked;

                    int? result =  new BLL.logic().ExcuteDTSxProcess(userID, clientMachineIP, loadType, instID, ignoreErrors);
                    return result;

                }
                catch (Exception)
                {

                    throw;
                }
            }

Please tell me what is going wrong in the code. As per some reffered post I tried to add a a rcount variable in stored procedure but issue still not resolved.

Answer 1

Your Procedure UploadToDatabase has 5 parameter defined,but in designer.cs you have 6 parameter including rcout. You can change your procedure to have rcout as a parameter or output parameter if you are using it as output parameter.

 ALTER PROCEDURE [dbo].[UploadToDatabase] 
        @UserId bigint,
        @ClientMachineIP nvarchar(15),
        @LoadType nvarchar(25) ='Upload',
        @InstId bigint = null, 
        @bIsIgnoreErrors bit = 'False',
        @rcout int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    declare @srcConnection varchar(MAX) 
    set @srcConnection = 'Data Source=PAth Of my .mdb file;Provider=Microsoft.Jet.OLEDB.4.0;'
    declare @ssispath varchar(1000)
    declare @cmd varchar(1000)
    set @ssispath = 'Package Path\Package.dtsx'
    set @cmd = @cmd + 'Package.Variables["SourceConnectionString"].Value = @srcConnection' 
    set @cmd = 'dtexec /F "' + @ssispath + '"' 
    print @cmd
    exec master..xp_cmdshell @cmd
    Select @rcout= @@ROWCOUNT