How to update one field in specific row using sqlite in android

Question

0.00/5 (No votes)

See more:

I am developing database in Sqlite. I created the table the schema are Sms_Address,Sms_Body,Status_Msg. The initial status of message is 0. I am updating the status to 1 by using setStatus() function in the database. But the status of all rows are updated. I want to update only one field named as Status_Msg checking by checkbox.

The setStatus() function is given below:

Java

public String setStatus(){
        SQLiteDatabase sqldb = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        if (Status_Msg.equals(Status_Msg)) {
            values.put(Status_Msg, 1);
            sqldb.update(DB_Table1, values, "status = 0", null);
        }
        System.out.println("" + Status_Msg);
        sqldb.close();
        return Status_Msg;
    }

I am displaying data in the database by using showStatusChangedData() function. The control moves properly in this function. This function does not show messages in listview Please check these two functions.

The showStatusChangedData() function is given below:

Java

public List<Message> showStatusChangedData(){
       SQLiteDatabase sqldb = getWritableDatabase();
       List<Message> list = new ArrayList<>();
       String select_query = "SELECT * FROM " + DB_Table1 + " where status = 1";

       Cursor cursor = sqldb.rawQuery(select_query,null);
           if (cursor.moveToFirst()) {
               do {
                   Message msg = new Message();
                   msg.setmAddress(cursor.getString(0));
                   msg.setmBody(cursor.getString(1));
                   msg.setmStatus(cursor.getString(2));
                   list.add(msg);
               } while (cursor.moveToNext());
           }
       return list;
   }

What I have tried:

I tried the following query

Java

sqldb.update(DB_Table1, values,Sms_Address +"="+ Status_Msg,null);

But this not works.

Posted 23-Feb-16 22:38pm

Member 12299159

Updated 13-Apr-18 2:31am

v6

Add a Solution

Comments

Richard MacCutchan 24-Feb-16 7:43am

What does "it not works" mean? And what result do you expect from the statement if (Status_Msg.equals(Status_Msg))?

Member 12299159 24-Feb-16 8:31am

The status of message is equal to 1 then update the status of message.

Member 12299159 25-Feb-16 2:27am

I solved the yesterdays problem. This is a new problem I face. Please help me for solving this problem. I updated the question.

Richard MacCutchan 25-Feb-16 3:57am

Add some code to check the values that get returned when you call some method. Do not assume that you will get a good result each time. The cursor returned from your SELECT statement may not contain any items. You can quickly check by using your debugger.

Member 12299159 25-Feb-16 4:05am

I checks by using debugger the update query updates value 0 to 1 and
Select query not contains any item.

Member 12299159 25-Feb-16 5:01am

How I create new setStatus() method?
How I create update query?

Member 12299159 26-Feb-16 7:16am

I solved the yesterdays problem. I updated the question.Please check this and help me this for solving.

David Crow 13-Apr-18 9:07am

Not related to any of the problems, but why is showStatusChangedData() calling getWritableDatabase() instead of getReadableDatabase()?

The code in the setStatus() function is going to change all Status_msg columns to 1 for those rows whose status column is equal to 0. That may be why you are updating more than one row.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

GVS13 · Answer 1 · 2016-02-28T17:58:00

Solution 1

To update the field in specific row, try this;

ContentValues cv = new ContentValues();
cv.put(Status_Msg, "1");
sqldb.update(DB_Table1, cv, "id="+_id, null);
sqldb.close();

It's good practice to have a primary key for each row data.
Here _id would be your primary key.

One more suggestion, when you done with iterating all rows in the table, close the cursor object (cursor.close()).

Posted 28-Feb-16 17:58pm

GVS13

Updated 28-Feb-16 20:33pm

v2

Comments

Member 12299159 29-Feb-16 2:25am

I want to update Status_Msg to value 1.
Your code update Sms_Address to status.
Please give me another solution.

GVS13 · Answer 2 · 2016-02-28T20:33:00

Solution 2

See the updated solution above.

Posted 28-Feb-16 20:33pm

GVS13

Comments

Member 12299159 29-Feb-16 3:16am

By using your code whole column of status updated.
I want to update specific field selected by check box.
Please give me solution.

GVS13 29-Feb-16 3:59am

Do you have primary key for each row as I mentioned? If you use primary key for the respective row to update, it will work only for that row field.

Member 12299159 29-Feb-16 4:10am

Yes I have primary key as Sms_Id

GVS13 29-Feb-16 4:18am

Can you post the updated SetStatus function and the code snippet you are using to update the value.

Member 12299159 29-Feb-16 5:00am

yes

Member 12299159 29-Feb-16 5:01am

Code for setStatus Function
<pre lang="java">public String setStatus(){
SQLiteDatabase sqldb = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(Status_Msg, "1");
sqldb.update(DB_Table1, values, "sms_id ="+ Sms_Id, null);
System.out.println("" + Status_Msg);
sqldb.close();
return Status_Msg;
}
</pre>

GVS13 29-Feb-16 5:19am

It looks okay. Can you show me the table schema. I mean your table structure, how you create a table.

Member 12299159 29-Feb-16 5:21am

Yes
Create Table query

public  static  final String DB_Query = "create table " + DB_Table1 + "(" +Sms_Id+" integer primary key autoincrement," +Sms_Address+" text not null, "+Sms_Body+" text not null, "+Status_Msg+" integer default 1)";

GVS13 29-Feb-16 5:23am

You have kept Status_Msg default 1. That is the reason you are getting the value for all Status_Msg as 1. Keep it default 0.

Member 12299159 29-Feb-16 5:25am

But I checks the display function for displaying data in database. It shows the status is 0.

GVS13 29-Feb-16 5:26am

Try keeping default as 0.

Member 12299159 29-Feb-16 5:27am

Ok

Member 12299159 29-Feb-16 5:36am

I checked keeping default 0 value.
The whole column is updated to 1.

GVS13 29-Feb-16 5:46am

Hold on. First make some changes, as your Status_Msg is an integer field keep values.put(Status_Msg, 1). And what are you trying to get by printing System.out.println("" + Status_Msg)? I guess Status_Msg is just a String column name that you have in your table. Isn't it?

Member 12299159 29-Feb-16 6:07am

The Status_Msg is integer variable but it initialize string datatype.
I trying to get updated status by using this statement System.out.println("" + Status_Msg);

GVS13 29-Feb-16 6:21am

I didn't get what exactly you are doing in your code. Anyways do this;
//Create database and table if not exist
SQLiteDatabase sqldb = openOrCreateDatabase("myDatabase", MODE_PRIVATE, null);
sqldb.execSQL("Create table if not exists DB_Table1 (sms_id INTEGER primary key autoincrement, sms_address TEXT not null, sms_body TEXT not null, status_msg INTEGER default 0);");
sqldb.close();

//To update table specific value

ContentValues cv = new ContentValues();
cv.put("status_msg", 1);
sqldb.update(DB_Table1, cv, "sms_id="+_id, null);
sqldb.close();

//To get updated data
SQLiteDatabase sqldb = openOrCreateDatabase("myDatabase", MODE_PRIVATE, null);
Cursor res = sqldb.rawQuery("select * from DB_Table1 where sms_id=" + _id + "", null);
res.moveToFirst();
int iStatus_Msg = res.getInt(res.getColumnIndex("status_msg"));
res.close();
sqldb.close()

Member 12299159 29-Feb-16 6:32am

I explain you. I want to implement function for moving sms from spam to inbox. I create database for storing spam sms. In this database Status_Msg is a column for storing status of sms. Initially status of all messages is 0. I implement one function setStatus for updating status of sms. When status of sms updated to 1 this sms will move in inbox and display. This is the working.

Member 12299159 29-Feb-16 6:34am

You want a database file then I will upload on this page.

GVS13 29-Feb-16 6:36am

Just try with the code I posted. And see the result.

Member 12299159 29-Feb-16 6:46am

What is SQLiteDatabase sqldb = openOrCreateDatabase("myDatabase", MODE_PRIVATE, null); MODE_PRIVATE in this statement.
I am getting error for MODE_PRIVATE statement.

GVS13 29-Feb-16 7:08am

That will create a database named myDatabase if it not created or open it if it is already there.

Member 12299159 29-Feb-16 7:15am

I do not understand your comment
The second position MODE_PRIVATE error occurred.

Member 12299159 29-Feb-16 8:24am

The above code does not worked properly in my project.
Please give me another solution.

Member 13777950 · Answer 3 · 2018-04-13T02:32:00

Solution 3

sqldb.update(DB_Table1, values,Sms_Address +"=?",new String[]{Status_Msg;});

Posted 13-Apr-18 2:32am

Member 13777950