Link to home
Start Free TrialLog in
Avatar of Victor  Charles
Victor CharlesFlag for United States of America

asked on

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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

How do you load your grid?
Avatar of Victor  Charles

ASKER

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
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.
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
You can use the WriteXml method of the dataset as shown in http://support.softartisans.com/kbview_1301.aspx
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

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

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

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?
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.
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)
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

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

How do you declare your ds?
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

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

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

you are just ignoring my comments!
I will read them again.

Thanks,

V.
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.
Correction,

Save button:

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

Will not need the schema.

Thanks
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
Help.
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you.