I have a project where I have to make "shoping list". It's my first time with SQlite DB.
I got:
E/SQLiteLog: (1) near "list": syntax error
**E/SQLiteDatabase: Error inserting name list=
android.database.sqlite.SQLiteException: near "list": syntax error (code 1): , while compiling: INSERT INTO LISTS(name list) VALUES (?)**
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
at com.listof.myapplication.ListsManagerDAO.insertLists**(ListsManagerDAO.kt:37)**
at com.listof.myapplication.MainActivity$adding$1.onClick**(MainActivity.kt:50)**
at android.view.View.performClick(View.java:4780)
at android.view.View$PerformClick.run(View.java:19866)
at android.os.Handler.handleCallback(Handler.java:739)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:135)
at android.app.ActivityThread.main(ActivityThread.java:5254)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
and my code:
class ListsHelper(context: Context) : SQLiteOpenHelper(context, DB_NAME, null, DB_VERSION) {
override fun onCreate(db: SQLiteDatabase?) {
db!!.execSQL(CREATE_TABLE_LISTS)
db!!.execSQL(CREATE_TABLE)
//db!!.execSQL("CREATE TABLE IF NOT EXISTS"+ TABLE_LISTS +"("+LIST_ID +"INTEGER PRIMARY KEY,"+ NAME_LIST +"TEXT);")
}
override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
db!!.execSQL("DROP TABLE IF EXISTS $TABLE_PRODUCT")
db!!.execSQL("DROP TABLE IF EXISTS $TABLE_LISTS")
onCreate(db)
}
companion object {
// Tables Name
val TABLE_PRODUCT = "PRODUCTS"
val TABLE_LISTS = "LISTS"
// Tables columns
val LIST_ID= "_id"
val NAME_LIST = "name_list"
val _ID = LIST_ID
val PRODUCT = "product"
val AMOUNT = "amount"
// Database Information
internal val DB_NAME = "LISTS.DB"
// database version
internal val DB_VERSION = 1
// Creating tables query
/*private val CREATE_TABLE_LISTS = ("create table " + TABLE_LISTS + "("
+ LIST_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ NAME_LIST + " TEXT NOT NULL,"
+_ID+ "INTEGER,"+" FOREIGN KEY("+_ID+ ") REFERENCES "+ TABLE_PRODUCT+"("+_ID+")")*/
private val CREATE_TABLE_LISTS = ("create table " + TABLE_LISTS + "("
+ LIST_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ NAME_LIST + " TEXT NOT NULL"+");")
private val CREATE_TABLE = ("create table " + TABLE_PRODUCT + "(" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + PRODUCT + " TEXT NOT NULL, " + AMOUNT + " TEXT)")
}
}
ListManagerDAO code:
class ListsManagerDAO(private val context: Context) {
private var dbHelper: ListsHelper? = null
private var database: SQLiteDatabase? = null
@Throws(SQLException::class)
fun open(): ListsManagerDAO {
dbHelper = ListsHelper(context)
database = dbHelper!!.writableDatabase
return this
}
fun close() {
dbHelper!!.close()
}
fun insertLists(lists:String){
val listContentValue= ContentValues().apply {
put(ListsHelper.NAME_LIST,lists)
}
database!!.insert(ListsHelper.TABLE_LISTS, null, listContentValue)
}
fun fetchLists(): Cursor? {
val columns = arrayOf(ListsHelper.LIST_ID, ListsHelper.NAME_LIST)
val cursor = database!!.query(ListsHelper.TABLE_LISTS, columns, null, null, null, null, null)
cursor?.moveToFirst()
return cursor
}
fun updateLists(_id: Long, lists: String){
val listContentValue= ContentValues().apply {
put(ListsHelper.NAME_LIST,lists)
}
database!!.update(ListsHelper.TABLE_LISTS, listContentValue, ListsHelper._ID + " = " + _id, null)
}
fun delete(_id: Long) {
database!!.delete(ListsHelper.TABLE_LISTS, ListsHelper.LIST_ID + "=" + _id, null)
}
}
MainActivity code:
class MainActivity : AppCompatActivity(){
private var dbManager: ListsManagerDAO? = null
private var list: ListView? = null
private var adapter: SimpleCursorAdapter? = null
private var addtext: EditText? = null
internal var add: ImageView? = null
internal val from = arrayOf(ListsHelper.LIST_ID,ListsHelper.NAME_LIST)
internal val to = intArrayOf(R.id.list)
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
dbManager = ListsManagerDAO(this)
dbManager?.open()
val cursor = dbManager?.fetchLists()
list = findViewById(R.id.list) as ListView
//list!!.emptyView = findViewById(R.id.editTextView)
addtext = findViewById(R.id.editTextView) as EditText
add = findViewById(R.id.imgViewAdd) as ImageView
adapter=SimpleCursorAdapter(this,android.R.layout.simple_list_item_single_choice, cursor, from, to, 0)
adapter!!.notifyDataSetChanged()
list!!.adapter = adapter
adding()
}
fun adding() {
add?.setOnClickListener(object : OnClickListener {
override fun onClick(v: View) {
val listName: String = addtext!!.getText().toString()
dbManager!!.insertLists(listName)
}
})
override fun onDestroy() {
super.onDestroy()
dbManager?.close()
}
}
I don't see any "missing quota" or soemthing like in: Android - SQLite - syntax error (code 1): , while compiling: CREATE TABLE or: table has no column named PosY (code 1): , while compiling: INSERT INTO Sample(Name,No,PosY,Img,PosX) VALUES (?,?,?,?,?)
