c#xamarin.androidandroid-sqlite
no such column: EPF (code 1 SQLITE_ERROR) Xamarin android
This is my second time building a database and everything looks perfectly fine compared to my first database that works, but when I run it I get this error:
Android.Database.Sqlite.SQLiteException: no such column: EPF (code 1 SQLITE_ERROR): , while compiling: SELECT Id, Name, Age, PCB, EPF, SOCSO, EIS, GrossSalary, NetSalary, EmployerEPF, EmployerSOCSO, EmployerEIS FROM payrollTable
at Java.Interop.JniEnvironment+InstanceMethods.CallObjectMethod (Java.Interop.JniObjectReference instance, Java.Interop.JniMethodInfo method, Java.Interop.JniArgumentValue* args) [0x0006e] in <89755ea61d9c4ae0a40ce90b872c9e2d>:0
at Java.Interop.JniPeerMembers+JniInstanceMethods.InvokeVirtualObjectMethod (System.String encodedMember, Java.Interop.IJavaPeerable self, Java.Interop.JniArgumentValue* parameters) [0x0002a] in <89755ea61d9c4ae0a40ce90b872c9e2d>:0
at Android.Database.Sqlite.SQLiteDatabase.Query (System.String table, System.String[] columns, System.String selection, System.String[] selectionArgs, System.String groupBy, System.String having, System.String orderBy) [0x000e6] in <2df86f44c8e946618f31cb3aed952f5b>:0
at PayrollParrots.Helper.PayrollHelper.GetPayrollList (Android.Content.Context context) [0x0007e] in /Users/danyalqureshi/Projects/PayrollParrots/PayrollParrots/Helper/PayrollHelper.cs:103
at PayrollParrots.MainActivity.BindData () [0x00001] in /Users/danyalqureshi/Projects/PayrollParrots/PayrollParrots/MainActivity.cs:93
at PayrollParrots.MainActivity.OnCreate (Android.OS.Bundle savedInstanceState) [0x00055] in /Users/danyalqureshi/Projects/PayrollParrots/PayrollParrots/MainActivity.cs:35
at Android.App.Activity.n_OnCreate_Landroid_os_Bundle_ (System.IntPtr jnienv, System.IntPtr native__this, System.IntPtr native_savedInstanceState) [0x0000f] in <2df86f44c8e946618f31cb3aed952f5b>:0
at at (wrapper dynamic-method) Android.Runtime.DynamicMethodNameCounter.4(intptr,intptr,intptr)
at android.database.sqlite.SQLiteException: no such column: EPF (code 1 SQLITE_ERROR): , while compiling: SELECT Id, Name, Age, PCB, EPF, SOCSO, EIS, GrossSalary, NetSalary, EmployerEPF, EmployerSOCSO, EmployerEIS FROM payrollTable
at at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
at at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
at at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
at at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1408)
at at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1255)
at at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1126)
at at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1294)
at at crc642ab3dab37b1a36d6.MainActivity.n_onCreate(Native Method)
at at crc642ab3dab37b1a36d6.MainActivity.onCreate(MainActivity.java:29)
at at android.app.Activity.performCreate(Activity.java:7136)
at at android.app.Activity.performCreate(Activity.java:7127)
at at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
at at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
at at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
at at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
at at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
at at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
at at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
at at android.os.Handler.dispatchMessage(Handler.java:106)
at at android.os.Looper.loop(Looper.java:193)
at at android.app.ActivityThread.main(ActivityThread.java:6669)
at at java.lang.reflect.Method.invoke(Native Method)
at at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
This is my Database code:
using System.Collections.Generic;
using Android.Content;
using Android.Database.Sqlite;
using PayrollParrots.Model;
using Android.Database;
namespace PayrollParrots.Helper
{
public class PayrollHelper
{
private const string TableName = "payrollTable";
private const string ColumnID = "Id";
private const string ColumnName = "Name";
private const string ColumnAge = "Age";
private const string ColumnPCB = "PCB";
private const string ColumnEPF = "EPF";
private const string ColumnSOCSO = "SOCSO";
private const string ColumnEIS = "EIS";
private const string ColumnGross = "GrossSalary";
private const string ColumnNet = "NetSalary";
private const string ColumnEmployerEPF = "EmployerEPF";
private const string ColumnEmployerSOCSO = "EmployerSOCSO";
private const string ColumnEmployerEIS = "EmployerEIS";
public const string CreateQuery = "CREATE TABLE " + TableName + " ( "
+ ColumnID + " INTEGER PRIMARY KEY,"
+ ColumnName + " TEXT,"
+ ColumnAge + " INTEGER,"
+ ColumnPCB + " BLOB,"
+ ColumnEPF + " BLOB,"
+ ColumnSOCSO + " BLOB,"
+ ColumnEIS + " BLOB,"
+ ColumnGross + " BLOB,"
+ ColumnNet + " BLOB,"
+ ColumnEmployerEPF + " BLOB,"
+ ColumnEmployerSOCSO + " BLOB,"
+ ColumnEmployerEIS + " BLOB)";
public const string DeleteQuery = "DROP TABLE IF EXISTS " + TableName;
public PayrollHelper()
{
}
public static void InsertPayrollData(Context context, Payroll payroll)
{
SQLiteDatabase db = new DataStore(context).WritableDatabase;
ContentValues contentValues = new ContentValues();
contentValues.Put(ColumnName, payroll.Name);
contentValues.Put(ColumnAge, payroll.Age);
contentValues.Put(ColumnPCB, payroll.PCB);
contentValues.Put(ColumnEPF, payroll.EPF);
contentValues.Put(ColumnSOCSO, payroll.SOCSO);
contentValues.Put(ColumnEIS, payroll.EIS);
contentValues.Put(ColumnGross, payroll.GrossSalary);
contentValues.Put(ColumnNet, payroll.NetSalary);
contentValues.Put(ColumnEmployerEPF, payroll.EmployerEPF);
contentValues.Put(ColumnEmployerSOCSO, payroll.EmployerSOCSO);
contentValues.Put(ColumnEmployerEIS, payroll.EmployerEIS);
db.Insert(TableName, null, contentValues);
db.Close();
}
public static Payroll SelectPayroll(Context context, int payrollId)
{
Payroll payroll;
SQLiteDatabase db = new DataStore(context).ReadableDatabase;
string[] columns = new string[] { ColumnID, ColumnName, ColumnAge, ColumnPCB, ColumnEPF, ColumnSOCSO, ColumnEIS, ColumnGross, ColumnNet, ColumnEmployerEPF, ColumnEmployerSOCSO, ColumnEmployerEIS };
using (ICursor cursor = db.Query(TableName, columns, ColumnID + "=" + payrollId, null, null, null, null))
{
if (cursor.MoveToNext())
{
payroll = new Payroll
{
Id = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnID)),
Name = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnName)),
Age = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnAge)),
PCB = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnPCB)),
EPF = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEPF)),
SOCSO = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnSOCSO)),
EIS = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEIS)),
GrossSalary = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnGross)),
NetSalary = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnNet)),
EmployerEPF = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerEPF)),
EmployerEIS = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerSOCSO)),
EmployerSOCSO = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerEIS))
};
}
else
{
payroll = null;
}
}
return payroll;
}
public static List<Payroll> GetPayrollList(Context context)
{
List<Payroll> payroll = new List<Payroll>();
SQLiteDatabase db = new DataStore(context).ReadableDatabase;
string[] columns = new string[] { ColumnID, ColumnName, ColumnAge, ColumnPCB, ColumnEPF, ColumnSOCSO, ColumnEIS, ColumnGross, ColumnNet, ColumnEmployerEPF, ColumnEmployerSOCSO, ColumnEmployerEIS };
using (ICursor cursor = db.Query(TableName, columns, null, null, null, null, null))
{
while (cursor.MoveToNext())
{
payroll.Add(new Payroll
{
Id = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnID)),
Name = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnName)),
Age = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnAge)),
PCB = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnPCB)),
EPF = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEPF)),
SOCSO = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnSOCSO)),
EIS = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEIS)),
GrossSalary = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnGross)),
NetSalary = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnNet)),
EmployerEPF = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerEPF)),
EmployerEIS = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerSOCSO)),
EmployerSOCSO = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerEIS))
});
}
}
db.Close();
return payroll;
}
public static void DeletePayroll(Context context, Payroll payroll)
{
SQLiteDatabase db = new DataStore(context).WritableDatabase;
db.Delete(TableName, ColumnName + "=? AND " + ColumnAge + "=? AND " + ColumnPCB + "=? AND " + ColumnEPF + "=? AND " + ColumnSOCSO + "=? AND " + ColumnEIS + "=? AND " + ColumnGross + "=? AND " + ColumnNet + "=? AND " + ColumnEmployerEPF + "=? AND " + ColumnEmployerSOCSO + "=? AND " + ColumnEmployerEIS + "=? OR " + ColumnID + "=" + payroll.Id, new string[] { payroll.Name });
db.Close();
}
public static Payroll SelectPayroll(Context context)
{
Payroll payroll;
SQLiteDatabase db = new DataStore(context).WritableDatabase;
string[] columns = new string[] { ColumnID, ColumnName, ColumnAge, ColumnPCB, ColumnEPF, ColumnSOCSO, ColumnEIS, ColumnGross, ColumnNet, ColumnEmployerEPF, ColumnEmployerSOCSO, ColumnEmployerEIS };
using (ICursor cursor = db.Query(TableName, columns, ColumnName + "=? AND " + ColumnAge + "=? AND " + ColumnPCB + "=? AND " + ColumnEPF + "=? AND " + ColumnSOCSO + "=? AND " + ColumnEIS + "=? AND " + ColumnGross + "=? AND " + ColumnNet + "=? AND " + ColumnEmployerEPF + "=? AND " + ColumnEmployerSOCSO + "=? AND " + ColumnEmployerEIS + "=?", new string[] { },null, null, null))
{
if (cursor.MoveToNext())
{
payroll = new Payroll
{
Id = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnID)),
Name = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnName)),
Age = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnAge)),
PCB = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnPCB)),
EPF = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEPF)),
SOCSO = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnSOCSO)),
EIS = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEIS)),
GrossSalary = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnGross)),
NetSalary = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnNet)),
EmployerEPF = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerEPF)),
EmployerEIS = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerSOCSO)),
EmployerSOCSO = cursor.GetDouble(cursor.GetColumnIndexOrThrow(ColumnEmployerEIS))
};
}
else
{
payroll = null;
}
}
return payroll;
}
}
}
And this is my models file:
namespace PayrollParrots.Model
{
public class Payroll
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public double PCB { get; set; }
public double EPF { get; set; }
public double SOCSO { get; set; }
public double EIS { get; set; }
public double GrossSalary { get; set; }
public double NetSalary { get; set; }
public double EmployerEIS { get; set; }
public double EmployerSOCSO { get; set; }
public double EmployerEPF { get; set; }
public Payroll()
{
}
}
}
If I delete the "ColumnEPF" and all the columns after that my code works, but when I add ColumnEPF it doesn't work. I've tried changing the name of the ColumnEPF but that doesn't work either.
@ORTUND LIKE THIS?
Any help Appreciated!
Solution
If you are updating database in application, you need to upgrade database to update table.
Please check below link for reference. https://gist.github.com/matpag/b2545cc22c8e22449cd7eaf6b4910396
You can use below query while update table.
ALTER TABLE table_name ADD COLUMN column_definition;