I have a problem in my database. When changing data in one of my rows, the old info is not replaced, it’s added to. How can I stop that?
Database Dilemma
Collapse
X
-
LOL Yeah that would help!
local sFirst = Input.GetText("Input_First");
local sLast = Input.GetText("Input_Last");
local sCell = Input.GetText("Input_Cell")
--Removes the dashes from the phone number.
dash1_result = Input.GetText("Input_Cell");
sCell = String.Replace(dash1_result, "-", "", false);
local nCarrierSel = ComboBox.GetSelected("ComboBox_Carrier");
local nGroupSel = ComboBox.GetSelected("ComboBox_Group");
--Checks to make sure the fields are filled in.
if sFirst == "" or sLast == "" or sCell == "" then
Dialog.Message("Notice", "You must enter a name (First and Last) and a cell number for this client.", MB_OK, MB_ICONEXCLAMATION, MB_DEFBUTTON1)
else
if nCarrierSel == 1 then
Dialog.Message("Notice", "You must set a carrier for this clients cell number.", MB_OK, MB_ICONEXCLAMATION, MB_DEFBUTTON1)
else
if nGroupSel == 1 then
Dialog.Message("Notice", "You must set a group for this client.", MB_OK, MB_ICONEXCLAMATION, MB_DEFBUTTON1)
else
local nCarrierID = ComboBox.GetItemData("ComboBox_Carrier", nCarrierSel)
local nGroupID = ComboBox.GetItemData("ComboBox_Carrier", nGroupSel)
local sType = xButton.GetText("xButton_AddClient")
if sType == "Add" then
local dash1_result = Input.GetText("number Input");
number_result = String.Replace(dash1_result, "-", "", false);
-- Adds a new client into the database.
SQLite.Query(db,"INSERT IGNORE INTO Clients VALUES (NULL, "..Enclose(sFirst)..", "..Enclose(sLast)..", "..Enclose(sCell)..", "..nCarrierID..", "..nGroupID..")")
Input.SetText("Input_Cell", "");
Input.SetText("Input_First", "");
Input.SetText("Input_Last", "");
ComboBox.SetSelected("ComboBox_Carrier", 1);
ComboBox.SetSelected("ComboBox_Group", 1);
xButton.SetText("xButton_AddClient", "Add");
-- Updates the database if your editing the client.
else
local tSel = ListBox.GetSelected("ListBox_Clients")
if tSel then
local nID=tonumber(ListBox.GetItemData("ListBox_Clients" , tSel[1]));
SQLite.Query(db,"UPDATE Clients SET First = "..Enclose(sFirst)..", Last = "..Enclose(sLast)..", Cell = "..Enclose(sCell)..", CarrierID = "..nCarrierID..", GroupID = "..nGroupID.." WHERE ID = "..nID);
Input.SetText("Input_Cell", "");
Input.SetText("Input_First", "");
Input.SetText("Input_Last", "");
ComboBox.SetSelected("ComboBox_Carrier", 1);
ComboBox.SetSelected("ComboBox_Group", 1);
xButton.SetText("xButton_AddClient", "Add");
end
end
local err=Application.GetLastError();
if err ~= SQLite.OK then
Dialog.Message( "Error",SQLite.GetErrorString(err));
else
LoadClientsListBox("ListBox_Clients")
end
end
end
end
Comment
-
-
BUMP
I'm thinking somewhere in here:
local tSel = ListBox.GetSelected("ListBox_Clients")
if tSel then
local nID=tonumber(ListBox.GetItemData("ListBox_Clients" , tSel[1]));
SQLite.Query(db,"UPDATE Clients SET First = "..Enclose(sFirst)..", Last = "..Enclose(sLast)..", Cell = "..Enclose(sCell)..", CarrierID = "..nCarrierID..", GroupID = "..nGroupID.." WHERE ID = "..nID);
Input.SetText("Input_Cell", "");
Input.SetText("Input_First", "");
Input.SetText("Input_Last", "");
ComboBox.SetSelected("ComboBox_Carrier", 1);
ComboBox.SetSelected("ComboBox_Group", 1);
xButton.SetText("xButton_AddClient", "Add");
end
end
local err=Application.GetLastError();
if err ~= SQLite.OK then
Dialog.Message( "Error",SQLite.GetErrorString(err));
else
LoadClientsListBox("ListBox_Clients")
end
end
end
end
Comment
-
-
SQLite.Query(db,"INSERT IGNORE INTO Clients VALUES .......
Try
SQLite.Query(db,"INSERT OR REPLACE INTO Clients VALUES
This should where you don't supply a value inset a null (or preset where its been defined) value into a cell. It should also replace the value in a specific cell with another you provide in the sql statement.
SQLITE should be doing this anyway on INSERT and I'm not 100% sure if the replace command was just added for cross sql platform compatibility. It's probably good form to use more universal statements anyway just in case you need to use another sql platform.
Comment
-
-
i would go with "UPDATE OR IGNORE bla bla WHERE ID=", if you insert with a row id it will bump the rest of the preceding rows down 1 (i think) and give a new entry
and your right, REPLACE was added to the INSERT command for MYSql compatibility, REPLACE is simply an alias for INSERT OR REPLACE, UPDATE should do the trick
REMOVED EDIT, I WAS WRONG (AGAIN) lolLast edited by RizlaUK; 05-09-2012, 11:12 AM.Embrace change in your life, you never know, it could all work out for the best
Comment
-
-
Rizla that does look better insert can add a new row
I was wondering how the 3 and 4 became 34, that's got to be be a concatenation assuming that the group IDs are 1 to 4 (not 1 to 30) but I don't see a double pipe anywhere. It might just be a bug in the sqlite action set??
Comment
-
-
Hey Bruce, good to see you. First off, we know that SQL UPDATE does work. If it didn’t someone would have noticed before now. That means the most likely thing is a malformed query. When I run into this issue, I usually print out the concatenated query and make sure it says what I think it says.
I believe that in your case you will see that you forgot to surround string values with single quotes. Your concatenation should look something like this:
Code:"UPDATE Clients SET First = '"..sFirst.."', Last = "'..sLast.."', Cell = '"..sCell.."', CarrierID = "..nCarrierID..", GroupID = "..nGroupID.." WHERE ID = "..nID
Comment
-
-
you get the selection of ComboBox_Group here
Code:local nGroupSel = ComboBox.GetSelected("ComboBox_Group");
Code:local nGroupID = ComboBox.GetItemData([B][COLOR="red"]"ComboBox_Carrier"[/COLOR][/B], nGroupSel)
Code:SQLite.Query(db,"INSERT IGNORE INTO Clients VALUES (NULL, "..Enclose(sFirst)..", "..Enclose(sLast)..", "..Enclose(sCell)..", "..nCarrierID..", "..nGroupID..")")
Code:SQLite.Query(db,"UPDATE Clients SET First = "..Enclose(sFirst)..", Last = "..Enclose(sLast)..", Cell = "..Enclose(sCell)..", CarrierID = "..nCarrierID..", GroupID = "..nGroupID.." WHERE ID = "..nID);
Comment
-
Comment