none
SubmitChanges() is not updating the database? Why? RRS feed

  • Question

  • Hi,

     

    Any help will be more than welcome. I already spent many hours around this issue and I haven’t been able to resolve it.

     

    I was trying Linq to SQL for the first time but I wasn’t able to update data in my database. I have been reading some threads with people that had similar problems but I wasn’t able to resolve my issues. I’m using Microsoft Visual C# 2008 Express Edition and SQL Server Express Edition.

     

    These are the steps that I have performed:

     

    > Created a database called “HistoricalPrices.mdf” inside Microsoft Visual C# 2008 Express Edition in the Database Explorer. The way I created the database I was not given the choice to choose for a local database. I created one table called “TestHistoricalPrices” with the following fields: Date (primary key), OpenPrice, HighPrice, LowPrice and ClosePrice.

     

    > Then, inside Visual C# 2008 Express Edition in the Database Explorer I manually included the following 3 rows of data in the table “TestHistoricalPrices”;

            

    09-09-2009

    80.0000

    82.0000

    78.0000

    81.0000

    2000000

    10-09-2009

    82.0000

    84.0000

    80.0000

    83.0000

    3000000

    11-09-2009

    84.0000

    86.0000

    82.0000

    85.0000

    4000000

     

    > Then I used the ORM designer to automatically create the entity “TestHistoricalPrice” which reflects the information contained in the table on the database.

     

    > To test Linq to SQL I’m trying change the close price of 11-9-2009 from 85.00 to 90.0 and update the database. Below you can see the code that I’m using to achieve that. The question is that the first time that I use this code it works fine but after running the code I go to the Database Explorer and I choose “Show table data” on the table “TestHistoricalPrices” and I still see the ClosePrice of 11-09-2009 as 85. It looks that the Database wasn’t updated but when I run the same code again I get an “InvalidOperationException” (“sequence contains no elements”) since the query returns no values which means that it looks that on the database the ClosePrice on 11-09-2009 is not anymore 85.00. I’m confused! This is the code that I’m using to achieve that (I want to see the result of the query on a DataGridView):

     

    HistoricalPricesDataContext db = new HistoricalPricesDataContext();

     

    db.Log = Console.Out;

     

    TestHistoricalPrice LastHistoricalPrice = (from hp in

    db.TestHistoricalPrices where hp.ClosePrice == 85 select hp).First();

     

    LastHistoricalPrice.ClosePrice = 90;

                

    db.SubmitChanges();

    After reading some threads related to similar problems I decided to change to the property “Copy to Output Directory” of the database from “Copy always” (default value) to “Do not copy”. I test and again I get the same results.

     

    Could these problems be related to the fact that Visual C# 2008 Express Edition keep two different databases with the same name in different directories? It looks that the database that I have access within Visual C# 2008 Express Edition is not updated but somehow the other database is updated since I get an error by using a query that previously worked. Note: I have to say that I haven’t yet fully understood the logic for the fact that C# keeps 2 databases with the same name in different directories.

     

    This is the information that I obtain in the file “app.config” (including the connection string to connect to the database that was automatically generated):

    <connectionStrings>

            <add name="Testing_Trading_Strategies.Properties.Settings.HistoricalPricesConnectionString"

    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\HistoricalPrices.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    providerName="System.Data.SqlClient" />

    …………………………………

     

    Thanks.

     

    Best regards,

    Miguel Teixeira.

     


    Best, Miguel.
    Tuesday, October 6, 2009 4:57 PM

Answers

  • I suggest you specify the database setting as you want to work now. Don't worry about it until you have to publish to a user. At that time you could either create a new project, or rewire the database situation to accommodate a publish.


    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by Miguel Txa Monday, October 19, 2009 2:01 AM
    Sunday, October 18, 2009 12:48 PM
    Moderator
  • Yes you should be fine. But I would back up the database...just in case.

    But frankly there is another route...one can script out any data to be added to the database at a later time. That way the database is formed with tables, but then during install, a script could be run to load the data into the database.

    I do similar on my database projects, but the difference is that I am not publishing a local database as you are but to a SQL Server instance. So I understand your situation. :-) GL

    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by Miguel Txa Friday, November 6, 2009 12:53 PM
    Tuesday, October 20, 2009 6:06 PM
    Moderator

All replies

  • Everytime you change code a new version of the mdf file gets rebuilt and all data from a previous session goes away. Once you have the code installed on a target machine. The database will never be overwritten because the end user is not doing what you do in Visual Studio.

    William Wegerson (www.OmegaCoder.Com)
    Thursday, October 15, 2009 12:36 PM
    Moderator
  • Hi William,

    If that's the case how can I include a local database in my project and still be able to see the updated data in the Database Explorer of Visual Studio? If this is not possible what is the alternative? Everytime that I do any changes to my application I need to exit Visual Studio and execute the ".exe" file?! It doesn't look a good solution!

    While I was dragging to the O/R Designer the first table of my database I was asked the following: "The connection you selected uses a local data file that is not in the current project. Would you lile to copy the file to your project and modify the connection? If you copy the data file to your project, it will be copied to the project's output directory each time you run the application. Press F1 for controlling this behavior". I used to choose "Yes" and I couldn't see any updated information of my database in the Database Explorer of Visual Studio. So, I decided to use "No" instead and now it looks that everything is working fine. However, I would prefer to have my local database included in the project.

    Thanks for your reply and sorry for my late reply.

    Best,
    Miguel Txa.
    Sunday, October 18, 2009 10:46 AM
  • I suggest you specify the database setting as you want to work now. Don't worry about it until you have to publish to a user. At that time you could either create a new project, or rewire the database situation to accommodate a publish.


    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by Miguel Txa Monday, October 19, 2009 2:01 AM
    Sunday, October 18, 2009 12:48 PM
    Moderator
  • Hi William,

    I assume that when I will do that ("publish to a user") I will not loose all the information in the database, correct?

    If that's true then we have a solution.

    Thanks for your help.

    Best,
    Miguel Txa.

    Monday, October 19, 2009 2:00 AM
  • Yes you should be fine. But I would back up the database...just in case.

    But frankly there is another route...one can script out any data to be added to the database at a later time. That way the database is formed with tables, but then during install, a script could be run to load the data into the database.

    I do similar on my database projects, but the difference is that I am not publishing a local database as you are but to a SQL Server instance. So I understand your situation. :-) GL

    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by Miguel Txa Friday, November 6, 2009 12:53 PM
    Tuesday, October 20, 2009 6:06 PM
    Moderator