הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Wednesday, August 7, 2013 2:35 PM
Dear Sir or Madam,
I've been tried this code to sort the string as numerical but it sorted
Private Sub ContactDataGridView_ColumnHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles ContactDataGridView.ColumnHeaderMouseClick
ContactDataGridView.Columns(5).SortMode = DataGridViewColumnSortMode.Programmatic
ContactDataGridView.Sort(FrmMainDataGridView.Columns(5), System.ComponentModel.ListSortDirection.Descending)
End Sub
9
8
71
70
6
50
5
49
48
.
It would be sort like:
71
70
50
49
48
9
8
6
5
.
Help, Please
Kind regards,
Kalunche
All replies (39)
Wednesday, August 7, 2013 5:58 PM
Dear Sir or Madam,
I've been tried this code to sort the string as numerical but it sorted
Private Sub ContactDataGridView_ColumnHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles ContactDataGridView.ColumnHeaderMouseClick
ContactDataGridView.Columns(5).SortMode = DataGridViewColumnSortMode.Programmatic
ContactDataGridView.Sort(FrmMainDataGridView.Columns(5), System.ComponentModel.ListSortDirection.Descending)
End Sub
9
8
71
70
6
50
5
49
48
.
It would be sort like:
71
70
50
49
48
9
8
6
5
.
Help, Please
Kind regards,
Kalunche
Wednesday, August 7, 2013 7:03 PM | 1 vote
Hi
Do you have to have the data in that column as strings? If not, you could set it to Type Integer, and make sure where you load the data that that column only gets integer values. Then the sort would go as you would expect.
You can set a DataGridView column to an Integer Type with:
Me.DataGridView1.Columns("Dummy").ValueType = GetType(Integer)
In this case, it is the column "Dummy", of course, yours would be different.
If you do need to have only strings in the column, then here is some code to show how to sort indirectly via a dummy column added to the DataGridView. It only shows a Descending sort based on one particular column (In my example it is column 2, but you may need another column)
This is just an example project to show the idea.
' new project with default Form1 with DataGridView1
' with 3 columns and Button1
' replace all Form1 code with this code
Option Strict On
Option Infer Off
Public Class Form1
Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
' this is just for testing - fill with dummy data (all strings)
Dim r As New Random
For i As Integer = 0 To 99
Me.DataGridView1.Rows.Add(r.Next(1, 99).ToString, r.Next(1, 99).ToString, r.Next(1, 99).ToString)
Next
' this adds a new column of type Integer
Me.DataGridView1.Columns.Add("Dummy", "Dummy")
Me.DataGridView1.Columns("Dummy").ValueType = GetType(Integer)
' hide the new column
Me.DataGridView1.Columns("Dummy").Visible = False
' copy the original column values (here I use column 2)
' as Integers into new column
For Each row As DataGridViewRow In Me.DataGridView1.Rows
With row
Dim v As Integer = 0
If Integer.TryParse(.Cells(2).Value.ToString, v) Then
.Cells("Dummy").Value = v
End If
End With
Next
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
' sort DataGridView on column ("Dummy") which indirectly sorts on
' original column(2) - Descending
Me.DataGridView1.Sort(Me.DataGridView1.Columns("Dummy"), System.ComponentModel.ListSortDirection.Descending)
End Sub
End Class
Regards Les, Livingston, Scotland
Wednesday, August 7, 2013 7:06 PM | 1 vote
You have to use a custom reverse comparer to sort it like you are showing
Here an example of how to do it
Public Class Form1
Private Sub DataGridView1_ColumnHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick
DataGridView1.Sort(New Colomn5ReverseComparer)
End Sub
End Class
Class Colomn5ReverseComparer : Implements IComparer
Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
Dim XX As DataGridViewRow = DirectCast(x, DataGridViewRow)
Dim YY As DataGridViewRow = DirectCast(y, DataGridViewRow)
If CInt(XX.Cells(5).Value) < CInt(YY.Cells(5).Value) Then
Return 1
ElseIf CInt(XX.Cells(5).Value) > CInt(YY.Cells(5).Value) Then
Return -1
Else
Return 0
End If
End Function
End Class
Thursday, August 8, 2013 1:31 AM
Hi Kalunche,
This forum is to discuss problems of C# development. Your question is not related to the topic of this forum.You can consider posting it in VB forum for more efficient responses.http://social.msdn.microsoft.com/Forums/en-US/home?forum=vbgeneral
Thanks
Eason_H
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thursday, August 8, 2013 4:07 AM
Dear Crazypennie,
I got an error with this
ContactDataGridView.Sort(New Colomn5ReverseComparer) ==> ( DataGridView control is data-bound. The control cannot use the comparer to perform the sort operation. ) What I need to do next.
Kind regards,
Kalunche
Thursday, August 8, 2013 4:22 AM
Yes I have the data-bound in that column as strings.
the numbers in column5 not fix like your code above
It's mean that it will be increase while I add the new contact name.
Thursday, August 8, 2013 9:09 AM | 1 vote
Hi Kalunche,
I create a sample using VB.NET and DataGridView. I have implemented this function.
When we click ‘Sort’ button, data can be sorted automatically. The UI looks like the following figure. I will share my database script with you.
VB.NETCode:
Imports System.Data
Imports System.ComponentModel
Public Class FrmMain
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim strConn As String = "Data Source=localhost\SQLExpress;Initial Catalog=MySampleDB;User ID=sa;Password=LX9e@423"
Dim connection As New SqlConnection()
connection.ConnectionString = strConn
connection.Open()
Dim cmdText As String = "SELECT * FROM UserInformation ORDER BY UserId ASC"
Dim adapter As New SqlDataAdapter()
Dim dataTable As New DataTable()
adapter.SelectCommand = New SqlCommand(cmdText, connection)
adapter.Fill(dataTable)
DgvInfo.DataSource = dataTable
connection.Close()
End Sub
Private Sub BtnSort_Click(sender As Object, e As EventArgs) Handles BtnSort.Click
Dim count As Integer = DgvInfo.Rows.Count
Dim first As Integer = Convert.ToInt32(DgvInfo.Rows(0).Cells(0).Value.ToString())
Dim last As Integer = Convert.ToInt32(DgvInfo.Rows(count - 1).Cells(0).Value.ToString())
If first > last Then
DgvInfo.Sort(DgvInfo.Columns(0), System.ComponentModel.ListSortDirection.Ascending)
Else
DgvInfo.Sort(DgvInfo.Columns(0), System.ComponentModel.ListSortDirection.Descending)
End If
End Sub
End Class
I have uploaded the sample to SkyDrive. Here is download link.
Any unsure, please let me know.
Best Regards,
Jimmy
Jimmy Yang
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thursday, August 8, 2013 10:19 AM | 1 vote
Hello,
When posting a question please only post once rather than multiple times. This allows others to see what has been suggested already so not to repeat or to make alternate suggestions.
Original post
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.
Thursday, August 8, 2013 11:29 AM | 1 vote
If your data is bound, to my knowledge, there are not much you can do.
The datagridview and the dataview dont work with a IComparer when the data is bound (except if bound to a IList)
The bug was reported to Microsoft long ago and the case was closed "As won't fix"
The only workaround I can think of is to implement your own binding methods. This can be relatively complicated in this particular case since the order of the rows in the datasource and in the datagridview will not be the same. A system of Key-Value can be used to do so, but I would expect bad performance on a large set of data
May be someone else can have better advice, I never really have work on trying find the way to use a IComparer on a datagridview
Friday, August 9, 2013 11:09 AM
Dear Jimmy Yang,
I face an error here
Dimlast AsInteger= Convert.ToInt32(FrmMainDataGridView.Rows(count - 1).Cells(0).Value.ToString()) ==>>
(Object reference not set to an instance of an object.)
And also thank you very much for download link but it look like the file have been removed.
Kind regards,
Kalunche
Friday, August 9, 2013 11:39 AM
Hi
Did you try out the code I posted at the top of this thread? It works, and can easily be adjusted for sorting any column as numerical data (provided the data is a string of a number).
Regards Les, Livingston, Scotland
Friday, August 9, 2013 12:31 PM | 2 votes
You should not store numbers as text in a database unless they are of fixed length. In any event, I would use a SQL function to convert the value to numeric. For example, if you are working with an Access database you can use the CLng function:
SELECT CLng(UserID) As UserIDNumber, UserName, LastName, etc.
Paul ~~~~ Microsoft MVP (Visual Basic)
Friday, August 9, 2013 1:53 PM
Dear leshay, You code I difficult to make change this point For i As Integer = 0 To 99 Me.DataGridView1.Rows.Add(r.Next(1, 99).ToString, r.Next(1, 99).ToString, r.Next(1, 99).ToString) Next my string not limit from 0-99 or bla bla... like your How can I set it For I As Integer = 0 To ... Because this string will be increase when I am adding the new contacts. Kind regards, Kalunche
Friday, August 9, 2013 2:06 PM
Hi
That part of the code is ONLY to set up the DataGridView with some test data for the example.
In the code, I have used 100 rows, but regardless of how many rows there are, the code cycles through all of the rows using:
For Each row As DataGridViewRow In Me.DataGridView1.Rows
Basically, by adding a new column to the DGV and copying the values into it from your desired numerical string column, but as a true numerical value, you can sort in the way you want just by sorting on the new column.
You could for example, set up a sub with a parameter for column to copy and one for sort direction, and by passing those to the sub, it would sort the DGV as you desire.
Regards Les, Livingston, Scotland
Saturday, August 10, 2013 4:17 AM
Hi Kalunche,
I am very sorry for the link cannot download. I re-uploaded the file to SkyDrive. The database script was contained. You can download from this link.
https://skydrive.live.com/redir?resid=F6BFF8BD172F0399!125
Best Regards,
Jimmy
Jimmy Yang
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Saturday, August 10, 2013 8:46 AM
Dear Jimmy Yang,
I've been downloaded it and found your sample code only have one file and I can't open it.
Thanks regards,
Kalunche
Saturday, August 10, 2013 9:01 AM
Hi Kalunche,
I think we have different IDE, so you can not open it. My IDE is Visual Studio Ultimate 2012. What is version of your IDE.
Best Regards,
Jimmy
Jimmy Yang
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Saturday, August 10, 2013 9:25 AM
Dear leshay,
I still don't know what else should I change and I tried it show error (Object reference not set to an instance of an object)
For Each row As DataGridViewRow In Me.ContactDataGridView.Rows
Next
' this adds a new column of type Integer
Me.ContactDataGridView.Columns.Add("IdCode", "IdCode")
Me.ContactDataGridView.Columns("IdCode").ValueType = GetType(Integer)
' hide the new column
Me.ContactDataGridView.Columns("IdCode").Visible = False
' copy the original column values (here I use column 5)
' as Integers into new column
For Each row As DataGridViewRow In Me.ContactDataGridView.Rows
With row
Dim v As Integer = 0
If Integer.TryParse(.Cells(5).Value.ToString, v) Then
.Cells("IdCode").Value = v
End If
End With
Next
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
' sort DataGridView on column ("IdCode") which indirectly sorts on
' original column(5) - Descending
Me.ContactDataGridView.Sort(Me.ContactDataGridView.Columns("IdCode"), System.ComponentModel.ListSortDirection.Descending)
End Sub
My Local database is ( Microsoft SQL Server Compact Edition (.sdf))
For the column string (to fill the number is Column 5 I didn't set it to int or Bigint) I elected nvarchar = 20 of Length
Kind regards,
Kalunche
Saturday, August 10, 2013 1:45 PM
Hi
Here is the same example, but using a datatable bound to the DGV instead. The Button Click event calls the mySort sub with 2 parameters, Col as string and direction as string.
Try a new project with this code to see that it works with you before trying to adapt to your own project.
Also, if you post code, please use the Code Block tool on the toolbar at the top.
' new project with default Form1 with BLANK DataGridView1
' and a Button1
' replace all Form1 code with this code
Option Strict On
Option Infer Off
Public Class Form1
' for binding example
Private t As New DataTable
Private Sub Form1_Load1(sender As Object, e As System.EventArgs) Handles Me.Load
' this is test data just for example
' a table with some columns - could be any number of columns
'
t.Columns.Add("c1")
t.Columns.Add("c2")
t.Columns.Add("IdCode")
' fill table with dummy data (all strings)
' yours would have its own way to add data
Dim r As New Random
For i As Integer = 0 To 15
Dim s() As String = {r.Next(1, 99).ToString, r.Next(1, 99).ToString, r.Next(1, 99).ToString}
t.Rows.Add(s)
Next
'
' set DGV datasource to original data
Me.DataGridView1.DataSource = t
' add the Dummy column for sorting
t.Columns.Add("Dummy", GetType(Integer))
' hide the Dummy column on the DGV
Me.DataGridView1.Columns("Dummy").Visible = False
End Sub
Private Sub Button1_Click_1(sender As System.Object, e As System.EventArgs) Handles Button1.Click
' on button click, call mySort sub with column to sort
' and sort direction - both as strings
mySort("IdCode", "descending")
End Sub
Private Sub mySort(col As String, direction As String)
' find column to sort and transfer strings to
' numeric in column("Dummy")
Dim sortC As Integer = t.Columns(col).Ordinal
For Each row As DataRow In t.Rows
With row
Dim v As Integer = 0
If Integer.TryParse(.Item(sortC).ToString, v) Then
.Item("Dummy") = v
End If
End With
Next
' sort DataGridView on column ("Dummy") which indirectly sorts on
' original column("IdCode") - Descending
Select Case direction.ToLower
Case "ascending"
Me.DataGridView1.Sort(Me.DataGridView1.Columns("Dummy"), System.ComponentModel.ListSortDirection.Ascending)
Case "descending"
Me.DataGridView1.Sort(Me.DataGridView1.Columns("Dummy"), System.ComponentModel.ListSortDirection.Descending)
End Select
End Sub
End Class
Regards Les, Livingston, Scotland
Monday, August 12, 2013 3:20 AM
Dear leshay,
Your code is very well but it quite different from datagridview format and I don't know what should I change with mine while comparing it.
Kind regards,
Kalunche
Monday, August 12, 2013 8:34 AM
Hi
Do you have a DataTable bound to the DataGridView - if so,what is its name?
Regards Les, Livingston, Scotland
Monday, August 12, 2013 4:27 PM
Kalunche is using a datatable.
Monday, August 12, 2013 6:06 PM
Hi
Kalunche, you will need to post your code to get any more help. Post your code by using the code block tool on the toolbar of the post edit window.
In my example, the only parts you would need (I think) is the adding of the 'Dummy' column and the mySort subroutine. You can call the mySort with the column to sort and the sort direction (both strings) from anywhere you need to and the column will sort. So, after you have set the DGV datasource, just add code to add the Dummy column, add the subroutine and it 'should' work.
Regards Les, Livingston, Scotland
Tuesday, August 13, 2013 11:18 AM
Dear Leshay,
I am now will show more of my project that i am trying to create. I hope this will get help correctly.
My database name: contactdata.sdf is a Local Database(Microsoft SQL Server Compact Edition)
Table name: contact
DataGridview name: contactDataGridview
I set the table as shown below:
0-Id DataType: bigint, Length: 8, Primary: Yes =>>Identity: True
1-FirstName DataType: nvarchar, Length: 20
2-LastName DataType: nvarchar, Length: 20
3-Sex DataType: nvarchar, Length: 6
4-PhoneNumber DataType: nvarchar, Length: 30
5-IdCode DataType: nvarchar, Length: 7
I have created two forms. Form1 is a Main form it is name Contact List and Form2 is a Form Add Name so in this form i added (firstname, lastname, sex, phonenumber, and idcode)
Please note that I am tried not to sort column Id(column0)
The column that i tried to sort is column IdCode(column5)
The problem is the sorting column(5) like i posted at the top of this thread.
Kind regards,
Kalunche
Tuesday, August 13, 2013 11:40 AM
Hi
OK then, please post you actual code (in a code block please) where you set the contactDataGridview datasource.
Regards Les, Livingston, Scotland
Tuesday, August 13, 2013 4:38 PM
Dear Leshay,
I don't know how to set a data source. But I just open it from run button and then clicking on Add button on form1 and after that I start to add the firstname, lastname, ....
Kind regards,
Kalunche
Tuesday, August 13, 2013 5:14 PM
Hi
You say the Data is bound to the DataGridView?
How do you do that?
Is the code you have all written by you?
Please post ALL your code.
Regards Les, Livingston, Scotland
Tuesday, August 13, 2013 6:24 PM | 2 votes
If you change
Dim cmdText As String = "SELECT * FROM UserInformation ORDER BY UserId ASC"
to
Dim cmdText As String = "SELECT [Id],[FirstName],[LastName],[Sex],[PhoneNumber], CAST(UserId AS INT) AS UserId2 FROM UserInformation ORDER BY UserId2 ASC"
does it do what you want? (Not tested, but you should see the idea from that.)
You should not use "*" to select columns.
--
Andrew
Wednesday, August 14, 2013 4:54 AM
Dear Leshay,
All the code is written by me. I watched the videos from Youtube. So to do a search I added 5 textboxes(txtfirstname, txtlastname, txtsex, txtphonenum, and txtidcode) on to form1 and then clicking on the contactdatagridview and the small run button on the top right => Add Query => New query name:
FillbyMe =>Query Builder... In Filter Column I fill with:
1.LIKE @FirstName + '%'
2.LIKE @LastName + '%'
3.LIKE @Sex + '%'
4.LIKE @PhoneNumber + '%'
5.LIKE @CodeId + '%'
Ok to finish.
So through this I can search well but except to do a sort on column CodeId
Kind regards,
Kalunche
Wednesday, August 14, 2013 7:38 AM
Hi
Do you not understand that you need to post the code you have. All of it. Post the code from the code window you are using into a code block here. You are just posting words - we need code please.
Regards Les, Livingston, Scotland
Thursday, August 15, 2013 9:41 AM
Dear Leshay,
In form1 only search button from method FillbyMe(got it from added query) and form2 only clicking on (+) and Save sign.
Kind regards,
Kalunche
Thursday, August 15, 2013 10:53 AM
Hi
Please read my last post again............... post all of your code please.
Regards Les, Livingston, Scotland
Thursday, August 15, 2013 3:06 PM
Dear Les,
Please kindly check my codes as in Form1 and Form2 like shown below:
Form1
Public Class Main
Private Sub Main_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Me.ContactTableAdapter.FillBy(Me.ContactdataDataSet.contact, FirstNameToolStripTextBox.Text, LastNameToolStripTextBox.Text, SexToolStripTextBox.Text, PhoneNumberToolStripTextBox.Text, IdCodeToolStripTextBox.Text)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
AddName.ShowDialog()
End Sub
Form2
Public Class AddName
Private Sub AddName_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'ContactdataDataSet.contact' table. You can move, or remove it, as needed.
Me.ContactTableAdapter.Fill(Me.ContactdataDataSet.contact)
End Sub
Private Sub ContactBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ContactBindingNavigatorSaveItem.Click
Me.Validate()
Me.ContactBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.ContactdataDataSet)
End Sub
End Class
Kind regards,
Kalunche
Thursday, August 15, 2013 4:43 PM
Hi
Sorry, out of my league. Maybe someone else can take up your case and help you out.
Regards Les, Livingston, Scotland
Thursday, August 15, 2013 6:26 PM | 1 vote
The problem is that you have "5-IdCode DataType: nvarchar, Length: 7". This is a String. The result you show in your first post is the result of sorting on that field as a string.
You have the data from the database bound to a DataGridView. Crazypennie informed you that you will not be able to use a custom sorting for the DGV column in that case.
The simplest way to get the result you want is to change the IdCode column in the database to a numeric type, for example INT, as Paul said a week ago.
The next-easiest way is to use the SQL SELECT statement to return that column as an INT, as shown by Paul and myself.
The hardest way is to bind a DataTable to the SQL server data, create a DataView, bind the DGV to the DataView, and write a custom sort for the DataView.
Your choice... ;)
HTH,
Andrew
Friday, August 16, 2013 8:37 AM
Dear Andrew,
As your mention about how could I convert this column(5) value to a numeric. Because when I run the form it show me all data that I have filled before. And I don't know how to use the select method like as your shown above.
PrivateSubMain_Load(ByValsender AsSystem.Object, ByVale AsSystem.EventArgs) HandlesMyBase.Load
Me.ContactTableAdapter.Fill(Me.ContactdataDataSet.contact)
EndSub
Kind regards,
Kalunche
Friday, August 25, 2017 3:18 PM
Can't add columns to a "bounded" DataGridView.
Friday, August 25, 2017 3:19 PM
Can't add comparer to a "bounded" DataGridView.
Saturday, August 26, 2017 8:29 AM
Vittorio,
It is strange that so many persons answer. It is has no mark as answer. Not so strange a bound datagridview cannot be sorted using the DataGridView.
However, the question is from 2013. To answer the question you must know what kind of datasource is used.
Therefore, create your own question instead of adding to this one.
Success
Cor