question

RichCraxton-5796 avatar image
0 Votes"
RichCraxton-5796 asked XingyuZhao-MSFT commented

Multiple SQL tables within form, need to update just one

Have a VB form in which there is a main SQL table with 3 other SQL tables linked to get various data. Can't update main table, throws exceptions for the other 3 linked tables. Believe form is trying to update the linked tables as well.

Is there a way to update the main SQL table, without updating the other 3 linked tables?

Thanks in advance for any help

dotnet-visual-basic
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You haven't provided us any code for how you're populating your UI and how your tables are linked together. This is going to determine how you go about solving this problem. Using a dataset, for example, requires the data adapter and relationships to be defined properly. Using EF then the navigation properties need to be right.

0 Votes 0 ·
RichCraxton-5796 avatar image
0 Votes"
RichCraxton-5796 answered XingyuZhao-MSFT commented

Yes - I had looked at the example before this post. Unfortunately, I don't get an option of just update. UpdateAll, UpdateOrder and UpdateOrderOption are the only choices. None of which I want.

Thanks.

@cooldadtx

· 14
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

To help track the conversation, please use the Comment button on answers to reply. This will allow you to mark the response that answers your question for others, once you have it.

The TAM is auto-generated by the designer when you added the datasets to the project. I'm going to assume that after the original designer code was produced there were additional DB changes that aren't reflected. Open up the dataset designer that should still be in your code. In the Toolbox you should see a TableAdapter. Drag and drop that onto the designer and it should kick of the UI to create the table adapter for it. Once you've filled in the details the TA should be set up and I believe you should suddenly have a new UpdateXYZ in your TAM class. You should now be able to update the table.

0 Votes 0 ·

Sorry - missed the reply button. Got it now.

There is only one dataset created - the other tables were added at the same time. Perhaps they should not have been? Did as you suggested, but no new "Update". Same options as before...

Thanks

0 Votes 0 ·

I took a look at how the current designer generates code so I don't know if it lines up with your version. But in the current version the Update method is on the table adapter.

Me.TableAdapterManager.TableIWantToUpdateAdapter.Update(Me.ThermodbDataSet.TableIWantToUpdate)
0 Votes 0 ·
Show more comments

Hi @RichCraxton-5796 ,
Could you share your sample or project on github or onedrive? We will download it and make a test.
Note: please delete any privacy information in the project.
We are waiting for your update.

0 Votes 0 ·
RichCraxton-5796 avatar image
0 Votes"
RichCraxton-5796 answered

Code to get data -->

SELECT condsingle.product_no, condsingle.spec_id, condsingle.issued_date, condsingle.spec_revision_level, condsingle.cable_description, condsingle.insulation_color, condsingle.extruder_die_size, condsingle.tip_size,
condsingle.nominal, condsingle.target, condsingle.minimum, condsingle.max_runout, condsingle.average_minimum_wall, condsingle.minimum_point_wall, condsingle.min_lb, condsingle.max_lb, condsingle.sparker_voltage,
condsingle.process_oil, condsingle.serve, condsingle.conductor_wt_mft, condsingle.insulator_wt_mft, condsingle.print, condsingle.notes, condsingle.note_2, condsingle.note_3, condsingle.note_4, condsingle.note_5,
condsingle.note_6, condsingle.note_7, condsingle.note_8, condsingle.note_9, condsingle.note_10, condsingle.ul, condsingle.csa, condsingle.sae, condsingle.chrysler, condsingle.ford, condsingle.packard, condsingle.other,
condsingle.prod_type, cond_approve.user_name AS ApproveUserName, cond_verify.user_name AS VerifyUserName, cond_ccode.specification AS CcodeSpecification, cond_ccode.die_size AS CcodeDieSize,
cond_compound.description AS CompDesc, cond_compound.obsolete AS CompObsol, condsingle.verified_by AS CondSingleVer, condsingle.approved_by AS CondSingleAppr,
condsingle.compound_code AS CondSingleCompCod, condsingle.conduct_code AS CondSingleCondCod
FROM condsingle INNER JOIN
cond_approve ON condsingle.approved_by = cond_approve.approve_by INNER JOIN
cond_verify ON condsingle.verified_by = cond_verify.verify_by INNER JOIN
cond_ccode ON condsingle.conduct_code = cond_ccode.conduct_code INNER JOIN
cond_compound ON condsingle.compound_code = cond_compound.cp_no
ORDER BY condsingle.spec_id


Where exception occurs -->

 Private Sub CondsingleBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles CondsingleBindingNavigatorSaveItem.Click
     Dim ProContinue As String = "Y"
     ChkData(ProContinue)
     If ProContinue = "Y" Then
         Me.Validate()
         Me.CondsingleBindingSource.EndEdit()
         Me.TableAdapterManager.UpdateAll(Me.ThermodbDataSet)
     End If

 End Sub

Thinking I need a different approach to how the tables are linked together.

Thanks

@cooldadtx


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

So you're using ADO.NET's Dataset to load the data. What exact error are you getting when you try to call UpdateAll?

Assuming that you aren't violating a DB constraint (such as associating a bad approve_by value then the adapter should be able to save the data properly. Can you post the UpdateCommand that you set on your data adapter?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichCraxton-5796 avatar image
0 Votes"
RichCraxton-5796 answered

First - I don't want to UpdateAll - I only want to update Condsingle data table. The other tables are used for displaying information as the user goes thru the data, info changes accordingly.

Error message - TableAdapterManager contains no connection information. Set each TableAdapterManager TableAdapter property to a valid TableAdapter instance.

Can you post the UpdateCommand that you set on your data adapter? --- Not sure where to get this from? Which could be an issue.

Thanks
@cooldadtx

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

Since you're using TableAdapterManager then that tells me your project is using a typed dataset. That means all the code for that type is in your project. You can right click the type and jump to the code to look at it. Under the hood it is going to use a DataAdapter to do the actual saving.

To update a single table use the Update method and pass the table name. Although it shouldn't matter because the adapter is only going to save changes that were actually made.

Me.TableAdapterManager.Update(Me.ThermodbDataSet.TableIWantToUpdate)


The error message indicates that not all the table adapters have been configured. Normally the TAM is just a series of TableAdapters. Each TA, which is also custom generated and in your project, maps to a single table and knows how to save a specific table entry. When the TAM is associated with a connection it should apply the connection to each child TA as well. It's possible your error will go away when you change the above code to save just the table you care about. If not then we can investigate further.

Refer to this example as an example of what that code looks like.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.