0

I'm trying to write a simple insert statement for a SQL Server CE database, and my code works perfectly! ...except that the row doesn't actually get inserted into the database...

My code is: 

public static int InsertTourCreater(string userName, string password)
{
    SqlCeConnection connection = GuidedTourDB.GetConnection(); //Returns connection
    string insertStatement =
            "INSERT INTO [Tb_Creater] " +
            "([User_Name], [Password]) " +
            "VALUES (@UserName, @Password)" ;
    SqlCeCommand insertCommand = new SqlCeCommand(insertStatement, connection);
    insertCommand.Parameters.Add(new SqlCeParameter("@UserName", SqlDbType.NVarChar)).Value = userName;
    insertCommand.Parameters.Add(new SqlCeParameter("@Password", SqlDbType.NVarChar)).Value = password; 

    try
    {
        connection.Open();
        int success = insertCommand.ExecuteNonQuery();
        return success; 
    }

Code runs fine. Stepping through in debug mode, the connection opens, the values all get added correctly, and the ExecuteNonQuery() returns value of 1, but then I go to look at table data in Tb_Creater in my Server Explorer, and the record isn't there.

I've refreshed and even restarted visual studio. Not there. No exceptions are thrown.

Note: I'm adding parameters like that instead of AddWithValue() because I was worried that the strings would convert to varchar (which isn't available in SQL Server CE) by default, and cause problems, however the code operates the same either way.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Logan Black
  • 567
  • 3
  • 10
  • 21
  • Can you show us your connection string? – marc_s Nov 26 '12 at 16:51
  • To further confuse me, I get a 'cannot insert duplicate value' exception if I try using a value for User_Name that already exists. (It's a unique field.) AND, if I past my insert statement into a T-SQL editor and replace the variables with hard-coded values, it runs fine, so it shouldn't be a syntactical error.. – Logan Black Nov 26 '12 at 16:51
  • private static string connectionString = @"Data Source=|DataDirectory|\GuidedTourDB.sdf;Password=password"; public static SqlCeConnection GetConnection() { SqlCeConnection connection = new SqlCeConnection(connectionString); return connection; } Edit: Sorry, I have no idea how to paste code in comments... I have a select statement command working fine using this same connection. – Logan Black Nov 26 '12 at 16:52
  • Thanks - second question: do you have that .sdf file inside your Visual Studio solution? If so: when you click on it in Solution Explorer and look at its properties - how is the Copy to Output Directory property set?? – marc_s Nov 26 '12 at 16:53
  • 2
    You're probably looking at your source data. If you're running in the debugger changes are made to the output directory. Read http://stackoverflow.com/a/11801886/22437 – Dour High Arch Nov 26 '12 at 16:59
  • 1
    @DourHighArch I think you're probably right.. But what is the solution for this? Does it only write to that output while in debug mode? (So, after I'm sure my code works, I can make permanent changes by running w/o debug? EDIT: You ARE correct, I just checked the output database and it's all in there. – Logan Black Nov 26 '12 at 17:08
  • The "solution" is to make permanent changes to your source data and test changes to the output directory, the way you're supposed to and the way it will work in production. When you deploy software you don't usually deploy your source. – Dour High Arch Nov 26 '12 at 17:13
  • Sorry, this is my first foray into 3-tier applications as a final project for a CIS 3-- class. But, if I'm understanding correctly, I was actually trying to connect to the output directory, when I need to be connecting to the DB in my project source. So, changing my connection string to "Data Source=.\GuidedTourDB.sdf; ..." should do the trick programmatically? (And looking in the output db when I'm trying to see the changes I've effected.) Thank you for your help. – Logan Black Nov 26 '12 at 18:52
  • You should almost never use hard-coded paths in your projects, you should almost always use `|DataDirectory|`. You must explain exactly what you are trying to do for me to give a definite answer, but `.\MyDatabase.sdf` is almost certainly wrong. Please read [this answer](http://stackoverflow.com/a/13313075/22437). – Dour High Arch Nov 27 '12 at 00:07

1 Answers1

0

I happened to encounter this issue and solved. Come to your debug output path and run your app(exe) with the administrator mode. Because I saw the log message that you have no access to the sdf file. Actually no any error popup in visual studio.

FLCL
  • 2,445
  • 2
  • 24
  • 45
Tao
  • 1