CODEWITHSUNDEEP
sqlite
TrungNguyen
TrungNguyen

Reputation: 31

SQLite: Cannot delete row in database

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>

Upvotes: 3

Views: 3890

Answers (2)

Dan
Dan

Reputation: 67

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]);
    });

Upvotes: 0

awiebe
awiebe

Reputation: 3836

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
}

Upvotes: 1

Related Questions