-
Dec 8th, 2017, 10:05 AM
#1
Thread Starter
Lively Member
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.
-
Dec 8th, 2017, 10:19 AM
#2
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
-
Dec 8th, 2017, 10:19 AM
#3
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.
-
Dec 8th, 2017, 10:24 AM
#4
Re: DataAdapter updating all but the first row
Originally Posted by kpmc
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
-
Dec 8th, 2017, 10:31 AM
#5
Thread Starter
Lively Member
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.
-
Dec 8th, 2017, 10:34 AM
#6
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.
-
Dec 8th, 2017, 10:37 AM
#7
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
-
Dec 8th, 2017, 10:40 AM
#8
Thread Starter
Lively Member
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
-
Dec 8th, 2017, 10:46 AM
#9
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.
-
Dec 8th, 2017, 10:49 AM
#10
Thread Starter
Lively Member
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 .
-
Dec 8th, 2017, 10:57 AM
#11
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.
-
Dec 8th, 2017, 11:00 AM
#12
Thread Starter
Lively Member
Re: DataAdapter updating all but the first row
Haha, thanks for the laugh! I'll do some more research!
-
Dec 8th, 2017, 11:04 AM
#13
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
-
Dec 8th, 2017, 11:11 AM
#14
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
-
Dec 8th, 2017, 11:18 AM
#15
Re: DataAdapter updating all but the first row
Originally Posted by kpmc
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
-
Dec 8th, 2017, 11:20 AM
#16
Re: DataAdapter updating all but the first row
I've seen some his conducting performances before..
Very jealous.
-
Dec 8th, 2017, 11:26 AM
#17
Re: DataAdapter updating all but the first row
Originally Posted by kpmc
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
-
Dec 8th, 2017, 11:49 AM
#18
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
-
Dec 8th, 2017, 01:01 PM
#19
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.
-
Dec 8th, 2017, 01:48 PM
#20
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?
-
Dec 8th, 2017, 01:56 PM
#21
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
-
Dec 8th, 2017, 04:01 PM
#22
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|