none
Problems Inserting Row with SQL CE 3.0 + C# -- Read Works, no insert RRS feed

  • Question

  • Hi all,

    After hour of banging my head, I'm stopping to ask directions.

     

    I have a C# program with an SQL CE database.  I can read data from the db using the SQLCEDataReader object.  I'm trying to insert a row, but it's not taking.  I get a return of 1 on the cmd.ExecuteNonQuery();  just like it inserted the row, but if I go and open the table in the Data Designer, it's not there.  I can insert rows through the Data Designer, but can't figure what's going on in my code.

     

    I simplified it all down to a table with 2 columns (nvarchar 100), to see if I could figure out what is going on.

     

    Here's the code:

     

    Code Block

     

    private void button2_Click(object sender, EventArgs e)

    {

    db.connect();

    SqlCeCommand cmd = new SqlCeCommand("Insert INTO testtable Values('s','e')", db.conn);

    MessageBox.Show("" + cmd.ExecuteNonQuery());

    }

     

     

     I get a message box with "1" in it.  But viewing the table...no rows!  Any help would be greatly appreciated.

     

    Thanks,

    Byron

    Friday, November 30, 2007 6:41 AM

Answers

  • Hi Byron,

    The problem is not because of using |DataDirectory|, if that had been the case, then it would have thrown error at the time of connection. Your application is working fine Smile, and the data is also getting added to the DB. But you may be wondering why the data is getting lost when re-run from VS IDE. This is due to the property setting 'Copy to Output Directory' is set to 'Copy always'. If you set it to 'Copy if newer', then you will find your data is no more lost. OR just go to the Debug folder and run the generated EXE, and do some database activity, close application and re-run it, you find data is no more lost.

    The reason is, when you run the application with Debug(F5) feature of VS IDE, it copies the files to the 'Debug' folder under 'bin'. When the setting is 'Copy always', it overwrites the previous files in this location. This is same for database files, so you are losing your data that got inserted in your previous Debug run. Now when you specify the absolute path, then even your VS IDE copies the database file to Debug location, but the database used in this case is the orginal one (absolute path) and not the database under Debug folder! Hence, the data is not lost even in subsequent Debug run.

    Hope, you find the explaination helpful.
    Anisur


    Sunday, December 2, 2007 11:32 AM

All replies

  • You have created a SqlCeCommand but that's all you've done... simply call cmd.ExecuteNonQuery() and notice that your statement will execute Smile
    Friday, November 30, 2007 7:15 AM
  •  

    Tim,

    Thanks for the reply!  Unfortunately, I do call the cmd.ExecuteNonQuery()...it's hidden in that MessageBox.Show() statement (see code in first post).  I really wish it was that...I'm pulling my hair out.

     

    When that message box fires, it executesnonquery and I get the response back that 1 record was affected, but I can't for the life of me find where that got written.  Same effect when I call it outside of the messagebox. 

     

    Also, I've got tables with tableadapters and I've tried that method as well.  No joy.  I try to view Preview Data for tables with table adapters and nothing is there, or Open table from the Server Explorer.  In Open table, I can write records there, and later retrieve them with my db.conn connection, so that appears to be at least semi-ok.

     

    I'm beginning to wonder if it's something to do with the DB connection.  To connect, I'm using:

     

    db.conn.ConnectionString = global::MyProgramName.Properties.Settings.Default.qfmConnectionString;

     

    This connection works fine retrieving data, but I haven't been able to use it to write data.

     

    Thanks again for your help!

     

    Byron

    Friday, November 30, 2007 2:20 PM
  • Ok, I've got the problem fixed.  I was using something like [DataDirectory]\databasename.sdf as my Data Source.  This must not have been correct.  Even though using that connection, I could read the tables, I couldn't insert into them.

     

    When I change the Data Source to a specific string location, I can now read/write. 

     

    So, how do I form a SQL CE connection string that represents an embedded database file?  I want to be able to deploy the application without having to worry about what path the user installs the app to.

     

    Thanks,

    Byron

    Friday, November 30, 2007 4:39 PM
  • Hi Byron,

    The problem is not because of using |DataDirectory|, if that had been the case, then it would have thrown error at the time of connection. Your application is working fine Smile, and the data is also getting added to the DB. But you may be wondering why the data is getting lost when re-run from VS IDE. This is due to the property setting 'Copy to Output Directory' is set to 'Copy always'. If you set it to 'Copy if newer', then you will find your data is no more lost. OR just go to the Debug folder and run the generated EXE, and do some database activity, close application and re-run it, you find data is no more lost.

    The reason is, when you run the application with Debug(F5) feature of VS IDE, it copies the files to the 'Debug' folder under 'bin'. When the setting is 'Copy always', it overwrites the previous files in this location. This is same for database files, so you are losing your data that got inserted in your previous Debug run. Now when you specify the absolute path, then even your VS IDE copies the database file to Debug location, but the database used in this case is the orginal one (absolute path) and not the database under Debug folder! Hence, the data is not lost even in subsequent Debug run.

    Hope, you find the explaination helpful.
    Anisur


    Sunday, December 2, 2007 11:32 AM
  • Tim,
    Byron was right, the cmd.ExecuteNonQuery() is called as soon as MessageBox.Show(..... is executed.
    Sunday, December 2, 2007 11:39 AM
  • Thanks, Anisur.

    That was the problem.  I eventually narrowed it down to the Copy Always, but I didn't try Copy if Newer.  I hard coded my connections to something that wasn't moving around and was planning on switching it back before creating the install pack.  This will work much better!

    Thanks again to all!

    Byron
    Sunday, December 2, 2007 10:00 PM