Datagrid view to DataBase - Update

NANCO

Hi! I need Help once Again. I used this command to show the orders a particular customer made. DataGridView1.Rows.Add(str0,str1, str2, str3, str4, str5) Now, How can I send all the entry From DataGridView1 to a common DataBase where all the orders of all the customers are stored. (Only differentiated by the Customer ID ,str0 = CustomerID) (In the DataBase You can get orders made by a particular customer by the CustomerID) So, can anyone tell me How to Send all the Orders made a Customer to the Common Database.(I heard about the Update method and all) But any Help is Appreciated. (as the orders are made it Will be shown in the DataGridView(only for the current customer)) -Thanx-

Johan Hakkesteegt

Hi Nanco, From the way you describe the process, I understand that you have made a form with (probably) textboxes. The user enters order information into these boxes which you then gather into the DataGridView1. Assuming that this is correct, and that you are using vb.net (please say you are)... The best way to proceed is to start using a DataAdapter and DataSet, linked to the database table that you want to store the orders in. Next you use this dataset as the datasource for the datagrid, and last but not least, you register the user input (textbox values) to the dataset and not to the datagrid. This way it is infinitely more easy to retrieve existing orders (DataAdapter.Fill(DataSet)), and more importantly, the datagridview can be used to allow the user to add and/or edit his order (DataAdapter.Update(DataSet)) The DataAdapter will take care of retrieving and saving data to and from the database. Hope this helps you on your way, Johan

My advice is free, and you may get what you paid for.

Trupti Mehta

Hello Johan, I was looking out for Updating data from Datagrid to Database and came through this reply. In my code in VB 2005, records based on SEarch are displayed in the datagrid. Dataset is bounded the way you mentioned datagrid1.datasource = dataset. Now, when I make changes in the datagrid and click Update button, it gets saved in the dataset, but nor in the database. My code is :

' POPULATE DATA GRID
Private Sub PopulateDataGrid(ByVal index As Integer)
    Try
        ' Retreive data by passing Category Id and save in Data set
        Dim i As Integer = Me.TblEmpTableAdapter.FillByCatId(Me.AlbumsDataSet.Tables("tblEmployee"), index)

().ToString())
Me.EmpDataGrid.SelectionMode = DataGridViewSelectionMode.FullRowSelect
' Set the DataSet as Datasource for the Data grid
Me.EmpDataGrid.DataSource = Me.EmpDataSet.Tables("tblEmployee")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

Private Sub UpdateDetails()
    If (MsgBox("Are you sure to Update the Changes made? ", MsgBoxStyle.YesNo, "Update Details") = MsgBoxResult.Yes) Then
        ' Me.EmpDataSet.Tables("tblEmployee").AcceptChanges()
        Dim i As Integer = Me.TblEmpTableAdapter.Update(Me.EmpDataSet.tblEmployee)
        Me.EmpDataSet.AcceptChanges()
        MsgBox("Updated Status = " + i.ToString())
    End If
End Sub

Private Sub EmpDataGrid\_UserDeletingRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowCancelEventArgs) Handles EmpDataGrid.UserDeletingRow
    If (MsgBox("Are you sure, you want to Delete the record ", MsgBoxStyle.YesNo, "Delete Record?") = MsgBoxResult.Yes) Then

        MsgBox("Record Deleted")
    Else
    End If
End Sub

Their is no BindingSource used in this for Datagrid. I tried a lot in several ways. Surfed the net alot for some solution, but could find any. Can you please help me out. I also want to add and delete records from the Datagrid. For deleting I just caught the event and was wondering what to do if user says No. Couldn't find any solution forr that also. I would appreciate, if you can help me out.

Thanks Terry

Johan Hakkesteegt

Hi Terry,

Trupti Mehta wrote:

Private Sub UpdateDetails() If (MsgBox("Are you sure to Update the Changes made? ", MsgBoxStyle.YesNo, "Update Details") = MsgBoxResult.Yes) Then ' Me.EmpDataSet.Tables("tblEmployee").AcceptChanges() Dim i As Integer = Me.TblEmpTableAdapter.Update(Me.EmpDataSet.tblEmployee) Me.EmpDataSet.AcceptChanges() MsgBox("Updated Status = " + i.ToString()) End If End Sub

The first thing that I notice in your code, is that you called .AcceptChanges and then .Update and then once again .AcceptChanges. This is not necessary and in this case would have even caused the problem. Here is why: when a user makes changes in the datagrid, the rowstate of the rows in the linked DataSet will change accordingly. Change a value and the rowstate becomes something like "changed", delete a row and its rowstate becomes "deleted" (or that is the general idea anyway). When you call .Update the dataset will compare the rows with an altered rowstate, with the corresponding rows in the database, implement the changes where necessary, and finally reset the rowstates and refresh the dataset. Now when you first call .AcceptChanges all these rowstates are reset. When you now call .Update there are no altered rowstates, and therefore the dataset has nothing to compare, nor implement. Apparently you commented the first .AcceptChanges away, so as far as I can tell it should work. You may however want to also remove the second .AcceptChanges, as .Update already does that. One thing you could check is that there isn't some other function or sub that calls .AcceptChanges before the user tries to update his order (this is a mistake I made once, and it took me weeks to finally figure out that I called .AcceptChanges somewhere else entirely). The .Update method will actually implement all changes for you, also deleting rows, etc. If you want to give a user the chance to change his mind about deleting a row, you can just call .AcceptChanges, .Clear and then DataAdapter.fill(YourDataSet) if he chooses 'No' or .Update if he chooses 'Yes'. Hope this helps, Johan

My advice is free, and you may get what you paid for.

Trupti Mehta

Thanks Johan, But nothing worked. AcceptChanges was before also as I was just trying to figure out working here & there. Otherwise the code is :

Dim i As Integer = Me.TblAlbumsTableAdapter.Update(Me.AlbumsDataSet.Tables("tblAlbums"))
MsgBox("Updated Status = " + i.ToString())

Dataset has 2 table in it. I tried the above way & also by passing Dataset. That goes to ListSelectionEvent Changed (1 table is connected to the list) and then to second where I want to make changes. It just gors to ReadOnly Property .. Me.TableBase . Nothing else. Do I need to manually set the values of current changed row to DS or some other way. For Delete also :

    If (MsgBox("Are you sure, you want to Delete the record ", MsgBoxStyle.YesNo, "Delete Record?") = MsgBoxResult.Yes) Then
        Me.TblAlbumsTableAdapter.Update(Me.AlbumsDataSet)
        MsgBox("Record Deleted")
    Else
        Me.AlbumsDataSet.RejectChanges() ' .AcceptChanges()
        Me.AlbumsDataSet.Tables("tblAlbums").Clear()
        Me.PopulateDataGrid(Me.categoryList.SelectedIndex + 1)
        Me.AlbumsDataGrid.Refresh()
    End If

If No is selected, the row gets deleted, on selecting other list item & returning to the same, it shows the record back. If Yes is selected, it happens the same way. But, it is NOT deleted from DB. I hope you can help me out more with the problem.

Thanks Terry

Johan Hakkesteegt

Hi Terry, From what I can tell, the problem with the db not being updated, happens because somehow the connections between your database, DataAdapter, DataSet and DataGrid are not configured correctly. Perhaps you could try to add a new test form to your app, put a datagrid on it, then create a DataAdapter that gets data from a single (test) table only, create a DataSet from it, and then select the dataset as the datasource in the datagrid properties. Load the data into the datagrid during the load event () and update the table in the database with a button and the .Update method. See if it works, and if it does, you can check how this test differs from your case. If the test doesn't work, you may have to do some additional googling about why not. As for your delete code, it is actually working the way it should according to your code. .RejectChanges only resets the rowstates in the dataset. I am not sure what this PopulateDataGrid does, but if it doesn't include a .Fill somewhere, then it is correct that your datagrid does not actually update its content. .Refresh will actually only repaint the control, and is best avoided, because it can screw up the scrollbars. Good luck, Johan

My advice is free, and you may get what you paid for.

Trupti Mehta

Thanks Johan, .Accept or Reject nothing was working. Finally someone like you suggested me to use e of Eventargs e.Cancel That worked out. With that the event procedure is cancelled and you are back to normal. Nothing else is required. Reg. Update, now finally things have come to track. Actually the code was proper & well. But, I didn't select Copy if newer of CoptToOutpurDir property of Database. That was Copy Always which was creating all the problem. That point someow clicked in my mind last night & things got working. Thanks a lot for all the effort and interest. I really appreciate your help and guidance.

Thanks Terry