Results 1 to 39 of 39

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

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

    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.
    Last edited by ebellounisoft; Oct 15th, 2017 at 07:22 PM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    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?

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

    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
    * 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??? *

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by jmcilhinney View 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?
    Done it in Microsoft SQL Management Studio . ProductID is the primary key and identity columns. Read lines 1 to 7 where in PROBLEM.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    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..

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by techgnome View Post
    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 !!!

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by wes4dbt View Post
    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.

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by wes4dbt View Post
    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.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by ebellounisoft View Post
    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,
    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.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    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.

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by wes4dbt View Post
    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.

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by jmcilhinney View Post
    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 ???

  17. #17
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by wes4dbt View Post
    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.
    Last edited by ebellounisoft; Oct 15th, 2017 at 07:49 PM.

  19. #19
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by ebellounisoft View Post
    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,
    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.

  20. #20

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by wes4dbt View Post
    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
    Last edited by ebellounisoft; Oct 15th, 2017 at 07:57 PM.

  21. #21
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

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

    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
    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
    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.

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by wes4dbt View Post
    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 ?.

  23. #23
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

    Quote Originally Posted by ebellounisoft View Post
    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 View Post
    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
    * 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??? *

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by techgnome View Post
    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 ???
    Last edited by ebellounisoft; Oct 15th, 2017 at 08:18 PM.

  25. #25

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

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

  26. #26
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

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

    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.

  27. #27

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by wes4dbt View Post
    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.

  28. #28

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

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

  29. #29

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    Quote Originally Posted by ebellounisoft View Post
    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 !!!

  30. #30
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  31. #31
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

    Quote Originally Posted by ebellounisoft View Post
    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
    * 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??? *

  32. #32
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  33. #33
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    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.
    Last edited by wes4dbt; Oct 16th, 2017 at 12:16 AM.

  34. #34
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

    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
    * 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??? *

  35. #35
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    Quote Originally Posted by wes4dbt View Post
    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 View Post
    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).

  36. #36
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by techgnome View Post
    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.
    Done it in Microsoft SQL Management Studio . ProductID is the primary key and identity columns. Read lines 1 to 7 where in PROBLEM.

  37. #37

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

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

    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

  38. #38
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

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

    Quote Originally Posted by ebellounisoft View Post
    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.

  39. #39
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    Quote Originally Posted by ebellounisoft View Post
    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.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width