CODEWITHSUNDEEP
javaandroidsqlite
user2534554
user2534554

Reputation: 21

No column name - after it was created

I am sure I am doing something wrong but failed, after put a lot of time into it, to identify what. In short the error is that a certain column does not exist but I create it just before:

static final String itemTable="ITEMS";
static final String colItemID="ItemID";
static final String colItemName="ItemName";
static final String colQuantity="ItemQuantity";
static final String colPrice="ItemPrice";
static final String colCategory="ItemCategory";
static final String colAisle="ItemAisle";
static final String colCreator="ItemCreator";
static final String colActMemID="ItemActivityMemberID";
static final String colDate="ItemDate";
static final String colHistory="ItemHistory";
static final String colPicture="ItemPicture";

the create table command:

try {
db.execSQL("CREATE TABLE " + itemTable + " " + "(" + colItemID + 
" INTEGER PRIMARY KEY AUTOINCREMENT, " + colItemName + " TEXT, " + 
colQuantity + " Integer, " + colPrice + "REAL, " + colCategory + " TEXT, " 
+ colAisle + " TEXT, " + colCreator + " TEXT, " + colActMemID + " TEXT, " + 
colDate + " TEXT, " + colHistory + " TEXT, " + colPicture + " TEXT)");
} catch (SQLException e) {
System.out.println(e);
}

and then, when I try to create a view:

try {
    db.execSQL("CREATE VIEW "+viewList+
        " AS SELECT "+itemTable+"."+colItemID+" AS _id,"+
        " "+itemTable+"."+colItemName+","+
        " "+itemTable+"."+colPrice+","+
        " "+itemTable+"."+colQuantity+","+

        " "+membersTable+"."+colMemName+""+
        " FROM "+itemTable+" JOIN "+membersTable+
        " ON "+itemTable+"."+colActMemID+" ="+membersTable+"."+colMemID
        );
} catch (SQLException e) {
    // TODO Auto-generated catch block
    System.out.println(e);
}

he complain a bout specific column:

07-22 10:33:26.548: I/System.out(1753): back from db construct
07-22 10:33:26.628: E/SQLiteLog(1753): (1) no such column: ITEMS.ItemPrice
07-22 10:33:26.638: D/AndroidRuntime(1753): Shutting down VM
07-22 10:33:26.638: W/dalvikvm(1753): threadid=1: thread exiting with uncaught exception (group=0x40a71930)
07-22 10:33:26.688: E/AndroidRuntime(1753): FATAL EXCEPTION: main
07-22 10:33:26.688: E/AndroidRuntime(1753): android.database.sqlite.SQLiteException: no such
column: ITEMS.ItemPrice (code 1): , while compiling: CREATE VIEW ViewList AS SELECT ITEMS.ItemID
AS _id, ITEMS.ItemName, ITEMS.ItemPrice, ITEMS.ItemQuantity, MEMBERS.MemberName FROM ITEMS JOIN
MEMBERS ON ITEMS.ItemActivityMemberID =MEMBERS.MemberID
07-22 10:33:26.688: E/AndroidRuntime(1753):     at 
android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
07-22 10:33:26.688: E/AndroidRuntime(1753):     at 
android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
07-22 10:33:26.688: E/AndroidRuntime(1753):     at 
android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
07-22 10:33:26.688: E/AndroidRuntime(1753):     at android.database.sqlite.SQLiteSession.prepare
(SQLiteSession.java:588)

I looked in as many answers as I could but found no clue, your help will be highly appreciated!

Tnx,

Gal

Upvotes: 1

Views: 80

Answers (1)

Reimeus
Reimeus

Reputation: 159784

You're missing a space character after colPrice so the column name will be ItemPriceREAL instead. Change to

try {
   db.execSQL("CREATE TABLE " + itemTable + " " + "(" + colItemID + 
      " INTEGER PRIMARY KEY AUTOINCREMENT, " + colItemName + " TEXT, " + 
      colQuantity + " Integer, " + colPrice + " REAL, " + colCategory + " TEXT, " 
      + colAisle + " TEXT, " + colCreator + " TEXT, " + colActMemID + " TEXT, " + 
      colDate + " TEXT, " + colHistory + " TEXT, " + colPicture + " TEXT)");
} catch (SQLException e) {
   System.out.println(e);
}

In general, it's always better to avoid String concatenation when building SQL queries to protect against SQL injection attacks. In this case it (having the query as a single String literal) would have highlighted the missing space.

Upvotes: 4

Related Questions