showing only the filtered column in the datagridview

Question

0.00/5 (No votes)

See more:

i am a newbie and a student i have a windows form in which is have a datadrid view. i have table in sql server, i have binded with the datagrid, in the table there is a 3 column and in which one column named date of small-date-time datatype, i just binded the datagrid view to the table and it is showing all the rows of the three column i want to filter that. i have three column one is "name" second is "surname" and third is "date" date contains smalldatetime datatype and i want to show all the three columns but the columns which has the "date" columns as 30/12/12 if the date is 31/12/12 means the record from the previous date sorry for the bad explanation i am weak in english i dont know what to use here can i achieve this?

Posted 31-Dec-12 1:14am

shaikh-adil

Updated 31-Dec-12 2:27am

v2

Add a Solution

Comments

sariqkhan 31-Dec-12 8:23am

bro i have commented on my freinds and called them... they will help u soon. i am also searching

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)

OriginalGriff · Answer 1 · 2012-12-31T02:31:00

Solution 1

Try:

SQL

SELECT * FROM myTable WHERE CONVERT(date, [date])=CONVERT(date, DATEADD(d, -1, GETDATE()))

Posted 31-Dec-12 2:31am

OriginalGriff

Comments

shaikh-adil 31-Dec-12 8:36am

sir where i have to write this

OriginalGriff 31-Dec-12 8:48am

Exactly where you have to write it depends on how you are loading your DataGridView. But if you find the Select command you are currently using (it will start with "SELECT" and contain your table name) and replace the text with the above - remembering to change the table name - it should work.
Look at where you create your DataAdapter if you are using one.

shaikh-adil 31-Dec-12 8:59am

thank you very much sir thanks a lot
can you explain what it is?
CONVERT(date, [date])=CONVERT(date, DATEADD(d, -1, GETDATE()))
how it works

OriginalGriff 31-Dec-12 9:16am

You're welcome!
CONVERT is an SQL function that changes one datatype to another. In this case, it takes a DateTime and convertes it to a Date - the difference being that Date has no time element, and DateTime (obviously) does.
DATEADD is an SQL function that adds a specified amount of time to an existing DateTime. In this case, we specify "d" for "days" and the number as minus one, meaning that it returns the date yesterday.
You need to CONVERT on each side to discard the time element - both from the current date and teh version you are checking against. Otherwise, it would compare the time stored in the database against the time now, and not return records entered 25 hours ago, but still after midnight yesterday.
So if the Database record is '2012-12-30 06:45' and the current date is '2012-12-31 14:13'
CONVERT(date, [date])
returns '2012-12-30' having discarded the time part
DATEADD(d, -1, GETDATE())
returns '2012-12-30 14:13' having taken one day from the current
and
CONVERT(date, DATEADD(d, -1, GETDATE()))
returns '2012-12-30' having discarded the time part of that.
The comparison then matches, and the record is returned.

shaikh-adil 31-Dec-12 9:29am

current date is '2012-12-31 14:13'
CONVERT(date, [date]) returns '2012-12-31' having discarded the time part
i think this must be written in the explanation right?

OriginalGriff 31-Dec-12 9:38am

No - the [date] part gets the value from the current row, from the column called "date". It needs the '[' and ']' because "date" is an SQL reserved word (it is the datatype that has no time part) so the brackets stop SQL from looking at it as anything other than a column name. (Generally it is best not to call columns things like "date" or "time" or "count" because they do tend to clash with reserved words - "InsertDate" or "UserCount" is normally better).
The GETDATE function returns the current date and time.

shaikh-adil 31-Dec-12 9:47am

okay i got

shaikh-adil 31-Dec-12 9:11am

what is this sir
DATEADD(d, -1, GETDATE()))

OriginalGriff 31-Dec-12 9:20am

If you aren't sure what an SQL function does, the best place to look is here:
http://www.w3schools.com/sql/default.asp
They cover SQL syntax pretty well and explain functions with simple examples (but JOINS might make your head spin for a while!) Use the search feature to find the function you want:
http://www.w3schools.com/sql/func_dateadd.asp

shaikh-adil 31-Dec-12 9:17am

sir if i want all the columns from last week so i edited to
SELECT * FROM myTable WHERE CONVERT(date, [date])=CONVERT(date, DATEADD(d, -7, GETDATE()))
but the problem is it is showing all the column on that day if i want all the entries till 7 days how can i do that?

OriginalGriff 31-Dec-12 9:22am

:laugh:
Change the "equals" to "greater-than-or-equals"
...[date])=CONVERT...
becomes
...[date])>=CONVERT...

shaikh-adil 31-Dec-12 9:25am

laugh
i am a kid in dotnet
silly mistake

OriginalGriff 31-Dec-12 9:28am

Easily done!

shaikh-adil 31-Dec-12 9:27am

thank you very very much sir, you are the father of coding. and thank you for the explanation

OriginalGriff 31-Dec-12 9:29am

You're welcome!