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 = "";}