SQLite: Cannot delete row in database

Question

I have the code as following to insert data into TestDB database, I can do insert and delete all rows successful but Delete one row didn't work.

Please show me what's wrong with the code. I think the problem comes from the function deleteRow not recognize the column1 value.

Thanks

<script>

    var db = window.openDatabase("Test", "1.0", "Test DB", 1000000);
    var column1= document.getElementById("column1");
    var column2= document.getElementById("column2");
    var column3= document.getElementById("column3");
    var results = document.getElementById("results");
    var id = document.getElementById('id');     
    var Delete = document.getElementById('Delete');     


    function InsertDB(){
       if ((column1.value!='') && (column2.value!='') && (column3.value!='')) {
      db.transaction(function(tx) {
          tx.executeSql('CREATE TABLE IF NOT EXISTS TestDB (id INTEGER, column1 TEXT, column2 TEXT, column3 TEXT)');
          tx.executeSql('INSERT INTO TestDB (column1, column2, column3) VALUES (?, ?, ?)', [column1.value, column2.value, column3.value], ShowandReset);
    }); 
    }
         else alert("Column cannot be empty");
    }



    function ShowDB() { 
    results.innerHTML = ''; 
    db.transaction(function(tx) {

      tx.executeSql("SELECT * FROM TestDB", [], function(tx, result) {
        dataset = result.rows;
        for (var i = 0, item = null; i < dataset.length; i++) {
         item = dataset.item(i);
         results.innerHTML += item['column1'] + ' , ' + item['column2'] + ' , ' + item['column3'] + '   ' + '<a href="#" onclick="deleteRow('+item['column1']+')"> Delete this row</a>' + '</br>';
        }               

        });
            });
                        }


function deleteRow(a) {

   db.transaction(function(tx) {
        tx.executeSql("DELETE FROM TestDB WHERE column1=a");
        });
        ShowDB();
        }

function DeleteDB() {
   db.transaction(function(tx) {
        tx.executeSql("DELETE FROM TestDB");
        });
        ShowDB();
        } 

function resetForm(){
    column1.value = '';
    column2.value = '';
    column3.value = '';
    id.value = ''; 
    }
function ShowandReset(){ 
    resetForm();
    ShowDB();
    }
function LoadRow(i) {

    var item = dataset.item(i);     
    column1.value = item['column1'];
    column2.value = item['column2'];
    column3.value = item['column3'];
    id.value = item['id']; 
    }


</script>

Not sure where and how this will be run, but keep in mind that anyone with access to your site, will be able to run arbitrary queries on your database. — Grimace of Despair
– Grimace of Despair, Commented Jul 8, 2013 at 7:49
When you unsure in something, use command interpreter to learn how to write requests correctly. — Alexander.Iljushkin
– Alexander.Iljushkin, Commented Jul 8, 2013 at 7:52
Also, i'm agreed with @GrimaceofDespair, you sould use defence mechanisms from an attack on your site. Sql injection is most common way to ruin your job. — Alexander.Iljushkin
– Alexander.Iljushkin, Commented Jul 8, 2013 at 7:53
This is a test database for practice purpose and I think nothing inside the database is useful. — TrungNguyen
– TrungNguyen, Commented Jul 8, 2013 at 7:55

awiebe · Accepted Answer · 2013-07-08 08:13:59Z

1

Ok new solution now that the problem has been clarified.

function deleteRow(a)
{
//a is a paramete
//this is a query string, so you need to append a otherwise it is interpreted litterally
 var queryString = "DELETE FROM TestDB WHERE column1='"+a+"'";
//Execute queryString as before
}

edited Jul 8, 2013 at 8:13

answered Jul 8, 2013 at 7:49

awiebe

3,8784 gold badges25 silver badges34 bronze badges

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

3 Comments

awiebe Over a year ago

You cannot delete a row from a database, a database contains a list of tables, tables contain rows. If you want to remove a table from a Database you use DROP, you want to remove a particular row from a table. Are you less confused now? I suppose test DB should be called testTable or testTab. Anyway I've found your syntax error: DELETE FROM TestDB WHERE column1='a' String litterals must be escaped.

TrungNguyen Over a year ago

Thanks, What i wanna do is remove one row that i already added by insertDB() function. I tried "DELETE FROM TestDB WHERE column1='a'", "DELETE FROM TestDB WHERE column1=a" and "DELETE FROM TestDB WHERE column1=?,[a]" but none of them is woking. I tried using "DELETE FROM TestDB WHERE column1=1" and the row have column value=1 is deleted. The problem must be the function deleteRow(a) doesn't recognize a is a value.

TrungNguyen Over a year ago

Do you mean ?

function deleteRow(a) {       var queryString = "DELETE FROM TestDB WHERE column1='"+a+"'";        db.transaction(function(tx) {        		tx.executeSql(queryString);       		});

I do as above but it not working

Dan · Accepted Answer · 2015-09-18 18:31:29Z

0

try this:

db.transaction(function (tx) {
        tx.executeSql('DELETE FROM TestTable', []);
    });

with WHERE clause:

db.transaction(function (tx) {
        tx.executeSql('DELETE FROM TestTable WHERE COLUMN = ?', [yourValue]);
    });

answered Sep 18, 2015 at 18:31

Dan

671 silver badge5 bronze badges

Collectives™ on Stack Overflow

SQLite: Cannot delete row in database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related