Can’t refresh Products Table in a typed DataSet and the Database to the original

Create a typed DataSet:

1. Created a Windows Application Form with Visual Studio 2015
2. Bind a DataGridView control with Visual Studio (not through its properties).
3. I added a connection to Northwind Database
4. I created a DataSet for this example with the Categories, Suppliers, and Products tables of the Northwind database
5. The DataSet contains three DataTables, and each DataTable is made of the columns you selected in the wizard. In this Form I will only use the Products Table only with 77 Products (rows).
6. This DataSet is typed, because it knows the structure of the data you’re going to store in it.
7. Selected the Products table. As soon as you select it, an arrow appears next to its name. Click this arrow to open a drop-down list with the binding options for the DataTable.
8. Selected the DataGridView option, and then drop the Products DataTable on the form.
9. The editor created a DataGridView control and bind it to the Products DataTable. In addition, it will create a toolbar at the top of the form with a few navigational and editing buttons
10. Added a Refresh Data button (see code)

PROBLEM: Can’t refresh Products Table in a typed DataSet and the Database to the original version. (See image).

1. Click the + button and add a new row (product). ProductID, primary key is -1 by default as you know.

2. Save it (click button save)

3. Click Refresh Data button. ProductID the first time is 78. Dataset and Database now have 78 products (rows).

4. Select row 78 and click X button (Delete row). Refresh DataSet in DataCode window. Checked with query SELECT * FROM Products in Microsoft SQL Management Studio. It is ok. Only 77 rows as the original database.

5. Close Project, shut down computer.

6. And here is when I have the big problem. Start Project (F5) and add row but is 79 not 78.

7. By the time I am writing this, ProductID is 119 !!!. In my code I run Me.ProductsTableAdapter.Fill (Me.NorthwindDataSet.Products) to Update DataSet but does not work.

Attach image and complete code in Form1 is generated automatically by Visual Studio. Background code is generated automatically by Visual Studio because it is a typed DataSet.

Please help me with this problem. Thank you.

Code:

---

Public Class Form1
    Private Sub ProductsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles ProductsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ProductsBindingSource.EndEdit()
        Me.TableAdapterManager.ProductsTableAdapter.
Adapter.ContinueUpdateOnError = True
        Me.TableAdapterManager.UpdateAll(Me.NorthwindDataSet)

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'NorthwindDataSet.Products' table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)

    End Sub

    Private Sub bttnRefreshData_Click(sender As Object, e As EventArgs) Handles bttnRefreshData.Click
        If NorthwindDataSet.HasChanges Then
            Dim reply As MsgBoxResult =
            MsgBox("The DataSet contains changes." &
            vbCrLf & "Reload data anyway?",
            MsgBoxStyle.YesNo Or MsgBoxStyle.Exclamation)
            If reply = MsgBoxResult.No Then Exit Sub
        End If
        Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)

    End Sub
End Class

---

Try to upload image but not possible. I can send project via e mail.

It is important to have a clear idea of the difference of an untype DataSet and a Type one. In the Type ones Visual Studio does all the work. Please read carefully my post.

You should do some reading on identity columns in SQL Server. Apart from that, why is this an issue anyway? The values are still uniquely identifying each record so what's the problem?

Quote:

---

6. And here is when I have the big problem. Start Project (F5) and add row but is 79 not 78.

---

That's how it is supposed to be. The field in Access presumbly is an autonumber... that's how autonumbers work! Just because a row got deleted, doesn't mean its ID will get reused... what happens when you deleted #12 ... it doesn't get reused... it moves on to the next one in the sequence. This is by design. It's this way to prevent problems down the road should there be orphaned child records or other related data that still has the old deleted ID in it.

-tg

Quote:

---

Originally Posted by jmcilhinney

You should do some reading on identity columns in SQL Server. Apart from that, why is this an issue anyway? The values are still uniquely identifying each record so what's the problem?

---

Done it in Microsoft SQL Management Studio . ProductID is the primary key and identity columns. Read lines 1 to 7 where in PROBLEM.

That's the way an Autonumber/Identity field works. Every time you add a new record the field automatically incremented. The number is not reset if you delete a row. If you don't want that behavior then you'll have to make the field type an Integer type and do the increment yourself via code.

Edit - Geeze, I must type slow. Two people answered while I was answering.. :)

Quote:

---

Originally Posted by techgnome

That's how it is supposed to be. The field in Access presumbly is an autonumber... that's how autonumbers work! Just because a row got deleted, doesn't mean its ID will get reused... what happens when you deleted #12 ... it doesn't get reused... it moves on to the next one in the sequence. This is by design. It's this way to prevent problems down the road should there be orphaned child records or other related data that still has the old deleted ID in it.

-tg

---

By the time I am writing this, ProductID is 119 !!!. I close project, shut down computer and keeps increasing !!!

Quote:

---

Originally Posted by wes4dbt

That's the way an Autonumber/Identity field works. Every time you add a new record the field automatically incremented. The number is not reset if you delete a row. If you don't want that behavior then you'll have to make the field type an Integer type and do the increment yourself via code.

Edit - Geeze, I must type slow. Two people answered while I was answering.. :)

---

Not the case in type DataSets. In untype ones is easy.

Quote:

---

Originally Posted by ebellounisoft

Not the case in type DataSets. In untype ones is easy.

---

It doesn't matter if it's a Typed Dataset or an UnTyped Dataset. What matters is how the field is defined in the database.

Quote:

---

Originally Posted by wes4dbt

It doesn't matter if it's a Typed Dataset or an UnTyped Dataset. What matters is how the field is defined in the database.

---

It is ok . The database es Northwind. I.ve use it in a lot of projects with no problem. in Other DataSet projects.

Quote:

---

Originally Posted by ebellounisoft

It is ok . The database es Northwind. I.ve use it in a lot of projects with no problem. in Other DataSet projects.

---

Not sure I understand you, there is nothing wrong with the results your getting. There's nothing wrong with the database. an Autonumber field always behaves this way. That why I said,

Quote:

---

If you don't want that behavior then you'll have to make the field type an Integer type and do the increment yourself via code.

---

It is important to have a clear idea of the difference of an untype DataSet and a Type one. In the Type ones Visual Studio does all the work. Please read carefully my post.

Quote:

---

Originally Posted by ebellounisoft

It is important to have a clear idea of the difference of an untype DataSet and a Type one. In the Type ones Visual Studio does all the work. Please read carefully my post.

---

Well I give up. I've been using typed and untyped datasets for many many years. And as I said it has nothing to do with this issue. good luck.

Quote:

---

Originally Posted by wes4dbt

Not sure I understand you, there is nothing wrong with the results your getting. There's nothing wrong with the database. an Autonumber field always behaves this way. That why I said,

---

In type ones Visual Studio does all the code. It is important to have a clear idea of the difference of an untype DataSet and a Type one. In the Type ones Visual Studio does all the work. Please read carefully my post.

Quote:

---

Originally Posted by ebellounisoft

It is important to have a clear idea of the difference of an untype DataSet and a Type one.

---

NO IT IS NOT! Well, it is in general but, in this case, it's irrelevant. The values here are being generated by the database. An identity column auto-generates sequential numerical values. What you're seeing is the exact behaviour you would and should expect to see from an identity column. If you had read about identities PROPERLY then you would know how they work, including how to reset one. An identity is never going to repeat a value simply because you delete a record.

I ask again though, why is it an issue that you have gaps in the sequence in your table. If it's just the fact that you'd like there to be no numbers missing then get over it. If it's something else, explain it because you haven't so far.

Quote:

---

Originally Posted by ebellounisoft

Please read carefully my post.

---

We've all read your post. You just don't know what you're talking about. That's OK - there are plenty of things that I don't know about - but you need to stop assuming that we are just not reading what you're posting. We are.

Quote:

---

Originally Posted by jmcilhinney

NO IT IS NOT! Well, it is in general but, in this case, it's irrelevant. The values here are being generated by the database. An identity column auto-generates sequential numerical values. What you're seeing is the exact behaviour you would and should expect to see from an identity column. If you had read about identities PROPERLY then you would know how they work, including how to reset one. An identity is never going to repeat a value simply because you delete a record.

I ask again though, why is it an issue that you have gaps in the sequence in your table. If it's just the fact that you'd like there to be no numbers missing then get over it. If it's something else, explain it because you haven't so far.

---

The issue is even If I close project or shut down computer when I open again the project it should be 78 the new row right ?. But instead it keeps increasing. Why ???

Quote:

---

Originally Posted by ebellounisoft

The issue is even If I close project or shut down computer when I open again the project it should be 78 the new row right ?. But instead it keeps increasing. Why ???

---

NO!!!!! The database has been modified, closing your project has no effect on the database.

Quote:

---

Originally Posted by wes4dbt

NO!!!!! The database has been modified, closing your project has no effect on the database.

---

Please read this of my post:

Before exit I Delete row, Save and Refresh Data.

4. Select row 78 and click X button (Delete row). Refresh DataSet in DataCode window. Checked with query SELECT * FROM Products in Microsoft SQL Management Studio. It is ok. Only 77 rows as the original database.

Quote:

---

Originally Posted by ebellounisoft

Please read this of my post:

Before exit I Delete row, Save and Refresh Data.

4. Select row 78 and click X button (Delete row). Refresh DataSet in DataCode window. Checked with query SELECT * FROM Products in Microsoft SQL Management Studio. It is ok. Only 77 rows as the original database.

---

that doesn't reset the database.

Once you've done this,

Quote:

---

1. Click the + button and add a new row (product). ProductID, primary key is -1 by default as you know.

2. Save it (click button save)

---

The database is permanently changed.

Quote:

---

Originally Posted by wes4dbt

that doesn't reset the database.

Once you've done this,

The database is permanently changed.

---

Done that every time I open again. When I click save instead of 78 it keeps increasing , and changes the database. Right now I in row 130 !!!. But if I Delete, Save and Refresh the Database is the original. I Checked it with query SELECT * FROM Products in Microsoft SQL Management Studio

That's the way it works, thats what we keep telling you.

Quote:

---

That's how it is supposed to be. The field in Access presumbly is an autonumber... that's how autonumbers work! Just because a row got deleted, doesn't mean its ID will get reused... what happens when you deleted #12 ... it doesn't get reused... it moves on to the next one in the sequence. This is by design. It's this way to prevent problems down the road should there be orphaned child records or other related data that still has the old deleted ID in it.

-tg

---

Post #5

Quote:

---

That's the way an Autonumber/Identity field works. Every time you add a new record the field automatically incremented. The number is not reset if you delete a row. If you don't want that behavior then you'll have to make the field type an Integer type and do the increment yourself via code.

---

Post #14

Quote:

---

NO IT IS NOT! Well, it is in general but, in this case, it's irrelevant. The values here are being generated by the database. An identity column auto-generates sequential numerical values. What you're seeing is the exact behaviour you would and should expect to see from an identity column. If you had read about identities PROPERLY then you would know how they work, including how to reset one. An identity is never going to repeat a value simply because you delete a record.

---

So, is it you don't believe us or you don't understand what we're saying.

Quote:

---

Originally Posted by wes4dbt

That's the way it works, thats what we keep telling you.



Post #5


Post #14


So, is it you don't believe us or you don't understand what we're saying.

---

I do respect your comments. Thank you. But tell me why is that the DataSet, even in I shut down my PC reminds o remembers the last ProductID ?.

Quote:

---

Originally Posted by ebellounisoft

Please read this of my post:

Before exit I Delete row, Save and Refresh Data.

4. Select row 78 and click X button (Delete row). Refresh DataSet in DataCode window. Checked with query SELECT * FROM Products in Microsoft SQL Management Studio. It is ok. Only 77 rows as the original database.

---

Refreshing the dataset doesn't do anything except update it with any changes from the database, such as adding or removing columns. So that isn't doing anything at this point.

Quote:

---

Originally Posted by ebellounisoft

Done that every time I open again. When I click save instead of 78 it keeps increasing , and changes the database. Right now I in row 130 !!!. But if I Delete, Save and Refresh the Database is the original. I Checked it with query SELECT * FROM Products in Microsoft SQL Management Studio

---

Putting your posts in bold doesn't do anything either except it's just short of shouting (all caps) ... But it also still doesn't change the fact that the ID is being generated by the database, as has been explained time and time again to you. When you delete a record, that ID is gone. G.O.N.E. It doesn't come back. You still haven't explained why this is a problem. Usually by this point in the discussion, the poster that has had a "problem" with is has gotten a clue and understands that that's how they work and moves on... for some reason you won't let it go. There must be a reason why. Instead you just keep restating that your ID keeps increasing, which we all get because we understand that's how it works. So we fail to see the problem.

-tg

Quote:

---

Originally Posted by techgnome

Refreshing the dataset doesn't do anything except update it with any changes from the database, such as adding or removing columns. So that isn't doing anything at this point.



Putting your posts in bold doesn't do anything either except it's just short of shouting (all caps) ... But it also still doesn't change the fact that the ID is being generated by the database, as has been explained time and time again to you. When you delete a record, that ID is gone. G.O.N.E. It doesn't come back. You still haven't explained why this is a problem. Usually by this point in the discussion, the poster that has had a "problem" with is has gotten a clue and understands that that's how they work and moves on... for some reason you won't let it go. There must be a reason why. Instead you just keep restating that your ID keeps increasing, which we all get because we understand that's how it works. So we fail to see the problem.

-tg

---

Sorry .Need bold. Do not have my glasses

PROBLEM : The issue is even If I close project or shut down computer when I open again the project it should be 78 the new row right ?. But instead it keeps increasing. Why ???

I can send Project via e mail . You need to have Northwind Database . Thank you all !!! :):wave:

Why would we need your project or the database??? Do you have a question???

Quote:

---

PROBLEM : The issue is even If I close project or shut down computer when I open again the project it should be 78 the new row right ?. But instead it keeps increasing. Why ???

---

We've already answered that several times.

Quote:

---

Originally Posted by wes4dbt

Why would we need your project or the database??? Do you have a question???


We've already answered that several times.

---

Typed DataSets are quite convenient when it comes to coding. The real advantage of typed DataSets is that they can simplify enormously the generation of data-bound forms. Instead of binding the DataGridView control through its properties, I can let Visual Studio perform the binding for me.

Got to go. I'll be back tomorrow morning. Thank you very much !!! :wave:

Quote:

---

Originally Posted by ebellounisoft

Typed DataSets are quite convenient when it comes to coding. The real advantage of typed DataSets is that they can simplify enormously the generation of data-bound forms. Instead of binding the DataGridView control through its properties, I can let Visual Studio perform the binding for me.

---

Got to go. I'll be back tomorrow morning. Thank you very much !!! :wave:

Quote:

---

Originally Posted by ebellounisoft

Typed DataSets are quite convenient when it comes to coding. The real advantage of typed DataSets is that they can simplify enormously the generation of data-bound forms. Instead of binding the DataGridView control through its properties, I can let Visual Studio perform the binding for me.

---

Ok, I agree. I use them all the time. But as said multiple times before, the Typed dataset has nothing to do with the ProductId field being incremented. That is done by the database.

Quote:

---

Originally Posted by ebellounisoft

Sorry .Need bold. Do not have my glasses

PROBLEM : The issue is even If I close project or shut down computer when I open again the project it should be 78 the new row right ?. But instead it keeps increasing. Why ???

---

If there is a loud banging sound you hear, it's the sound of our collective heads banging the desks in frustration. The answer is No. Maybe I need to bold that so you'll see it. No. It should NOT be 78, because that's not how Identity or Autonumbers work. They continously increase, even when you delete a row. That's how they work. That's how they have ALWAYS worked. It has NOTHING to do with datasets, typed or other wise... Try this youself... open the database. Add a row manually. You'll get the next number in the sequence.... delete it.... then add another row again... you'll see that it is one more than the last one you just deleted. THAT'S HOW THEY WORK. That is by design. As I stated back in some previous post, this is so that it prevents cross-contamination from related records that may still be around. CLosing the project just closes the project, doesn't affect what the database does. Still havent explained WHY this is a problem.

-tg

Quote:

---

Originally Posted by ebellounisoft

I shut down my PC reminds o remembers the last ProductID ?.

---

Because that's how an identity column works. The database knows what the last value it generated was, even if you restart your PC. The next time a value is required for that column, it generates the next value after the last one. That's how it works, that's how it is supposed to work and that is how you'd know it does work if you'd done appropriate reading on identity columns in SQL Server. If you want to regenerate values then you need to reset that identity and that has absolutely nothing whatsoever to do with your DataSet or your application in general for that matter. It is purely a database issue and would be the same even if your application didn't exist.

If you want to be able to reset a database then you have two main options, depending on how you created the database in the first place. If you have created the database in Management Studio so it is permanently attached then one option would be to create a backup of the database in its "clean" state and restore that backup whenever you want a "clean" database. A similar option to that is to create the database in Visual Studio as part of the project and have it attached on-demand. That way, your "clean" database will always be in your source folder and all you have to do to reset it is to delete the file in the 'bin' folder. The next time you run the project, the source data file will be copied to the output folder and your app will use that.

jmc,

Not sure this is an SQL Server database, MSDN uses the Northwind database in many of it's MS Access examples. I've seen Northwind in mdb and mdf formats.

Wes - doesn't matter... autonumber in Access works the same as Identity in SQLServer.

So it doesn't matter if the OP is using SQL Server or Access, the experience is the same... autonumbers/identity numbers, simply to do not repeat once used/deleted.

-tg

Quote:

---

Originally Posted by wes4dbt

jmc,

Not sure this is an SQL database, MSDN uses the Northwind database in many of it's MS Access examples. I've seen Northwind in mdb and mdf formats.

---

From post #1:

Quote:

---

Originally Posted by ebellounisoft

4. Select row 78 and click X button (Delete row). Refresh DataSet in DataCode window. Checked with query SELECT * FROM Products in Microsoft SQL Management Studio. It is ok. Only 77 rows as the original database.

---

I think that that is the only clue and I must have picked up on it initially or just assumed SQL Server.

If it was Access then the situation is identical to using a MDF file that is part of the project and attached on demand. Either way, you have a single source file that is part of the project and represents a clean state. The working data file in the output folder can be replaced with a new copy of that at any time, so there's really no issue. It's only always attached databases where you only have one copy and need to explicitly rebuild that in a clean state, either by restoring a backup or running an appropriate script(s).

Quote:

---

Originally Posted by techgnome

Wes - doesn't matter... autonumber in Access works the same as Identity in SQLServer.

So it doesn't matter if the OP is using SQL Server or Access, the experience is the same... autonumbers/identity numbers, simply to do not repeat once used/deleted.

-tg

---

Yeah I know, I only mentioned it because if the OP has only used Access databases he/she might be confused about the term Identity field. But I just spotted this in post #4 so I guess it is a SQL Server database.

Quote:

---

Done it in Microsoft SQL Management Studio . ProductID is the primary key and identity columns. Read lines 1 to 7 where in PROBLEM.

---

It is a Windows Application Form with Visual Studio 2015, not Access and I am using the one recommended for Visual Studio.
https://www.microsoft.com/en-us/down....aspx?id=23654

Quote:

---

Originally Posted by ebellounisoft

It is a Windows Application Form with Visual Studio 2015, not Access and I am using the one recommended for Visual Studio.
https://www.microsoft.com/en-us/down....aspx?id=23654

---

Do you now understand how Identity fields work? Do you have any other questions other than the one that has been answered? If not you can mark this thread resolved by selecting the Thread Tools option from the top of this page.

Quote:

---

Originally Posted by ebellounisoft

It is a Windows Application Form with Visual Studio 2015, not Access and I am using the one recommended for Visual Studio.
https://www.microsoft.com/en-us/down....aspx?id=23654

---

We know it's a WinForms app. That's not the issue. The issue is what database you're using, as has been stated several times. The answer is SQL Server, which is something that you should have clarified at the outset.