none
Updating a dataset using the Data Source system? RRS feed

  • Question

  • so likely I've started down the wrong path, seemed so simple.

    trying to update a row in a table after changing it in code. no form. 

    i have the table built with one line record, all that is needed. 

    i'm trying to set the xLocked field to true and save it back to the database. the code can pull the record out but won't save it back. still trying to learn this new way. last wrote data access programs with vb6 and access.. 

    Function... 

        'get the next available number in a letter series and locks that series. 

            Dim Rlock As String = KeyLetter & "Locked"
            Dim da As Object = Nothing
            Dim NxNumberData As DataTable = Nothing
            Dim NxNumberTableAdp As Object = New ImagingSystemDataSetTableAdapters.tblNextNumberTableAdapter

                 NxNumberData = NxNumberTableAdp.Getdata()

            If NxNumberData.Rows.Count = 1 Then
                Dim dat() As Object = NxNumberData(0).ItemArray
                Dim NxNum As String = NxNumberData(0).Item(KeyLetter)
                NxNumberData(0).Item(Rlock) = vbTrue
                Dim dat2() = NxNumberData(0).ItemArray

                NxNumberData.AcceptChanges()

             End If


            Return ""

        End Function


    • Edited by jb 1010 Tuesday, August 9, 2016 3:30 PM
    Tuesday, August 9, 2016 3:30 PM

Answers

  • I think the difference between .GetData() and .Fill() is that GetData() returns a new DataTable, whereas .Fill() puts the data into an existing DataSet (which could fill multiple DataTables in that DataSet). Again, since I don't use TableAdapters, I only know what I've read (and what I've played around with occasionally to answer questions).

    I should have just gone ahead and given you the links to some relevant DataAccess and DataSet blog posts. Even thought they are *not* in VB, perhaps they will help.

    First, check out my 3-part series on Data Access for some basic ideas. I'm using a SQL database, but the same would apply to other databases (except you'd use OleDb classes instead of Sql classes):

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post has a more complete example class, but it gets into using anonymous delegates and may be too much for a beginner (which, to be honest, I don't use the anonymous delegates anymore, but I used to on previous projects several years ago). However, even if you don't want to use the anonymous delegate approach, the more complete example class should show you how to save data, which the examples in the first two posts don't do. Note that I do *not* use TableAdapters.

    Those 3 posts are old, I wrote them back in 2009. They are still relevant, but I needed to add two things: implementing IDisposable and using TransactionScope for transactions. So, here's the latest post in my Data Access series:

    http://geek-goddess-bonnie.blogspot.com/2015/06/dataaccess-revisiting-yet-again.html

    Several of my readers have gotten back to me with some questions about this code and I realize that I'm still missing a few important items, such as DataAdapter.TableMappings!! So, I still have a few additions for this blog series, but I haven't been able to get to it yet. It's on my to-do list, though!  ;)

    Secondly, there's a post I have about creating Typed DataSets without TableAdapters being generated.I know that DataSets have gotten a "bum rap" lately since lots of people prefer using Entity Framework, LINQ to SQL and similar types of ORMs. But I find Typed DataSets to be quite simple to use (as long as you avoid the TableAdapters):

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    You'll also want to be aware of this little "gotcha" that sometimes happens with the MSDataSetGenerator:

    http://geek-goddess-bonnie.blogspot.com/2012/08/msdatasetgenerator-gone-wild.html

    I know that's a lot to digest, but I think it might help you a bit. My old head has had to keep on learning too ... keeps us young, right? ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, August 10, 2016 3:35 PM

All replies

  • AcceptChanges() is not what you want to use ... that basically makes it so that the DataSet thinks that it has no changes.

    What you want to use is the TableAdapter's .Update() command ... give that a try.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 9, 2016 6:13 PM
  • well replaced AcceptChanges() with NxNumberTableAdp.update()... and get the following. 

    Additional information: Overload resolution failed because no accessible 'update' accepts this number of arguments.

    If there is a handler for this exception, the program may be safely continued.

    so ? there is no update method in the data source table adapter? 

    just been out of it too long I'm guessing. error is way to cryptic for me anymore. Heading for the book store to see what I can find. doesn't seem to be any tutorials in the doc's, least haven't found any yet. 

    Tuesday, August 9, 2016 6:54 PM
  • Personally, I don't like TableAdapters (I use the DataAdapters instead), but TableAdapters are supposed to be easier to get started with. Also, I'm a C# person, so my VB skills are minimal (I'll typically get the VB syntax wrong).

    At any rate, remember that Intellisense is your friend! If you started typing NxNumberTableAdp.Update( as soon as you hit the (, you should be shown, via Intellisense, what the parameters are for the Update method. In this case, you probably just want to send it the entire DataSet (easiest way to do it, I think).

    Try it, see if you get the .Update() parameters in Intellisense. You'll want to end up having your statement look like this:

    NxNumberTableAdp.Update(NxNumberData)

    You might find some good tutorials on Microsoft's free Virtual Academy:

    https://mva.microsoft.com/

    I might have some interesting tidbits on my blog (link below in my sig), although it's all in C#. I have several blog posts about DataAccess that may or may not be helpful ... you can search my blog and see what you can find. I'd usually recommend specific posts in my replies here on the Forums, but I'm not sure which posts might benefit you. So, you could browse if you wish ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 9, 2016 8:06 PM
  • thanks for the insights. will take a look at your blog.

    it's seems some of the errors I was receiving were caused by the fact the table in question didn't have a primary key. after some head shaking I added a primary key to the table and stopped  the run time errors. it still won't update but the crashing stopped.

    if I understand correctly, now, the Getdata() returns a 'read-only' dataset? I have to use the Fill() to get one I can save. although I may have this wrong too. new book sitting on my desk to read though.  

    yes, still getting used to the Intellisense helper. I don't remember but I don't think I saw the Update method popup when I added that code, now I do. something about the wizard not adding the method if there wasn't a primary key in the table.... 

    getting harder every year to add more information into this old head. 

    jb..

    Wednesday, August 10, 2016 12:32 PM
  • I think the difference between .GetData() and .Fill() is that GetData() returns a new DataTable, whereas .Fill() puts the data into an existing DataSet (which could fill multiple DataTables in that DataSet). Again, since I don't use TableAdapters, I only know what I've read (and what I've played around with occasionally to answer questions).

    I should have just gone ahead and given you the links to some relevant DataAccess and DataSet blog posts. Even thought they are *not* in VB, perhaps they will help.

    First, check out my 3-part series on Data Access for some basic ideas. I'm using a SQL database, but the same would apply to other databases (except you'd use OleDb classes instead of Sql classes):

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post has a more complete example class, but it gets into using anonymous delegates and may be too much for a beginner (which, to be honest, I don't use the anonymous delegates anymore, but I used to on previous projects several years ago). However, even if you don't want to use the anonymous delegate approach, the more complete example class should show you how to save data, which the examples in the first two posts don't do. Note that I do *not* use TableAdapters.

    Those 3 posts are old, I wrote them back in 2009. They are still relevant, but I needed to add two things: implementing IDisposable and using TransactionScope for transactions. So, here's the latest post in my Data Access series:

    http://geek-goddess-bonnie.blogspot.com/2015/06/dataaccess-revisiting-yet-again.html

    Several of my readers have gotten back to me with some questions about this code and I realize that I'm still missing a few important items, such as DataAdapter.TableMappings!! So, I still have a few additions for this blog series, but I haven't been able to get to it yet. It's on my to-do list, though!  ;)

    Secondly, there's a post I have about creating Typed DataSets without TableAdapters being generated.I know that DataSets have gotten a "bum rap" lately since lots of people prefer using Entity Framework, LINQ to SQL and similar types of ORMs. But I find Typed DataSets to be quite simple to use (as long as you avoid the TableAdapters):

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    You'll also want to be aware of this little "gotcha" that sometimes happens with the MSDataSetGenerator:

    http://geek-goddess-bonnie.blogspot.com/2012/08/msdatasetgenerator-gone-wild.html

    I know that's a lot to digest, but I think it might help you a bit. My old head has had to keep on learning too ... keeps us young, right? ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, August 10, 2016 3:35 PM