Question updating through dgv

[jamie123]

Hi, I'm programming in vb.net using vs2008 and sql2005.

Here is my issue, I have the following code:

     If e.ColumnIndex = 0 Then
            param = InputBox("Edit frame name?", "Edit")
            id = e.ColumnIndex + 1
            Me.MaterialsTableAdapter.EditFrame(param, id)
            Me.MaterialsTableAdapter.fill(Me.Dataset.Materials)
            Me.MaterialsTableAdapter.Update(Me.DataSet.Materials)

This occurs when a datagridview column is clicked, if the column happens to be 0, it does that routine (edits the row/column) Here is the code that is contained in the "editframe" sql query:

UPDATE [dbo].[Materials] SET [Frame] = @Frame WHERE (ID = @ID)

MY PROBLEM:

After updating this code, and refilling the dataset, my dgv should reflect the changes, yes?

It doesn't though, and I feel as if its not a matter of the datagridview not being updated, if I insert new data into the dgv using insert statements, the change is reflected in the dgv and the dgv is refreshed using the same code which i showed above. I don't think something is updating right, but I've always updated info in sql servers using the above type method, why is this not working correctly?

Thanks !

 

[Tom]

I dont understand why your calling fill (retrieving records) from the database before doing your update. Again I dont know the surrounding code but I assume since there editing the DGV, its attached to a dataset/datatable that is already filled.

 

[jmcilhinney]

As Tom suggests, ther is no refilling the DataSet. You call Fill first and then bind the DataTable to your control(s). You then edit the data, either through the controls of the UI or in code via the DataTable. Finally you call Update and save the changes. That's it. The changes are already in the DataTable BEFORE they're in the database, so there's no need to retrieve data again.

 

[jamie123]

Okay, that makes sense, but how can I have these changes that are in the datatable reflected onto the datagridview?

I used the .fill commands because I had trouble about a year ago refreshing a dgv
(http://www.vbdotnetforums.com/winforms-grids/26989-refreshing-dgv-have-tried-everything.html) I found that when refilling the datatable it would refresh the control, to date this is the only way i've found to "refresh" a datagridview.

 

[jamie123]

Maybe my question would be better asked as :

"How can I PROPERLY refresh a datagridview after the attached datatables have had information changed?"

 

[jmcilhinney]

The answer is that you don't. That's the whole point of data-binding: the control and the data source are bound together so whatever affects one affects the other. When the user makes a change in the grid the bound DataTable is automatically updated. Likewise when you nake any changes to the DataTable in code the bound grid is automatically updated.

 

[jamie123]

Well that makes sense, but I don't really know what else to say other than in my case, its not. It never has for me, and this is the second data-bound project I've worked on. After I execute those update statements, nothing in the datagridview changes. And they are indeed updating the table that is databound to the datagridview. I don't know what else I should do, what should I look at? I assure you all I have is a databound grid and an update statement.

 

[jamie123]

Likewise, I have a databound combobox. I insert a row into the table the combobox is databound to. According to what you said, databound controls should update when their datatable changes, but this is not the case for this either, the combobox does not update until the form is closed then reloaded. I know this is getting repetitive, but I do not know what else to do. I understand what you're saying but its just not working like that on my end, perhaps you can show me some ideal code that would typically work in the way you described?

 

[jmcilhinney]

I would say that you probably aren't actually binding the data, or else binding to a different set of data to the one you're editing. Try this:

1. Create a new WinForms Application project.
2. Add a ComboBox and a Button to the form.
3. Add the following code:

Private table As New DataTable

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    With Me.table.Columns
        .Add("ID", GetType(Integer))
        .Add("Name", GetType(String))
    End With

    With Me.table.Rows
        .Add(1, "Peter")
        .Add(2, "Paul")
        .Add(3, "Mary")
    End With

    With Me.ComboBox1
        .DisplayMember = "Name"
        .ValueMember = "ID"
        .DataSource = Me.table
    End With
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Me.table.Rows.Add(4, "John")
End Sub

4. Run the project.
5. Drop-down the ComboBox and note the contents.
6. Click the Button.
7. Drop-down the ComboBox and note the contents.

Voila!

Now try another project but this time add a DataGridView instead of a ComboBox and use this code:

Private table As New DataTable

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    With Me.table.Columns
        .Add("ID", GetType(Integer))
        .Add("Name", GetType(String))
    End With

    With Me.table.Rows
        .Add(1, "Peter")
        .Add(2, "Paul")
        .Add(3, "Mary")
    End With

    Me.DataGridView1.DataSource = Me.table
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Me.table.Rows.Add(4, "John")
End Sub

Again, voila!

 

[jamie123]

Okay, I tried that and it does work perfectly. However, I created a similar small testing application that is created almost exactly like my project, and it does not work in the way that your app does. Perhaps you know why?

Private table As New DataTable

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'TestdatabaseDataSet.Table1' table. You can move, or remove it, as needed.
Me.Table1TableAdapter.Fill(Me.TestdatabaseDataSet.Table1)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Table1TableAdapter.InsertQuery("hello")
End Sub

Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
MsgBox("dd")

Table1TableAdapter.UpdateQuery("dd")
End Sub

Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

End Sub
End Class

Use the app you created except bind the datagridview to a sample database with a "Table1" and a varchar column "A". Attach the database and set the datasource by clicking on the dgv and selecting the appropriate database. Use the strongly typed dataset that gets created automatically when adding the database to create an insert statement and an update statement. After executing either of them, there are no errors and the datagridview is not updated.

Any thoughts?

Thanks!

 

[jmcilhinney]

OK, you are NOT editing the data in the DataTable. By calling InsertQuery and UpdateQuery you are interacting directly with the database and bypassing the DataSet. As I said way back in my first reply, you populate a DataTable, bind it, edit it and then save it. You have omitted the last two steps. Those methods you are using are known as "DB Direct" methods because they edit the database directly. You would only do that if you did NOT have bound data. You do have bound data, so you need to edit that bound data, otherwise why would the controls the data is bound to show any change? So, you need to edit the data in the bound DataTable, either directly in code or else through the bound control(s). You then need to call Update (NOT UpdateQuery) on the TableAdapter and tell it to save that edited DataTable.

 

[jamie123]

Okay, well now that makes sense completely thank you for explaining it!


If I still wanted to use these dbdirect methods (simply for their easy and graphical nature):

Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0 - ScottGu's Blog Shows a tutorial on something to do with databases (didn't look at it, not relevant) but noticed the eighth or ninth photo down it says "Refresh DataTable" I was wondering, if I still wanted to use the dataset's "insertquery" and "updatequery", would this refresh datatable option solve my issues? If it does, the reason I'm asking and not trying it for myself is because a few photos up it shows in the "Enter a SQL statement" box an Advanced Options button, that button is what contains the option to Refresh Datatable. I don't know how to get that button to appear? And again, would this "refresh datatable" have any implication on my issue?

Thanks!

 

[jmcilhinney]

No, that Refresh DataTable option has nothing to do with those DB Direct Methods. The point of Refresh DataTable is to retrieve PK values and other data generated by the database when you insert new records.

Bound DataTables and DB Direct methods are essentially mutually exclusive. An example of when you might use DB Direct methods is when you are working with a single record at a time. If the user just entered field values into TextBoxes on a form and you wanted to insert that data into the database then you might use a DB Direct INSERT. If you have a list of records in a DataTable bound to one or more controls then you DO NOT use DB Direct methods.

 

[jamie123]

Alright, so without using the db direct method, how do i programmatically insert into the datatable?

I can insert into the tableadapter, but that yields the same result as the insertqueries. I'm not sure which object to invoke the method on?

edit: I also tried this

   DataSet.Labs.AddLabsRow(txtName.Text, txtAddress.Text, txtCity.Text, txtState.Text, txtZip.Text, txtPhone.Text, txtFax.Text)
            LabsTableAdapter.Update(DataSet.Labs)

and this

DataSet.Labs.Rows.Add(txtName.Text, txtAddress.Text, txtCity.Text, txtState.Text, txtZip.Text, txtPhone.Text, txtFax.Text)

which i got from: How to: Add Rows to a DataTable


..unfortunately yielded the same result as before (information updated after the form is closed then reopened)

Thanks again!

 

[Tom]

I'm wondering if your using two different datasets. One that is attached to the datagridview and creating another (unconnected) dataset when your adding records and doing your insert. Can you confirm that your using a form or global level dataset and are not dimesioning a new dataset in your other events?