Update changes in datatatable to data base

[make me rain]

i am having a datatable ( populated externally in a form & bound to a datagridview )
how can i update the changes back to the database please

please don't ask me to google it, i am bit tired

[jmcilhinney]

please don't ask me to google it, i am bit tired

Oh you poor dear. That Googling is exhausting so I can understand why you'd want us to make the effort for you. Perhaps we could fetch you a pipe and slippers too. Anyway, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. It includes several examples of saving changes from a DataTable to a database using a data adapter and various other ADO.NET examples besides.

[CoachBarker]

[make me rain]

Oh you poor dear. That Googling is exhausting so I can understand why you'd want us to make the effort for you. Perhaps we could fetch you a pipe and slippers too.

please don't ask me to google it, i am bit tired

i mentioned that i was tired googled, ( ofcourse to the best of my english knowledge )
Secondly in any part of the english world, the above para convey that to say Some one has to search in google and reply to me !

Perhaps we could fetch you a pipe and slippers too.

i felt it is over reaction and insult to me.
my apology to those who are all hurt by my post, & i would like to reiterate to the forum that by profession i am a train driver and not programmer, it is just my hobby that's it
in future i request one and all while replying please keep it in your soul And like me there are N number of guys

what i asked was

( populated externally in a form & bound to a datagridview )

A function of a class is returning a populated datatable
and then it is being binded to a datagridview control
do some changes

now these changes need to be Updated back to the datatabse
and here where i am unable perform.

@jmc
thanks for your link to your signature, it is pretty good and usefull
but not working for me

thanks one and all

[techgnome]

it is pretty good and usefull but not working for me

at the risk of seeming harsh... yeah, that doesn't do it... you've been here long enough to know that just doesn't cut it... that's like me telling you the train isn't moving... doesn't mean squat does it. Now if I say that the switch was in the open position and that the train went through in the wrong direction and the closure rail caused a derail... and THAT's why it's not running... well, heck, you'd know what to do then right? same thing ... you're code has derailed and you haven't given and further insight in to the problem.

-tg

[make me rain]

thanks for reply tech, i was upset today out of my 5 years being here
any way forget it
my code is here

Private _BindingTable As New DataTable = QueryActivater.FillDataTable() '' class method that fills the datatable
    '' and then binded in to a datagridview 
    '' via binding source object
 
    Private Sub SaveChanges()
 
        Dim SQL_upDate = <sql>
UPDATE crewcurrentstatus SET crewcurrentstatus.UnderPr = @UnderPr,crewcurrentstatus.prhrs = @Prhrs,
        crewcurrentstatus.undercasualty = @undercasualty WHERE crewcurrentstatus.Status_serla = @Status_serla;
                         </sql>
 
 
        Dim Sql_UpdateString As String = SQL_upDate.Value.ToString
 
        Dim Conn As New Connector '' // a class to open / close connection
 
        Dim Cnn_Connection As New MySqlConnection
        Dim Da5468 As New MySqlDataAdapter '' here i have a problem as what SQL SELECT statement to include
        '' when already a populated datat table is available
 
 
        Dim Cmd5468 As New MySqlCommand(Sql_UpdateString, Cnn_Connection)
 
        With Cmd5468
            .Parameters.Add("@UnderPr", MySqlDbType.Int16, 1, "UnderPr")
            .Parameters.Add("@Prhrs", MySqlDbType.Int16, 2, "Prhrs")
            .Parameters.Add("@undercasualty", MySqlDbType.Int16, 1, "undercasualty")
            .Parameters.Add("@Status_serla", MySqlDbType.Int16, 1, "Status_serla")
        End With
 
        Conn.Cobs_Connect(True, Cnn_Connection) '' open connection
        With Da5468
            .UpdateCommand = Cmd5468
            .MissingSchemaAction = MissingSchemaAction.AddWithKey
            .Fill(Me._BindingTable)
            .Update(Me._BindingTable)
        End With
 
        Conn.Cobs_Connect(False, Cnn_Connection) '' close connection
        Cmd5468.Dispose()
        Da5468.Dispose()
 
        '' but changes not reflection in datatabse
 
 
    End Sub

[techgnome]

there's your mistake....

        With Da5468
            .UpdateCommand = Cmd5468
            .MissingSchemaAction = MissingSchemaAction.AddWithKey
            .Fill(Me._BindingTable)
            .Update(Me._BindingTable)
        End With

You filled your data table just before updating it... so you when the update is called, all the records have not changed (since you just filled it...
you should fill it, do your edits then update... don't re-fill it... you'll just get the same unedited rows back.

-tg

[make me rain]

Changes to datatable is commited by calling acceptchanges method
in datagridview cellendedit event

i have commented out this line
'.Fill(Me._BindingTable)
But still not changes not reflecting
any other advise pl

[techgnome]

you're NOT calling acceptchanges are you? If so, you shouldn't... that doesn't actually update the database... all that does is mark all rows in the data table as "unchanged" ... but it doesn't actually send anythign to the database... only .Update does...

-tg

[make me rain]

Private Sub Dgv_Container_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Dgv_Container.CellEndEdit
 
       
 
        With Me.Dgv_Container '' my datagridview
 
            .CommitEdit(DataGridViewDataErrorContexts.Commit)
            .EndEdit()
 
            If .CurrentRow.Cells(1).Value = True Then
                UnderPr = True
 
  
                '  _BindingTable.AcceptChanges() '' this line also commented out
                ' _Binding.EndEdit()
            
            Else
                UnderPr = False
            End If
                
 
        End With
 
    
 
    End Sub

but still no action

[techgnome]

where did THIS code come from? and what does it have to do with the other code you posted? Don't make me break out the trout slap...

-tg

[make me rain]

Ok ok tech

this how my project is

(1) form contains a datagridview
(2) at the form level i have created
(a)

Private _BindingSource as new bindingsource
                                        Private _BindingTable as new datatable

in form load event, i have populated the _BindingTable by using a function in class

_BindingTable = QueryActivater.FillDataTable()
_BindingSource.DataSource = _BindingTable
Me.Dgv_Container.DataSource = _BindingTable '' binded the datagridview 
me.

now i want make the changes to the datatable which user does in the datagridview, hence i used
datagridview cellendedit event ( that's i posted above )

Private Sub Dgv_Container_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Dgv_Container.CellEndEdit
 
       
 
        With Me.Dgv_Container '' my datagridview
 
            .CommitEdit(DataGridViewDataErrorContexts.Commit)
            .EndEdit()
 
            If .CurrentRow.Cells(1).Value = True Then
                UnderPr = True
 
  
                '  _BindingTable.AcceptChanges() '' this line i commented out after post # 9
                ' _Binding.EndEdit()
            
            Else
                UnderPr = False
            End If
                
 
        End With
     
    End Sub

now i want to update the database with the changes made in the datatable ( i,e _BindingTable changes to database )
and that is this code

Private Sub SaveChanges()
 
        Dim SQL_upDate = <sql>
UPDATE crewcurrentstatus SET crewcurrentstatus.UnderPr = @UnderPr,crewcurrentstatus.prhrs = @Prhrs,
        crewcurrentstatus.undercasualty = @undercasualty WHERE crewcurrentstatus.Status_serla = @Status_serla;
                         </sql>
 
 
        Dim Sql_UpdateString As String = SQL_upDate.Value.ToString
 
        Dim Conn As New Connector '' // a class to open / close connection
 
        Dim Cnn_Connection As New MySqlConnection
        Dim Da5468 As New MySqlDataAdapter '' here i have a problem as what SQL SELECT statement to include
        '' when already a populated datat table is available
 
 
        Dim Cmd5468 As New MySqlCommand(Sql_UpdateString, Cnn_Connection)
 
        With Cmd5468
            .Parameters.Add("@UnderPr", MySqlDbType.Int16, 1, "UnderPr")
            .Parameters.Add("@Prhrs", MySqlDbType.Int16, 2, "Prhrs")
            .Parameters.Add("@undercasualty", MySqlDbType.Int16, 1, "undercasualty")
            .Parameters.Add("@Status_serla", MySqlDbType.Int16, 1, "Status_serla")
        End With
 
        Conn.Cobs_Connect(True, Cnn_Connection) '' open connection
        With Da5468
            .UpdateCommand = Cmd5468
            .MissingSchemaAction = MissingSchemaAction.AddWithKey
           ' .Fill(Me._BindingTable)
            .Update(Me._BindingTable)
        End With
 
        Conn.Cobs_Connect(False, Cnn_Connection) '' close connection
        Cmd5468.Dispose()
        Da5468.Dispose()
 
        
    End Sub

till now i did this much

[jmcilhinney]

If it's not working for you then you didn't do it like I did. It's that simple. In my examples there's not an AcceptChanges in sight, so you added that arbitrarily. I also don;t have a Fill and Update call together anywhere, so to say that my code is not working for you is simply not the case.

Anyway, I would consider the CellEndEdit event of the grid to be the wrong event to handle. If you really must save after every change, which I'd generally recommend against, then I'd suggest handling the CurrentItemChanged event of the BindingSource. Call EndEdit on the BindingSource and then Update on the data adapter.

[techgnome]

The other thing I noticed was that there was no call to the SaveChanges method...

-tg