CODEWITHSUNDEEP
iphonedatabasexcodeipadsqlite
Anand
Anand

Reputation: 3406

Data insertion problem in sqlite

My table definition is: CREATE TABLE "Product" ("ProID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "BarCode" VARCHAR NOT NULL UNIQUE , "ProductName" VARCHAR NOT NULL );

when i am inserting data i have the problem like: 2011-03-25 15:43:08.792 BarCode12[6698:207] * Assertion failure in -[sqlClass addRecord:], /Users/admin/Desktop/BarCode14copy/Classes/sqlClass.m:121 2011-03-25 15:43:08.793 BarCode12[6698:207] * Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Error while inserting data. 'constraint failed'' ...

The code written is: - (void) addRecord:(NSMutableDictionary *)recordDict {

if(addStmt == nil) {
    const char *sql = "insert into Product(ProID, BarCode , ProductName ) Values(?,?,?)";
    if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
        NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
}
NSInteger recordId =  [[recordDict objectForKey:@"ProID"] intValue];
NSLog(@"%d",recordId);
sqlite3_bind_int(addStmt, 1,recordId);
sqlite3_bind_text(addStmt, 2, [[recordDict objectForKey:@"BarCode"] UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(addStmt, 3, [[recordDict objectForKey:@"ProductName"] UTF8String], -1, SQLITE_TRANSIENT);


if(SQLITE_DONE != sqlite3_step(addStmt))
    NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
else
    sqlite3_last_insert_rowid
    rowID = sqlite3_last_insert_rowid(database);
NSLog(@"last inserted rowId = %d",rowID);

sqlite3_reset(addStmt);

}

i am also tried without constraints but the same problem will continues..

if the consistency is removed then it shows "database Locked" error.. how can we remove this error and how to insert the data into database?

Upvotes: 3

Views: 2705

Answers (1)

Martijn
Martijn

Reputation: 13622

The bit about "constraint failed" says that you are violating a constraint. That means your Product table has PRIMARY KEY, UNIQUE or FOREIGN KEY constraints, and that you’re inserting values which would violate those constraints.

  • For PRIMARY KEY and UNIQUE constraints: you’re inserting a duplicate value.
  • For FOREIGN KEY constraints: you’re inserting a value that has no corresponding match in the referenced table/field.

Update: When you have an INTEGER PRIMARY KEY AUTOINCREMENT, then you shouldn’t specify a ProdID in your INSERT statement; SQLite will generate it automatically. So change your insert statement to

INSERT INTO Product (BarCode , ProductName) Values (?,?)

since your already retrieve it afterward with

rowID = sqlite3_last_insert_rowid(database);

Don’t forget to remove the binding, too!

Upvotes: 3

Related Questions