returning null value from procedure

[developerit]

hi iam using asp.net2.0 with c# with sql server 2000 when iam executing the procedure in local system it is working fine.but when iam executing in client system procedure returns null value, i have checked all connection string , and session value all are working can you correct the code which helps me

    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", fromDate);
    cmdproc.Parameters.AddWithValue("@ToDate", toDate);

    SqlParameter p1 = cmdproc.Parameters.Add("@tot", SqlDbType.Decimal);
    p1.Direction = ParameterDirection.Output;
    if ((cmdproc.ExecuteScalar() != DBNull.Value) && (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");

    }

[Tej Aj]

check this post http://opexsolution.com/forum/viewtopic.php?f=15&t=55&p=169#p169[^]

[shreekar]

Not an answer, but a question - Why are you creating the SP in your code? Shreekar

[T M Gray]

Is the data the same in the two systems? Are you sure the query returns anything? From the documentation for ExecuteScalar: The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. You could try getting rid of the output parameter and just using a SELECT instead of a SET and RETURN. Also, look into @ strings rather than lots of concatenation.