none
Execute ExecuteNonQuery successful but can't effect

    Question

  • Hello,

    I write simple phonebook and insert one row to local database then Execute ExecuteNonQuery method successfully but don't insert new record in database.

    I attached this project in OneDrive.

    Thanks

    Tuesday, February 6, 2018 4:28 PM

Answers

  • Sometimes such kind of problems is caused by the fact that the original database file, which is located in your project, is copied to target destination (e.g. “bin/Debug” subfolder), where the built application is placed. The program accesses that copy, not the original file. In order to keep the added data, you can, for example, close the program, (and maybe disconnect Visual Studio), then copy the .mdf and .ldf files from “bin” subfolder to your project, overriding the original files.



    • Edited by Viorel_MVP Tuesday, February 6, 2018 5:52 PM
    • Proposed as answer by KareninstructorMVP Wednesday, February 7, 2018 2:50 AM
    • Marked as answer by Arash_89 Wednesday, February 7, 2018 4:51 AM
    Tuesday, February 6, 2018 5:51 PM
  • Here is your problem as mentioned by Viorel_, you need to change from "Copy always" to "Copy if newer"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Arash_89 Wednesday, February 7, 2018 4:51 AM
    Wednesday, February 7, 2018 2:49 AM
  • Hi Arash,

    It seems the database what you select and insert are not the same because you set it to "copy always", when you insert some records to the database with code, it is actually inserted to the database under the bin/Debug folder, so please refer to the solution provided by Viorel and Karen, Or change the path of the connection string from:

    sqlConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\MyDB.mdf;Integrated Security=True";

    To:

    sqlConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\bin\Debug\MyDB.mdf;Integrated Security=True";

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Arash_89 Wednesday, February 7, 2018 4:52 AM
    Wednesday, February 7, 2018 4:00 AM
    Moderator
  • It's possible answer my question? Thanks
    See my MSDN code sample/article for answers and How to: Manage Local Data Files in Your Project

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Arash_89 Friday, February 9, 2018 2:42 PM
    Friday, February 9, 2018 2:15 PM

All replies

  • Sometimes such kind of problems is caused by the fact that the original database file, which is located in your project, is copied to target destination (e.g. “bin/Debug” subfolder), where the built application is placed. The program accesses that copy, not the original file. In order to keep the added data, you can, for example, close the program, (and maybe disconnect Visual Studio), then copy the .mdf and .ldf files from “bin” subfolder to your project, overriding the original files.



    • Edited by Viorel_MVP Tuesday, February 6, 2018 5:52 PM
    • Proposed as answer by KareninstructorMVP Wednesday, February 7, 2018 2:50 AM
    • Marked as answer by Arash_89 Wednesday, February 7, 2018 4:51 AM
    Tuesday, February 6, 2018 5:51 PM
  • Here is your problem as mentioned by Viorel_, you need to change from "Copy always" to "Copy if newer"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Arash_89 Wednesday, February 7, 2018 4:51 AM
    Wednesday, February 7, 2018 2:49 AM
  • Hi Arash,

    It seems the database what you select and insert are not the same because you set it to "copy always", when you insert some records to the database with code, it is actually inserted to the database under the bin/Debug folder, so please refer to the solution provided by Viorel and Karen, Or change the path of the connection string from:

    sqlConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\MyDB.mdf;Integrated Security=True";

    To:

    sqlConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\bin\Debug\MyDB.mdf;Integrated Security=True";

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Arash_89 Wednesday, February 7, 2018 4:52 AM
    Wednesday, February 7, 2018 4:00 AM
    Moderator
  • Thank you, Copy Always that means with every change Database one copy located to output directory? So why in my case after DB update doesn't copy to output directory? (Copy Always)

    Or for first time copy located to output directory. because I think copy always should be copied DB with every change in output.

    Wednesday, February 7, 2018 5:29 AM
  • It's possible answer my question? Thanks
    Friday, February 9, 2018 1:09 PM
  • It's possible answer my question? Thanks
    See my MSDN code sample/article for answers and How to: Manage Local Data Files in Your Project

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Arash_89 Friday, February 9, 2018 2:42 PM
    Friday, February 9, 2018 2:15 PM