Why is this SQLite Insert failing?

Question

I've got this code to insert 1..N records in a SQLite database in a Windows CE / Compact Framework app:

void IPlatypusDBUtils.SaveSiteMappingData(IEnumerable<SiteMapping> siteMappings)
{
    ExceptionLoggingService.Instance.WriteLog("Reached TestPlatypusDBUtils.SaveSiteMappingData");
    IEnumerable<SiteMapping> siteMaps = siteMappings;
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(PlatypusUtils.GetDBConnection()))
        {
            conn.Open();
            foreach (SiteMapping sm in siteMaps)
            {
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {   
                    cmd.CommandText =
                            @"INSERT INTO SiteMapping (Id, SiteNumber, LocationNumber, SiteName) VALUES (@Id, @SiteNumber, @LocationNumber, @SiteName)";
                    cmd.Parameters.Add(new SQLiteParameter("LocationNumber", sm.Id));
                    cmd.Parameters.Add(new SQLiteParameter("SiteNumber", sm.SiteNumber.ToString())); // or Platypusconsts.currentsitenum ?
                    cmd.Parameters.Add(new SQLiteParameter("LocationNumber", sm.LocationNumber));
                    cmd.Parameters.Add(new SQLiteParameter("SiteName", sm.SiteName));
                    cmd.ExecuteNonQuery();
                } // using (SQLiteCommand cmd
            } // foreach (SiteMapping sm in siteMaps)
            conn.Close();
        } // using (SQLiteConnection conn
    } // try
    catch (Exception ex)
    {
        String msgInnerExAndStackTrace = String.Format(
            "{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message, ex.InnerException, ex.StackTrace);               ExceptionLoggingService.Instance.WriteLog(String.Format("Exception in TestPlatypusDBUtils.SaveSiteMappingData: {0}", msgInnerExAndStackTrace));
    } // catch
}

...but it fails to insert any records. The log file contains "Reached TestPlatypusDBUtils.SaveSiteMappingData" (the Insert method is reached) but not "Exception in TestPlatypusDBUtils.SaveSiteMappingData". Is ExecuteNonQuery() not the right call for an Insert, or what is the problem?

UPDATE

I added this just below the first log write:

MessageBox.Show(String.Format("siteMappings about to be inserted into SiteMapping table: {0}", siteMappings));

...and I see this:

enter image description here

I don't know if this means there's actually nothing in SiteMappings, or that I'm trying to view the contents in the wrong way. I first appended a ".ToString()" but it was grayed out, so thought it was moot and would be mute.

UPDATE 2

Apparently the fundamental problem is that no SiteMappings are being passed to the method. I added this:

foreach (SiteMapping sm in siteMaps)
{
    MessageBox.Show(String.Format("this siteMap is {0}", sm));

...and never see "this siteMap is [bla]".

For future reference, the scream shot above shows what's contained in an "empty" IEnumerable.

UPDATE 3

It turns out it was a data problem (GIGO). Or, garbage trying to go in doesn't go in.

also move this line `cmd.CommandText = @"INSERT INTO SiteMapping (Id, SiteNumber, LocationNumber, SiteName) VALUES (@Id, @SiteNumber, @LocationNumber, @SiteName)";` outside of the foreach loop — MethodMan, Jan 16 '15 at 19:04
I don't see how to do that; it has to be in the "using cmd" block, and that has to be, AFAICT, within the foreach loop. — B. Clay Shannon-B. Crow Raven, Jan 16 '15 at 19:09
NO it doesn't I dont think that you understand how this works.. why should you be redefining or reassigning the same variable text multiple times.. this is only needed once.. and if you are not familiar with paramerized query's then you should read up on it..`cmd.CommandText =` is not changing only the parametervalues which you are passing by `@param, paramvalue` — MethodMan, Jan 16 '15 at 19:13
So what you're saying is make the string const and then assign it to CommandText. I'll do that, but I think that's a nicety for performance's sake, not a bugmaker. — B. Clay Shannon-B. Crow Raven, Jan 16 '15 at 19:21
Clay Shannon! :) It looks like you need to name your parameters to match your SQL statement. — , Jan 16 '15 at 20:06

score 3 · Accepted Answer · answered Jan 16 '15 at 19:03

3

You have LocationNumber mantioned twice.

     ....
      //SHOULD BE [ID], PROBABBLY
      cmd.Parameters.Add(new SQLiteParameter("LocationNumber", sm.Id)); 


      cmd.Parameters.Add(new SQLiteParameter("SiteNumber", sm.SiteNumber.ToString()));                       
cmd.Parameters.Add(new SQLiteParameter("LocationNumber", sm.LocationNumber));
      cmd.Parameters.Add(new SQLiteParameter("SiteName", sm.SiteName));
       ....

answered Jan 16 '15 at 19:03

Tigran

61,654
8
86
123

@B.ClayShannon it could fail for 2 reasons that I can see off the bat.. #1. what if the Id is an auto generated field..? then you don't pass the id #2 the location number could be a varchar.. and you're trying to insert a varchar into an INT field wont work.. have you debugged this..? – MethodMan Jan 16 '15 at 19:10
there could be different resasons: for example you sqlite driver may not accept 2 parameters with the same name, your sqllite setup may not accept autocinrement column (which should be [ID], if you don't want to insert its value esplicitly) . More on autoincrement in sqlite (http://www.sqlite.org/autoinc.html) – Tigran Jan 16 '15 at 19:11
@MethodMan: Even with it fixed, it doesn't work. And I'm only trying to insert one record for now. – B. Clay Shannon-B. Crow Raven Jan 16 '15 at 19:20
@B.ClayShannon: do you get some exception, or something .. ? – Tigran Jan 16 '15 at 19:20
@Tigran: No, as mentioned, the exception string is not logged, and no exception is thrown visibly, either. – B. Clay Shannon-B. Crow Raven Jan 16 '15 at 19:22

score 1 · Answer 2 · 2015-01-16T20:21:50.163

My guess is someone before you decided that site mappings was a way they could store information on Windows CE without actually needing to create a new file.

Why IEnumerable, though? How about passing a List instead?

So, on your server, what do the fields look like?

I have not used SQLite in 5+ years, and even then I didn't use it for long.

If SQLite supports named parameters, you could do something like this (which is based off of Microsoft T-SQL):

private void IPlatypusDBUtils.SaveSiteMappingData(List<TSql> siteMappings) {
    var isnull = (siteMapping == null) ? "True" : "False";
    var count = (siteMapping == null) ? "-1" : siteMapping.Count.ToString();
    ExceptionLoggingService.Instance.WriteLog(
      String.Format("siteMapping is null? {0} siteMapping Count: {1}",
      isnull, count));
    string sqlCmd =
        @"INSERT INTO SiteMapping " +
        "(Id, SiteNumber, LocationNumber, SiteName) " +
        "VALUES " +
        "(@Id, @SiteNumber, @LocationNumber, @SiteName)";
    using (var cmd = new SQLiteCommand(sqlCmd, new SQLiteConnection(PlatypusUtils.GetDBConnection()))) {
        // Setup your parameters here,
        // using the data types found in your database,
        // before ever opening the database connection.
        cmd.Parameters.Add("@Id", DbType.Int);
        cmd.Parameters.Add("@SiteNumber", DbType.VarChar, 50); // or Platypusconsts.currentsitenum ?
        cmd.Parameters.Add("@LocationNumber", DbType.Int);
        cmd.Parameters.Add("@SiteName", DbType.VarChar, 50);
        try { // now open your connection
            cmd.Connection.Open();
            foreach (var sm in siteMappings) {
                cmd.Parameters["@Id"].Value = sm.Id;
                cmd.Parameters["@SiteNumber"].Value = sm.SiteNumber.ToString();
                cmd.Parameters["@LocationNumber"].Value = sm.LocationNumber;
                cmd.Parameters["@SiteName"].Value = sm.SiteName;
                cmd.ExecuteNonQuery();
            }
        } catch (SQLiteException ex) {
            // Focus your Exception on the SQLiteException
            String msgInnerExAndStackTrace = String.Format(
                    "{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message, ex.InnerException, ex.StackTrace);
            ExceptionLoggingService.Instance.WriteLog(String.Format("Exception in TestPlatypusDBUtils.SaveSiteMappingData: {0}", msgInnerExAndStackTrace));
        } finally {
            // Placing your Close command here will work even if
            // the code throws an exception.
            cmd.Connection.Close();
        }
    }
}

Since I don't have SQLite installed anywhere, I can't test it for you.

If it works, though, you would only need to create a single command and only open a single database connection.

LOL - I've been doing a lot of PHP. Hey, also check that your incoming sitemapping is not null and contains values. — , Jan 16 '15 at 20:07
Yes, it's a simple class, used to parallel the SiteMapping table's structure. BTW, PHP stands for Pointy Haired ...what? — B. Clay Shannon-B. Crow Raven, Jan 16 '15 at 20:14
I edited the method to accept a **List**, so you can see the number of items passed in. Are you able to edit your code to use that? Usually, a List will work for IEnumerable. — , Jan 16 '15 at 20:22

Why is this SQLite Insert failing?

UPDATE

UPDATE 2

UPDATE 3

2 Answers2