Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Monday, October 15, 2012 4:40 PM | 1 vote
I have a datatable linked to a datagridview.
What I want to do is move selected rows in the datagridview to another specified position. And I want the move to be done in the datatable.
I am using this code:
unlink_datasource1() 'code used to temporary unlink table and datafrigview For i = 0 To Form1.DataSet1.Tables(0).Rows.Count - 1 'for each row in table
If Form1.DataGridView1.Rows.Item(i).Selected = True Then 'if selected
Dim chosenraw, newrow As DataRow 'define rows
chosenraw = Form1.DataSet1.Tables(0).Rows(i) '"old" row
newrow = chosenraw '"copy" to a new row
Form1.DataSet1.Tables(0).Rows.Remove(chosenraw) 'remove old
Form1.DataSet1.Tables(0).Rows.InsertAt(newrow, position_to_place) 'insert new
End If nextrelinkdatasource1 () 'code to relink table an datagridview
My problem is that the rows inserted are blank, instead of having the oldrow data.
Any suggestions?
All replies (11)
Tuesday, October 16, 2012 2:17 PM ✅Answered
Hello again,
I am going to provide you with another example that works off a BindingSource which has a DataTable as it's DataSource. Yes I understand you are using a DataTable in a DataSet so the only difference really is the BindingSource which allows us to get the current position in the DataGridView. So if you are willing to step out of your current mindset this will do the following. Lastly I did not attempt to reproduce what you have but instead give you how I would do this.
- Press a button to move the current row up (test for top index)
- Press a button to move the current row down (test for botton index)
Requires
- VS2008 or higher (current code in VS2010, for VS2008 you need to add line continuations)
- VB.NET
- Option Strict On
- Option Infer On
- One DataGridView
- Two Button controls
As you can see I include complete code showing the data comes from an MS-Access 2007 database table.
Public Class Form1 WithEvents bsData As New BindingSource Private BuilderAccdb As New OleDb.OleDbConnectionStringBuilder With { .Provider = "Microsoft.ACE.OLEDB.12.0", .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb") } Private Sub Form1_Load( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim dt = Load_Customers() bsData.DataSource = dt DataGridView1.AllowUserToAddRows = False DataGridView1.DataSource = bsData DataGridView1.CurrentCell = DataGridView1(1, 0) For Each Column As DataGridViewColumn In DataGridView1.Columns Column.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells Next DataGridView1.Columns("CompanyName").HeaderText = "Company" DataGridView1.Columns("ContactName").HeaderText = "Contact" DataGridView1.Columns("ContactTitle").HeaderText = "Title" End Sub Public Function Load_Customers() As DataTable Using cn As New OleDb.OleDbConnection With { .ConnectionString = BuilderAccdb.ConnectionString } Using cmd As New OleDb.OleDbCommand With {.Connection = cn} cmd.CommandText = <SQL> SELECT TOP 10 Identifier, CompanyName, ContactName, ContactTitle FROM Customer Order By CompanyName </SQL>.Value Dim dt As New DataTable cn.Open() dt.Load(cmd.ExecuteReader) dt.Columns("Identifier").ColumnMapping = MappingType.Hidden dt.AcceptChanges() Return dt End Using End Using End Function Private Sub cmdMoveUp_Click( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMoveUp.Click Dim CurrentColumnIndex As Integer = DataGridView1.CurrentCell.ColumnIndex Dim NewIndex As Int32 = CInt(IIf(bsData.Position = 0, 0, bsData.Position - 1)) Dim dt = CType(bsData.DataSource, DataTable) Dim RowToMove As DataRow = DirectCast(bsData.Current, DataRowView).Row Dim celldata(dt.Columns.Count - 1) As Object celldata = RowToMove.ItemArray Dim NewRow As DataRow = dt.NewRow NewRow.ItemArray = celldata dt.Rows.RemoveAt(bsData.Position) dt.Rows.InsertAt(NewRow, NewIndex) dt.AcceptChanges() bsData.Position = NewIndex DataGridView1.CurrentCell = DataGridView1(CurrentColumnIndex, NewIndex) End Sub Private Sub cmdMoveDown_Click( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMoveDown.Click Dim CurrentColumnIndex As Integer = DataGridView1.CurrentCell.ColumnIndex Dim UpperLimit As Int32 = bsData.Count - 1 Dim NewIndex As Int32 = CInt(IIf( bsData.Position + 1 >= UpperLimit, UpperLimit, bsData.Position + 1)) Dim dt = CType(bsData.DataSource, DataTable) Dim RowToMove As DataRow = DirectCast(bsData.Current, DataRowView).Row Dim celldata(dt.Columns.Count - 1) As Object celldata = RowToMove.ItemArray Dim NewRow As DataRow = dt.NewRow NewRow.ItemArray = celldata dt.Rows.RemoveAt(bsData.Position) dt.Rows.InsertAt(NewRow, NewIndex) dt.AcceptChanges() bsData.Position = NewIndex DataGridView1.CurrentCell = DataGridView1(CurrentColumnIndex, NewIndex) End Sub Private Sub cmdClose_Click_1( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click Close() End SubEnd Class
Screenshot
KSG
Monday, October 15, 2012 5:03 PM
You do that with the currencymanager (that is not about money) we have more samples about that on our website.
http://www.vb-tips.com/CurrencyManager.aspx
Success
Cor
Monday, October 15, 2012 5:55 PM
The following works via Drag-n-Drop. Requires a DataGridView and 1 button control. Note you need to be aware of sorting, if the data source is sorted it will appear the move did not work when performing the move.
Form code
Public Class frmReorder WithEvents bsPeople As New BindingSource Private Identifier As String = "" Private MouseDownRectangle As Rectangle Private rowIndexFromMouseDown As Integer Private rowIndexOfItemUnderMouseToDrop As Integer <System.Diagnostics.DebuggerStepThrough()> _ Private Sub dataGridView1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles DataGridView1.MouseMove If (e.Button And MouseButtons.Left) = MouseButtons.Left Then If MouseDownRectangle <> Rectangle.Empty AndAlso (Not MouseDownRectangle.Contains(e.X, e.Y)) Then Dim dropEffect As DragDropEffects = DataGridView1.DoDragDrop(DataGridView1.Rows(rowIndexFromMouseDown), DragDropEffects.Move) End If End If End Sub <System.Diagnostics.DebuggerStepThrough()> _ Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs) Handles DataGridView1.MouseDown rowIndexFromMouseDown = DataGridView1.HitTest(e.X, e.Y).RowIndex If rowIndexFromMouseDown <> -1 Then Dim dragSize As Size = SystemInformation.DragSize MouseDownRectangle = New Rectangle(New Point(e.X - (dragSize.Width \ 2), e.Y - (dragSize.Height \ 2)), dragSize) Else MouseDownRectangle = Rectangle.Empty End If End Sub <System.Diagnostics.DebuggerStepThrough()> _ Private Sub dataGridView1_DragOver(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragOver e.Effect = DragDropEffects.Move End Sub Private Sub dataGridView1_DragDrop(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragDrop Dim OriginalIdentifier As String = "" Dim clientPoint As Point = DataGridView1.PointToClient(New Point(e.X, e.Y)) rowIndexOfItemUnderMouseToDrop = DataGridView1.HitTest(clientPoint.X, clientPoint.Y).RowIndex If e.Effect = DragDropEffects.Move Then OriginalIdentifier = bsPeople.CurrentRow(PrimaryIdentifier) Dim rowToMove As DataGridViewRow = TryCast(e.Data.GetData(GetType(DataGridViewRow)), DataGridViewRow) Dim celldata(rowToMove.Cells.Count - 1) As Object For col As Integer = 0 To rowToMove.Cells.Count - 1 celldata(col) = rowToMove.Cells(col).Value Next Dim row As DataRow = bsPeople.DataTable.NewRow() row.ItemArray = celldata bsPeople.DataTable.Rows.InsertAt(row, rowIndexOfItemUnderMouseToDrop) rowToMove.DataGridView.Rows.Remove(rowToMove) If Not String.IsNullOrEmpty(bsPeople.Sort) Then bsPeople.Sort = "" cmdSort.Text = "Sort" End If If Not String.IsNullOrEmpty(OriginalIdentifier) Then bsPeople.Locate(PrimaryIdentifier, OriginalIdentifier) End If End If End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim ds As New DataSet ds.ReadXml("People.xml") bsPeople.DataSource = ds.Tables("Person") DataGridView1.DataSource = bsPeople bsPeople.Sort = "LastName" DataGridView1.Columns(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill For Each col As DataGridViewColumn In DataGridView1.Columns col.SortMode = DataGridViewColumnSortMode.NotSortable Next End Sub Private Sub bsPeople_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsPeople.PositionChanged If bsPeople.Current IsNot Nothing Then Identifier = bsPeople.PrimaryKey Label1.Text = String.Format("BindingSource Primary key [{0}]", bsPeople.PrimaryKey) End If End Sub Private Sub Sorter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSort.Click If String.IsNullOrEmpty(bsPeople.Sort) Then bsPeople.Sort = "LastName" cmdSort.Text = "unsort" Else bsPeople.Sort = "" cmdSort.Text = "Sort" End If End Sub Private Sub DataGridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Sorted bsPeople.Locate(PrimaryIdentifier, Identifier) End SubEnd Class
Code module code
Module Extensions Public Function PrimaryIdentifier() As String Return KeyIdentifier End Function <System.Diagnostics.DebuggerStepThrough()> _ <System.Runtime.CompilerServices.Extension()> _ Public Function Locate(ByVal sender As BindingSource, ByVal Key As String, ByVal Value As String) As Integer Dim Position As Integer = -1 Position = sender.Find(Key, Value) If Position > -1 Then sender.Position = Position End If Return Position End Function Private KeyIdentifier As String = "ID" <System.Diagnostics.DebuggerStepThrough()> _ <System.Runtime.CompilerServices.Extension()> _ Public Function IsNewRow(ByVal sender As BindingSource) As Boolean Return sender.CurrentRow.RowState = DataRowState.Detached End Function <System.Diagnostics.DebuggerStepThrough()> _ <System.Runtime.CompilerServices.Extension()> _ Public Function CurrentRow(ByVal sender As BindingSource) As DataRow Return DirectCast(sender.Current, DataRowView).Row End Function <System.Diagnostics.DebuggerStepThrough()> _ <System.Runtime.CompilerServices.Extension()> _ Public Function PrimaryKey(ByVal sender As BindingSource) As String Dim Value As String = "New row" If Not sender.IsNewRow() Then Value = DirectCast(sender.Current, DataRowView).Row(KeyIdentifier).ToString End If Return Value End Function <System.Diagnostics.DebuggerStepThrough()> _ <System.Runtime.CompilerServices.Extension()> _ Public Function CurrentRow(ByVal sender As BindingSource, ByVal Column As String) As String Return DirectCast(sender.Current, DataRowView).Row(Column).ToString End Function <System.Diagnostics.DebuggerStepThrough()> _ <System.Runtime.CompilerServices.Extension()> _ Public Function DataTable(ByVal sender As BindingSource) As DataTable Return DirectCast(sender.DataSource, DataTable) End FunctionEnd Module
People.xml place in same folder as your executable
<?xml version="1.0" encoding="utf-8"?><?xml-stylesheet type="text/css" href="people.css"?><People> <Person> <ID>246-12-5645</ID> <FirstName>Kevin</FirstName> <LastName>Gallagher</LastName> <Age>40</Age> <TimeStamp>20 March 2009</TimeStamp> </Person> <Person> <ID>246-12-5646</ID> <FirstName>Kevin</FirstName> <LastName>MIlls</LastName> <Age>41</Age> <TimeStamp>12 September 2009</TimeStamp> </Person> <Person> <ID>246-12-5647</ID> <FirstName>Jon</FirstName> <LastName>Wilson</LastName> <Age>40</Age> <TimeStamp>20 March 2009</TimeStamp> </Person> <Person> <ID>246-12-5648</ID> <FirstName>Brian</FirstName> <LastName>Jones</LastName> <Age>43</Age> <TimeStamp>20 June 2004</TimeStamp> </Person> <Person> <ID>246-12-5649</ID> <FirstName>Sue</FirstName> <LastName>Olson</LastName> <Age>34</Age> <TimeStamp>20 December 1999</TimeStamp> </Person> <Person> <ID>246-12-5650</ID> <FirstName>Ardythe</FirstName> <LastName>Smith</LastName> <Age>30</Age> <TimeStamp>20 March 2009</TimeStamp> </Person> <Person> <ID>246-12-5651</ID> <FirstName>Greg</FirstName> <LastName>White</LastName> <Age>33</Age> <TimeStamp>01 April 2001</TimeStamp> </Person> <Person> <ID>246-12-5652</ID> <FirstName>Cleve</FirstName> <LastName>Owens</LastName> <Age>55</Age> <TimeStamp>20 March 2009</TimeStamp> </Person></People>
KSG
Tuesday, October 16, 2012 7:17 AM
Thanks for your replies.
But why my code doesn't work?
If I put
MsgBox(newrow.Item(0))
after newrow=chosenrow the message that pops-up is correct. I mean the data displayed in it is really the data that is contained to oldrow.item(0). That means copying is done. So why when newrow is inserted cells are blank?
Tuesday, October 16, 2012 7:51 AM
And I want the move to be done in the datatable.
I did not noticed this part earlier. Be aware that a datatable represents a database. The position of a row in a database table is complete irrelevant (and can not be changed).
You need the options to show it to give it a certain order.
Success
Cor
Tuesday, October 16, 2012 8:00 AM
I didn't know that a row's position cannot be changed in a database.
Even then, what my code is supposed to do is copy the row, paste the new copied row somewhere else and then delete the original row. I mean it doesn't actually change the row's position, it copies the row and then deletes it.
Couldn't that work?
Tuesday, October 16, 2012 8:40 AM
Now I see what your problem is,
A Remove, removes from the datatable completely including information to do that in the database.
A Delete marks a row to be removed and at an update it is removed from the database and from the datatable.
Form1.DataSet1.Tables(0).Row(i).Delete 'remove old
Be aware that you have to do this from end to begin so
For i = Form1.DataSet1.Tables(0).Rows.Count - 1 to 0 steps -1 'for each row in table
Don't ask me why they made it so difficult by putting the Remove method in the DataRowCollection and the Delete as a property of Row
Success
Cor
Tuesday, October 16, 2012 10:30 AM
Thanks for your replies.
But why my code doesn't work?
If I put
MsgBox(newrow.Item(0))after newrow=chosenrow the message that pops-up is correct. I mean the data displayed in it is really the data that is contained to oldrow.item(0). That means copying is done. So why when newrow is inserted cells are blank?
Hard to say as for one there are two lines of code at the start and end that I have no clue to what they do. Also as I mentioned prior, if the data is sorted then that will make the move appear not to have work although it did work. So a simple sort will mask the move.
KSG
Tuesday, October 16, 2012 11:16 AM
@ Kevininstructor
The
unlink_datasource1() relink_datasource1()
subs are used justto make the code run faster as by using them multiple refreshes of datagridview are avoided.
Those subs can be omitted. Then the result is still the same blank rows.
Tuesday, October 16, 2012 11:26 AM
Did you handle my reply or are you ignoring it?
Did not make that code for fun.
Success
Cor
Tuesday, October 16, 2012 11:27 AM
Did you handle my reply or are you ignoring it?
Did not make that code for fun.
Success
Cor