Update changes in datatatable to data base

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

Quote:

---

Originally Posted by make me rain

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.

:eek: :lol:

Quote:

---

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.

---

:ehh:

Quote:

---

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 )
:mad: 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 !

Quote:

---

Perhaps we could fetch you a pipe and slippers too.

---

:sick: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

Quote:

---

( 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

Quote:

---

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

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

vb.net Code:

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

there's your mistake....

Code:

---

        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

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

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

vb.net Code:

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

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

Ok ok tech

this how my project is

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

vb.net Code:

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

vb.net Code:

_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 )

vb.net Code:

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

vb.net 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

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.

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

-tg