problem in sending and receiving date from c# code to sql procedure RRS feed

  • Question

  • i am generating a report through sending some parameter from c# code to sql procedure. i am sending a string value and two date values as a sql procedure parameters, but i am getting some problem in date parameter. I have checked my sql procedure after passing manual value it is giving right result but when i am passing through my code it is showing nothing in report. Kindly check the same. SQL Procedure:

    [GetLedger](@optb varchar(50),@startsession date,@endsession date)
    declare @openingtable
    declare @strtdt date
    declare @enddate date
    declare @query nvarchar(max)
    set @openingtable=@optb
    set @strtdt=@startsession
    set @enddate=@endsession
    set @query=N'SELECT  l.trandate,l.voucherno,l.itemno,l.itemname,o.opening + COALESCE(SUM(l.recieve - l.returnback - l.issue) OVER (PARTITION BY l.itemno ORDER BY l.trandate,seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS opening,l.recieve,l.returnback,l.issue,o.opening + SUM(l.recieve - l.returnback - l.issue) OVER (PARTITION BY l.itemno ORDER BY l.trandate,seq) AS balance,l.to_dept,l.remarks
    FROM @openingstock o
    SELECT trandate,voucherno,itemno,itemname,to_dept,remarks,isnull(recieve,0) AS recieve,isnull(issue,0) AS issue,isnull(returnback,0) AS returnback,
    row_number() over (partition by itemno,trandate order by itemno) as seq
    FROM @ledgertable
    ) l
    ON l.itemno = o.itemno
    where l.trandate between CONVERT(Date,''' + CONVERT(VARCHAR(10),@strtdt, 101)+''', 101) and CONVERT(DATE,'''+ CONVERT(VARCHAR(10),DATEADD(DD,1,@enddate),101) + ''', 101)
    ORDER BY l.itemno,trandate'

    C# code is:

    private ledgerdt GetData(string p1,string p2,string p3)
                string DateString = p2;
                IFormatProvider culture = new CultureInfo("en-US", true);
                DateTime pp2 = DateTime.ParseExact(DateString, "yyyy-MM-dd", culture);
                string DateString2 = p2;
                IFormatProvider culture2 = new CultureInfo("en-US", true);
                DateTime pp3 = DateTime.ParseExact(DateString, "yyyy-MM-dd", culture2);
                using (SqlConnection con = new SqlConnection(conString))
                    SqlCommand cmd = new SqlCommand("GetLedger", con);
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@optb", p1);
                        cmd.Parameters.AddWithValue("@startsession", pp2.ToString("yyyy-MM-dd"));
                        cmd.Parameters.AddWithValue("@endsession", pp3.ToString("yyyy-MM-dd"));
                        sda.SelectCommand = cmd;
                        using (ledgerdt ds=new ledgerdt())
                            sda.Fill(ds, "ledgerdt");
                            return ds;

    please tell the solution.....i know that i am doing something wrong in data type.

    Thursday, September 20, 2018 4:33 AM

All replies

  • Do you really have the tables called “@openingstock” and “@ledgertab”?

    In C#, since you return ds, do not use using with this variable.

    Thursday, September 20, 2018 7:57 AM
  • I'm perplexed that your "procedure" executes at all, cause it does not even compile due to sever syntactic errors. And even when correcting them, it cannot work. Cause you cannot access an outer table variable in the scope of an EXECUTE.

    But when I ignore all that (d'oh):

    Well, the problem is the missing SET NOCOUNT ON;. Thus you'll get two result sets in C# and you're evaluating the wrong one.

    Also, format your procedure, it is unreadable. After doing so, and after removing unnecessary code. E.g.

    ALTER PROCEDURE [GetLedger] (
        @optb VARCHAR(50) ,
        @startsession DATE ,
        @endsession DATE
        DECLARE @Start NVARCHAR(255) = '''' + FORMAT(@startsession, 'yyyyMMdd') + '''';
        DECLARE @End NVARCHAR(255) = '''' + FORMAT(@endsession, 'yyyyMMdd') + '''';
        DECLARE @query NVARCHAR(MAX) = N'
                SELECT l.trandate ,
                       l.voucherno ,
                       l.itemno ,
                       l.itemname ,
                       o.opening + 
                                SUM(l.recieve - l.returnback - l.issue) OVER ( PARTITION BY l.itemno ORDER BY l.trandate , l.seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) ,
                                0 ) AS opening ,
                       l.recieve ,
                       l.returnback ,
                       l.issue ,
                       o.opening + 
                            SUM(l.recieve - l.returnback - l.issue) OVER ( PARTITION BY l.itemno ORDER BY l.trandate , l.seq ) AS balance ,
                       l.to_dept ,
                FROM   @openingstock o
                       INNER JOIN (   SELECT trandate ,
                                             voucherno ,
                                             itemno ,
                                             itemname ,
                                             to_dept ,
                                             remarks ,
                                             ISNULL(recieve, 0) AS recieve ,
                                             ISNULL(issue, 0) AS issue ,
                                             ISNULL(returnback, 0) AS returnback ,
                                             ROW_NUMBER() OVER ( PARTITION BY itemno , trandate ORDER BY itemno ) AS seq
                                      FROM   @ledgertable ) l ON l.itemno = o.itemno
                WHERE	l.trandate BETWEEN @Start AND @End
                ORDER BY l.itemno,trandate';
        SET @query = REPLACE(@query, '@Start', @Start);
        SET @query = REPLACE(@query, '@End', @End);
        EXECUTE ( @query );

    btw, if @optb contains a table name, then you use the wrong data type. In this case it should be SYSNAME.

    p.s. using abbreviations for non-common terms is a bad idea, cause the reader must guess.

    p.p.s. it seems that you have a model issue. Using generic access procedure is bad style.
    Thursday, September 20, 2018 7:58 AM
  • thanks for your answer but i have got the solution.
    Thursday, September 20, 2018 8:44 AM
  • thanks for your answer but i have got the solution.
    It's nice if you were to explain your solution as others whom may have a similar issue could use your solution.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 20, 2018 11:16 AM