Sqlite3 INSERT INTO Question × 377

Question

I am creating an exercise app that will record the weight used and the number of "reps" the user did in 4 "Sets" per day over a period of 7 days so the user may view their progress.

I have built the database table named FIELDS with 2 columns ROW and FIELD_DATA and I can use the code below to load the data into the db. But the code has a sql statement that says,

INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA)VALUES (%d, '%@'); 

When I change the statment to:

INSERT INTO FIELDS (ROW, FIELD_DATA)VALUES (%d, '%@'); 

Nothing happens. That is no data is recorded in the db.

Below is the code:

#define kFilname @"StData.sqlite3"

- (NSString *)dataFilePath
{
  NSArray *paths = NSSearchPathForDirectoriesInDomains
    (NSDocumentDirectory, NSUserDomainMask, YES);
  NSString *documentsDirectory = [paths objectAtIndex:0];
  return [documentsDirectory stringByAppendingPathComponent:kFilname];
}

-(IBAction)saveData:(id)sender;
{

  for (int i = 1; i <= 8; i++)
  {
    NSString *fieldName = [[NSString alloc]initWithFormat:@"field%d", i];
    UITextField *field = [self valueForKey:fieldName];
    [fieldName release];

    NSString *insert = [[NSString alloc] initWithFormat:
        @"INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA)
          VALUES (%d, '%@');",i, field.text];
    // sqlite3_stmt *stmt;

    char *errorMsg;

    if (sqlite3_exec (database, [insert UTF8String],
        NULL, NULL, &errorMsg) != SQLITE_OK)
    {
      // NSAssert1(0, @"Error updating table: %s", errorMsg);
      sqlite3_free(errorMsg);
    }  
  }
  sqlite3_close(database);    
}

So how do I modify the code to do what I want? It seemed like a simple sql statement change at first but obviously there must be more. I am new to Objective-C and iPhone programming. I am not new to using sql statements as I have been creating web apps in ASP for a number of years.

Any help will be greatly appreciated, this is driving me nuts!

Answer 1

Suggestions:

• write an insert statement with hardcoded values to see if the insert works
• your filename has no path. does it assume the current directory when executed? what directory is it running from?
• write a message to the screen if possible to see what the values you're getting are. Are they correct?