sql statement isnt saving any data into table

Question

Whenever I execute my C# code everything goes well, no compiler errors, nothing. But when I go to look at my table in the server explorer, nothing was inserted. Restarted Visual Studio, still nothing.

I went to debug and I looked at the cmd string before it executes ExecuteNonQuery() and the string still is @itmId,... etc. Not sure if that would effect it or not. Any help?

try
{
    Item workingItem = mItemList.Items[itemCombo.SelectedIndex - 1] as Item;
    SqlCeConnection sc = new SqlCeConnection(SalesTracker.Properties.Settings.Default.salesTrackerConnectionString);
    SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Sales VALUES(@itmId, @itmNm,@fstNm, @date,@prft, @commision)", sc);
    cmd.Parameters.AddWithValue("@itmId", workingItem.ItemId);
    cmd.Parameters.AddWithValue("@itmNm", workingItem.ItemName);
    cmd.Parameters.AddWithValue("@fstNm", logedSalesmen.ID);
    cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
    cmd.Parameters.AddWithValue("@prft", workingItem.Profit);
    cmd.Parameters.AddWithValue("@commision", workingItem.Commision);
    sc.Open();
    cmd.ExecuteNonQuery();
    sc.Close();
    MessageBox.Show("Save successfull");
    this.Close();
} 
catch (Exception exc)
{
    MessageBox.Show(exc.Message);
}

EDIT:So it is a matter of the temporary debug database being used, i used select count(0) to figure that out. But im not sure what i should use in my connection string to fix it.

Answer 1

The most common error here is actually a deployment thing - i.e. having 2 different database files in play. In particular, commonly the database file you are debugging (etc) against is often the one in "bin/debug" or similar, and gets overwritten every time you build. But the file people often look at to see the change is the one in their project tree.

Make sure you are looking at the right file.

The code looks fine; the fact that the parameters are still parameters is entirely expected and correct. If you want a simple way of validating the insert, then just check

SELECT COUNT(1) FROM Sales 

before and after the insert; I expect it will be incrementing.

Also check that you are closing and disposing the connection cleanly (in case this is simply a buffered change that didn't get written before the process terminated). Both sc and cmd are IDisposable, so you should use using really:

using(SqlCeConnection sc = new SqlCeConnection(
    SalesTracker.Properties.Settings.Default.salesTrackerConnectionString))
using(SqlCeCommand cmd = new SqlCeCommand(
    "INSERT INTO Sales VALUES(@itmId, @itmNm,@fstNm, @date,@prft, @commision)",
    sc))
{
    cmd.Parameters.AddWithValue("@itmId", workingItem.ItemId);
    cmd.Parameters.AddWithValue("@itmNm", workingItem.ItemName);
    cmd.Parameters.AddWithValue("@fstNm", logedSalesmen.ID);
    cmd.Parameters.AddWithValue("@date",
        DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
    cmd.Parameters.AddWithValue("@prft", workingItem.Profit);
    cmd.Parameters.AddWithValue("@commision", workingItem.Commision);
    sc.Open();
    cmd.ExecuteNonQuery();
}

Answer 2

You shouldn't convert DateTime.Now to a string - pass it just as DateTime.Now

You should specify the columns in your insert statement: Ie:

INSERT INTO Sales (ItemID,ItemName...) VALUES (@itmID)

You can use SQL Profiler to check what is being passed to the Database.