Click here to Skip to main content
12,066,952 members (58,348 online)
Rate this:
 
Please Sign up or sign in to vote.
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(
''
);
}
$('#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();
});
Posted 29-Jun-13 8:31am
Comments
enhzflep 30-Jun-13 5:02am
   
"Problem is that when i delete a row using query like where ID =? it delete a whole row with ID"

No, the only problem is you didn't read and understand the documentation yet. That's what the sql delete command is supposed to do.
ravi1989h 30-Jun-13 9:08am
   
solve that issue

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160208.1 | Last Updated 29 Jun 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100