Database Dilemma

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts
  • Bruce
    Indigo Rose Customer
    • Jun 2001
    • 2134

    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?

  • Cybergraph
    Indigo Rose Customer
    • Feb 2007
    • 1633

    #2
    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 Files
    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.

    Comment

    • Bruce
      Indigo Rose Customer
      • Jun 2001
      • 2134

      #3
      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

      • Bruce
        Indigo Rose Customer
        • Jun 2001
        • 2134

        #4
        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

        • Bruce
          Indigo Rose Customer
          • Jun 2001
          • 2134

          #5
          BUMP... anyone? I am mysql brain dead.

          Comment

          • Nickj
            Forum Member
            • Jul 2010
            • 180

            #6
            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

            • RizlaUK
              Indigo Rose Customer
              • May 2006
              • 5552

              #7
              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, 11:12 AM.
              Embrace change in your life, you never know, it could all work out for the best

              Comment

              • Nickj
                Forum Member
                • Jul 2010
                • 180

                #8
                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

                • RizlaUK
                  Indigo Rose Customer
                  • May 2006
                  • 5552

                  #9
                  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

                  Comment

                  • Bruce
                    Indigo Rose Customer
                    • Jun 2001
                    • 2134

                    #10
                    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, 02:40 PM.

                    Comment

                    • JimS
                      Indigo Rose Customer
                      • May 2003
                      • 1057

                      #11
                      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. :yes

                      Comment

                      • Bruce
                        Indigo Rose Customer
                        • Jun 2001
                        • 2134

                        #12
                        Thanks Jim I'll take a look.

                        Comment

                        • reteset
                          Indigo Rose Customer
                          • May 2006
                          • 1692

                          #13
                          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([B][COLOR="red"]"ComboBox_Carrier"[/COLOR][/B], nGroupSel)
                          and then you use wrong nGroupID here (insert)

                          Code:
                          SQLite.Query(db,"INSERT IGNORE 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

                          Comment

                          Working...
                          X