更好的代码呢? [英] better code for this ?

问题描述

hi在我的项目中，我使用两个下拉列表来搜索记录，并在单击搜索"按钮后显示了两个日期之间的总和.
但是问题无法显示两个日期之间的总和.我的意思是我只能从两个下拉列表中显示相同的日期记录.所以我必须在两个下拉列表中选择相同的日期.

所以这是我的代码，用于搜索reocrd并显示总和

hi in my project am using two dropdowns to search records and after clicking search button am displaying total sum between two dates.
but issue is am not able to display sum between two dates.i mean i can display only same dates record from two dropdowns.so i have to select same date in two dropdowns.

so here is my code to search reocrd and display total sum

protected void ibtnSearch_Click(object sender, ImageClickEventArgs e)
  {
      SearchAllPL stPL = new SearchAllPL();
      stPL.Date = ddSearch1.SelectedValue;
      stPL.Date = ddlSearch2.SelectedValue;


      SearchAllBLL stBLL = new SearchAllBLL();
      DataTable dt = new DataTable();
      dt = stBLL.SearchVisaAndEmigrationDate(stPL);

      if (dt.Rows.Count > 0)
      {
          grdCurrencyProfit.DataSource = dt;
          grdCurrencyProfit.DataBind();
      }


      string StartDate;
      StartDate = ddSearch1.SelectedValue;
      string StartDate2 = ddlSearch2.SelectedValue;

      con.Open();

      SqlCommand cmd = new SqlCommand();
      cmd.Connection = con;
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = "usp_RptProfitVisaAndEmigration";



      SqlParameter pSum = new SqlParameter("@Sum", SqlDbType.Int);
      SqlParameter pStartDate = new SqlParameter("@STARTDATE", SqlDbType.VarChar,50);
      SqlParameter pEndDate = new SqlParameter("@ENDDATE", SqlDbType.VarChar,50);



      pStartDate.Value = StartDate;
      pEndDate.Value = StartDate2;

      pSum.Direction = ParameterDirection.Output;

      cmd.Parameters.Add(pSum);
      cmd.Parameters.Add(pStartDate);
      cmd.Parameters.Add(pEndDate);

      SqlDataReader dr = cmd.ExecuteReader();



      tbSum.Text = pSum.Value.ToString();



      con.Close();

  }

这个sp是要搜索日期

and this sp is to search date

ALTER PROCEDURE [dbo].[usp_SearchTicketingDate]
@STARTDATE datetime,
@ENDDATE datetime


AS
BEGIN

    SET NOCOUNT ON;


    SELECT * from tblTicketing  where tDateofJourney=@STARTDATE and tDateofJourney=@ENDDATE
END

所以任何人都可以建议我..如何在aspx.cs中提供代码以查找两个日期之间的总和..现在能够找出同一日期之间的总和，
表示如果在同一日期的数据库中是否有3条记录，并且我从下拉列表中选择日期，我将获得总计..

所以请建议我.
并在下拉列表中获取两个日期之间的总和是否好.???

感谢

so can any one suggest me..how to give code in aspx.cs to find the total sum between two dates..now am able to find out total sum between same date,,
means if in my data base for same date if there 3 records and i select from dropdowns tat date i will get total sum..

so plea suggest me..
and is it good to get total sum for between two dates in dropdowns.???

thanks

推荐答案

找到两个日期之间的总和
您无法获取两个日期之间的数据的原因是您使用日期参数的方式.您需要将其作为DateTime字段而不是字符串传递，然后根据Datetime 操作更改查询.

需要更改:
1.将日期作为DateTime参数传递给SP
2.更改SP查询以将其处理为DateTime，然后将其作为WHERE子句进行处理.

像这样的东西:

find the total sum between two dates
The reason why you are unable to get the data between two dates is the way you have used the date parameters. You need to pass it as DateTime fields instead of a string and change query based on Datetime operation.

Changes needed:
1. Pass dates as DateTime parameters to SP
2. Change SP query to handle it as DateTime and then WHERE clause based on it.

Something like:

SqlParameter pStartDate = new SqlParameter("@STARTDATE", SqlDbType.DateTime);
SqlParameter pEndDate = new SqlParameter("@ENDDATE", SqlDbType.DateTime);
 
pStartDate.Value = Convert.ToDateTime(StartDate);
pEndDate.Value = Convert.ToDateTime(StartDate2);

参考: MSDN:SqlDbType枚举 [ ^ ]

将查询更改为(假设tDateofJourney在DB中定义为Date feild，如果未定义的话):

Refer: MSDN: SqlDbType Enumeration[^]

Change query to (assuming tDateofJourney is defined as Date feild in DB, if not define it):

SELECT * from tblTicketing  where tDateofJourney BETWEEN @STARTDATE and @ENDDATE

参考: MSDN:BETWEEN(Transact-SQL) [

Refer: MSDN: BETWEEN (Transact-SQL)[^]

这篇关于更好的代码呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！