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

[Abhinav Tripathi9]

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)
as
declare @openingtable
declare @strtdt date
declare @enddate date
declare @query nvarchar(max)
begin
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
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 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'
execute(@query)

end


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.



Continue reading...