Question Having a hard time updating SQL table through tableadapter

[cdognight]

This is my first attempt in .NET creating a form app and I'm having a little bit of difficulty grasping the concept of the DataSource/TableAdapter/Query/Fill/GetBy/etc. relationships.

I have a form with a bunch of textboxes and a couple of checkboxes that act as spec input to configure a product. The product type is selected with a combobox, "cmbProduct". I have a SQL table, "UserInputs", that I want to use to pre-fill the form with the specs that the user entered for the previous configuration of that product. The table will have a unique record for each user/product combination.

I have created a dataset we'll call "myDataSet".

I have created a TableAdapter, "UserInputsTableAdapter". I set the "main" SQL query to simply be (select * from UserInputs) and VS created Fill() and GetBy() methods for me.

I added a SQL query to the TableAdapter to pull the desired record.... (select * from UserInputs where username = @username and product = @product). This also created methods, which I named FillByUserAndProgram() and GetDataByUserAndProgram().

I pulled up the DataSet designer and from my TableAdapter dragged-n-dropped my fields onto their respective controls.

In my form_load I have:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '...'

        pUser = GetUserName

        UserInputsTableAdapter.FillByUserAndProgram(myDataSet.UserInputs, pUser, cmbProduct.SelectedValue)

End Sub

I also have the same FillBy in the event handler for the combobox change...

Private Sub cmbProduct_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbProduct.SelectedIndexChanged

        UserInputsTableAdapter.FillByUserAndProgram(myDataSet.UserInputs, pUser, cmbProduct.SelectedValue)

End Sub

This all seems to work fine, visually, although I may be setting it up wrong. Whenever I change the selection in the combobox, all of my fields are populated with the appropriate data from UserInputs.

At this point, the user may obviously change some of these fields, and I'd like to write those changes back to the record once they submit the form. This is where my confusion with the relationship and connection between the data in my form and the actual record in my database comes in.

My assumption was that after the fields had been changed that I could simply do this...

UserInputsTableAdapter.Update()

But the Update method is asking for a (dataRow as System.Data.DataRow) object as a parameter. Am I supposed to be creating a dataRow object somewhere? Should I be using the GetDataBy method to retrieve the record to begin with? Any help is appreciated.

Thanks,
Chris

 

[jmcilhinney]

When you call Update you need to pass it the DataTable that contains the changes, just as you need to pass the DataTable you want to populate when you call Fill.

 

[cdognight]

I've modified my update to the following and assigned it to a button...

UserInputsTableAdapter.Update(myDataSet.UserInputs)

I had to choose the one of four available Update methods that wanted dataTable as a parameter. Not sure how that works (I'm still very new to VB.NET).

I select a product from the combobox, my data fills, I modify one of the fields, click the button with the update, refresh my actual table on SQL Server and the data is still the same. Changing the combobox value and changing it back also repopulates the original data.

 

[cdognight]

I put a breakpoint on my Update line in the button handler and created a watch for the datatable "myDataSet.UserInputs". I start the program and select a Product from the combobox. The appropriate data comes up in all my textbox fields. In the textbox associated to the "qty" field, I change the data from "3" to "22", and hit my button that runs the Update. While at the breakpoint, and before the Update is run, I branch out my watch on the datatable object and the value for myDataSet.UserInputs.Results.(0).qty = "22", however, the value for myDataSet.UserInputs.Results.(0).RowState is "Unchanged {2}", which I assume would need to be at "Changed" (or whatever) to flag an update to be performed.

This is where I'm confused. I don't understand the relationship between the dataTable created by my TableAdapter's query and the actual MSSQL data table. Apparently, according to the watch results, the value for "qty" is being changed in the datatable. Why does VB flag its status as unchanged, and what do I need to do to get the changed data written back to the actual table?

 

[cdognight]

After some more reading, I found a user who was having a problem with the RowState remaining "Unchanged" after a form field edit, and their resolution was to add...

NameOfBindingSource.EndEdit()

...before the update command, so I added that to my button handler and now have this:

Private Sub btnConfigure_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfigure.Click

        UserInputsBindingSource.EndEdit()
        UserInputsTableAdapter.Update(myDataSet.UserInputs)

End Sub

... which apparently does the trick (whether it's the preferred approach or not), because my RowState now shows as "Modified" at the Update breakpoint.

Now, the Update method, which is actually trying to update since RowState = Modified, is returning the following error:

"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

When I look in the vb code behind the DataSet designer (which, no, I haven't modified manually), I'm seeing a SqlCommand object with CommandText strings associated to the tableadapter for selects, inserts, and deletes, but nothing for updates. Is it possible the DataSet designer didn't create proper Update methods when configuring? I'm lost.

 

[cdognight]

OK, after some more reading on the exception that was returned, it turns out that the tableadapter can have a hard time creating the INSERT, DELETE, and UPDATE commands necessary for doing edits if the table does not have a primary key set, which I did not. After setting a primary key, then deleting and recreating the tableadapter, UPDATEs work fine.