Need some concepts in deleting a row from table using sqlite phonegap

See more:

Javascript

SQL

PhoneGap

I am using sqlite in phonegap .In which i create a table ,Insert some data in table , update row ,fetch data from table .I am getting problem only in Deleting a row .I am creating table using primary key which is automatically in increment not Null .Problem is that when i delete a row using query like where ID =? it delete a whole row with ID .

Now i will explain more i am creating the dynamically list along save that value in database Like that .Actually i am giving the ID to row like (o,1,2,3).and table ID is like that(1,2,3,4).Whenever i want to update or fetch row value i do like that TableID=RowID+1; But on deleting when i delete the row let take example (First Row)it's ID id deleted .Then There is table having row ID (o)and table ID 2.When i update or delete the value of first row it display an error .Can you please suggest different method to do the same task.

function onDeviceReady() {
//$(document).bind('backbutton', onPressBack);
//$.mobile.ajaxEnabled=false

db = window.openDatabase("Casepad", "1.0", "Casepad", 200000);
if (window.localStorage.getItem("isAddSomeData") == "yes") {

db.transaction(getallTableData, errorCB);
}
// db.transaction(populateDB, errorCB, successCB);
}

function insertData() {
db.transaction(createTable, errorCB, afterSuccessTableCreation);
}

//create table and insert some record
function createTable(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS CaseTable (id INTEGER PRIMARY KEY AUTOINCREMENT, CaseName TEXT NOT NULL,CaseDate INTEGER ,TextArea TEXT NOT NULL)');
tx.executeSql('INSERT INTO CaseTable(CaseName,CaseDate,TextArea) VALUES ("' + $('.caseName_h').val() + '", "' + $('.caseDate_h').val() + '","' + $('.caseTextArea_h').val() + '")');
}

//function will be called when an error occurred
function errorCB(err) {
alert("Error processing SQL: " + err.code);
}

//function will be called when process succeed
function afterSuccessTableCreation() {
console.log("success!");
db.transaction(getallTableData, errorCB);
}



//select all from SoccerPlayer
function getallTableData(tx) {
tx.executeSql('SELECT * FROM CaseTable', [], querySuccess, errorCB);
}

function querySuccess(tx, result) {
var len = result.rows.length;
$('#folderData').empty();
for (var i = 0; i < len; i++) {

$('#folderData').append(
'

• ' + '' + '<img src="img/Blue-Folder.png">' + '

  ' + result.rows.item(i).CaseName + '

  ' + '

  ' + result.rows.item(i).CaseDate + '

  ' + '

  ' + result.rows.item(i).TextArea + '

  ' + '' + i + '' +
  '<button class="edit">Edit</button><button class="del">Delete</button>'+'

'
);
}
$('#folderData').listview('refresh');

}
//<-----------------------------------------Edit Data Functionality-------------------------------------->
function editData() {
db.transaction(editqueryDB, editerrorDB);
}

function editqueryDB(tx) {


tx.executeSql('SELECT * FROM CaseTable where id=' + ROW_ID, [], editquerySuccess, editerrorDB);
}

function editerrorDB(err) {
alert("Error processing SQL: " + err.code);
}

function editquerySuccess(tx, result) {
$('.caseName_h').val(result.rows.item(0).CaseName);
$('.caseDate_h').val(result.rows.item(0).CaseDate);
$('.caseTextArea_h').val(result.rows.item(0).TextArea);
$('#CaseInformationScreen').popup("open")

}

//<-----------------------------------------Edit Data Functionality End-------------------------------------->

//<-----------------------------------------Update Data Functionality End-------------------------------------->
function updatedata() {
db.transaction(updatequeryDB, updateerrorDB);
}

function updatequeryDB(tx) {


// tx.executeSql('SELECT * FROM CaseTable where id='+ROW_ID, [], updatequerySuccess, editerrorDB);

var caseName = $('.caseName_h').val();
var caseDate = $('.caseDate_h').val();
var caseTextArea = $('.caseTextArea_h').val();
tx.executeSql('UPDATE CaseTable SET CaseName = "' + caseName + '",CaseDate = "' + caseDate + '",TextArea = "' + caseTextArea + '" WHERE id = ' + ROW_ID);

afterSuccessTableCreation();
}

function updateerrorDB(err) {
alert("Error processing SQL: " + err.code);
}

function updatequerySuccess(tx, result) {


}

//<-----------------------------------------Update Data Functionality End-------------------------------------->

//<-----------------------------------------Delete Data Functionality Start-------------------------------------->
function deleteData() {
db.transaction(deletequeryDB, deleteerrorDB);
}

function deletequeryDB(tx) {


// tx.executeSql('SELECT * FROM CaseTable where id='+ROW_ID, [], updatequerySuccess, editerrorDB);


tx.executeSql('Delete FROM CaseTable where id='+ ROW_ID);


afterSuccessTableCreation();
}

function deleteerrorDB(err) {
alert("Error processing SQL: " + err.code);
}

function deletequerySuccess(tx, result) {


}

------------------Script-------------------
$(document).on('click', '#AddButton', function() {
var isvalid = validationField();
if (isvalid) {
if(isUpdaterequired){
updatedata();

}else{
insertData();
}

window.localStorage.setItem("isAddSomeData", "yes");

$.mobile.changePage($("#Home"));

}
});

$(document).on('click', '.del', function(event) {
// alert("clicked on row no "+ $(this).closest(".caseRowClick").attr("id"));
ROW_ID = $(this).closest(".caseRowClick").attr("id");
ROW_ID = parseInt(ROW_ID) + 1;


deleteData();
$(".ctrl").toggleClass("togg");
event.stopPropagation();
});
$(document).on('click', '.edit', function(event) {
// alert("clicked on row no : " + $(this).closest(".caseRowClick").attr("id"));
ROW_ID = $(this).closest(".caseRowClick").attr("id");
ROW_ID = parseInt(ROW_ID) + 1;

isUpdaterequired=true;

editData();
$(".ctrl").toggleClass("togg");
event.stopPropagation();
});