save datatable to database

[[gja]]

Hello There,
i would to ask question on how to update my database table by the data inside my datatable.

this is my class

public class Sample

Private ds As New DataSet("myDs")
private dt as new Datatable("myDT")
Private data_adapter As SqlDataAdapter

private sub FormLoad(....)


        'Add datatable object to dataset object.
        ds.Tables.Add(dt)

        'Create a new column for datatable.
        Dim dc As New DataColumn("RangeNo", System.Type.[GetType]("System.String"))
        dc.DefaultValue = ""
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        dt.PrimaryKey = New DataColumn() {dt.Columns("RangeNo")}

        'Create a new column for datatable.
        dc = New DataColumn("Minimum", System.Type.[GetType]("System.String"))
        dc.DefaultValue = ""
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Maximum", System.Type.[GetType]("System.String"))
        dc.DefaultValue = "0"
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Max_Interest", System.Type.[GetType]("System.String"))
        dc.DefaultValue = "0"
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Term", System.Type.[GetType]("System.String"))
        dc.DefaultValue = "0"
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Remainder", System.Type.[GetType]("System.String"))
        dc.DefaultValue = "0"
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("IsPercent", System.Type.[GetType]("System.Boolean"))
        'dc.DefaultValue = False
        dc.AllowDBNull = False

        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("ID", System.Type.[GetType]("System.Int32"))
        'dc.DefaultValue = False
        'dc.AllowDBNull = False

        'Add created column to datatable object.
        dt.Columns.Add(dc)

'--------------------------------------------------------------
               ds.Tables("myDT").Clear()
                If oCNN.State = ConnectionState.Closed Then
                    DB_Init()
                End If
                data_adapter = New SqlDataAdapter(SELECT_STRING, oCNN)

                data_adapter.FillSchema(ds, SchemaType.Source, "myDT")

                data_adapter.Fill(ds, "myDT")
end sub

private sub DeleteRow(...)
   ds.tables("myDT").rows(myGrid.visiblerow -1).delete
   ds.tables("myDT").AcceptChanges()

   myGrid.datasource = ds.tables("myDT")
end sub

private sub UpdateDatabase(..)
    data_adapter = New SqlDataAdapter("SELECT * FROM Table1 WHERE myField like 'Range%' order by ID", oCNN)

                Dim objCommandBuilder As New SqlCommandBuilder(data_adapter)

                data_adapter.InsertCommand = objCommandBuilder.GetInsertCommand()
                data_adapter.DeleteCommand = objCommandBuilder.GetDeleteCommand()
                data_adapter.UpdateCommand = objCommandBuilder.GetUpdateCommand()

                data_adapter.Update(ds, "myDT")
end sub
end class

i had problem in sub deleterow AcceptChanges() method not seems to work.i cannot update my database if i put AcceptChanges() method after the delete row..

if i delete 1 row then hit the updatedatabase sub the record in my database is the same..seems like there is no delete happened..my server is mssql2000

Thank you..

[Nightwalker83]

Could you please wrap your code in [code][/code] tags to make it easier to read. Thank you!

[jmcilhinney]

I see various issues with your code. First, you're building the DataTable schema manually, then you call FillSchema on a DataAdapter, then you call Fill on the same DataAdapter. The point of FillSchema is to build the schema of a DataTable based on a query if you are NOT actually retrieving any data. You ARE retrieving data so you can get rid of the FillSchema call. Also, Fill will build the schema for you anyway so why are you building the schema yourself?

Next, you appear to be trying to store numbers as text, and that is never a good thing. If you're working with numbers then they should be stored as numbers at all times. That includes in the database and in your app.

Next, you're creating two diffreent DataAdapters. Create one and use one.

Next, what's the DataSet for? Why do you need it at all? Why not just use the DataTable alone?

Finally, you definitely shouldn't be calling AcceptChanges. After calling AcceptChanges or RejectChanges, there are no changes in your DataTable, so that means nothing to save. When you call Update on a DataAdapter, it implicitly calls AcceptChanges AFTER the changes have been saved.

I strongly suggest that you follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. It contains several ADO.NET code examples, including one that uses a DataAdapter to retrieve and save data, with potential editing in between.

[[gja]]

Public Class myClass

Private adapter As New SqlDataAdapter("SELECT * FROM Table1 WHERE RangeNo like 'Range%' order by ID", conn)

Private builder As New SqlCommandBuilder(adapter)

Private dt As New DataTable("myDT")

Private sub FormLoad(...)
    DesignTableStyle()
    
     'Create a new column for datatable.
        Dim dc As New DataColumn("RangeNo", System.Type.[GetType]("System.String"))
        dc.DefaultValue = ""
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        dt.PrimaryKey = New DataColumn() {dt.Columns("RangeNo")}

        'Create a new column for datatable.
        dc = New DataColumn("Minimum", System.Type.[GetType]("System.Double"))
        dc.DefaultValue = 0
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Maximum", System.Type.[GetType]("System.Double"))
        dc.DefaultValue = 0
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Max_Interest", System.Type.[GetType]("System.Double"))
        dc.DefaultValue = 0
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Term", System.Type.[GetType]("System.Int32"))
        dc.DefaultValue = 0
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("Remainder", System.Type.[GetType]("System.Double"))
        dc.DefaultValue = 0
        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("IsPercent", System.Type.[GetType]("System.Boolean"))
        'dc.DefaultValue = False
        dc.AllowDBNull = False

        'Add created column to datatable object.
        dt.Columns.Add(dc)

        'Create a new column for datatable.
        dc = New DataColumn("ID", System.Type.[GetType]("System.Int32"))
        'dc.DefaultValue = False
        'dc.AllowDBNull = False

        'Add created column to datatable object.
        dt.Columns.Add(dc)

        GetData()
End sub

Private Sub GetData()

        If conn.State = ConnectionState.Closed Then
            open DB connection here
        End If
        'Retrieve the data.

        Me.adapter.Fill(dt)
End Sub

Private Sub SaveData()

        'Save the changes.

        Me.adapter.Update(dt)

        Me.myGrid.DataSource = dt

End Sub

Private Sub DeleteRow(...)
    dt.Rows(myGrid.VisibleRowCount - 1).Delete()
    dt.datasource = dt
End sub

End Class

Thank you for your quick reply jm.i follow your instructions this is now my modified code.it update my database now,but ive encountered problem when deleting datarow in table "dt" and then add a new row again in table "dt".

this is the error:
Deleted row information cannot be accessed through the row.

any idea how to resolve it?

[jmcilhinney]

This is the problem:

Private Sub DeleteRow(...)
    dt.Rows(myGrid.VisibleRowCount - 1).Delete()
    dt.datasource = dt
End sub

You cannot use the index from the grid to refer to a row in the DataTable because there are numerous reasons that they would not coincide, one of which is that you have deleted a row.

I would recommend upgrading to a more recent version of VB. At least three versions have been released since the version you're using, so you're a bit out of date. If you were using VB 2005 or later then you could simply bind your DataTable to a DataGridView via a BindingSource. Deleting a row would then be a simple matter of calling RemoveCurrent on the BindingSource.

As it stands, you will have to access the appropriate BindingContext. Change that code above to this:

Private Sub DeleteRow(...)
    DirectCast(Me.BindingContext(dt).Current, DataRowView).Delete()
End sub

[jmcilhinney]

Also, you're still creating the DataTable's schema manually. You don't need to add the columns because calling Fill will do that for you.

[[gja]]

Private Sub DeleteRow(...)
    DirectCast(Me.BindingContext(dt).Current, DataRowView).Delete()
End sub

Hello jm,
i get the same error again on your code.

error:
An unhandled exception of type 'System.Data.DeletedRowInaccessibleException' occurred in system.data.dll

Additional information: Deleted row information cannot be accessed through the row.


the scenario:
i click the deleterow sub and after that i add a new row again...

i do a quick google about this problem and it says ..when you call a delete method of datatable it is not actually delete the row in the rowcollection..



btw,im using vb2003 because this system is slightly big and it takes time to upgrade this..but i made a proposal to the company to upgrade this to vb2005 or vb2008.