SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 46 of 46
  1. #26
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by joeyramirez View Post
    How do I split this values 1, 3, 4, 5 to be inserted like INSERT INTO tbltest (pid) VALUES ('1),('3'),('4'),('5');
    If the data is string data then the VALUES should have single quotes (') around them (the first one is missing its end quote).

    If the data is numeric it should not have quotes around it: INSERT INTO tbltest (pid) VALUES (1),(3),(4),(5);
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  2. #27
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is the actual statement
    HTML Code:
       
    <%
    Dim sql,exec
    sql="INSERT INTO tbltest (pid) VALUES ("&Request.QueryString("chkChosenField")&")"
    Set exec=Conn.Execute(sql)
    If err.number=0 then
    Response.Write "Save Completed"
    
    End If
    Conn.Close
    Set exec=nothing
    %>

  3. #28
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But how can i pass those values (1,3,4,5) to the INSERT STATEMENT INSERT INTO tbltest (pid) VALUES (1),(3),(4),(5); I always get 'Column count doesn't match value count at row 1' when submitting multiple check box values.

  4. #29
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    It would be cool if you could use replace() to do this but I think you're going to need to do some string manipulation to make this work and it gets a bit complicated.

    This isn't very imaginative but try this.... Split Request.QueryString("chkChosenField") on the comma into an array and then loop the array into a string formatted for your SQL query.

    Something like this:

    Code:
    <%
    
    ' DECLARE YOUR VARIABLES
    ChosenFieldString = Request.QueryString("chkChosenField") 
    ChosenFieldArray = Split(ChosenFieldString, ",") 
    ChosenFieldValues = ""
    
    ' LOOP THROUGH THE ARRAY AND CREATE A STRING
    for each value in ChosenFieldArray
        ChosenFieldValues = ChosenFieldValues & "(" & value & "), "
    next
    
    ' THERE IS ONE EXTRA COMMA IN THE ChosenFieldValues STRING SO YOU NEED TO TRIM THAT
    ChosenFieldValues = left(ChosenFieldValues, len(ChosenFieldValues)-1)
    
    ' FINALLY CREATE YOUR SQL STRING
    sql = "INSERT INTO tbltest (pid) VALUES " &  ChosenFieldValues
    
    %>
    It's been a while since I've done ASP coding so hopefully I haven't mangled the syntax but that should do the job... Just in case I would remark out Set exec=Conn.Execute(sql) and put a Response.Write sql statement to print the Query to the screen so you can see if it looks right before trying it.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  5. #30
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks sir. I appreciate the help. I'll try your code and see if it works.

  6. #31
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can't make it work. Should I display it like this ? Response.Write(Request("ChosenFieldValues")) to see the values?

  7. #32
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok.got it now and it displayed INSERT INTO tbltest (pid) VALUES (1), ( 5), ( 6),

    how do i remove the last comma?

  8. #33
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Yes, and I see why.

    I only removed one character from the end of the string, thinking it would be the comma but I forgot I also added a space after each comma which isn't necessary. Try this (I just removed the space in the loop ChosenFieldValues = ChosenFieldValues & "(" & value & "), "):

    Code:
    <%
    
    ' DECLARE YOUR VARIABLES
    ChosenFieldString = Request.QueryString("chkChosenField") 
    ChosenFieldArray = Split(ChosenFieldString, ",") 
    ChosenFieldValues = ""
    
    ' LOOP THROUGH THE ARRAY AND CREATE A STRING
    for each value in ChosenFieldArray
        ChosenFieldValues = ChosenFieldValues & "(" & value & "),"
    next
    
    ' THERE IS ONE EXTRA COMMA IN THE ChosenFieldValues STRING SO YOU NEED TO TRIM THAT
    ChosenFieldValues = left(ChosenFieldValues, len(ChosenFieldValues)-1)
    
    ' FINALLY CREATE YOUR SQL STRING
    sql = "INSERT INTO tbltest (pid) VALUES " &  ChosenFieldValues
    
    %>
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  9. #34
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face

    Thanks sir.I got it now. I really appreciate the help.

  10. #35
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by joeyramirez View Post
    Thanks sir.I got it now. I really appreciate the help.
    Great! Glad I could help out.

    Cheers,
    Andrew
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  11. #36
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How can I display

    INSERT INTO tbltest (pid,ticketnumber) VALUES (1,1000),(2,1000),(3,1000)

    where 1000 is the ticketnumber and 1,2 and 3 are the PIDs.

    my query looks like this :

    sql = "INSERT INTO tbltest (pid,ticketnumber) VALUES " & ChosenFieldValues & , Request("tnum")

    Request("tnum") is ticket number

    How can I put the ticketnumber in the array?

    HTML Code:
    Dim ChosenFieldString,ChosenFieldArray,ChosenFieldValues,sql,exec,value
    ChosenFieldString = Request.QueryString("chkChosenField")
    ChosenFieldArray = Split(ChosenFieldString, ",") 
    
    
    
    for each value in ChosenFieldArray  
        ChosenFieldValues = ChosenFieldValues & "(" & value & ")," 
    next
    
    ChosenFieldValues = left(ChosenFieldValues, len(ChosenFieldValues)-1)
    
    
    sql = "INSERT INTO tbltest (pid,ticketnumber) VALUES " & ChosenFieldValues & , Request("tnum")
    

  12. #37
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it now.
    HTML Code:
    for each value  in ChosenFieldArray  
        ChosenFieldValues = ChosenFieldValues & "(" & value & ","& Request("tnum")& ")," 
    next

  13. #38
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you will find that searching for a specific value inside the column is very, very painful
    Storing (and searching) multiple Bit values in a single column is in fact very simple

    Code SQL:
    CREATE TABLE [#checkbox](
    	[ID] [INT] IDENTITY(1,1) NOT NULL,
    	[checkbox1] [bit] NOT NULL,
    	[checkbox2] [bit] NOT NULL,
    	[checkbox3] [bit] NOT NULL,
    	[checkbox4] [bit] NOT NULL)
     
    CREATE TABLE [#bitwise](
    	[ID] [INT] IDENTITY(1,1) NOT NULL,
    	[bitwise] [INT] NOT NULL)
     
    DECLARE @checkbox1 bit
    DECLARE @checkbox2 bit
    DECLARE @checkbox3 bit
    DECLARE @checkbox4 bit
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SELECT checkbox1, checkbox2, checkbox3, checkbox4 FROM #checkbox
    SELECT CASE WHEN bitwise & 1 = 1 THEN 1 ELSE 0 END AS checkbox1
          ,CASE WHEN bitwise & 2 = 2 THEN 1 ELSE 0 END AS checkbox2
          ,CASE WHEN bitwise & 4 = 4 THEN 1 ELSE 0 END AS checkbox3
          ,CASE WHEN bitwise & 8 = 8 THEN 1 ELSE 0 END AS checkbox4
    FROM   #bitwise

  14. #39
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    dear verschha, at the time i made that post, we did not know what sort of multiple values were being discussed in this thread, did we

    perhaps i should have said comma-delimited values in a single varchar column are extremely painful to search

    your demo with bit strings is very interesting, but not all that useful, as disk space nowadays is pretty cheap and the need for bit strings is fairly rare
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #40
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    dear verschha, at the time i made that post, we did not know what sort of multiple values were being discussed in this thread, did we
    Yes we did...

    Quote Originally Posted by joeyramirez View Post
    How to insert multiple checkbox values

  16. #41
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no we didn't
    Code:
    <form ...>
    <input type="checkbox" name="stooge1" value="curly" checked="checked">Curly<br>
    <input type="checkbox" name="stooge2" value="larry">Larry<br>
    <input type="checkbox" name="stooge3" value="moe" checked="checked">Moe<br>
    </form>
    please, where are the bits?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #42
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you really mean that you don't understand that a checkbox (ussualy) represents a bit field in a database?
    I see 3 checkboxes, which will ussauly be stored in the database as 3 bit values. However, I showed you that you can easily store multiple checkbox values in 1 integer field as well.

  18. #43
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by verschha View Post
    Do you really mean that you don't understand that a checkbox (ussualy) represents a bit field in a database?
    i understand it, but i don't believe it

    please cite some statistics for your claim

    in my experience, checkboxes are ~not~ usually stored in a database as bits
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #44
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Storing checkbox values to Database

    What is the best way to insert many checkbox inputs into database(mysql)?

  20. #45
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by joeyramirez View Post
    What is the best way to insert many checkbox inputs into database(mysql)?
    one value per row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #46
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    google "asp -.net insert checkbox values into database"

    you will find many examples and discussion of this topic


Bookmarks

Posting Permissions

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