Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Apr 2007
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Take value from checkboxs and insert into DB

    I have 2 page asp
    1. page1.asp
    2. page2.asp

    At page1 I have 1 form include number checkbox equal number record in DB (which I used query to take) (checkboxs have the same name, value="rs('user')" )
    Then I check in checkboxs...I want to take value of them at page2 and insert into database...
    But then I used request.form("checkboxname"), I received 1 string as:" user1,user2,user3,user4" (as checkboxs which I choosed)

    What must I do to insert serial values into the same filed in database ...
    ex : user1 into group1, user2 into group1, user3 into group1, user4 into group1

    Somebody help me...

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Welcome the SPLIT() function and array handling... I do this on a daily basis, so SPLIT would be the best way to start. Also, if you do not understand one-dimensional arrays, you need to study up on that first.

    Code:
    <%
    chkBoxArray = SPLIT(request.form("checkboxname"),", ")
    '
    strSQL = "INSERT INTO tblTester (Group1) VALUES "
    '
    for x = 0 to ubound(chkBoxArray)-1
      strSQL = strSQL & "('" & chkBoxArray(x) & "'),"
    next
    '
    Erase chkBoxArray
    '
    strSQL = left(strSQL,len(strSQL)-1)
    '
    response.write(strSQL)
    response.flush
    %>
    Now for the explanation:

    Code:
    chkBoxArray = SPLIT(request.form("checkboxname"),", ")
    Your HTML form returns checkboxes as ", ". So, the SPLIT function will make an array out of your variable based on a delimiter. The delimiter can be ANYTHING, but in this case, it's ", ".

    As we all know (or should know), each single quote on an empty line tells ASP to skip to the next line (or comment).

    Code:
    strSQL = "INSERT INTO tblTester (Group1) VALUES "
    Start of the SQL statement.

    Code:
    for x = 0 to ubound(chkBoxArray)-1
      strSQL = strSQL & "('" & chkBoxArray(x) & "'),"
    next
    This is the array loop used to get the values out of the form. Starting at 0 (since all ASP arrays are zero-based), you will use the UBOUND() to get the upper limit of the array. Since HTML checkboxes ALWAYS add a ", " to everything, we don't want the highest number, but the second highest number, ubound()-1. The 'strSQL' SQL code is continued on with parentheses, single quotes, the HTML form value based on the array number, and lastly, single quotes, close parentheses, and a comma (to denote next value to enter into the database). The 'NEXT' loops you through this until you have reached the UPPER BOUND (ubound()).

    Code:
    Erase chkBoxArray
    This is cleanup code. Always remember to destroy variables and arrays when you are done using them.

    Code:
    strSQL = left(strSQL,len(strSQL)-1)
    Again, since we were using a LOOP, we have an extra comma on the end of the SQL statement. The line above removes that extra comma.

    Code:
    response.write(strSQL)
    response.flush
    Show me the command and flush the buffers. Use this to test your SQL to make sure that you have it right the first time.

    Let me know if this is in anyway confusing or if you need any more help with this.
    Last edited by Daemonspyre; 04-04-2007 at 07:55 PM.

  • #3
    New Coder
    Join Date
    Apr 2007
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot

    but there are some issues... (sorry because my English is so bad ! )

    After I response strSQL on the screen, result:
    Code:
    INSERT INTO tblGroupUser(User) VALUES ('user1'),('user2'),('user3')
    (while I choosed 4 users, not three...It's wrong !)

    Ah, I will say about my table...so you can easy to help me...
    Table name : tblGroupUser have 3 fields:
    GroupUserID : IDENTITY(1)
    GroupID: int
    UserID: int

    I want to insert all user into the same group, I think that I must run 4 INSERT command to insert...
    ex :
    Code:
    INSERT INTO tblGroupUser(GroupID,UserID) 
    VALUES ('1','userID1')
    INSERT INTO tblGroupUser(GroupID,UserID)
    VALUES ('1','userID2')
    ....
    But if I choose n UserID in checkboxes, then I'll must run n INSERT Command, how must I do ?
    Hope I explain enough to you understand...
    (this code is really important with me pls help me soon...)

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Take out the "-1" from the ubound statement. That will let you see all four.

    For your code syntax, it would be:

    Code:
    <%
    chkBoxArray = SPLIT(request.form("checkboxname"),", ")
    '
    strSQL = "INSERT INTO tblGroupUser (GroupID,UserID) VALUES "
    '
    for x = 0 to ubound(chkBoxArray)
      strSQL = strSQL & "(1,'" & chkBoxArray(x) & "'),"
    next
    '
    Erase chkBoxArray
    '
    strSQL = left(strSQL,len(strSQL)-1)
    '
    response.write(strSQL)
    response.flush
    %>
    You don't have to put single quotes around numbers.

    You can see that I changed your strSQL code slightly to match your table.

    You will also see that the "-1" from the ubound() statement has been removed.

    Let me know if this fixes your problem.

  • #5
    New Coder
    Join Date
    Apr 2007
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok !

    But issue at here is INSERT command to insert data into DB is wrong:
    With fix...Now command as follow:
    Code:
    INSERT INTO tblGroupUser (GroupID, UserID) 
    VALUES (1,'lannh'),(1,'huudq'),(1,'linhnt'),(1,'baochau')
    While syntax of INSERT command as:
    Code:
    INSERT INTO table(field1,field2) VALUES ('value1','value2')
    I tried run INSERT cmd that is the first result ... but it's wrong (Incorrect syntax near ',' )

    what must i do

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    you have to create more than one sql, like
    Code:
    INSERT INTO tblGroupUser (GroupID, UserID) VALUES (1,'lannh');
    INSERT INTO tblGroupUser (GroupID, UserID) VALUES (1,'huudq');
    INSERT INTO tblGroupUser (GroupID, UserID) VALUES (1,'linhnt');
    INSERT INTO tblGroupUser (GroupID, UserID) VALUES (1,'baochau')
    note the semicolons between the statements

    Code:
    <%
    chkBoxArray = SPLIT(request.form("checkboxname"),", ")
    
    ' start with an empty string
    strSQL = ""
    
    'for each array element, add a full insert statement
    for x = 0 to ubound(chkBoxArray)
      strSQL = strSQL & "INSERT INTO tblGroupUser (GroupID,UserID) VALUES (1,'" & chkBoxArray(x) & "');"
    next
    
    ' clean up
    Erase chkBoxArray
    
    'show sql
    response.write(strSQL)
    
    ' execute the sql here
    connectionobject.execute(strSQL)
    response.flush
    %>
    I am the luckiest man in the world

  • #7
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    It is NOT necessary to use more than one INSERT statement. You can insert multiple values using 1 INSERT. I know, as I just inserted more than 1000 records into a database using 1 INSERT.

    Code:
    INSERT INTO tmpTABLE (tstFld1,tstFld2) VALUES 
    (1,1),
    (1,2),
    .
    .
    .
    (1,1000)
    I do not know why you would think that you would need more. If you do use more, then you are executing a SCRIPT, not an INSERT statement which requires different permissions to your database/table.

    According to your table definition, your UserID field is an INT field, so entering in 'lannh' WOULD cause the statement to fail.

    Ah, I will say about my table...so you can easy to help me...
    Table name : tblGroupUser have 3 fields:
    GroupUserID : IDENTITY(1)
    GroupID: int
    UserID: int
    Change your UserID field to a VARCHAR(6) using:

    Code:
    ALTER TABLE tblGroupUser MODIFY `UserID` `UserID` VARCHAR(6) DEFAULT NULL;


    If you are worried about single-quotes, then change the strSQL statement to:

    Code:
    strSQL = strSQL & "('1','" & chkBoxArray(x) & "'),"
    But as a matter of course, SQL does not need single quotes around numbers inserted into INT fields.
    Last edited by Daemonspyre; 04-05-2007 at 01:28 PM.

  • #8
    New Coder
    Join Date
    Apr 2007
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To DaemonSpyre : thanks a lot about your idea...but really I can't insert data by:
    Code:
    INSERT INTO tmpTABLE (tstFld1,tstFld2) VALUES 
    (1,1),
    (1,2),
    .
    .
    .
    (1,1000)
    (syntax error; I run this command in Query Analyzer of SQL Server2000)
    But I'm very happy with knowledges which you teached me ! thanks

    To Roelf : thanks about ur idea...I try it , and success !

    Thanks everybody so much ^_^

  • #9
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    The query above was automatically generated by a script I wrote, it was not designed to be used. It was only for example.

    Glad we could help solve your problem.

    If you would like to try to do a multi-value, single INSERT statement, try this:

    Code:
    INSERT INTO tblGroupUser (GroupID, UserID) VALUES (1,'lannh'),(1,'huudq'),(1,'linhnt'),(1,'baochau')
    You will see that it works.


  •  

    Posting Permissions

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