Andromo - Start Making Apps - Free Signup

Announcement

Collapse
No announcement yet.

Database Dilemma

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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


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


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


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

          Comment


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

            Comment


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


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


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


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


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


                      • #12
                        Thanks Jim I'll take a look.

                        Comment


                        • #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("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

                          Comment

                          Working...
                          X