CODEWITHSUNDEEP
c#sql-server-ce
Ben
Ben

Reputation: 669

Having trouble with a SQL Server CE insertion function

I've written the below function, which errors out correctly with non-int input and with int input returns that the audit was started properly. Unfortunately when I check the table I see that the data was never actually inserted.

Any suggestions for what I'm doing wrong?

public string SqlLocation = "Data Source="+ new FileInfo(Path.GetDirectoryName(Application.ExecutablePath) + "\\DRAssistant.sdf");

public string StartAudit(string sqlLocation, string dps)
{
  int dpsInteger;
  if (!int.TryParse(dps, out dpsInteger))
    return "DPS is not a number!";

  try
  {
    var myConnection = new SqlCeConnection(sqlLocation);
    myConnection.Open();
    var myCommand = myConnection.CreateCommand();
    myCommand.CommandText = string.Format("SELECT dps FROM DispatchReviews 
        WHERE dps = {0}", dpsInteger);
    SqlCeDataReader reader = myCommand.ExecuteReader();
    if (reader.Read())
      { return "DPS review has already started!"; }

    myCommand.CommandText = "INSERT INTO DispatchReviews (dps, starttime, 
        reviewer) VALUES (@dps, @starttime, @reviewer)";

    myCommand.Parameters.AddWithValue("@dps", dpsInteger);
    myCommand.Parameters.AddWithValue("@starttime", DateTime.Now);
    myCommand.Parameters.AddWithValue("@reviewer", Environment.UserName);
    myCommand.Prepare();
    myCommand.ExecuteNonQuery();

    myCommand.Dispose();
    myConnection.Close();
    return "Dispatch Review Started!";
    }
  catch(Exception ex)
    { return "Unable to save DPS!" + ex.Message; }
  }

Edit: Turns out this was just an idiot problem--which anybody looking at the SqlLocation could probably figure out--in that every time I built the application a new copy of the .sdf was copied into the application directory, overwriting the previous one. Also, the database I was checking for updates was not the one in the execution directory, but the one that was being copied into it, which is why it was always empty. I noticed this because when I tried to add the same DPS multiple times the first time I would get the DPS review started message, but subsequent attempts would give the error that it had previously been created.

Upvotes: 1

Views: 69

Answers (1)

marc_s
marc_s

Reputation: 755321

Can you please show us your connection string??

Most likely, if you test this inside Visual Studio, the database file is being copied around (from your initial directory to the output directory where the app runs) and your INSERT will probably work just fine - but you're just looking at the wrong file when you check that fact.

Upvotes: 1

Related Questions