Help with updating datatable from DataGridView using VB.NET

Hi,

I'm loading a DatagridView with data from a datatable, when I update the DatagridView how do I save the changes to the datatable using VB.NET (Windows App)?

Thanks,

Victor
vcharlesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
How do you load your grid?
0
Éric MoreauSenior .Net ConsultantCommented:
0
vcharlesAuthor Commented:
Hi,

Thanks for the link. I'm using the code below to load the grid. How do I modify the code in the example to work with the DataTable?

Dim FilteredDT As DataTable
        Dim DV As New DataView(dtsetlinkBEL.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
        FilteredDT = DV.ToTable
        If FilteredDT.Rows.Count > 0 Then

            C1Screen1.DataSource = FilteredDT.DefaultView.ToTable(True, New String() {"ID", "Field1",  "Field2", "Field3", "Field4", "Field5",})
         
Thanks,

Victor
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Fernando SotoRetiredCommented:
Hi Victor;

You most likely used a SqlDataAdapter so you can try something like the following.

adapterInstance .Update(FilteredDT);

Where adapterInstance is the instance of the SqlDataAdapter used in your code.
0
vcharlesAuthor Commented:
Hi Fernando,

I'm not using an SQLDataAdapter. The data source is an xml file imported to a dataset. Multiple DataGridViews will be loaded from the same DataTable, once I update the data in the DataGridViews, need to update FilteredDT.


 Dim curfile As String = (Application.StartupPath + "\linkSearch.xml")
            fslinkBEL = New System.IO.FileStream(Application.StartupPath + "\linkSearch.xml", IO.FileMode.Open)
            dtsetlinkBEL.Clear()
            dtsetlinkBEL.ReadXml(fslinkBEL)
            fslinkBEL.Close()

Open in new window

Thanks,

Victor
0
Éric MoreauSenior .Net ConsultantCommented:
You can use the WriteXml method of the dataset as shown in http://support.softartisans.com/kbview_1301.aspx
0
vcharlesAuthor Commented:
Hi,

Unfortunately the link didn't help with my issue. Below is the code used with a third party control (C1TrueDBGrid), how do you modify it to work with a DataGridView? This approach will save changes from each grid to an xml file. I  will then consolidate them to create one xml file.

 Dim dtnew3 As DataTable = New DataTable()
        For col As Integer = 0 To C1Screen3.Splits(0).DisplayColumns.Count - 1
            If C1Screen3.Splits(0).DisplayColumns(col).Visible = True Then
                dtnew3.Columns.Add(C1Screen3.Splits(0).DisplayColumns(col).Name, C1Screen3.Columns(col).DataType)
            End If
        Next

        For row = 0 To C1Screen3.Splits(0).Rows.Count - 1
            dtnew3.Rows.Add(dtnew3.NewRow())
            For col As Integer = 0 To dtnew3.Columns.Count - 1
                dtnew3.Rows(row)(col) = C1Screen3(row, col)
            Next
        Next

        ds.Tables.Add(dtnew3)
        ds.Tables(2).WriteXml(Application.StartupPath + "\AOP40SC3.xml")

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
you can surely cast the DataSource of your grid to a DataSet:

dim ds as dataset = directcast(C1Screen1.DataSource , dataset)
ds.WriteXml("XML/MyData.xml", XmlWriteMode.WriteSchema)

Open in new window

0
vcharlesAuthor Commented:
Hi,

How do I modify the code below to fix the errors "Splits and DataType" are not properties of the DataGridView.

   Private Sub Button28_Click(sender As System.Object, e As System.EventArgs) Handles Button28.Click
        Dim curfile As String = (Application.StartupPath + "\linkSearch.xml")
        fslinkBEL = New System.IO.FileStream(Application.StartupPath + "\linkSearch.xml", IO.FileMode.Open)
        dtsetlinkBEL.Clear()
        dtsetlinkBEL.ReadXml(fslinkBEL)
        fslinkBEL.Close()


        Dim dtnew3 As DataTable = New DataTable()
        For col As Integer = 0 To DataGridView1.Splits(0).DisplayColumns.Count - 1
            If DataGridView1.Splits(0).DisplayColumns(col).Visible = True Then  ****Error Splits not member of control
                dtnew3.Columns.Add(DataGridView1.Splits(0).DisplayColumns(col).Name, DataGridView1.Columns(col).DataType) ****Error DataType not member of control
            End If
        Next

        For row = 0 To DataGridView1.Splits(0).Rows.Count - 1
            dtnew3.Rows.Add(dtnew3.NewRow())
            For col As Integer = 0 To dtnew3.Columns.Count - 1
                dtnew3.Rows(row)(col) = DataGridView1(row, col)
            Next
        Next

        dtsetlinkBEL.Tables.Add(dtnew3)
        dtsetlinkBEL.Tables(2).WriteXml(Application.StartupPath + "\AOP40SC3.xml")


        Dim ds As DataSet = DirectCast(DataGridView1.DataSource, DataSet)
        ds.WriteXml(Application.StartupPath + "\linkSearchUpdate.xml", XmlWriteMode.WriteSchema)
    End Sub

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
Have you switched to the plain old DataGridView? This control has less feature than its C1 competitor.

What are you trying to do with these lines?
0
vcharlesAuthor Commented:
For now, I  am trying to use both controls, eventually would like to  avoid third party controls, was able to fix the errors  and now testing the code.
0
vcharlesAuthor Commented:
Hi,

I am getting error message: Object reference not set to an instance of an object.

on line:

ds.WriteXml(Application.StartupPath + "\linkSearchUpdate.xml", XmlWriteMode.WriteSchema)
0
Éric MoreauSenior .Net ConsultantCommented:
if you just want to test the DataGridView, remove all the unnecessary code.

Load the grid:
DataSet ds = new DataSet()
ds.ReadXml("XML/MyData.xml", XmlReadMode.ReadSchema)
DataGridView1.DataSource = ds

Open in new window


Save the grid:
dim ds as dataset = directcast(DataGridView1.DataSource , dataset)
ds.WriteXml("XML/MyData.xml", XmlWriteMode.WriteSchema)

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
>>I am getting error message: Object reference not set to an instance of an object.

How do you declare your ds?
0
vcharlesAuthor Commented:
Hi,

I used the wrong approach before, now using two controls, one to load the grid and the other to save data modified to an xml file, but receiving error message:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

On line:

dtnew3.Rows(row)(col) = DataGridView1(row, col)

 Private Sub Button28_Click(sender As System.Object, e As System.EventArgs) Handles Button28.Click
        ' Dim curfile As String = (Application.StartupPath + "\aop5.xml")
        fslinkBEL = New System.IO.FileStream(Application.StartupPath + "\aop5.xml", IO.FileMode.Open)
        dtsetlinkBEL.Clear()
        dtsetlinkBEL.ReadXml(fslinkBEL)
        fslinkBEL.Close()
        DataGridView1.DataSource = dtsetlinkBEL.Tables(0)

       
    End Sub

    Private Sub Button29_Click(sender As System.Object, e As System.EventArgs) Handles Button29.Click
        Dim dtnew3 As DataTable = New DataTable()
        For col As Integer = 0 To DataGridView1.Columns.Count - 1
            If DataGridView1.Columns(col).Visible = True Then
                dtnew3.Columns.Add(DataGridView1.Columns(col).Name, DataGridView1.Columns(col).CellType)
            End If
        Next

        For row = 0 To DataGridView1.Rows.Count - 1
            dtnew3.Rows.Add(dtnew3.NewRow())
            For col As Integer = 0 To dtnew3.Columns.Count - 1
                dtnew3.Rows(row)(col) = DataGridView1(row, col)
            Next
        Next

        dtsetlinkBEL.Tables.Add(dtnew3)
        dtsetlinkBEL.Tables("dtnew3").WriteXml(Application.StartupPath + "\AOP55.xml")


        Dim ds As DataSet = DirectCast(DataGridView1.DataSource, DataSet)
         ds.WriteXml(Application.StartupPath + "\linkSearchUpdate.xml", XmlWriteMode.WriteSchema)
          End Sub

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
why do you recopy your grid into a datatable:

dim dtnews3 as datatable
dtnews3 = directcast(datagridview1.datasource, datatable)
Dim ds As new DataSet 
ds.tables.add(dtnews3)
ds.WriteXml(Application.StartupPath + "\linkSearchUpdate.xml", XmlWriteMode.WriteSchema)

Open in new window

0
vcharlesAuthor Commented:
I think I need t code below to retrieve the changes made in the grid. I'm still getting the same error meage in that part of the code.

Dim dtnew3 As DataTable = New DataTable()
        For col As Integer = 0 To DataGridView1.Columns.Count - 1
            If DataGridView1.Columns(col).Visible = True Then
                dtnew3.Columns.Add(DataGridView1.Columns(col).Name, DataGridView1.Columns(col).CellType)
            End If
        Next

        For row = 0 To DataGridView1.Rows.Count - 1
            dtnew3.Rows.Add(dtnew3.NewRow())
            For col As Integer = 0 To dtnew3.Columns.Count - 1
                dtnew3.Rows(row)(col) = DataGridView1(row, col)
            Next
        Next

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
you are just ignoring my comments!
0
vcharlesAuthor Commented:
I will read them again.

Thanks,

V.
0
vcharlesAuthor Commented:
Hi

If i understand  correctly,  I need to replace code the second button with the last code you posted.
In transit, will try it when i get home.

Thanks,
V.
0
vcharlesAuthor Commented:
Correction,

Save button:

dim ds as dataset = directcast(DataGridView1.DataSource , dataset) ds.WriteXml("XML/MyData.xml")

Will not need the schema.

Thanks
0
vcharlesAuthor Commented:
Hi,

I tried you approach in part A but unable to load the grid because the record count = 0, when I tried the approach in Part B I am able to load the Grid but I received error message:

Unable to cast object of type 'System.Data.DataTable' to type 'System.Data.DataSet'.

On line:

Dim ds As DataSet = DirectCast(DataGridView1.DataSource, DataSet)

How do I avoid this error?


Part A:

Looad grid:

DataSet ds = new DataSet()
ds.ReadXml(Application.StartupPath + "\AOP40.xml", XmlReadMode.ReadSchema)
''''Ds.rows.count = 0 eventhough the xml file contains data
DataGridView1.DataSource = ds

Save Data in Grid:

dim ds as dataset = directcast(DataGridView1.DataSource , dataset)
ds.WriteXml("XML/MyData.xml", XmlWriteMode.WriteSchema)

Part B:


Load Grid:
 fslinkBEL = New System.IO.FileStream(Application.StartupPath + "\AOP40.xml", IO.FileMode.Open)
        dtsetlinkBEL.Clear()
        dtsetlinkBEL.ReadXml(fslinkBEL)
        fslinkBEL.Close()
        DataGridView1.DataSource = dtsetlinkBEL.Tables(0)


Save Data in Grid.

Dim ds As DataSet = DirectCast(DataGridView1.DataSource, DataSet) ****Error
ds.WriteXml(Application.StartupPath + "\linkSearchUpdate.xml")


Thanks,

Victor
0
vcharlesAuthor Commented:
Help.
0
Éric MoreauSenior .Net ConsultantCommented:
This is code I just written and tested:

    Private Sub btnGenerate_Click(sender As Object, e As EventArgs) Handles btnGenerate.Click
        'Create a table
        Dim dt As New DataTable("Person")
        With dt
            .Columns.Add("FirstName", GetType(String))
            .Columns.Add("LastName", GetType(String))


            'Add rows
            .LoadDataRow(New Object() {"Joe", "Dalton"}, True)
            .LoadDataRow(New Object() {"Jack", "Dalton"}, True)
            .LoadDataRow(New Object() {"Willam", "Dalton"}, True)
            .LoadDataRow(New Object() {"Averel", "Dalton"}, True)
        End With

        dt.WriteXml("data.xml", XmlWriteMode.WriteSchema)
    End Sub

    Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
        dim ds as New DataSet
        ds.ReadXml("data.xml", XmlReadMode.ReadSchema)
        DataGridView1.DataSource = ds.tables(0)
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        dim dt As DataTable = TryCast(DataGridView1.DataSource, DataTable)
        dt.WriteXml("data.xml", XmlWriteMode.WriteSchema)
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.