Indigo Rose Software

Professional Software Development Tools

 
+ Reply to Thread
Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2001
    Location
    U.S.A - California
    Posts
    2,134

    Database Dilemma

    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?


  2. #2
    Join Date
    Feb 2007
    Location
    Como, Italy
    Posts
    1,632
    I'm not skilled at all with databases, but posting the source code probably could give others the possibility to check what's wrong in your code.

    Otherwise only with a magic ball people can find why.
    Attached Images
    We are slowly invading your planet to teach lazy humans to read the user manual.
    But don't be scared: we are here to help.

  3. #3
    Join Date
    Jun 2001
    Location
    U.S.A - California
    Posts
    2,134
    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 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

  4. #4
    Join Date
    Jun 2001
    Location
    U.S.A - California
    Posts
    2,134
    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

  5. #5
    Join Date
    Jun 2001
    Location
    U.S.A - California
    Posts
    2,134
    BUMP... anyone? I am mysql brain dead.

  6. #6
    Join Date
    Jul 2010
    Posts
    156
    SQLite.Query(db,"INSERT 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.

  7. #7
    Join Date
    May 2006
    Posts
    5,556
    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) lol
    Last edited by RizlaUK; 05-09-2012 at 11:12 AM.
    Embrace change in your life, you never know, it could all work out for the best

  8. #8
    Join Date
    Jul 2010
    Posts
    156
    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??

  9. #9
    Join Date
    May 2006
    Posts
    5,556
    that meant to read

    if you insert with a "WHERE ID=" row id it will bump
    Embrace change in your life, you never know, it could all work out for the best

  10. #10
    Join Date
    Jun 2001
    Location
    U.S.A - California
    Posts
    2,134
    No UPDATE did not work, nor did INSERT OR REPLACE INTO :-b

    The working file is here: http://www.indigorose.com/forums/thr...ee-SMS-project
    under the Clients page/ADD button
    Last edited by Bruce; 05-09-2012 at 02:40 PM.

  11. #11
    Join Date
    May 2003
    Location
    Pendleton, Oregon
    Posts
    1,056
    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
    On second look, your Enclose must be some function that puts the single quotes around the string? Not sure because I don't see the function. I don't kmow, but the concat I wrote above should work.

  12. #12
    Join Date
    Jun 2001
    Location
    U.S.A - California
    Posts
    2,134
    Thanks Jim I'll take a look.

  13. #13
    Join Date
    May 2006
    Posts
    1,688
    you get the selection of ComboBox_Group here
    Code:
    local nGroupSel = ComboBox.GetSelected("ComboBox_Group");
    but you get data from ComboBox_Carrier here
    Code:
    local nGroupID = ComboBox.GetItemData("ComboBox_Carrier", nGroupSel)
    and then you use wrong nGroupID here (insert)

    Code:
    SQLite.Query(db,"INSERT INTO Clients VALUES (NULL, "..Enclose(sFirst)..", "..Enclose(sLast)..", "..Enclose(sCell)..", "..nCarrierID..", "..nGroupID..")")
    or here (update)

    Code:
    SQLite.Query(db,"UPDATE Clients SET First = "..Enclose(sFirst)..", Last = "..Enclose(sLast)..", Cell = "..Enclose(sCell)..", CarrierID = "..nCarrierID..", GroupID = "..nGroupID.." WHERE ID = "..nID);
    amsplugins.com Is Closed.

    Facebook Page

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts