Syntax error sqlite

Question

I'm trying to create a database with two tables in it. However, this syntax error keeps coming up whenever i try to run it. I've checked through many times but still can't figure what's wrong. Any help would be appreciated!

public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 2;
private static final String DATABASE_NAME = "contacts.db";
private static final String TABLE_NAME = "contacts";
private static final String COLUMN_ID = "id";
private static final String COL_1 = "Name";
private static final String COL_2 = "Pass";
private static final String COL_3 = "email";
private static final String COLUMN_ID1 = "id";

private static final String TABLE_NAME2 = "staff";
private static final String COLUMN_NAME = "Staff Name";
private static final String COLUMN_EMAIL = "Staff Email";
private static final String COLUMN_PASS = "Staff Password";
SQLiteDatabase db;

private static final String TABLE_CREATE = "create table contacts (id integer primary key not null , " +
        "name text not null , pass text not null, email text not null);";

private static final String TABLE_CREATE2 = "create table staff (id integer primary key not null , " +
        "name text not null , password text not null, email text not null);";


public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TABLE_CREATE);
    db.execSQL(TABLE_CREATE2);
    this.db = db;

}

public void insertContact(Contact c)
{
    db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    String query = "select * from contacts";
    Cursor cursor = db.rawQuery(query , null);
    int count = cursor.getCount();

    values.put(COLUMN_ID , count);
    values.put(COL_1 , c.getName());
    values.put(COL_2 , c.getPass());
    values.put(COL_3, c.getEmail());

    db.insert(TABLE_NAME, null, values);
    db.close();
}

public String searchPass(String name)
{
    db = this.getReadableDatabase();
    String query = "select Name, Pass from " + TABLE_NAME;
    Cursor cursor = db.rawQuery(query , null);
    String a, b;
    b = "not found";

    if(cursor.moveToFirst())
    {
        do {
            a = cursor.getString(0);


            if(a.equals(name))
            {
                b = cursor.getString(1);
                break;
            }
        }
        while (cursor.moveToNext());
    }

    return b;

}

public void insertStaff(Staff s)
{
    db = this.getWritableDatabase();
    ContentValues values1 = new ContentValues();

    String query1 = "select * from staff";
    Cursor cursor1 = db.rawQuery(query1 , null);
    int count = cursor1.getCount();

    values1.put(COLUMN_ID1 , count);
    values1.put(COLUMN_NAME , s.getStaffname());
    values1.put(COLUMN_EMAIL , s.getStaffemail());
    values1.put(COLUMN_PASS , s.getStaffpass());

    db.insert(TABLE_NAME2 , null , values1);
    db.close();

}

public String searchStaff(String name)
{
    db = this.getReadableDatabase();
    String query1 = "select name, password from " + TABLE_NAME2;
    Cursor cursor1 = db.rawQuery(query1, null);
    String a, b;
    b = "not found";
    if (cursor1.moveToFirst())
    {
        do{
            a=cursor1.getString(0);
            b=cursor1.getString(1);

            if (a.equals(name))
            {
                b = cursor1.getString(1);
                break;
            }

        }
        while(cursor1.moveToNext());
    }

    return b;
}




@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String query = "DROP TABLE IF EXISTS " + TABLE_NAME;
    db.execSQL(query);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME2);
    this.onCreate(db);






}
}

Logcat

02-06 17:13:32.552 9101-9101/? E/SQLiteLog﹕ (1) near "Password": syntax error 02-06 17:13:32.552 9101-9101/? E/SQLiteDatabase﹕ Error inserting id=0 Staff Password= Staff Email= Staff Name= android.database.sqlite.SQLiteException: near "Password": syntax error (code 1): , while compiling: INSERT INTO staff(id,Staff Password,Staff Email,Staff Name) VALUES (?,?,?,?) at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467) at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339) at com.example.username.mp.DatabaseHelper.insertStaff(DatabaseHelper.java:106) at com.example.username.mp.Signup.onRegClick(Signup.java:47) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:511) at android.support.v7.internal.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:273) at android.view.View.performClick(View.java:4084) at android.view.View$PerformClick.run(View.java:16966) at android.os.Handler.handleCallback(Handler.java:615) at android.os.Handler.dispatchMessage(Handler.java:92) at android.os.Looper.loop(Looper.java:137) at android.app.ActivityThread.main(ActivityThread.java:4745) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:511) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553) at dalvik.system.NativeStart.main(Native Method)

Signup.java

public class Signup extends AppCompatActivity {

DatabaseHelper helper1 = new DatabaseHelper(this);

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_signup);
}

public void onRegClick(View v)
{
    if(v.getId()== R.id.button20)
    {
        EditText name1 = (EditText)findViewById(R.id.editText12);
        EditText email1 = (EditText)findViewById(R.id.editText13);
        EditText pass3 = (EditText)findViewById(R.id.editText14);
        EditText pass4 = (EditText)findViewById(R.id.editText15);

        String name1str = name1.getText().toString();
        String email1str = email1.getText().toString();
        String pass3str = pass3.getText().toString();
        String pass4str = pass4.getText().toString();

        if(!pass3str.equals(pass4str))
        {
            Toast pop = Toast.makeText(Signup.this , "Password don't match" , Toast.LENGTH_SHORT);
            pop.show();
        }
        else
        {
            Staff s = new Staff();
            s.setStaffname(name1str);
            s.setStaffemail(email1str);
            s.setStaffpass(pass3str);

            helper1.insertStaff(s);
            Toast.makeText(getApplicationContext(), "Account Successfully Created ", Toast.LENGTH_LONG).show();
        }
    }

}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
    // Inflate the menu; this adds items to the action bar if it is present.
    getMenuInflater().inflate(R.menu.menu_signup, menu);
    return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
    // Handle action bar item clicks here. The action bar will
    // automatically handle clicks on the Home/Up button, so long
    // as you specify a parent activity in AndroidManifest.xml.
    int id = item.getItemId();

    //noinspection SimplifiableIfStatement
    if (id == R.id.action_settings) {
        return true;
    }

    return super.onOptionsItemSelected(item);
}
}

Valentino · Accepted Answer · 2017-02-06 17:28:56Z

1

Try to remove spaces from COLUMN_NAME,COLUMN_EMAIL and COLUMN_PASS values.

answered Feb 6, 2017 at 17:28

Valentino

2,1351 gold badge20 silver badges26 bronze badges

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

7 Comments

J.Cummings Over a year ago

hey thank you for the response. do you mean removing spaces meaning COLUMN_NAME="Staff Name"; ?

Valentino Over a year ago

Yes, exactly :)

J.Cummings Over a year ago

Unfortunately the exact same error still pops up :(

Valentino Over a year ago

Ok. Do you change the variables in the DatabaseHelper class COLUMN_NAME,COLUMN_EMAIL and COLUMN_PASS in order to remove spaces? (for example, like: COLUMN_NAME = "Staff_Name" , COLUMN_PASS="Staff_Password" etc.)

J.Cummings Over a year ago

hey i changed it and the syntax error is gone. however this new error message pops up table staff has no column named Staff_Name. any idea what might be causing this?

|

Collectives™ on Stack Overflow

Syntax error sqlite

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related