VS 2015 DataAdapter updating all but the first row-VBForums
Results 1 to 22 of 22

Thread: DataAdapter updating all but the first row

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    DataAdapter updating all but the first row

    I have a form that loads a database (Microsoft Access) of about 100 rows, and 30 columns and populates it into a DataGridView. The form then displays the information of each row in the DGV in textboxes. 4 rows of the DGV are displayed per page in textboxes (I navigate through the textboxes and therefore the DGV by way of buttons). Everything goes well, but I am having some trouble updating the first row of each page.
    So I have a page:
    1 Row
    2 Row
    3 Row
    4 Row

    Next page:
    5 Row
    6 Row
    7 Row
    8 Row

    All but the 1st row and 5th row is updating.


    Throughout the code, I detect any changes to my textboxes and apply it to the DGV. I then save the row number into a list so that I can reference which rows need to be updated.
    Code:
    If TextBox1.Text <> DataGridView.Rows(PageRows).Cells("Col3").Value Then
            DataGridView.Rows(PageRows).Cells("Col3").Value = TextBox1.Text
            SaveList.Add(dgvDataBase.Rows(PageRows).Cells("ID").Value - 1)
    End If

    So as you can tell, this list, "SaveList" contains all the rows that need to be updated in the database. The below code saves everything successfully except for the first row. There are no errors, and I get the "Data Updated!" message box. In order to see whether or not the first row was even being looked at, I added as messagebox at the end of the For/Next loop, and it successfully displayed the correct info.
    Code:
    Dim CommandBuilder As New OleDb.OleDbCommandBuilder(DataAdapter)
    
    For Each UpdateRow In SaveList
            DataTable.Rows(UpdateRow).Item("FACID") = dgvDataBase.Rows(UpdateRow).Cells("Col1").Value
            DataTable.Rows(UpdateRow).Item("BUILDINGNAME") = dgvDataBase.Rows(UpdateRow).Cells("Col2").Value
            DataTable.Rows(UpdateRow).Item("MGMT") = dgvDataBase.Rows(UpdateRow).Cells("Col3").Value
            DataTable.Rows(UpdateRow).Item("CITY") = dgvDataBase.Rows(UpdateRow).Cells("Col4").Value
            DataTable.Rows(UpdateRow).Item("PM") = dgvDataBase.Rows(UpdateRow).Cells("Col5").Value
            DataTable.Rows(UpdateRow).Item("TYPE") = dgvDataBase.Rows(UpdateRow).Cells("Col6").Value
            DataTable.Rows(UpdateRow).Item("NOTES") = dgvDataBase.Rows(UpdateRow).Cells("Col7").Value
            DataTable.Rows(UpdateRow).Item("EMPLOYEE") = dgvDataBase.Rows(UpdateRow).Cells("Col8").Value
            DataTable.Rows(UpdateRow).Item("RESIDENT") = dgvDataBase.Rows(UpdateRow).Cells("Col9").Value
            MessageBox.Show(DataTable.Rows(UpdateRow).Item("Col1"))
    Next
    Try
            DataAdapter.Update(DataTable)
            MessageBox.Show("Data Updated!")
    Catch ex As Exception
            MessageBox.Show("Error")
    End Try
    Last edited by lolikols; Dec 8th, 2017 at 10:12 AM.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    31,328

    Re: DataAdapter updating all but the first row

    You need to look at a bit more than that. The simplest thing is that the Update command returns the number of rows impacted. You could look at that number to see if it is right, but that's a bit pointless. Better still would be to put a breakpoint on the Update line. All the dataadapter does is process either UPDATE, INSERT, or DELETE commands on each row based on the RowState of the rows. Therefore, if the first row is not updating, then when you hit the breakpoint, you can look at the rows in the datatable. Specifically, look at the RowState property of the row. I believe the state should be Modified (Added or Deleted don't seem like they apply here), so if the state isn't Modified, then the problem has nothing to do with the Update, cause it's working right. In that case, it would be that, for some reason, the first row is not being changed.
    My usual boring signature: Nothing

  3. #3
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    Was the binding example too hard to follow? You seem to be driving a Pinto while you have a Ferrari in the garage.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    31,328

    Re: DataAdapter updating all but the first row

    Quote Originally Posted by kpmc View Post
    Was the binding example too hard to follow? You seem to be driving a Pinto while you have a Ferrari in the garage.
    Oh come now. For one thing, a pinto added a certain excitement to life....you also have to be old enough to remember them.
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    Re: DataAdapter updating all but the first row

    Shaggy Hiker, thanks! I'll take a deeper look!

    kpmc, You have great memory! I'm still pretty new to coding - I'm trying to teach myself as I go as a hobby of mine during the downtime at work. Coding is really fun and it's a really nice time sink during my downtime. I did this:
    Code:
            BindingSource.DataSource = DataTable
            DataGridView.DataSource = BindingSource
            DataTable.DefaultView.RowFilter = "Col1 = '" & FilterView & "'" where FilterView is the information that I'm filtering.
    So from there, I've displayed information from the DataGridView:
    Code:
    TextBox1.Text = DataGridView.Rows(PageRows).Cells("Col1").Value
    TextBox2.Text = DataGridView.Rows(PageRows + 1).Cells("Col1").Value
    TextBox3.Text = DataGridView.Rows(PageRows + 2).Cells("Col1").Value
    TextBox4.Text = DataGridView.Rows(PageRows + 3).Cells("Col1").Value
    but I wasn't sure how to update the rows in the DataTable because I only have 4 rows represented per page, and I didn't want to ask you guys to basically teach me how to do it. I did try to Google and teach myself, but nothing I could find either applied to what I was doing, or was a little too complicated for me to understand.

  6. #6
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    kpmc, You have great memory! I'm still pretty new to coding - I'm trying to teach myself as I go as a hobby of mine during the downtime at work. Coding is really fun and it's a really nice time sink during my downtime. I did this:
    I surly understand this. We are all learning everyday.
    My opinion is that you are learning to do it wrong though, but that is only my opinion.

  7. #7
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    Oh come now. For one thing, a pinto added a certain excitement to life
    If by excitement you mean never knowing if you're going to explode on your way to the supermarket... lol

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    Re: DataAdapter updating all but the first row

    Well, shoot. I suppose I'll go back and Google some more. If you want to though...you can totally show me how it would work in this situation. :x

  9. #9
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    Having a bindingsource is kind of like having a butler. If you could cast a datatable into a person he/she would walk around your kitchen eating everything and leaving a big mess. Now, add in a bindingsource, now you have this thing that tastes your food for you and makes sure that it's not poison, tells you when to swallow or flush... cleans up your mess, etc..

    I dont have time to really go over all things. Later on I could work with you if you'd like.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    Re: DataAdapter updating all but the first row

    Yes please! I would appreciate that greatly! I don't know how you would want to do it: do it here on this thread, over PMs, over email, I'm open to anything - I just want to learn .

  11. #11
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    Elaborating a bit more, after eating all your food and leaving your kitchen in a disaster, he will also invite his friends "DataGridGuy", and "ComboMan" and have a massive party in your livingroom rocking ACDC throwing beercans and hotpocket wrappers all over. Where with the butler they are going to sit on the couch and behave listening to a symphony in a perfectly orchestrated masterpiece.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    Re: DataAdapter updating all but the first row

    Haha, thanks for the laugh! I'll do some more research!

  13. #13
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    Now when you think bindingsource, you will imagine this
    https://www.youtube.com/watch?v=EJC-...=RDEJC-_j3SnXk

  14. #14
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,350

    Re: DataAdapter updating all but the first row

    Even though I know things might change with the binding... I want to revisit the code in post #1 for a sec... and mention there's a better way of tracking the changed rows in a datatable than in your own collection...
    When you update the data from the textboxes back to the datatable (that's bound to the grid)... just update it... don't worry about tracking anything. ADO.NET is going to do that for you. When you're ready to then update the database use .GetChanges on the datatable to get an array (I think it's an array, it might actually be a datatable) that has all of the changed rows. O.O They're right there, ready for you. From there, you can loop through and save each one, or use an .Update on the adaptor. Note - GetChanges does take an optional parameter so you can tell it what changes you want... you can ask for new rows, changed rows, deleted rows, or any combination of the three. The default is all changes. If you do the looping yourself (ie, don't use the .Update method) then you'll want to call. AcceptChanges on the datatable when you're done to reset all rows back to Unchanged state.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,350

    Re: DataAdapter updating all but the first row

    Quote Originally Posted by kpmc View Post
    Now when you think bindingsource, you will imagine this
    https://www.youtube.com/watch?v=EJC-...=RDEJC-_j3SnXk
    Thanks... I think I'm about to do down an André Rieu rabbit hole... I think he's a fantastic conductor. I've seen some his conducting performances before... Oh look Bolero!

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    I've seen some his conducting performances before..
    Very jealous.

  17. #17
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,350

    Re: DataAdapter updating all but the first row

    Quote Originally Posted by kpmc View Post
    Very jealous.
    I should note, not in person... I wish though.... but let's just say I have an unhealthy relationship with PBS at certain times of the year... this past summer they broadcast his show from Amsterdam Arena (which is now playing in the background) ... Rieu and .... Gustavo Dudamel (Los Angeles Philharmonic) I think are just phenomenal to watch and listen to.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    I agree! there are 2 reasons I choose to live in this world:
    0: Cow (steak, hamburgers, roast)
    1: André Rieu

  19. #19
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,286

    Re: DataAdapter updating all but the first row

    I like some of the ideas in the thread and don't like others.

    What I really don't like is definitively stating "never use DataAdapter" or "always use DataAdapter".

    DataAdapter and TableAdapter are tools used to write a specific kind of application. This application type is generally DataGridView-heavy, but you can use other controls with it. If you are writing that kind of application, they come with a lot of complicated features you don't want to write yourself. If you need those features, this is a great boon. The types are extensible in many ways, so you can augment those features to behave like you want.

    But DataAdapter and TableAdapter aren't magic. They are built using the lower-level tools like IDbCommand and IDataReader. Everything they can do, you can do. Better. They are generalized, and can't make assumptions. You know your project, and CAN make assumptions. So you can tweak your SQL queries in ways they can't, and you don't have to carry the burden of features you aren't using in those queries. And if something is broken, you KNOW it is your fault and can quickly address it.

    But if you need a lot of TableAdapter features, you'll waste a lot of time re-implementing them if you use the low-level types yourself. And if you're quickly prototyping an idea, a TableAdapter might get you there faster. Likewise, when you have large-scale performance concerns, you might end up making so many tweaks to a TableAdapter it rivals the code you might've written against the low-level types in complexity.

    It's also often useful to use the low-level types to debug what's going wrong with a TableAdapter. If you get "bad results" with the low-level types, you know you've either got the wrong query or the database schema's not what you expected. But if the TableAdapter results are different than the low-level types, you know you have a misconfigured TableAdapter. This is easier than stumbling through the GUI to find the one small thing that is wrong.

    A good carpenter doesn't learn how to use JUST a nailgun or JUST a hammer. She learns how to use either, and when they are appropriate. She picks the one that is right for the job she is doing.

    A good developer doesn't learn how to use JUST TableAdapters or low-level types. She learns how to use both, and when they are appropriate.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  20. #20
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: DataAdapter updating all but the first row

    I am sort of following you there Sitten. When you refer to the low level types, are you referring to methods/properties available at the Command level? Such as executing queries with params?

  21. #21
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    31,328

    Re: DataAdapter updating all but the first row

    I would count a dataadapter as low level if a tableadapter is high level. The dataadapter is a convenience more than a thing, kind of like the CommandBuilder. You can do what the dataadapter is doing. All you'd have to do is iterate through the rows in the datatable and check the RowState. If it is Added, then perform an INSERT query. If it is Modified, then perform an UPDATE. If Deleted (or perhaps it is Removed), then perform a DELETE. This would become both routine, and error prone, so the DataAdapter wraps up a bunch of boilerplate so that you don't have to write it.

    Technically, the TableAdapter just takes that a bit further....or perhaps a LOT further. Because of this, the TableAdapter is much more of a black box than the DataAdapter. Doing all the things that a DataAdapter does wouldn't be difficult. Doing all the things that a TableAdapter does would be quite a bit more complicated....and you likely wouldn't bother for lots of it. You get it all, and you get it all with little effort, which is what's nice about the TableAdapter, but is also the drawback, since you didn't do it, and don't have to understand it.
    My usual boring signature: Nothing

  22. #22
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,286

    Re: DataAdapter updating all but the first row

    I think the reason BOTH DataAdapter and TableAdapter are "high level" is they change how you talk about the database.

    When I use IDbCommand, I have to think about resultsets. Some commands return a scalar value. Others return rows. I have to decide how to execute the command based on what I expect. When I use IDataReader, I'm thinking about how it's a representation of a database query's resultset rows and I have to convert it to something else before I can use it.

    When I use either TableAdapter or DataAdapter, I'm not thinking that way. Now I'm just thinking of which query I use to fill a DataSet or DataTable. Some of those queries just return an Integer. I'm not doing so much conversion from "database schema" to "VB schema" here if I'm working with a DGV. So since in both cases I'm not thinking about the database schema, they're both "higher level".

    But TableAdapter is "higher" than DataAdapter, because it's even more specific.

    DataAdapter just gives me a DataTable with rows and columns, I have to do a lot of work to tell it which specific types go where and even then I have to work hard to cast them. TableAdapter does the work to set up the columns for me in advance. But that's really just returning "a preconfigured DataAdapter", it's not a lot of extra work.

    A more clear distinction would be an ORM like Entity Framework, where instead of "GetCustomers()" returning a DataTable, it'd return an array of some Customer type. Now we're talking "very high abstraction", because we don't think about database constructs at all!
    Last edited by Sitten Spynne; Dec 8th, 2017 at 04:06 PM.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.