Typed DataSets

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
Hello:

I have this very inconsistent code that may be mixing untyped and typed datasets. My goal is to have a typed dataset where I can just say dataset.Update to get any new information or changes.

I have read through several articles and am unable to relate what I am doing to them. I am using a table adapter to update my dataset, have no idea if that is right because every example I look at us using data adapters, which require sql statements. It seems to me that if I already have the dataset available for use, then why would I need a sql statement?

I also would not mind creating an instance of my table adapter rather than referring to it directly, but am concerned this is only for untyped datasets.

My code below also does add rows, because this is the only way I could get it to work. The problem is, the update only happens once this way.

I need to understand the difference between typed and untyped as far as coding is concerned.

Thank you for the help.

VB.NET:
        JobSheetDataSet.CaseSensitive = False

        Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
        ta_JobInfo.Fill(JobSheetDataSet.JobInfo)

        Dim JobInfoRow As DataRow = JobSheetDataSet.JobInfo.NewRow
        With JobInfoRow
            .Item("JobNo") = JobNo
            .Item("ProjectItem") = ProjectItem
            .Item("DrawingNo") = DrawingNo
            .Item("Started") = Started ' date
            .Item("Completed") = Completed ' date
            .Item("ToProd") = Completed ' date
            .Item("Engineer") = Engineer
            .Item("Designer") = Designer
            .Item("EstHrs") = EstHrs ' decimal
            .Item("Checker") = Checker
            .Item("EngineeringJobNotes") = EngineeringJobNotes
            .Item("ShipTo") = ShipTo
            .Item("ShipDate") = ShipDate ' date
            .Item("FMLDrawingNo") = FMLDrawingNo

        End With

        Try
            JobSheetDataSet.JobInfo.Rows.Add(JobInfoRow)

        Catch

        End Try

        ta_JobInfo.Update(JobSheetDataSet.JobInfo)
 
According to this: 13.2 Adding a Row :: Chapter 13. Strongly Typed DataSets :: Part I: ADO.NET Tutorial :: Ado.net :: Programming :: eTutorials.org

This might be right...

VB.NET:
        Try
            Dim row = JobSheetDataSet.JobInfo.NewJobInfoRow
            With row
                .JobNo = JobNo
                .ProjectItem = ProjectItem
                .DrawingNo = DrawingNo
                .Started = Started ' date
                .Completed = Completed ' date
                .ToProd = ToProd ' date
                .Engineer = Engineer
                .Designer = Designer
                .EstHrs = EstHrs ' decimal
                .Checker = Checker
                .EngineeringJobNotes = EngineeringJobNotes
                .ShipTo = ShipTo
                .ShipDate = ShipDate ' date
                .FMLDrawingNo = FMLDrawingNo

            End With

            JobSheetDataSet.JobInfo.AddJobInfoRow(row)

        Catch

        End Try

        Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
        ta_JobInfo.Fill(JobSheetDataSet.JobInfo)
        ta_JobInfo.Update(JobSheetDataSet.JobInfo)

Except that it still does not put the data in the database!
 
Update is a function that returns "The number of rows successfully updated from the DataSet.", so check that number.
Also, this is recommended read How to: Manage Local Data Files in Your Project | Microsoft Docs This the same as first link in jmcilhinney's signature, the one that says "Why is my data not saved to my database?"
 
jcm:
I think I have this working right, if you could agree or disagree. But then again, I also think I don't because the Update is not doing anything. Adding rows works, but Update does not change data.

Thanks.

VB.NET:
        Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
        ta_JobInfo.Fill(JobSheetDataSet.JobInfo)

        Dim row1 As JobSheetDataSet.JobInfoRow = JobSheetDataSet.JobInfo.NewJobInfoRow
        With row1
            .JobNo = JobNo
            .ProjectItem = ProjectItem
            .DrawingNo = DrawingNo
            .Started = Started ' date
            .Completed = Completed ' date
            .ToProd = ToProd ' date
            .Engineer = Engineer
            .Designer = Designer
            .EstHrs = EstHrs ' decimal
            .Checker = Checker
            .EngineeringJobNotes = EngineeringJobNotes
            .ShipTo = ShipTo
            .ShipDate = ShipDate ' date'

            If FMLDrawingNo.Length > 8 Then
                .FMLDrawingNo = "-1"

            Else
                .FMLDrawingNo = FMLDrawingNo

            End If

        End With

        Try
            JobSheetDataSet.JobInfo.Rows.Add(row1)

        Catch

        End Try

        ta_JobInfo.Update(JobSheetDataSet.JobInfo)
 
Adding rows works, but Update does not change data.
And how EXACTLY did you determine that? Have you tested the value returned by the Update call? You wouldn't be the first person to say that their data wasn't being saved when it actually was and they just thought it wasn't because they didn't look for it properly. That's the very reason I added the link to my signature.
 
I changed the data in the database and re-read the code which reads the values from spreadsheets and populates arrays. The data was not changed.

I changed the value of the drawing number in the first record from E7104824 to E7104823. Below is the output after running the code (reading the spredsheet):

18-1863A, BT400 x 71'0", E7104824

But it is not changed back, and is still E7104823 in the database.
 
Last edited:
You keep saying "the database". Which database? If you're using a local data file, e.g. Access MDB or ACCDB or an MDF with SQL Server Express or LocalDB, then there are two databases and one of them gets overwritten regularly by default. Is that the case for you and, if so, are you checking the correct database and at the correct time, because many people don't and it turns out that things are working exactly as they should but they just can't see it.

Also, if you're talking about changing existing data, you're not talking about the code posted here. If you're adding a row to your DataTable then you're inserting a new row into the database when you call Update. If your intention is to update an existing row then you need to Fill the DataTable using the table adapter, get the existing row from the DataTable, set the appropriate property(ies) to the appropriate value(s) and then call Update to save the changes. Are you doing that? If so, show us that code and tell us what value is returned by the call to Update.
 
I thought with typed datasets, update took care of all inserts, updates, deletes automatically based on some articles I was reading. I thought addrow was required for the new row.

What I am hearing now is I need addrow and update just to add the rows, and I have to do old school data checks (similar to untyped datasets with connection strings) to change data in the table, if in fact there are any changes.

Thanks!
 
That might be what you're hearing but it's not what I'm saying. I seriously can't understand how you're making this so difficult. Whether you are using a typed or untyped DataSet, if you want to update existing records then you call Fill on an adapter to retrieve the record(s) into a DataTable, then edit the row(s) as required, then call Update on the adapter to save the changes back to the database. The pattern is EXACTLY the same for a typed or untyped DataSet. The difference is in the specifics, i.e. you use a table adapter for a typed DataSet, which already contains a data adapter and the associated commands and connection, instead of a data adapter that you create and configure yourself, as well as methods and properties specific to your data instead of the more generic ones. The principles don't change though.
 
jcm:

I got it.

VB.NET:
        Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
        ta_JobInfo.Fill(JobSheetDataSet.JobInfo)

        ' This adds row to the database
        Dim row_JobInfo_new As JobSheetDataSet.JobInfoRow = JobSheetDataSet.JobInfo.NewJobInfoRow
        With row_JobInfo_new
            .JobNo = JobNo
            .ProjectItem = ProjectItem
            .DrawingNo = DrawingNo
            .Started = Started ' date
            .Completed = Completed ' date
            .Engineer = Engineer
            .Designer = Designer
            .EstHrs = EstHrs ' decimal
            .Checker = Checker
            .EngineeringJobNotes = EngineeringJobNotes
            .ShipTo = ShipTo
            .ShipDate = ShipDate ' date'

            If FMLDrawingNo.Length > 8 Then
                .FMLDrawingNo = "-1"

            Else
                .FMLDrawingNo = FMLDrawingNo

            End If

        End With

        Try
            JobSheetDataSet.JobInfo.Rows.Add(row_JobInfo_new)
            ta_JobInfo.Update(JobSheetDataSet.JobInfo)

        Catch

        End Try

        ' This updates a specific Row to the database
        Dim row_JobInfo_update As JobSheetDataSet.JobInfoRow = JobSheetDataSet.JobInfo.FindByJobNo(JobNo)
        With row_JobInfo_update
            .JobNo = JobNo
            .ProjectItem = ProjectItem
            .DrawingNo = DrawingNo
            .Started = Started ' date
            .Completed = Completed ' date
            .Engineer = Engineer
            .Designer = Designer
            .EstHrs = EstHrs ' decimal
            .Checker = Checker
            .EngineeringJobNotes = EngineeringJobNotes
            .ShipTo = ShipTo
            .ShipDate = ShipDate ' date'

            If FMLDrawingNo.Length > 8 Then
                .FMLDrawingNo = "-1"

            Else
                .FMLDrawingNo = FMLDrawingNo

            End If

        End With

        Try
            ta_JobInfo.Update(JobSheetDataSet.JobInfo)

        Catch

        End Try
 
I do have one more question, and yes I am probably making this difficult. I do wish there was a good ADO.NET book on this stuff. I just don't see a lot on typed datasets. Maybe looking under typed datasets is the wrong approach if the rules don't change. Most of my experience is with the old school ado connection strings and a lot more code.

I am now populating textboxes with the data.

The code below takes the three tables and pulls them onto a form. The first one works, but the subsequent forms fail.

This line (#23 below) produces System.NullReferenceException: 'Object reference not set to an instance of an object.'
VB.NET:
If Not IsNothing(.PrintPickSheet_new) Then txtPrintPickSheet_new.Text = .PrintPickSheet_new


VB.NET:
    Private Sub UpdateJobNoData(ByVal _JobNo As String)
        Dim row_JobInfo_read As JobSheetDataSet.JobInfoRow = JobSheetDataSet.JobInfo.FindByJobNo(_JobNo)
        With row_JobInfo_read
            If Not IsNothing(.JobNo) Then txtJobNo.Text = .JobNo
            If Not IsNothing(.ProjectItem) Then txtProjectItem.Text = .ProjectItem
            If Not IsNothing(.DrawingNo) Then txtDwgNo.Text = .DrawingNo
            If Not IsNothing(.Started) Then txtStarted.Text = .Started ' date
            If Not IsNothing(.Completed) Then txtCompleted.Text = .Completed ' date
            If Not IsNothing(.Engineer) Then txtEngineer.Text = .Engineer
            If Not IsNothing(.Designer) Then txtDesigner.Text = .Designer
            If Not IsNothing(.EstHrs) Then txtEstHrs.Text = .EstHrs ' decimal
            If Not IsNothing(.Checker) Then txtChecker.Text = .Checker
            If Not IsNothing(.EngineeringJobNotes) Then rtbEngineeringJobNotes.Text = .EngineeringJobNotes
            If Not IsNothing(.ShipTo) Then txtShipTo.Text = .ShipTo
            If Not IsNothing(.ShipDate) Then txtShipDate.Text = .ShipDate ' date
            If Not IsNothing(.FMLDrawingNo) Then txtFMLDrawingNo.Text = .FMLDrawingNo

        End With

        Dim row_JobDetail_read As JobSheetDataSet.JobDetailRow = JobSheetDataSet.JobDetail.FindByJobNo(_JobNo)
        With row_JobDetail_read
            ' If Not IsNothing(.JobNo) Then txtJobNo.Text = .JobNo
            If Not IsNothing(.PrintPickSheet_new) Then txtPrintPickSheet_new.Text = .PrintPickSheet_new
            If Not IsNothing(.CheckJobLetter_new) Then txtCheckJobLetter_new.Text = .CheckJobLetter_new
            If Not IsNothing(.CheckJobLetter_rw) Then txtCheckJobLetter_rw.Text = .CheckJobLetter_rw
            If Not IsNothing(.ObtainSerialNumber_new) Then txtObtainSerialNumber_new.Text = .ObtainSerialNumber_new
            If Not IsNothing(.CheckIssueTracker_new) Then txtCheckIssueTracker_new.Text = .CheckIssueTracker_new
            If Not IsNothing(.CheckInterference_new) Then txtCheckInterference_new.Text = .CheckInterference_new
            If Not IsNothing(.CheckInterference_rw) Then txtCheckInterference_rw.Text = .CheckInterference_rw
            If Not IsNothing(.ProcessingComplete_new) Then txtProcessingComplete_new.Text = .ProcessingComplete_new
            If Not IsNothing(.ProcessingComplete_rw) Then txtProcessingComplete_rw.Text = .ProcessingComplete_rw
            If Not IsNothing(.CheckDescQtyAndRev_new) Then txtCheckDescQtyAndRev_new.Text = .CheckDescQtyAndRev_new
            If Not IsNothing(.CheckDescQtyAndRev_rw) Then txtCheckDescQtyAndRev_rw.Text = .CheckDescQtyAndRev_rw
            If Not IsNothing(.CheckReleasedState_new) Then txtCheckReleasedState_new.Text = .CheckReleasedState_new
            If Not IsNothing(.CheckReleasedState_rw) Then txtCheckReleasedState_rw.Text = .CheckReleasedState_rw
            If Not IsNothing(.CheckMfst_Producability_new) Then txtCheckMfst_Producability_new.Text = .CheckMfst_Producability_new
            If Not IsNothing(.CheckMfst_Producability_rw) Then txtCheckMfst_Producability_rw.Text = .CheckMfst_Producability_rw
            If Not IsNothing(.CheckPrintAndMfst_Peer_new) Then txtCheckPrintAndMfst_Peer_new.Text = .CheckPrintAndMfst_Peer_new
            If Not IsNothing(.CheckPrintAndMfst_Peer_rw) Then txtCheckPrintAndMfst_Peer_rw.Text = .CheckPrintAndMfst_Peer_rw
            If Not IsNothing(.SendPrintAndMfst_ToAdmin_new) Then txtSendPrintAndMfst_ToAdmin_new.Text = .SendPrintAndMfst_ToAdmin_new
            If Not IsNothing(.SendPrintAndMfst_ToAdmin_rw) Then txtSendPrintAndMfst_ToAdmin_rw.Text = .SendPrintAndMfst_ToAdmin_rw
            If Not IsNothing(.CompleteChanges_AfterPE_new) Then txtCompleteChanges_AfterPE_new.Text = .CompleteChanges_AfterPE_new
            If Not IsNothing(.CompleteChanges_AfterPE_rw) Then txtCompleteChanges_AfterPE_rw.Text = .CompleteChanges_AfterPE_rw
            If Not IsNothing(.UpdateFML_new) Then txtUpdateFML_new.Text = .UpdateFML_new
            If Not IsNothing(.UpdateFML_rw) Then txtUpdateFML_rw.Text = .UpdateFML_rw
            If Not IsNothing(.UpdateCustomerCD_new) Then txtUpdateCustomerCD_new.Text = .UpdateCustomerCD_new
            If Not IsNothing(.UpdateCustomerCD_rw) Then txtUpdateCustomerCD_rw.Text = .UpdateCustomerCD_rw
            If Not IsNothing(.UpdatePrintAndMfst_Shop_new) Then txtUpdatePrintAndMfst_new.Text = .UpdatePrintAndMfst_Shop_new
            If Not IsNothing(.UpdatePrintAndMfst_Shop_rw) Then txtUpdatePrintAndMfst_rw.Text = .UpdatePrintAndMfst_Shop_rw

        End With

        Dim row_JobHrs_read As JobSheetDataSet.JobHrsRow = JobSheetDataSet.JobHrs.FindByJobNo(_JobNo)
        With row_JobHrs_read
            ' If Not IsNothing(.JobNo) Then txtJobNo.Text = .JobNo
            If Not IsNothing(.ActualHrs) Then txtActHrs.Text = .ActualHrs ' decimal
            If Not IsNothing(.ReworkHrs) Then txtReworkHrs.Text = .ReworkHrs ' decimal
            If Not IsNothing(.ToProd) Then txtToProd.Text = .ToProd ' date

        End With

    End Sub
 
jmc:

I figured this out too, by adding the following:
VB.NET:
        Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
        ta_JobInfo.Fill(JobSheetDataSet.JobInfo)

My only confusion is, the table adapter is not used anywhere in the code.

Thanks for all the help!
 
Glad you've made progress. I think that you were probably just looking at something the wrong way and that was preventing you seeing anything else beyond that. Not so unusual. Frustrating for me, because it doesn't matter what I say about anything beyond that point if you can't get past that point. Probably frustrating for you too. It's not always easy to do, because we inherently apply what we already in order to understand new things, but it's often important to forget the way you used to do things and just learn how something new works without baggage, because you can end up trying to force this square peg you don't know into the round hole you do.
My only confusion is, the table adapter is not used anywhere in the code.
I'm not 100% sure what you mean there. Are you saying that, after calling Fill, you're not using that table adapter instance anymore? If so then don't worry about it. If you're calling Fill in one method and Update in another for the same DataTable then there's nothing wrong with using different instances of the same table adapter type in each method. Because the system creates all the SQL code and parameters for you, doing so involves very little code of your own. If you were using an untyped DataSet and data adapters, there would be more code involved in creating an instance and thus you may be more inclined to just create one and reuse it. That said, there's nothing necessarily wrong with reusing a table adapter. You can declare a field of that type and assign an instance to it, then use that every time you need to retrieve or save data. You are using the same DataTable each time, after all, so why not use the same table adapter. You can even drag one from the Toolbox onto your form to have the designer declare a field and create the instance automatically, just as happens for controls and other components added in the designer.
 
Back
Top