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)
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
You imply that you want to learn but you have straight-up ignored what I told you elsewhere. told you that JobSheetDataSet.JobInfo has a method to create a typed DataRow and I told you what it would likely be called. Have you looked for that method? Once you've got that typed DataRow, as I also told you before, you'll find that it has a property for each column, so you don't have to use magic strings with the Item property. Finally, the DataTable itself has a method to add that row, so you won't use the Rows collection for that.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
It's also worth noting that typed DataSet classes inherit the equivalent standard, untyped DataSet classes, i.e. the typed DataSet, DataTables and DataRow classes all inherit the standard DataSet, DataTable and DataRow classes. That means that, to know what is specific to a typed DataSet and its related classes, you simply have to compare them to the standard classes, which is easy enough to do.

The typed DataSet and related classes are generated on-demand and are specific to your data. That means that the additional members they have over the standard classes will all be specific to your data. For instance, the standard DataSet class has a Tables property that contains all its DataTables. You need to index that collection, either by name or by ordinal, to get the DataTable you want. A typed DataSet still has that, because it inherits the standard DataSet class, but it also has a property for each DataTable and those properties will be named after the tables in your database. This means that you get full Intellisense and compiler support to find the DataTable you need and ensure that an incorrect name doesn't sneak through and cause a run-time exception. The typed DataRow classes have similar properties for each column.

The table adapters are slightly different. Rather than inherit a data adapter, they wrap one, i.e. there is a reference to a data adapter object stored inside each table adapter. The reason for this is to be able to store multiple queries. The SelectCommand of that data adapter will contain a "SELECT *" query by default and that will be executed when you call Fill but you can add more queries too, and each of them will have its own method that executes it, e.g. a query "SELECT * FROM MyTable WHERE ParentId = @ParentId" would (should) be executed by calling a method named FillByParentId. Calling Update on the table adapter will, internally, call Update on the data adapter.
 

Steve_A

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

I do not think I've ignored what you are saying! In fact, I've read your replies over and over for two weeks trying to put the puzzle together in my mind.

I believe I have about half of the information I need, with no good references for instruction. Safari Books offers me thousands of books. Why is there not a good reference for typed datasets. Every example I've looked at uses sql statements with data adapters, which I hope is considered untyped.

Questions:
1. Do I instantiate the dataset?
2. Is my use of table adapter typed or untyped?
3. Do I need data adapters?
4. "to know what is specific to a typed DataSet and its related classes, you simply have to compare them to the standard classes, which is easy enough to do." Do you have an article on this?

Your initial reply on typed datasets was:
"A typed DataSet is just like an untyped DataSet - it inherits the same classes - but has some extra members that are specific to your data. This makes writing code easier as you get help from Intellisense and compile-time checking of names, types,etc. A table adapter is just a wrapper for a data adapter that is customised for your data too. If you follow the CodeBank link in my signature below, you'll find one or two threads that deal with data access that may help."

I never found an example in the codebank you referenced.

Thank you for your patience.
 

Steve_A

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

I'm a little discouraged here. You should know that I am not a professional developer. I work in the manufacturing engineering field supporting and enhancing CAD tools and other information. Obviously I have asked too many questions and worn out my welcome on this forum. I do not think my questions are unreasonable. I think we are not connecting well, speaking perhaps different languages or something, to which you have interpreted as me just asking for answers instead of learning. THIS IS HARDLY THE CASE!

I think you should have one of your colleagues review your posts in answer to my questions.

If you like, if I am such a burden to you, I am happy to leave the forum.

I do thank you for what you have tried to answer regarding this post.
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
In this example: Update data by using a TableAdapter - Visual Studio

This does not work:
Me.Validate()
Me.BindingSource1.EndEdit()
ta_JobInfo.Update(JobSheetDataSet.JobInfo)
JobSheetDataSetTableAdapters.JobInfoTableAdapter.Update(JobSheetDataSet.JobInfo)


I believe this is 100% of my question!!!

The xsd file is a typed dataset!

The problem is here: JobSheetDataSetTableAdapters.JobInfoTableAdapter.Update, all underlined in red.

Also prior to doing this I attempt to fill it.

JobSheetDataSetTableAdapters.JobInfoTableAdapter.Fill(JobSheetDataSet.JobInfo)

And again, the problem is here: JobSheetDataSetTableAdapters.JobInfoTableAdapter.Fill, all underlined in red.

If I instantiate the table adapter instead, it works, except that you have mentioned that I am no longer using a typed dataset, and the data does not update either.

The controls contained in the xsd file are the ones I am using, so as I understand tings I an referring to the typed dataset.

I believe this question is very straight forward. It has been my question all along. jcm does not care to answer this, and believes I need to learn. I do not know what he thinks I am trying to do?

Perhaps someone else might be able to help.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
Of course that line of code doesn't work. I have already told you elsewhere that you DO have to create an instance of a table adapter class, just like any other class. What you're showing there is like trying to set the Text property of the TextBox class rather than a TextBox instance. I have NEVER told you that creating a table adapter instance is not using a typed DataSet. Go back and read again. I don't recall exactly what I was referring to at the time but, if I remember correctly, the code in question contained no table adapters. It was all referring to tables and columns by String names instead of by properties.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
I just went back and had a look and, sure enough, there were no table adapters involved. This is part of it:
VB.NET:
' Table Adapter Update
sqlStr = "SELECT * FROM dbo.JobInfo"
da = New SqlDataAdapter(sqlStr, sqlCn)
da.Update(ds)
So the comment says "table adapter" but the code creates a data adapter. Do you not understand the difference yet, despite my having explained it in post #3 of this very thread.
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
You are looking at my code from before this was all applied a week ago. The code is at the top of this post, and below:

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)

Is this not an instance of the table adapter class?
VB.NET:
Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
You are looking at my code from before this was all applied a week ago.
Of course I was, because that was when I told you that you weren't using a typed DataSet. Lo and behold, you're STILL not using a typed DataSet properly, despite my having told you multiple times. I've told you more than once that your typed DataTable has a method that creates a typed DataRow and that typed DataRow has properties for each column and that the DataTable has a method to add the row and you have simply ignored that advice. That may or may not be related to your specific issue but it might be, so you should get that right first. I'm afraid I'm done with this thread and the one elsewhere. When advice is repeatedly ignored, it doesn't inspire me to provide more. You may have your reasons, intended or otherwise, but I'm not required to continue so I'll leave you to get help from others if they are up for it.
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
jcm: Perhaps you should ignore my posts on this. I just am not getting what you're saying. I mean no disrespect, but I think this is our problem. You are feeling that I am ignoring you, and at this point I have no idea what you are talking about.
The fact is, I am not understanding how to code typed datasets because at this point everything I have read on it used untyped examples. I thought I was properly instantiating the table adapter. Obviously I have not.
I am happy if you have an article referenced for coding typed datasets. I am happy to read and learn. I have a Safari membership and refereced over 20 books last week, as well as Microsofts website and come other articles. It just does not exist.

Perhaps it is so simple I'm just not seeing it, but I am not seeing it at all.

Thanks for all of your great help, I'm sure it will all add up eventually.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
I already had unsubscribed from your thread here and elsewhere but now you've piqued my interest again. Honestly, sometimes I don't understand what it is that people don't understand. I think the issue is that people think they're reading the words I (or others) write but they're really not. They're looking at the block of text but they're not really reading the sentences it contains because they are still looking at their problem as a whole and not breaking it down into parts and dealing with each part individually. For instance, take this line of code:
VB.NET:
Dim JobInfoRow As DataRow = JobSheetDataSet.JobInfo.NewRow
I told you days before you posted that here that you should not be calling that NewRow method because that is for an untyped DataTable rather than a typed DataTable. I told you that your typed DataTable, i.e. JobSheetDataSet.JobInfo has a method that will create a new typed DataRow and that you should be calling that instead, yet I see no indication that you have made any effort to do so. Maybe you have made such effort, but I don't see it. If you were to delete the .NewRow from the end of that line of code and then type a dot, Intellisense would show you a list of all the members available to you at that point and it would include the method I'm talking about. I even told you what it's name should likely be, although I'm not 100% sure because those names are generated specifically for your project and based on your data, which is the whole point of a typed DataSet. Here's a screenshot from a project I just whipped up with a typed DataSet based on an Access database that I named similarly to yours:

4459


As you can see, Intellisense is showing the NewRow method returning an untyped DataRow as well as the NewJobInfoRow method returning a typed DataRow, i.e. an instance of the JobInfoRow class that inherits DataRow and was generated by the Data Source wizard to match the schema of your specific database. What exactly is hard to understand about that, given that I told you what method not to call and which one to call instead? If you call that method, as I told you to days ago, then you will get an object of the appropriate type. You can then set its fields via properties named after each of the table columns rather than using a magic string and the Item property, i.e. instead of this:
VB.NET:
With JobInfoRow
    .Item("JobNo") = JobNo
    .Item("ProjectItem") = ProjectItem
you would have something like this:
VB.NET:
With JobInfoRow
    .JobNo = JobNo
    .ProjectItem = ProjectItem
Etc.
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
Is the table adapter no longer required?

I guess what I am really asking is, how is the dataset and data adapter connected to the properties class?

VB.NET:
        Dim JobInfoRow As DataRow = JobSheetDataSet.JobInfo.NewRow

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

        Dim JobInfo1 As New JobInfo
        With JobInfo1
            .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

        Dim row1 = JobSheetDataSet.JobInfo.NewJobInfoRow
        ta_JobInfo.Update(JobSheetDataSet.JobInfo)

VB.NET:
Public Class JobInfo
    ' Fields
    Private _jobno As String
    Private _projectitem As String
    Private _drawingno As String
    Private _started As Date
    Private _completed As Date
    Private _toprod As Date
    Private _engineer As String
    Private _designer As String
    Private _esthrs As Decimal
    Private _checker As String
    Private _engineeringjobnotes As String
    Private _shipto As String
    Private _shipdate As Date
    Private _fmldrawingno As String

    ' Properties
    Public Property JobNo() As String
        Get
            Return _jobno

        End Get
        Set(value As String)
            _jobno = value

        End Set

    End Property

    Public Property ProjectItem() As String
        Get
            Return _projectitem

        End Get
        Set(value As String)
            _projectitem = value

        End Set

    End Property

    Public Property DrawingNo() As String
        Get
            Return _drawingno

        End Get
        Set(value As String)
            _drawingno = value

        End Set

    End Property

    Public Property Started() As Date
        Get
            Return _started

        End Get
        Set(value As Date)
            _started = value

        End Set

    End Property

    Public Property Completed() As Date
        Get
            Return _completed

        End Get
        Set(value As Date)
            _completed = value

        End Set

    End Property

    Public Property ToProd() As Date
        Get
            Return _toprod

        End Get
        Set(value As Date)
            _toprod = value

        End Set

    End Property

    Public Property Engineer() As String
        Get
            Return _engineer

        End Get
        Set(value As String)
            _engineer = value

        End Set

    End Property

    Public Property Designer() As String
        Get
            Return _designer

        End Get
        Set(value As String)
            _designer = value

        End Set

    End Property

    Public Property EstHrs() As Decimal
        Get
            Return _esthrs

        End Get
        Set(value As Decimal)
            _esthrs = value

        End Set

    End Property

    Public Property Checker() As String
        Get
            Return _checker

        End Get
        Set(value As String)
            _checker = value

        End Set

    End Property

    Public Property EngineeringJobNotes() As String
        Get
            Return _engineeringjobnotes

        End Get
        Set(value As String)
            _engineeringjobnotes = value

        End Set

    End Property

    Public Property ShipTo() As String
        Get
            Return _shipto

        End Get
        Set(value As String)
            _shipto = value

        End Set

    End Property

    Public Property ShipDate() As Date
        Get
            Return _shipdate

        End Get
        Set(value As Date)
            _shipdate = value

        End Set

    End Property

    Public Property FMLDrawingNo() As String
        Get
            Return _fmldrawingno

        End Get
        Set(value As String)
            _fmldrawingno = value

        End Set

    End Property

End Class
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
Is the table adapter no longer required?
That you would ask that question is more proof that you have really read what I've posted, even if your eyes have passed over the words. I've told you more than once that you need to create an instance of the appropriate table adapter class. Why would that suddenly not be the case? It also suggests that you don;t know how ADO.NET works. How would you get data to and from the database with a table adapter or a data adapter and, given that a data adapter is NOT using a typed DataSet, why would you use one directly? That means that using a table adapter is the only option for using a typed DataSet and moving data between it and the database.
I guess what I am really asking is, how is the dataset and data adapter connected to the properties class?
Maybe some of the confusion comes from the fact that I'm referring to the class and an instance of the class in very similar ways, but that is quite common and not limited to typed DataSets. When you use the wizard, it generates a bunch of classes. The DataSet is a class, each DataTable is a class, there's a DataRow class for each DataTable and also a table adapter class for each DataTable. Those classes are just like any other, e.g. Form or TextBox, i.e. you need to create instances of them to use them in code. You create an instance of the DataSet class and it has properties for each DataTable, where each of those properties contains an instance of the appropriate DataTable class. In order to populate one of those DataTables from the database, you need to create an instance of the appropriate table adapter class and call its Fill method. Similarly, you call Update on a table adapter to save changes back to the database.

None of this is different in pattern at all from using an untyped DataSet. All the classes still play exactly the same roles, with the table adapter doing exactly what a data adapter does for an untyped DataTable, right down to the Fill and Update methods having the same names. As I have also told you several times, there is actually a data adapter inside each table adapter. When you call Fill on a table adapter, it calls Fill on its internal data adapter. The difference is that you don't have to write the connection string in the connection object or the SQL code in the command object at that point because the wizard has already generated those at design time, which is the whole point of a typed DataSet.

The idea is that a typed DataSet has all the same functionality as an untyped DataSet but it's easier/safer to use because it is extended to work with your data specifically. Every typed DataSet is different because every database that one is generated from is different. That's why I will refer to one specific typed DataSet class as "a DataSet": because it is one of many different typed DataSet classes. You'll also find that "a typed DataSet" is used to refer to all the generated classes as a group. The pattern is always the same though: the DataSet contains the DataTables, the DataTables contain the DataRows, the DataRows contain the data and the table adapters move data back and forth between the DataSet and the database.
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
Is this not an instance of the table adapter class?
VB.NET:
Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
        ta_JobInfo.Fill(JobSheetDataSet.JobInfo)
I just don't know what to do with it. If the property classes are now the way to get the data, I am not seeing how this connects with the table adapter.
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
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!
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
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)
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,100
Location
Sydney, Australia
Programming Experience
10+
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.
 

Steve_A

Member
Joined
Jul 9, 2019
Messages
16
Location
Minneapolis, MN
Programming Experience
5-10
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:
Top Bottom