charan sekhar

charan sekhar

  • NA
  • 108
  • 0

procedure returning no data even though data is present in database

Jun 21 2010 2:19 AM

hi, iam using asp.net2.0 with c#, with backend sql server2000
 
in my client system i have to create dynamically procedure and return the result
 
but when iam using in my local system the code is working fine but at client system it it not working can you correct my code which helps me
 
 
 

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

 

 
 
any date i select it is always showing no data.

Answers (3)