[RESOLVED] TableAdapter insert with data-bound controls

[ptrifonov]

Hello all,

It was extremely hard for me to formulate an accurate title for this topic, so excuse me for that.

I am building a vb.net application that is going to act as a evaluation wizard for evaluating the staff of a company.
I've decided to use the visual data set designer just to experiment, because before now I've always had used typed datasets. So I've created the following DataSet in the DataSet designer of Visual Studio 2012:


Full-size image.

Basically the application form has 29 ComboBoxes, which will allow the evaluator to determine a staff member assessment by a specific criterion. Every ComboBox represents the mark for a separate criteria. I have to populate these ComboBoxes from an SQL database table (dbo.combos) with a corresponding Display text and underlying Value. I also data-bound these 29 controls to column in another SQL table dbo.marks via the Selected value option. The values of the ComboBoxes are going to be inserted in the dbo.marks table (Criteria1, Criteria2...), by the TableAdapter manager:

Private Sub WizardPage10_Validated(sender As Object, e As EventArgs) Handles WizardPage10.Validated
        Try
            Me.Validate()
            Me.MarksBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.DSEw)
            frmMain.ssStatusLabel.Text = "Status: Update was successful."
        Catch ex As Exception
            MsgBox("Update failed!")
        End Try
    End Sub

And this thing works, but my problem is that I have to insert My.User.Name as value to the dbo.marks table (Evaluator column) and the current date along with all the rest of the data-bounded controls. I don't have controls to let user choose the today's date and don't have control to display My.User.Name property and don't want to have such controls. Shall I pass these values to the .FillBy method of the adapter somehow? Is it possible at all to pass data to TableAdapter by both data-bound controls and variables or system properties like My.User.Name?

Thank you all in advance and excuse me if I am not clear enough.

Kind regards,

Plamen.

[jmcilhinney]

When you call FillBy, you are populating a DataTable with zero, one or more DataRows. In your case, presumably it is one DataRow. That DataTable is bound to a BindingSource and the BindingSource is bound to the controls. The data you see in the controls comes from the object exposed by the Current property of the BindingSource. In your case, that object will be the DataRowView that corresponds to your one and only DataRow. You can get that DataRowView from the Current property of your BindingSource and set those fields manually. When you call Update on your table adapter, either explicitly or implicitly by calling UpdateAll, all the data in the DataRow will be saved, whether it got there by your setting it manually or the user setting it via the UI.

Dim row = DirectCast(Me.MarksBindingSource.Current, DataRowView)
 
row("Evaluator") = My.User.Name
row("DateColumn") = Date.Today

By the way, I hope your method isn't actually named FillBy. When you create a table adapter, it has Fill and GetData methods by default. The former populates an existing DataTable with all the records from the database table and the latter creates and returns a new DataTable containing all the records from the database table. When you add a new query, you are prompted for the names of the corresponding methods and FillBy and GetDataBy are populated by default. The idea is that you provide a meaningful name based on the query filter. For instance, if you have "WHERE UserId = ?" in your query then you would name the methods FillByUserId and GetDataByUserId.

[ptrifonov]

When you call FillBy, you are populating a DataTable with zero, one or more DataRows. In your case, presumably it is one DataRow. That DataTable is bound to a BindingSource and the BindingSource is bound to the controls. The data you see in the controls comes from the object exposed by the Current property of the BindingSource. In your case, that object will be the DataRowView that corresponds to your one and only DataRow. You can get that DataRowView from the Current property of your BindingSource and set those fields manually. When you call Update on your table adapter, either explicitly or implicitly by calling UpdateAll, all the data in the DataRow will be saved, whether it got there by your setting it manually or the user setting it via the UI.

Dim row = DirectCast(Me.MarksBindingSource.Current, DataRowView)
 
row("Evaluator") = My.User.Name
row("DateColumn") = Date.Today

By the way, I hope your method isn't actually named FillBy. When you create a table adapter, it has Fill and GetData methods by default. The former populates an existing DataTable with all the records from the database table and the latter creates and returns a new DataTable containing all the records from the database table. When you add a new query, you are prompted for the names of the corresponding methods and FillBy and GetDataBy are populated by default. The idea is that you provide a meaningful name based on the query filter. For instance, if you have "WHERE UserId = ?" in your query then you would name the methods FillByUserId and GetDataByUserId.

Thank you so much jmcilhinney for the nice explanation. Many things have become clear to me.

Regards,

Plamen