How can I display the data in gridview based on the date entered in textbox using C#

Question

0.00/5 (No votes)

See more:

I have below columns in my database as

Sno                Status              FromDate              ToDate
1                  Pending             02/20/2017            02/26/2017
2                  Hold                01/10/2017            01/25/2017
3                  Completed           02/21/2017            02/26/2017
4                  Completed           02/22/2017            02/28/2017
5                  Hold                01/15/2017            01/30/2017
6                  Pending             01/20/2017            02/22/2017

If we enter 02/25/2017 in textbox and click get then i need to show count of below records in gridview as

Pending              Hold                Completed
  1                    0                     2

Count 1 in pending is the first record in database as it has from and to date between the range 02/25/2017, Count 2 in Completed is the 3rd and 4th record in database as it has from and to date between the range 02/25/2017 and hold has count 0 as the date given in text box does not contain in hold.how can i do this can anyone help me out as i am new to this

What I have tried:

How can i do this either with c# code or with query

Posted 23-Jan-18 19:22pm

kav@94

Updated 23-Jan-18 19:57pm

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)

debasish mishra · Answer 1 · 2018-01-23T19:57:00

Solution 1

This is the idea below in SQL.You may get some error while executing because this below query was not checked.But the idea lies between where condition and Inner join

SELECT S.Status,  C.cnt
  FROM YourTable S
       INNER JOIN (SELECT Status, count(Status) as cnt
                     FROM YourTable
                    GROUP BY Status) C ON S.Status= C.Status where CONVERT(VARCHAR(10), inputdate, 101)between CONVERT(VARCHAR(10),S.FromDate,101) and CONVERT(VARCHAR(10),S.ToDate,101)

In above query

inputdate

is the date that you pass from your c# code to the stored procedure as input parameter.

After getting this data from sql feed it to the data table or list what ever you use and then bind to the data grid/gridview.

Posted 23-Jan-18 19:57pm

debasish mishra

Updated 23-Jan-18 19:59pm

v2

Comments

kav@94 24-Jan-18 1:58am

i want to make it through c# code as i had already tried using sql query

[no name] 24-Jan-18 2:00am

As you mentioned with C# or query Hence i posted the SQL query.If you can achieve with SQL then why yo need to do with C#.Just asking.

kav@94 24-Jan-18 2:20am

because sql query is not working how can i pass the entered date in textbox to sql query

[no name] 24-Jan-18 2:29am

You can get the textbox value by usinf Texboxid.Text and then use like this below.But, in real time scenario you should use principles of tier architectures.If your project demands ORM you can easily do with Entity framework.:

using (SqlConnection con = new SqlConnection(your connectionstring)) {
using (SqlCommand cmd = new SqlCommand("Your stored procedure name", con)) {
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@inputDate", SqlDbType.VarChar).Value = txtDateToCompare.Text;

con.Open();
cmd.ExecuteNonQuery();
}

kav@94 24-Jan-18 7:58am

I already have the query how can i get the data using this query by passing the date that i entered in textbox and by checking whether it is between from date and to date
SELECT (SELECT PName FROM PDet WHERE (ID = dws.PName )) AS ProjName, TTitle AS Title,
(SELECT Name FROM User WHERE (ID = dws.Dname)) AS Devname, Status,
(SELECT Name FROM User AS User_a WHERE (ID = dws.AWork))
AS AssWork, FromDate, ToDate, DescATask FROM Status AS ws

[no name] 24-Jan-18 8:19am

I don't see any Percentage calculation from your above query.

kav@94 24-Jan-18 8:51am

what is the percentage calculation you are asking about my above query is giving me this data
Sno Status FromDate ToDate
1 Pending 02/20/2017 02/26/2017
2 Hold 01/10/2017 01/25/2017
3 Completed 02/21/2017 02/26/2017
4 Completed 02/22/2017 02/28/2017
5 Hold 01/15/2017 01/30/2017
6 Pending 01/20/2017 02/22/2017
now i need to modify my above query to get this data
Pending Hold Completed
1 0 2

[no name] 24-Jan-18 9:03am

Did you try executing above query created in solution.

kav@94 24-Jan-18 9:06am

no using this query i need to create that solution

I already have the query how can i get the data using this query by passing the date that i entered in textbox and by checking whether it is between from date and to date
SELECT (SELECT PName FROM PDet WHERE (ID = dws.PName )) AS ProjName, TTitle AS Title,
(SELECT Name FROM User WHERE (ID = dws.Dname)) AS Devname, Status,
(SELECT Name FROM User AS User_a WHERE (ID = dws.AWork))
AS AssWork, FromDate, ToDate, DescATask FROM Status AS ws