procedure returning no data even though data is present in database

 

 
string strcon = "Data Source=";
strcon += Request.Params["REMOTE_ADDR"] + ";" + "Initial Catalog=POS;User ID=sa;Password=xx103";
 
SqlConnection concreate = new SqlConnection(strcon);
string pp = "";
pp = "create procedure getsum( @BranchKey int,@FromDate smalldatetime,@ToDate smalldatetime, @tot decimal output )";
pp += "as";
pp += "\n";
pp += "set @tot=(SELECT SUM(Amount) FROM AllTransactions WHERE BranchKey=@BranchKey and (TransCode <> 0) and (TransDate >@FromDate) and (TransDate < @ToDate) GROUP by BranchKey)";
pp += "return @tot";
pp += ";";
 
SqlCommand cmdcreate = new SqlCommand(pp, concreate);
concreate.Open();
cmdcreate.ExecuteNonQuery();
concreate.Close();
 
SqlConnection conproc = new SqlConnection(strcon);
conproc.Open();
SqlCommand cmdproc = new SqlCommand("getsum", conproc);
cmdproc.CommandType = CommandType.StoredProcedure;
 
cmdproc.Parameters.AddWithValue("@BranchKey", Convert.ToInt32(Session["BranchKey"]));
DateTime dt = Convert.ToDateTime(txtfrom.Text.ToString());
string fromDate = dt.ToShortDateString();
DateTime dt1 = Convert.ToDateTime(txtto.Text.ToString());
string toDate = dt1.ToShortDateString();
 
cmdproc.Parameters.AddWithValue("@FromDate", SqlDbType.SmallDateTime).Value = fromDate;
cmdproc.Parameters.AddWithValue("@ToDate", SqlDbType.SmallDateTime).Value=toDate;
SqlParameter p1 = cmdproc.Parameters.Add("@tot", SqlDbType.Decimal);
p1.Direction = ParameterDirection.Output;
if ((Convert.ToInt32(cmdproc.ExecuteNonQuery())!= -1) && (cmdproc.Parameters["@tot"].Value)!= DBNull.Value)
{
decimal ot = Convert.ToDecimal(cmdproc.Parameters["@tot"].Value);
 
if (ot != null)
{
txttotal.Text = ot.ToString();
conproc.Close();
}
}
else
{
 
clsdataset.ShowAlertMessage("No Data");
txttotal.Text = "";
}