View original (French)

Insert a date variable into a record

Solved
rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   -  
BunoCS Posted messages 5034 Registration date   Status Moderator Last intervention   -
Hello,

I am trying in vain to insert a current date variable into a DATE column in my SQLite database using Android Studio.

I have retrieved the format of the current date and displayed it in a TextView, and that works without any problem.

I initialized a variable with this date format, but I am unable to insert it into my database in the DATE column.

Below are my code excerpts: 

 Calendar c = Calendar.getInstance();

 SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
 String formatteDate = df.format(c.getTime());
 // formattedDate have current date
 // Toast.makeText((this), formatteDate,Toast.LENGTH_LONG).show();

 // now we display formattedDate value in TextView
 txtView.setText(" Current date of the day :" + formatteDate);
 txtView.setTextSize(20);

 varDateReport = formatteDate;


Everything is fine, my date displays on the device.

Now, the part of the code that I cannot resolve is the following excerpt: 

 public Cursor selecte_Dates(){
 SQLiteDatabase db = this.getWritableDatabase();
 ContentValues dater = new ContentValues();
 dater.put("DATE",varDateReport);//call of the variable varDateReport to get its value
 return db.rawQuery("SELECT ID as _id, * FROM book_table" + dater, null);
 }


Thank you for your help.

Best regards, see you soon
rapidegoyes

Configuration: Windows / Firefox 83.0

6 answers

  1.
    BunoCS Posted messages 5034 Registration date   Status Moderator Last intervention   3 930
     
    Hello,

    To retrieve a record from the database, I use the method 
    db.query()
    .
    Example: 

    final Cursor cursor = db.query(tableName, null, selection, selectionArgs, groupBy, having, orderBy, limit);


    In selection, I put my query, like 
    "ID = ?"

    and in selectionsArgs, I put my arguments, like 
    new String[]{"" + id}

    @+
    Buno, Moderator
    The urgent is done, the impossible is underway. For miracles, provide for a delay...
    2
  2. rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   13
     
    Good evening,

    First of all, my apologies for the inconvenience.

    It's unforgivable, let's stop torturing ourselves!!!

    Here is what you corrected for me in a previous message:
     return db.rawQuery("SELECT ID as _id, * FROM book_table WHERE DATE = '"+ varDateReport +"'" , null);


    I just revisited it tonight and to my surprise, this code indeed displays the products for the current date.

    I wonder what mistake I might have made?
    The date of birth doesn't help things!!!

    The goal is achieved when the activity opens and displays the products to be taken out of the freezer starting from today NOW.

    Thank you so much for your help that has shaped my project.

    As soon as the app is finalized, I’ll send you the APK.

    Best regards, talk soon +++

    rapidegoyes
    1
  3. rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   13
     
    Good evening

    I changed a line of code, here is the excerpt: 

     return db.rawQuery("SELECT ID as _id, * FROM book_table " + dater.toString(), null);


    No errors occur during compilation, but when I open the page in question, the app crashes.

    Strangely, when I go back a page, the app continues to function with the other commands.

    See you soon
    rapidegoyes
    0
    1. jordane45 Posted messages 30426 Registration date   Status Moderator Last intervention   4 830
       
      Hello

      In my opinion 
       SELECT ID as _id, book_table.* FROM book_table "
      0
  4. rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   13
     
    Good evening,
    Thank you for responding

    What you are proposing doesn't work

    Here is my code that I have already tried, it works but I cannot insert my variable
    initialized with the current day's system date.

    Below is my code 

    return db.rawQuery("SELECT ID as _id, * FROM book_table " , null);


    With this, I succeed in displaying all my data, what I'm looking for is simply to display a line with the current date of the day.

    To succeed, I need to be able to insert my date variable into this query, unfortunately I can't manage to do that.

    If you have another idea, I'm open to suggestions.

    See you +++ best regards

    rapidegoyes
    0
    1. jordane45 Posted messages 30426 Registration date   Status Moderator Last intervention   4 830
       
      Oh darn.... I misread your request...

      So you would like to "filter" on this variable?
      You forgot to include a WHERE in your query.
      0
    2. rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   13
       
      Good evening,

      Indeed, my goal is to filter the data with this date "now"

      I had already added WHERE with several attempts.

      But unfortunately, nothing works, which is strange because during compilation there are no errors, as mentioned above, when I want to display this activity the app crashes.

      No error message anywhere!!!

      It only crashes for the activity in question and I can continue on other views without any problem.

      Here is my attempt 

       return db.rawQuery("SELECT ID as _id, * FROM book_table WHERE DATE = mavariable" , null);


      I have been searching for at least 12 hours with trials in every direction but nothing!!!

      Now, pay attention, if I put a date there, it works, and the rows containing that date appear.

      example of the trial code: 

       return db.rawQuery("SELECT ID as _id, * FROM book_table WHERE DATE = '2020-12-04'" , null);


      So I have a problem inserting the variable, yet it exists and is properly initialized!

      Help, please

      Best regards, rapidegoyes or rapidegoNO!!!
      0
    3. jordane45 Posted messages 30426 Registration date   Status Moderator Last intervention   4 830 > rapidegoyes Posted messages 3 Registration date   Status Member Last intervention  
       
      mavariable must be enclosed in quotes
      0
    4. rapidegoyes > rapidegoyes Posted messages 3 Registration date   Status Member Last intervention  
       
      I tried putting the variable in quotes, it no longer crashes, but I don't have any display; the sort is null.

      See you later.
      0
    5. jordane45 Posted messages 30426 Registration date   Status Moderator Last intervention   4 830 > rapidegoyes
       
      Between quotes... but also concatenating it with the rest of the string...

      Something like 
       return db.rawQuery("SELECT ID as _id, * FROM book_table WHERE DATE = '" + mavariable + "'" , null);
      0
  6. rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   13
     
    Good evening,

    I tried the above but nothing works, I have red everywhere.

    Let me explain:

    I am trying to sort the products by date.

    Using the following method by using the same variable, I am able to modify dates
    below. 
     public void daterep(long id){
     SQLiteDatabase db = this.getWritableDatabase();
     ContentValues dater = new ContentValues();
     dater.put("DATE", varDateReport); //call the varChange variable to get its value
     db.update("book_table", dater, "id = ? ", new String[]{Integer.toString((int) id)});//on click on a row
     db.close();//closes the DB
     }


    Now my problem:
    With the following method by using the same variable, I am unable to sort the dates.
    No errors, the activity displays but empty.
    I deduce that my variable is not taken into account!

    below: 
     public Cursor selecte_Dates(){
     SQLiteDatabase db = this.getWritableDatabase();
     ContentValues dater = new ContentValues();
     dater.put("DATE", varDateReport);//call the varDateReport variable to get its value
     return db.rawQuery("SELECT ID as _id, * FROM book_table WHERE DATE = \"" + dater + "\"", null);
     }


    Another attempt with a date in quotes, there the sorting works and the data is displayed!
    below: 
    return db.rawQuery("SELECT ID as _id, * FROM book_table WHERE DATE = '2020-12-02' " , null);


    But my goal is to automatically sort by today's date.

    Hoping I have explained my problem well.
    Thank you for listening

    best regards see you soon
    rapidegoyes
    0
    1. BunoCS Posted messages 5034 Registration date   Status Moderator Last intervention   3 930
       
      In my opinion, it's not the variable that's the issue, but the method. In the first case, you correctly use the parameter selection, but not in the second. If I try to transpose your query with the query() method, it looks something like this: 
       // Query parameters String tableName = "TA_TABLE"; // the name of your table String columns = null; // 'null' to retrieve all columns, otherwise, it should be new String[]{"column1", "column2"}; String selection = "DATE = ?"; // the WHERE clause String[] args = new String[]{ varDateReport }; // the parameters for the WHERE clause, one String for each question mark above String orderBy = "DATE DESC"; // sorting is done directly in the query // Query Cursor c = db.query(tableName, columns, selection, args, null, null, orderBy);


      The advantage of doing it this way is not having to worry about escaping characters in the SQL query, as the system handles it.
      0
  7. rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   13
     
    Good evening,

    Thank you for your response; I applied your code.
    This time I have no errors anywhere.
    But when I run the specific activity, my app crashes with the message "Gest congel" has stopped.

    Here is the query I wrote: 

     Cursor c = db.query("book_table", null,"DATE = ?", new String[]{varDateReport}, null, null, "DATE DESC");
     return c;


    Did I make a mistake in copying?

    Here is the entire method: 

     public Cursor selecte_Dates(){
     SQLiteDatabase db = this.getWritableDatabase();

     Cursor c = db.query("book_table", null,"DATE = ?", new String[]{varDateReport}, null, null, "DATE DESC");
     return c;
     }


    Thanks again
    A+++ best regards

    rapidegoyes
    yes or no ?
    0
    1. BunoCS Posted messages 5034 Registration date   Status Moderator Last intervention   3 930
       
      But when I start the activity in question, my app crashes with the message "Gest congel" has stopped.
      In such cases, you need to check the logs to see where it crashes...

      Is 
       varDateReport
      of type String? Otherwise, it might crash here.
      0
      1. rapidegoyes Posted messages 3 Registration date   Status Member Last intervention   13 > BunoCS Posted messages 5034 Registration date   Status Moderator Last intervention  
         
        Good evening,

        I really checked everything, no errors anywhere or in the logs to report the crash.

        My variable is declared as a String below:

         //class variable declaration to report dates
         public static String varDateReport ;


        And here is the entire file dates_Diverses.java 

        package com.example.sqlitedatabase;

        import android.app.Activity;
        import android.app.AlertDialog;
        import android.content.Context;
        import android.content.DialogInterface;
        import android.database.Cursor;
        import android.os.Bundle;
        import android.view.Gravity;
        import android.view.View;
        import android.widget.AdapterView;
        import android.widget.ListView;
        import android.widget.SimpleCursorAdapter;
        import android.widget.TextView;
        import android.widget.Toast;
        import java.text.SimpleDateFormat;
        import java.util.Calendar;

        public class dates_Diverses extends Activity implements AdapterView.OnItemLongClickListener {
         SQLiteDataBaseHelper db; // object declaration for db

         //TextView object declaration
         static TextView txtView;

         //class variable declaration to report dates
         public static String varDateReport ;

         protected void onCreate(Bundle savedInstanceState) {
         super.onCreate(savedInstanceState);
         setContentView(R.layout.dates_limites_diverses);

         db = new SQLiteDataBaseHelper(this);

         final ListView lv = this.findViewById(R.id.Fiche_liste);
         txtView = findViewById(R.id.txtView);

         //*************toast layout********************
         Context context = getApplicationContext();
         CharSequence text = " Check your freezing end dates ";
         int duration = Toast.LENGTH_SHORT;
         Toast toast = Toast.makeText(context, text, duration);
         toast.setGravity(Gravity.TOP|Gravity.LEFT, 40, 800);
         toast.show();

         lv.setOnItemLongClickListener(this);//allows modification by clicking on a row

         Calendar c = Calendar.getInstance();

         SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
         String formatteDate = df.format(c.getTime());
         // formattedDate has current date
         // Toast.makeText((this), formatteDate,Toast.LENGTH_LONG).show();



         // now display formattedDate value in TextView
         txtView.setText(" Current date of the day :" + formatteDate);
         txtView.setTextSize(20);

         varDateReport = formatteDate; //initialization of the variable

         Cursor data = db.selecte_Dates();

         // creation of the SimpleCursorAdapter object...
         SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.row_item, data, new String[]
         {"TYPE", "NOM", "DATE", "NOMBRE", "ENDROIT"}, new int[]{R.id.textViewCol1, R.id.textViewCol2,
         R.id.textViewCol3, R.id.textViewCol4, R.id.textViewCol5});

         lv.setAdapter(adapter);

         } // end of onCreate

         @Override

         public boolean onItemLongClick(AdapterView<?> parent, View view, int position, final long id) {
         final AlertDialog.Builder modifAlert = new AlertDialog.Builder(this);
         modifAlert.setTitle("You can modify the date");
         modifAlert.setMessage("Date of the day!" );
         modifAlert.setPositiveButton("Date", null);
         modifAlert.setNegativeButton("Cancel", null);
         modifAlert.setPositiveButton("OK", new DialogInterface.OnClickListener()
         {
         @Override
         public void onClick(DialogInterface dialog, int which) {
         db.daterep(id);
         Toast.makeText(getApplicationContext(), "OK the date has been modified", Toast.LENGTH_SHORT).show();
         }
         });

         modifAlert.create().show();
         return false;
         }


        }


        I am trying to understand

        Is it the date format that could be causing a problem?

        I declared yyyy-MM-dd

        Or is it this: String formatteDate = df.format(c.getTime());

        With my thanks

        Best regards
        rapidgoyes
        0
      2. BunoCS Posted messages 5034 Registration date   Status Moderator Last intervention   3 930 > rapidegoyes Posted messages 3 Registration date   Status Member Last intervention  
         
        I really checked everything, no errors anywhere or in the logs to report the crash.
        You must have a crash...Try running in debug mode step-by-step with breakpoints.
        0