Updating database table for a specific value only

[Yuliya]

I have a table in my database called 'tasks.' The rows have a person, a task, a date, a time, and whether completed.

I need to have a Windows Form displaying tasks for just one particular person, and updating any changes the user might make.

What's the best way to do it?

(I can display the data with a DataGridView, without the 'person' column, filling it by 'person.' But how do I update the database once the user is done editing the DataGridView? How do I specify the value of the 'person' column in the TableAdapter?)

 

[jmcilhinney]

So, you have a Person table with an ID column. You then have a Task table with a PersonID column that contains an ID value from Person table. That's called a foreign key. Now, if you want tasks for a specific person you get Task records WHERE the PersonID is the ID of the Person you're interested in:

SELECT * FROM Task WHERE PersonID = @PersonID

You right-click your TableAdapter and select Add Query, then use the Query Builder to create a query like that, naming it FillByPersonID/GetDataByPersonID. The wizard will then add those methods to your TableAdapter. You now call FillByPersonID and specify the PersonID rather than calling Fill. That will get the desired subset of your data. Once you've made changes you call Update on the TableAdapter to save as normal.

 

[Yuliya]

That's what I did -- I have a FillByPerson in the TableAdapter. So now when I call Update, will it save the new rows of the DataGridView with the same PersonID?

 

[jmcilhinney]

The Update method of your TableAdapter will save all the changes contained in the DataTable you pass to it. That's all. If you only retrieved records for one PersonID in the first place then obviously it can only save changes for that one PersonID.

 

[Yuliya]

Right. But the problem is, if I add new rows to the DataGridView, and then call Update(), it saves the rows without specifying the PersonID. It leaves PersonID Null. How do I get it to save to the database with that specific PersonID, without the user having to input that ID manually?

 

[jmcilhinney]

No, it's not that IT leaves the PersonID Null. YOU are leaving the PersonID Null. YOU must know what the PersonID is because you used it to execute the query so YOU have to put that value in the PersonID field of any new rows.

 

[Yuliya]

OK, so here's what I did. I added the PersonID column to the DataGridView, but I made it invisible.

So when do I set the value of the PersonID for each row? I am thinking of putting it in either RowEnter or RowLeave event handler for the DataGridView. Is that a good way of doing it?

Thank you so much for all your help.

 

[jmcilhinney]

You don't need any hidden column in the grid because you should not be working with the grid. You should have bound your DataTable to a BindingSource and bound the BindingSource to the grid. The BindingSource is where you're supposed to work with bound data.

You should be handling the AddingNew event of the BindingSource, casting the e.NewObject as type DataRowView and setting its PersonID column:

Private Sub BindingSource1_AddingNew(ByVal sender As Object, _
                                     ByVal e As System.ComponentModel.AddingNewEventArgs) Handles BindingSource1.AddingNew
    DirectCast(e.NewObject, DataRowView)("PersonID") = personID
End Sub

 

[Yuliya]

This makes a lot more sense...

But it doesn't work. When the Windows Form opens, I click on a cell in DataGridView to enter a new row, and right away the AddingNew function gets called, but e.NewObject is Nothing, so I get an exception.

What am I doing wrong?

 

[JohnH]

You can also set a default value for the column dynamically:

Me.SomedbDataSet.Table2.Columns("t1id").DefaultValue = value

This approach is usable for cases where you are filtering by a foreign key and not binding to a master bindingsource and table relation (where the bindingsource would input the foreign key automatically).

 

[Yuliya]

Wow, this works! Thank you so much!

 

[cjard]

note that if youre getting the DB to calculate the ID of the row, with a sequence or SELECT MAX or something, then you can just submit a null, the db will assign an ID and the datatable will update itself with the new value during the save op.
If youre maintaining client side relations, then you can use the AutoIncrement properties of the datatable column (see the designer) to automatically assign a temprary numerical id