Stored procedure with parameters not working

Question

I am trying to add some filters (parameters) and it's not working... when I run the stored procedure, it returns the values I want however when I apply the filter I get the same results... any help would be most appreciated.

DAL:

public DataTable GetDebtOverview(string schoolCountry, string Centre, string salesRegion, string salesPerson, string agentCountry, string agentGroup)
{
        SqlConnection sqlConnection = new SqlConnection(connStr1);
        SqlCommand cmd = new SqlCommand();

        cmd.Parameters.AddWithValue("@schoolcountry", schoolCountry);
        cmd.Parameters.AddWithValue("@centre", Centre);
        cmd.Parameters.AddWithValue("@salesregion", salesRegion);
        cmd.Parameters.AddWithValue("@salesperson", salesPerson);
        cmd.Parameters.AddWithValue("@agentcountry", agentCountry);
        cmd.Parameters.AddWithValue("@agentgroup", agentGroup);

        DataTable dt = new DataTable();
        sqlConnection.Open();

        SqlDataReader dr = cmd.ExecuteReader();
        dt.Load(dr);

        sqlConnection.Close();

        return dt;
}

Service:

public List<DebtOverview> GetDebtOverviewList(string schoolCountry, string Centre, string salesRegion, string salesPerson, string agentCountry, string agentGroup)
{
        List<DebtOverview> debtoverviews = new List<DebtOverview>();

        sqlDal dal = new sqlDal();

        foreach (DataRow item in dal.GetDebtOverview(schoolCountry, Centre, salesRegion, salesPerson, agentCountry, agentGroup).Rows)
        {
            DebtOverview debtoverview = new DebtOverview();
            debtoverview.column_name = (string)item["column_name"];
            debtoverview.column_data = (decimal)item["column_data"];

            debtoverviews.Add(debtoverview);
        }
        return debtoverviews;
}

Model:

public class DebtOverview
{
    public string column_name { get; set; }
    public decimal column_data { get; set; }
}

Controller:

public ActionResult DebtOverviewRead(string schoolCountry, string Centre, string salesRegion, string salesPerson, string agentCountry, string agentGroup)
{
        Services.sqlService ss = new Services.sqlService();
        return Json(ss.GetDebtOverviewList(schoolCountry, Centre, salesRegion, salesPerson, agentCountry, agentGroup), JsonRequestBehavior.AllowGet);
}

Button:

$("#applyfilterFinance").click(function () {

    var url = "/Finance/DebtOverviewRead";

    var ftr1 = "'" + String($('#FinanceSchoolGeography').select2("val")).replace(/,/g, "','") + "'";
    var ftr2 = "'" + String($('#FinanceCentre').select2("val")).replace(/,/g, "','") + "'";
    var ftr3 = "'" + String($('#FinanceSalesRegion').select2("val")).replace(/,/g, "','") + "'";
    var ftr4 = "'" + String($('#FinanceSalesPerson').select2("val")).replace(/,/g, "','") + "'";
    var ftr5 = "'" + String($('#FinanceAgentCountry').select2("val")).replace(/,/g, "','") + "'";
    var ftr6 = "'" + String($('#FinanceAgentGroup').select2("val")).replace(/,/g, "','") + "'";

    var jsonObject = { schoolCountry: ftr1, Centre: ftr2, salesRegion: ftr3, salesPerson: ftr4, agentCountry: ftr5, agentGroup: ftr6 };

    $.post(url, jsonObject, function (data) {
        console.log(data);
    });
});

Stored procedure:

CREATE proc [dbo].[DashBoards_DebtOverview] 
    (@agentcountry varchar(500) = '',
     @SchoolCountry varchar(500) = '',
     @SalesRegion varchar(500) = '',
     @centre varchar(500) = '',
     @agentGroup varchar(max) = '',
     @salesperson varchar(500) = '')
AS
BEGIN
    --local variables
    declare @mdx as varchar(max)
    declare @FieldList as varchar(1000)
    declare @firstEntry varchar(50)
    declare @mdxCol varchar(100)

    --Agent Country 
    if rtrim(isnull(@agentcountry,''))=''
    begin
        set @fieldlist = '[Agent].[Agent Country].children'
    end
    else 
    begin
        set @FieldList = @agentcountry
        set @FieldList = REPLACE(@fieldlist,' ','')
        set @FieldList = ltrim(RTRIM(@FieldList))
        set @mdxCol = '[Agent].[Agent Country].[Agent Country].&['

        if @fieldlist like '%,%'
        begin
            set @firstEntry = (SELECT SUBSTRING(@FieldList, 1, CHARINDEX(',', @FieldList)-1) AS FirstName)
            set @FieldList = SUBSTRING(@FieldList,len(@firstentry)+1,len(@FieldList)+1-LEN(@firstentry))
            set @FieldList = '{' + @mdxCol + @firstEntry + '' + REPLACE(@fieldlist,',','],'+@mdxCol +'') + ']}'
        end
        else
        begin
            set @fieldlist = '[Agent].[Agent Country].[Agent Country].&[' + rtrim(ltrim(@fieldlist)) + ']'
        end
    end

    set @agentcountry = @fieldlist

    -- School Geography
    if rtrim(isnull(@SchoolCountry,''))=''
    begin
        set @fieldlist='[School Centre].[Country].children'
    end
    else 
    begin
        set @FieldList = @SchoolCountry
        set @FieldList=REPLACE(@fieldlist,' ','')
        set @FieldList=ltrim(RTRIM(@FieldList))
        set @mdxCol='[School Centre].[Country].&['

        if @fieldlist like '%,%'
        begin
            set @firstEntry=(SELECT SUBSTRING(@FieldList, 1, CHARINDEX(',', @FieldList)-1) AS FirstName)
            set @FieldList = SUBSTRING(@FieldList, len(@firstentry) + 1, len(@FieldList) + 1 - LEN(@firstentry))
            set @FieldList = '{' + @mdxCol + @firstEntry + '' + REPLACE(@fieldlist, ',', '],' + @mdxCol +'') + ']}'
        end
        else
        begin
            set @fieldlist = '[School Centre].[Country].&[' + rtrim(ltrim(@fieldlist)) + ']'
        end
    end

    set @SchoolCountry = @fieldlist

    --Centre
    if rtrim(isnull(@centre,''))=''
    begin
        set @fieldlist='[School Centre].[Centre].children'
    end
    else 
    begin
        set @FieldList = @centre
        set @FieldList=REPLACE(@fieldlist,' ',' ')
        set @FieldList=ltrim(RTRIM(@FieldList))
        set @mdxCol='[School Centre].[Centre].&['
        if @fieldlist like '%,%'
        begin
            set @firstEntry=(SELECT SUBSTRING(@FieldList, 1, CHARINDEX(',', @FieldList)-1) AS FirstName)
            set @FieldList=SUBSTRING(@FieldList,len(@firstentry)+1,len(@FieldList)+1-LEN(@firstentry))
            set @FieldList='{' + @mdxCol + @firstEntry + '' + REPLACE(@fieldlist,',','],'+@mdxCol +'') + ']}'
        end
        else
        begin
            set @fieldlist='[School Centre].[Centre].[All].[' + rtrim(ltrim(@fieldlist)) + ']'
        end
    end

    set @centre=@fieldlist

    -- Sales Region
    if rtrim(isnull(@SalesRegion,''))=''

    begin
    set @fieldlist='[Agent].[Sales Region].children'
    end
    else 
    begin
        set @FieldList = @SalesRegion
        set @FieldList=REPLACE(@fieldlist,' ','')
        set @FieldList=ltrim(RTRIM(@FieldList))
        set @mdxCol='[Agent].[Sales Region].&['
        if @fieldlist like '%,%'
        begin
            set @firstEntry=(SELECT SUBSTRING(@FieldList, 1, CHARINDEX(',', @FieldList)-1) AS FirstName)
            set @FieldList=SUBSTRING(@FieldList,len(@firstentry)+1,len(@FieldList)+1-LEN(@firstentry))
            set @FieldList='{' + @mdxCol + @firstEntry + '' + REPLACE(@fieldlist,',','],'+@mdxCol +'') + ']}'
        end
        else
        begin
            set @fieldlist='[Agent].[Sales Region].&[' + rtrim(ltrim(@fieldlist)) + ']'
        end
    end

    set @SalesRegion=@fieldlist

    --Sales Person
    if rtrim(isnull(@salesperson,''))=''

    begin
    set @fieldlist='[Agent].[Sales Person].children'
    end
    else 
    begin
        set @FieldList = @salesperson
        set @FieldList=REPLACE(@fieldlist,' ','')
        set @FieldList=ltrim(RTRIM(@FieldList))
        set @mdxCol='[Agent].[Sales Person].&['
        if @fieldlist like '%,%'
        begin
            set @firstEntry=(SELECT SUBSTRING(@FieldList, 1, CHARINDEX(',', @FieldList)-1) AS FirstName)
            set @FieldList=SUBSTRING(@FieldList,len(@firstentry)+1,len(@FieldList)+1-LEN(@firstentry))
            set @FieldList='{' + @mdxCol + @firstEntry + '' + REPLACE(@fieldlist,',','],'+@mdxCol +'') + ']}'
        end
        else
        begin
            set @fieldlist='[Agent].[Sales Person].&[' + rtrim(ltrim(@fieldlist)) + ']'
        end
    end

    set @salesperson=@fieldlist

    --Agent Group
    if rtrim(isnull(@agentgroup,''))=''
    begin
        set @fieldlist='[Agent].[Agent Group].children'
    end
    else 
    begin
        set @FieldList = @agentgroup
        set @FieldList=REPLACE(@fieldlist,' ','')
        set @FieldList=ltrim(RTRIM(@FieldList))
        set @mdxCol='[Agent].[Agent Group].&['
        if @fieldlist like '%,%'
        begin
            set @firstEntry=(SELECT SUBSTRING(@FieldList, 1, CHARINDEX(',', @FieldList)-1) AS FirstName)
            set @FieldList=SUBSTRING(@FieldList,len(@firstentry)+1,len(@FieldList)+1-LEN(@firstentry))
            set @FieldList='{' + @mdxCol + @firstEntry + '' + REPLACE(@fieldlist,',','],'+@mdxCol +'') + ']}'
        end
        else
        begin
            set @fieldlist='[Agent].[Agent Group].&[' + rtrim(ltrim(@fieldlist)) + ']'
        end
    end

    set @agentgroup=@fieldlist


    set @mdx='

                select {[Measures].[14 days] ,
                [Measures].[30 days],
                [Measures].[60 days],
                [Measures].[90 days],
                [Measures].[120 days],
                [Measures].[over 120 days],
                [Measures].[Not Yet Due],
                [Measures].[Unallocated]} on columns
    from [EC PTL AgedDebtors] where (' + @agentcountry + ',' + @SchoolCountry + ',' + @SalesRegion + ',' + @centre + ',' + @salesperson + ',' + @agentgroup + ')'


    declare @InnerSQL as nvarchar(max)
    declare @SQL as nvarchar(max)
    set @InnerSQL='select * from openquery(EC_PTL_CUBE,''' +   @mdx +''')'

    create table #table1 (fourteen decimal(14), thirty decimal(14), sixty decimal(14), ninety decimal(14), onetwenty decimal(14), moreonetwenty decimal(14),
            notyet decimal(14), unallocated decimal(14), duenow decimal(14));

    create table #table2(column_name varchar(50), column_data decimal(14) )
    set @SQL ='
    insert into #table1
    Select 
    convert(decimal(14),"[Measures].[14 days]") AS [14 days]    ,
    convert(decimal(14),"[Measures].[30 days]") AS [30 days]    ,
    convert(decimal(14),"[Measures].[60 days]") AS [60 days]    ,
    convert(decimal(14),"[Measures].[90 days]") AS [90 days]    ,
    convert(decimal(14),"[Measures].[120 days]") AS [120 days]  ,
    convert(decimal(14),"[Measures].[over 120 days]") AS [over 120 days]    ,
    convert(decimal(14),"[Measures].[Not Yet Due]") AS [Not Yet Due]    ,
    convert(decimal(14),"[Measures].[Unallocated]") AS [Unallocated]    ,

    convert(decimal(14),"[Measures].[14 days]")+
    convert(decimal(14),"[Measures].[30 days]") +
    convert(decimal(14),"[Measures].[60 days]") +
    convert(decimal(14),"[Measures].[90 days]") +
    convert(decimal(14),"[Measures].[120 days]")+
    convert(decimal(14),"[Measures].[over 120 days]") AS [Due Now]

    from (' + @InnerSQL + ') innerMDX'

    exec sp_executesql @SQL;

    insert into #table2
    select 'Due Now' as Column_Name, duenow as column_Data from #table1;

    insert into #table2
    select '14 days' as Column_Name, fourteen as column_Data from #table1;

    insert into #table2
    select '30 days' as Column_Name, thirty as column_Data from #table1;

    insert into #table2
    select '60 days' as Column_Name, sixty as column_Data from #table1; 

    insert into #table2
    select '90 days' as Column_Name, ninety as column_Data from #table1;

    insert into #table2
    select '120 days' as Column_Name, onetwenty as column_Data from #table1;

    insert into #table2
    select 'over 120 days' as Column_Name, moreonetwenty as column_Data from #table1;

    insert into #table2
    select 'Unallocated' as Column_Name, unallocated as column_Data from #table1;

    select column_name, column_data from #table2;

    drop table #table1;
    drop table #table2;

    end

how would that help me? the sp does work and when running it and i put in a parameter its returning the correct values...i believe its something in c# — Gawai, Sep 10 '15 at 13:24

score 0 · Answer 1 · edited May 23 '17 at 12:14

0

At no point in GetDebtOverview do you set the commandtext to the SP or the command type correctly.

See How to execute a stored procedure within C# program

edited May 23 '17 at 12:14

Community

1
1

answered Sep 10 '15 at 13:31

Mike Miller

16,195
1
20
27

I added the commandtype however I am getting an error..{"ExecuteReader: Connection property has not been initialized."} – Gawai Sep 10 '15 at 13:42
still doesn't work..keep getting syntex errors however the string is parsing in the correct format 'Example' – Gawai Sep 11 '15 at 09:08
this needs to be SqlCommand cmd = new SqlCommand("", sqlConnection); cmd.CommandType = CommandType.StoredProcedure; – Mike Miller Sep 11 '15 at 09:25
Hey Mike, i have tried the above and i still getting errors. {"Incorrect syntax near 'Malta'."} however my string being parsed to the parameter is 'Malta' – Gawai Sep 11 '15 at 11:36

Stored procedure with parameters not working

1 Answers1