Getting Error in SQLite ios?

Question

I’m building an iOS app using storyboards.I created a table using SQLite.

I am facing a problem in insertion of data shown me an error:

Failed to insert record  rc:1, msg=no such column: Afternoon

Here is my code:

Create table code:

char * query ="CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY AUTOINCREMENT, eventid  TEXT, addinfo TEXT,date TEXT,players TEXT, sportsname TEXT, Time TEXT,userid TEXT, venue TEXT)";

Insertion in a table function:

-(int) insert:(NSString *)filePath withName:(NSString *)eventid addinfo:(NSString*)addinfo date:(NSString*)date 
players:(NSString*)players sportsname:(NSString*)sportsname Time:(NSString*)Time userid:(NSString*)userid  venue:(NSString*)venue {
    sqlite3* db = NULL;
    int rc=0;
    rc = sqlite3_open_v2([filePath cStringUsingEncoding:NSUTF8StringEncoding], &db, SQLITE_OPEN_READWRITE , NULL);

   if (SQLITE_OK != rc) {
       sqlite3_close(db);
       NSLog(@"Failed to open db connection");
   }
   else {
       NSString * query  = [NSString stringWithFormat:@"INSERT INTO events  (eventid,addinfo,date,players,sportsname,Time,userid,venue) 
                           VALUES (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",%@,\"%@\",\"%@\")", eventid,addinfo,date,players,sportsname,Time,userid,venue];

       char * errMsg;
       rc = sqlite3_exec(db, [query UTF8String] ,NULL,NULL,&errMsg);

      if (SQLITE_OK != rc) {
          NSLog(@"Failed to insert record  rc:%d, msg=%s",rc,errMsg);
      }
      sqlite3_close(db);
   }
   return rc;
}

Get record function

-(NSArray *) getRecords:(NSString*) filePath where:(NSString *)whereStmt {
    NSMutableArray * events =[[NSMutableArray alloc] init];
    sqlite3* db = NULL;
    sqlite3_stmt* stmt =NULL;
    int rc=0;
    rc = sqlite3_open_v2([filePath UTF8String], &db, SQLITE_OPEN_READONLY , NULL);

    if (SQLITE_OK != rc) {
        sqlite3_close(db);
        NSLog(@"Failed to open db connection");
    } else {
        NSString  * query = @"SELECT * from events";

        if(whereStmt) {
            query = [query stringByAppendingFormat:@" WHERE %@",whereStmt];
        }

        rc =sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, NULL);
        if(rc == SQLITE_OK) {

            while (sqlite3_step(stmt) == SQLITE_ROW) //get each row in loop
            {
                NSString * eventid = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
                NSString *addinfo = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
                NSString *date = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
                NSString *players =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 4)];
                NSString *sportsname =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 5)];
                NSString *Time =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 6)];
                NSString *userid =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 7)];
                NSString *venue =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 8)];
                NSDictionary *student = [NSDictionary dictionaryWithObjectsAndKeys:eventid,@"eventid",addinfo,@"addinfo",date,@"date",players,@"players",sportsname,@"sportsname",Time,@"Time",userid,@"userid",venue,@"venue",nil];

                [events addObject:student];
                NSLog(@"eventsid= %@, userid=%@ , sportsname= %@ , date= %@ , Time= %@,players= %@, venue= %@, addinfo=%@",eventid,userid,sportsname,date,Time,players,venue,addinfo);
            }
            NSLog(@"Done");
            sqlite3_finalize(stmt);
        }
        else {
            NSLog(@"Failed to prepare statement with rc:%d",rc);
        }
        sqlite3_close(db);
    }
    return events;
}  


NSData* data = [NSData dataWithContentsOfURL:url];
NSArray *ys_avatars = [NSJSONSerialization JSONObjectWithData:data options:0 error:nil];
NSLog(@"arrayyyyyy=%@",ys_avatars);

if(ys_avatars) {

for (int j=0;j<ys_avatars.count;j++) {
    int rc= [self insert:[self getDbFilePath] withName:ys_avatars[j][@"_id"] addinfo:ys_avatars[j][@"addinfo"] date:ys_avatars[j][@"date"] players:ys_avatars[j][@"players"] sportsname:ys_avatars[j][@"sportsname"] Time:ys_avatars[j][@"time"] userid:ys_avatars[j][@"userid"] venue:ys_avatars[j][@"venue"]];

    if(rc != SQLITE_OK) {
        [self showMessage:@"ERROR" withMessage:@"Failed to insert record"];      
    } 
    else
        [self showMessage:@"SUCCESS" withMessage:@"Record is added"];

    NSArray * events = [self getRecords:[self getDbFilePath] where:nil];

Lalji · Accepted Answer · 2014-12-04 06:33:00Z

4

Your problem will be solved after,Just replace this line from

NSString * query  = [NSString
                         stringWithFormat:@"INSERT INTO events  (eventid,addinfo,date,players,sportsname,Time,userid,venue) VALUES (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",%@,\"%@\",\"%@\")",eventid,addinfo,date,players,sportsname,Time,userid,venue];

To

NSString * query  = [NSString
                             stringWithFormat:@"INSERT INTO events  (eventid,addinfo,date,players,sportsname,Time,userid,venue) VALUES (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\")",eventid,addinfo,date,players,sportsname,Time,userid,venue];

answered Dec 4, 2014 at 6:33

Lalji

6951 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hitesh Over a year ago

Thanks Lalji,i'm lauhing how i missed this.

Mehul Patel · Accepted Answer · 2014-12-04 06:45:00Z

The possible reason here is table column value is misleading with the table column name. I mean the record information for the table events is consider some where as column name.

From error it self you can get the details that record information Afternoon which is suppose to be a column value is consider as a column name.

So query string creation is wrong here:

Try to create query like this will minimise the possibilities of this kind of issues:

NSString * query  = [NSString stringWithFormat:@"INSERT INTO events (eventid, addinfo, date, players, sportsname, Time, userid, venue) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)"];

const char *insert_stmt = [query UTF8String];
if (sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {
    sqlite3_bind_text( statement, 1, [eventid UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(statement, 2, [addinfo UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_blob( statement, 3, [date UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text( statement, 4, [players UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text( statement, 5, [sportsname UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text( statement, 6, [Time UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text( statement, 7, [userid UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text( statement, 8, [venue UTF8String], -1, SQLITE_TRANSIENT);
}

Collectives™ on Stack Overflow

Getting Error in SQLite ios?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related