Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 5:22am
Comments
ThePhantomUpvoter at 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 at 22-Mar-13 10:43am
   
but doesn't sort the starttime and finishtime in datagrid. How can solve this query.
D.Nikolov at 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 at 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 at 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
0 OriginalGriff 406
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 205
3 Raul Iloc 170
4 Maciej Los 164
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
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