Click here to Skip to main content
Rate this: bad
good
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 at 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 at 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
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 29 Jun 2013
Copyright © CodeProject, 1999-2014
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