插入sqlite数据库

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://192.168.20.202/BD_Projet/login.php";
  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.

And it's the error on eclipse logcat :

02-28 22:13:07.791: D/Login attempt(24715): {"success":1,"message":"You have been sucessfully login","id":"1","firstName":"Mick","name":"KOZA","mail":"mick.koza@gmail.com","nationality":"France","city":"Sales"}
02-28 22:13:07.847: E/SQLiteLog(24715): (1) table login has no column named firstName
02-28 22:13:07.848: E/SQLiteDatabase(24715): Error inserting name=KOZA firstName=Mick country=France city=Sales mail=mick.koza@gmail.com
02-28 22:13:07.848: E/SQLiteDatabase(24715): android.database.sqlite.SQLiteException: table login has no column named firstName (code 1): , while compiling: INSERT INTO login(name,firstName,country,city,mail) VALUES (?,?,?,?,?)

Thank you very much for your help.

Mickey74

doubi6898
doubi6898 尝试使用GUI程序打开数据库文件。您可以使用adb从设备中复制它。也许你有一个区分大小写的问题。这是唯一一个带大写字母的专栏。
5 年多之前 回复

1个回答



在这里结合一些事情。 首先,在您的DatabaseHandler类中,您的表名和列应该是 public static final String </ code> s,而不是私有。 这样你就没有这样的拼写错误:</ p>

  public class DatabaseHandler扩展了SQLiteOpenHelper 
{
private static final String KEY_COUNTRY =“country”;
</ code > </ pre>

vs。</ p>

  public class LoginActivity extends Activity实现OnClickListener 
{
private static String KEY_COUNTRY =“nationality”; \ n </ code> </ pre>

对两个引用使用相同的变量... </ p>

其次,在更改数据库后卸载了应用程序 列? 看起来你做了改变。 除非你告诉DatabaseHandler“升级”或“重新安装”(你可以用“测试”或“重新安装”标志),否则表格不会因为你“更新”你的应用而改变。 这会在每次发布应用程序更新时删除所有数据 - 这将非常糟糕。</ p>
</ div>

展开原文

原文

Couple things here. First, in your DatabaseHandler class, your table name and columns should be public static final Strings, not private. That way you don't have typos like this:

public class DatabaseHandler extends SQLiteOpenHelper 
{
    private static final String KEY_COUNTRY = "country";

vs.

public class LoginActivity extends Activity implements OnClickListener
{
    private static String KEY_COUNTRY = "nationality";

Use the same variable for both references...

Second, did you uninstall the app after making changes to the database columns? It looks like you've made changes. And unless you tell the DatabaseHandler to "upgrade" or "reinstall" (which you could do with a "testing" or "reinstall" flag), then the table will not change simply because you "update" your app. That would delete all data every time an update for your app is published - that would be very bad.

dongping1012
dongping1012 谢谢你是对的,因为我从来没有卸载我的应用程序
5 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐