Click here to Skip to main content
12,394,887 members (76,199 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB SQL-Server VB.NET
Hi Guys, I have a datagrid .

Dim strQuery As String
            strQuery = "SELECT BENCHMARKNO 'BMK NO',BENCHMARKNAME 'BMK Name',CLIENTCODE 'Client ID',NOFILES 'No.of Files',REFERENCENO 'Reference No',NORESOURCES 'Resources',REPLACE(CONVERT(VARCHAR,STARTDATE,106),' ','-') AS 'Start Date', REPLACE(CONVERT(VARCHAR,FINISHDATE,106),' ','-')  AS  'Finish Date', AUTHORIZEDBY 'Authorized By',JOBNO 'Converted To Job No',REMARKS 'Remarks' FROM QMS_BENCHMARK ORDER BY BENCHMARKNO"
            Dim pBMKTrack As DataTable = GetTable(strQuery)
            dGridview.DataSource = pBMKTrack

in that query , start date and finish date are columns.

if i can tried to sort the column. it doesn't sorted.

I got the dates like these

example : 01-jan-2012

03-Feb-2010
12-aug-2013

I want : 03-Feb-2010
01-jan-2012
12-aug-2013

total date format should be changed.

In DataBase STARTTIME AND FINISHTIME COLUMNS DATATYPE ARE DATETIME.


Please help me ....
Posted 22-Mar-13 4:22am
Comments
ThePhantomUpvoter 22-Mar-13 10:33am
   
Maybe you could rephrase this. The title of your question, the query and the narrative all discuss 3 different things that appear to have nothing to do with each other. If StartTime and EndTime are already datetime what string are you converting to datetime? In your query, you are converting your datetime to a varchar. And where are you getting this sort from?
CH Guravaiah 22-Mar-13 10:43am
   
but doesn't sort the starttime and finishtime in datagrid. How can solve this query.
D.Nikolov 22-Mar-13 11:13am
   
The DataGrid sorts the column correctly for it's data type which is string. Have you tried to set Format(DisplayFormat or whatever the property name is) on your column and return datetime instead of string.
What DataGrid are you using? WPF, Silverlight, Windows Forms, WebForms?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Sorry, but your question has totally nonsense.

If your type of StartDate, EndDate fields is DATETIME, you don't need to convert it to datetime type. It's similarly like: how to convert string to string?

To format DGV columns with dates, see this: http://msdn.microsoft.com/en-us/library/f9x2790s.aspx[^]

BTW, you got your answer here: How to Sort the DateTime Column in DataGridview[^]

In the future, do not repost. If you have a question (to the author of solution/answer), please, use "Have a question or comment" widget.
  Permalink  
Comments
phil.o 22-Mar-13 12:43pm
   
5'd, at all levels ^^
See my solution, that seemed important to me, as this is a frequent mistake everyone should get rid of.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

There is something we see on many posts these days, people use to handle DateTime values with strings rather than with reserved type.

In your query, instead of getting two DateTime values from your database, you transform them into strings. Which leads you to serious problems when you want to sort them ; because you make a string sort, which is quite different from a DateTime sort (which makes sense, if you think about it).

So, do you a favor, just get a DateTime value from your database ; your DatagridView will know how to handle a DateTime sort.
And, for the string representation of your DatTime value, use a specific DateTime.Format() overload, so you will get a string representation of your DateTime value in the formatting culture you want.

What is important is : do not try to format your values in the query ; format them when you display them only.

I hope this helps.
  Permalink  
Comments
Maciej Los 22-Mar-13 12:59pm
   
Nice explained, +5!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try changing the 'Order By' clause to "Order By BENCHMARKNO, STARTDATE". This will sort your records first by benchmarkno and then by startdate.
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100