better code for this ?

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

, +

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();

  }

and this sp is to search date

SQL

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


AS
BEGIN

    SET NOCOUNT ON;


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

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

Posted 26-May-12 18:09pm

ythisbug

Updated 26-May-12 18:52pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2012-05-26T19:19:00

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:

C#

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

Refer: MSDN: SqlDbType Enumeration[^]

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

SQL

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

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