samedi 28 février 2015

Insert into sqlite database

I have search on the other same problem on stackoverflow but I haven't find my problem. When I want to insert values on my SQLite database, I have an error that say I don't know firstName in the table. However, I have create firstName.


I give you my code DatabaseHandler:



public class DatabaseHandler extends SQLiteOpenHelper
{

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "android_project_database_user";

// Login table name
private static final String TABLE_LOGIN = "login";

// Login Table Columns names
private static final String KEY_NAME = "name";
private static final String KEY_FIRSTNAME = "firstName";
private static final String KEY_EMAIL = "mail";
// private static final String KEY_UID = "id";
private static final String KEY_COUNTRY = "country";
private static final String KEY_CITY = "city";


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

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
// + KEY_UID + " INTEGER PRIMARY KEY,"
+ KEY_FIRSTNAME + " TEXT,"
+ KEY_NAME + " TEXT,"
+ KEY_EMAIL + " TEXT UNIQUE PRIMARY KEY,"
+ KEY_COUNTRY + " TEXT,"
+ KEY_CITY + " TEXT" + ")";
db.execSQL(CREATE_LOGIN_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);

// Create tables again
onCreate(db);
}

/**
* Storing user details in database
* */
public void addUser(String firstName, String name, String mail, String country, String city)
{
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_FIRSTNAME, firstName); // FirstName
values.put(KEY_NAME, name); // Name
values.put(KEY_EMAIL, mail); // Email
// values.put(KEY_UID, id); // id
values.put(KEY_COUNTRY, country); // country
values.put(KEY_CITY, city); // city

// Inserting Row
db.insert(TABLE_LOGIN, null, values);
db.close(); // Closing database connection
}

/**
* Getting user login status
* return true if rows are there in table
* */
public int getRowCount() {
String countQuery = "SELECT * FROM " + TABLE_LOGIN;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int rowCount = cursor.getCount();
db.close();
cursor.close();

// return row count
return rowCount;
}

/**
* Re crate database
* Delete all tables and create them again
* */
public void resetTables(){
SQLiteDatabase db = this.getWritableDatabase();
// Delete All Rows
db.delete(TABLE_LOGIN, null, null);
db.close();
}

}


And my login.java



public class LoginActivity extends Activity implements OnClickListener{

// Edit and Button that the user fill
private EditText mail, pass;
private Button bLogin;
private CheckBox checkBox;

/****************/
/* Réponse JSON */
/****************/
// private static String KEY_UID = "id";
private static String KEY_NAME = "name";
private static String KEY_FIRSTNAME = "firstName";
private static String KEY_EMAIL = "mail";
private static String KEY_COUNTRY = "nationality";
private static String KEY_CITY = "city";

/**********************************/
/* Enregistrement des préférences */
/**********************************/

public static final String PREFS_NAME = ".Preferences";
private static final String PREF_EMAIL = "email";
private static final String PREF_PASSWORD = "password";
private static final String PREF_CHECKED = "checked";


// Progress Dialog
private ProgressDialog pDialog;

// JSON parser class
JSONParser jsonParser = new JSONParser();
private static final String LOGIN_URL = "http://ift.tt/1N3GJGv";
private static final String TAG_SUCCESS = "success";
private static final String TAG_MESSAGE = "message";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_login);
mail = (EditText)findViewById(R.id.email);
pass = (EditText)findViewById(R.id.pass);
bLogin = (Button)findViewById(R.id.identify);
checkBox = (CheckBox)findViewById(R.id.cbRememberMe);

/***********************************************************************/
/* Restauration des préférences sauvegardées si la checkbox est cochée */
/***********************************************************************/

SharedPreferences pref = getSharedPreferences(PREFS_NAME,MODE_PRIVATE);
String email = pref.getString(PREF_EMAIL, "");
String password = pref.getString(PREF_PASSWORD, "");
String checked = pref.getString(PREF_CHECKED, "");

mail.setText(email);
pass.setText(password);
checkBox.setChecked(Boolean.parseBoolean(checked));

bLogin.setOnClickListener(this);
}

@Override
public void onClick(View v) {

/************************************************************/
/* Enregistrement des préférences si la checkbox est cochée */
/************************************************************/

if(checkBox.isChecked())
{
getSharedPreferences(PREFS_NAME,MODE_PRIVATE)
.edit()
.putString(PREF_EMAIL, mail.getText().toString())
.putString(PREF_PASSWORD, pass.getText().toString())
.putString(PREF_CHECKED,"TRUE")
.commit();
}

/***********************/
/* Sinon on les efface */
/***********************/

else if(!checkBox.isChecked())
{
getSharedPreferences(PREFS_NAME,MODE_PRIVATE).edit().clear().commit();
}

switch (v.getId()) {
case R.id.identify:
new AttemptLogin().execute();
// here we have used, switch case, because on login activity you may //also want to show registration button, so if the user is new ! we can go the //registration activity , other than this we could also do this without switch //case.
default:
break;
}
}

class AttemptLogin extends AsyncTask<String, String, String> {
/**
* Before starting background thread Show Progress Dialog
* */
boolean failure = false;

@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(LoginActivity.this);
pDialog.setMessage("Attempting for login...");
pDialog.setIndeterminate(false);
pDialog.setCancelable(true);
pDialog.show();
}

@Override
protected String doInBackground(String... args) {
// TODO Auto-generated method stub
// here Check for success tag
int success;
String email = mail.getText().toString();
String password = pass.getText().toString();
try {

List<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("mail", email));
params.add(new BasicNameValuePair("password", password));

Log.d("request!", "starting");

//utilise JSON pour renvoyer resultat
JSONObject json = jsonParser.makeHttpRequest(
LOGIN_URL, "POST", params);

// checking log for json response
Log.d("Login attempt", json.toString());

// success tag for json
success = json.getInt(TAG_SUCCESS);
if (success == 1) {

/***************************************/
/* Stocke les infos dans la BDD SQLite */
/***************************************/

DatabaseHandler db = new DatabaseHandler(getApplicationContext());
db.resetTables();
db.addUser(json.getString(KEY_FIRSTNAME), json.getString(KEY_NAME), json.getString(KEY_EMAIL), json.getString(KEY_COUNTRY), json.getString(KEY_CITY));

/***************************************/
/* Lancement de l'Activity "MainMenuActivity" */
/***************************************/

Log.d("Successfully Login!", json.toString());

Intent i = new Intent(LoginActivity.this,MainMenuActivity.class);

startActivity(i);

finish();

return json.getString(TAG_MESSAGE);
}else{

return json.getString(TAG_MESSAGE);

}
} catch (JSONException e) {
e.printStackTrace();
}

return null;
}
/**
* Once the background process is done we need to Dismiss the progress dialog asap
* **/
protected void onPostExecute(String message) {

pDialog.dismiss();
if (message != null){
Toast.makeText(LoginActivity.this, message, Toast.LENGTH_LONG).show();
}
}
}
}


And to finish my login.php



<?php
session_start();

include ('config.php');

// Recup elem to make the login_connection
$password=$_POST["password"];
$mail=$_POST["mail"];

if (!empty($_POST)) {
if (empty($_POST['mail']) || empty($_POST['password'])) {
// Create some data that will be the JSON response
$response["success"] = 0;
$response["message"] = "One or both of the fields are empty .";
die(json_encode($response));
}

//prepare the select statement
$query = $handler->prepare('SELECT id, name, firstName, mail, nationality, city FROM login WHERE mail = :mail AND password= :password');

//bind the parameters
$query->bindParam(':mail', $mail, PDO::PARAM_STR);
$query->bindParam(':password', $password, PDO::PARAM_STR);

//execute the prepared statement
$query->execute();

//create array
$response = array();

//check for a result
$row = $query->fetch(PDO::FETCH_ASSOC);

if (!empty($row)) {
$response["success"] = 1;
$response["message"] = "You have been sucessfully login";
$response["id"]= $row["id"];
$response["firstName"]= $row["firstName"];
$response["name"]= $row["name"];
$response["mail"]= $row["mail"];
$response["nationality"]= $row["nationality"];
$response["city"]= $row["city"];

$_SESSION['mail'] = $mail;
die(json_encode($response));
}
}
else{

$response["success"] = 0;
$response["message"] = " One or both of the fields are empty ";
die(json_encode($response));
}
?>


When I display the value of each elements that I want to put in my dataBase, they take their good value.


Thank you very much for your help.


Mickey74


Aucun commentaire:

Enregistrer un commentaire