SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    More than one checkbox checked and data to be retrieved from sql database

    i am not getting the information from the recordset when more than one checkbox is ticked. Could someone please advise,
    Thankyou


    Function IsChecked(check)
    Dim nChecked
    If (check<> " ") Then
    nChecked = True
    Else
    nChecked = False

    End If
    IsChecked = nChecked
    End Function
    If request("submit")="SUBMIT" then
    'declarationiof variables
    Dim i, check, myArray, where_clause
    'obtaining values from form
    check = Request("checkbox")
    myArray = Split(check,", ")
    where_clause = " "
    For i = 0 To UBound(myArray)
    if(IsChecked(check) = True) then
    if i > 0 then
    where_clause = "acct_code = " &"'"&myArray(i)&"'"& " "&"AND"& " "&where_clause
    else
    where_clause = "acct_code = " &"'"&myArray(i)&"'"
    'response.write(myArray(i))
    end if
    else
    if check = "" then
    Response.Write(" was NOT checked<br>")
    end if
    'response.write(where_clause & "<br>")
    end if
    Next
    qry = "Select name, email, phone, fax, acct_code from acctstable WHERE "& where_clause &""
    response.Write(qry)
    Set oRs= server.CreateObject("adodb.recordset")

    dim pagenum, page
    'pagecnt=oRs.PageCount
    oRs.Pagesize=50
    oRs.CursorLocation = 3
    'oRs.Locktype = 3
    oRs.CursorType = 3
    oRs.open qry, oConn, 3, 3

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,609
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What are you expecting to happen? Your SQL suggests "give me results where account code equals X and account code equals Y". I suspect the AND should be OR.
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried Or but it only brings up the results for one acct_code. I want to bring up the results for more than one acct_code if several are checked on the previous page.

    Thanks

  4. #4
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,609
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Think about how the records are actually stored in your database. Can ONE record have MORE THAN ONE account code? (I suspect not, that why I say OR is probably needed).

    If you do Response.End right after response.Write(qry) what do you see in the browser? (This will indicate if your SQL query is well-formed).
    Ian Anderson
    www.siteguru.co.uk

  5. #5
    SitePoint Addict itHighway's Avatar
    Join Date
    Jan 2008
    Location
    GUJ
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay. First print Request.Form("Checkbox") values to see if all values are properly passed.
    In this type of cases this is what I do:

    txtCheckBox = request.Form("CheckBox")

    '#remove space
    txtCheckBox = replace(txtCheckBox, " ", "")

    '#if database table field is a text field
    txtCheckBox = "'"& replace(txtCheckBox, ",", "','") &"'"


    '#Sql Query
    Where_Clause = " Account_Code IN ("&txtCheckBox&") "

  6. #6
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks I think i have gotten it now. I needed another for loop under the if i>0
    for t= 1 to i

    where_clause = where_clause = "acct_code = " &"'"&myArray(i)&"'"& " "&"OR"& " "&where_clause
    Next


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
  •