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.
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 58
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    need help with this search page

    I have a search page and the values searched are loc and title. I have a dept table which has these entries and when users search, they search the dept table for results. The titles in dept table are:

    Receptionist
    Customer Service Assistant
    Front Desk Clerk
    Data Entry Clerk
    Computer Operator
    Data Entry

    when the users select loc as 'North Carolina' and title as 'Receptionist', I am trying to bring all the results related to 'Receptionist' such as :

    Customer Service Assistant
    Front Desk Clerk

    and for 'Data Entry Clerk':

    Computer Operator
    Data Entry

    etc.,etc.,

    I am trying to use a Select Case to implement this search such as:
    Code:
    Select Case title 
    Case "A" 
    A = "Receptionist" 
    A = "Front Desk Clerk" 
    A = "Customer Service Assistant" 
    Case "B" 
    B = "Data Entry Clerk" 
    B = "Data Entry" 
    B = "Computer Operator" 
    End Select
    And my code doesn't seem to work. Can someone please let me know whether my approach to the search is right, if not what would be the best way to implement this search? Thanks in advance for any help provided.

  • #2
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Example on a page receiving a post of a SQL statement based on two listboxes (Rep and Customer) that might each have a value of "" (i.e. blank) to show all with no criteria restrictions.

    ' Get posted form vars.
    Rep = Request.Form("Rep")
    Customer = Request.Form("Customer")

    ' Set strSQL.
    strSQL = "SELECT * FROM MyTable WHERE (1=1)"
    If Rep <> "" Then
    strSQL = strSQL & " AND (Rep='" & Rep & "')"
    End If
    If Customer <> "" Then
    strSQL = strSQL & " AND (Customer='" & Customer & "')"
    End If

    Response.Write "strSQLWhere: " & strSQLWhere

    And to see something like this in action you can do the following:
    - Go to http://www.bullschmidt.com/login.asp (the ASP Web database demo's login page)
    - Click the Continue button
    - On the Main Menu click the Invoices Edit button
    - On the Invoices Edit Search Dialog's Customer listbox notice that you can choose a rep and/or customer and then click the Edit button to see all the invoices for that rep and/or customer
    Last edited by Bullschmidt; 07-09-2004 at 12:11 AM.
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is my full coding:

    Here is my code:

    <%

    Dim MyConn, SQL, RS campus, title, A,B

    A = "Receptionist"
    B = "Data Entry"

    campus = request.form("campus")
    title = request.form("title")

    If title = "A" Then
    A = "Receptionist"
    Elseif title = "A" Then
    A = "Customer service Assitance"
    Elseif title = "A" Then
    A = "Front Desk Clerk"
    End If

    If title = "B" Then
    B = "Data Entry"
    Elseif title = "B" Then
    B = "Computer Operator"
    End If

    SQL = "Select campus, title, hours from Mytable where campus Like '"&%campus%&"' And title Like '"&%title%&"'"

    Set MyConn=Server.CreateObject("ADODB.Connection")
    MyConn.Open "MyConnection"

    Set RS = Server.Createobject("Adodb.Recordset")

    Set RS=MyConn.Execute (sql)

    Response.Write "<center><table border=""0"" width='50%'>"


    If RS.EOF Then
    Response.write "No records were found for : "
    Response.write"<font color=""cc3300"">"
    Response.write campus & "," & title
    Response.write"</font>"
    Else
    Do While Not RS.EOF
    Response.write"<tr bgcolor=silver >"
    Response.write "<td >" & RS("campus") & "</td>"
    Response.write"<td >" & RS("title") & "</td>"
    Response.write"<td >" & RS("hours") & "</td>"
    Response.write"</tr>"



    RS.Movenext

    Loop
    RS.Close
    Response.write "</table>"
    End If
    MyConn.Close
    Set RS = Nothing
    Set MyConn = Nothing
    %>
    The above code is search page code which searches the dept table to find the information. The dept table have all detailed information such as Receptionist, Customer Service Assistant, and Front Desk Assistant. But the search criteria in search page has only Receptionist in the drop down list, which it should be able to pull all records from the dept table related to receptionist. That is why I am doing a Select Case or even if statement to let program know that it has to pull. I am kind of stuck in that part.

    In your example, "select * mytable where (1=1)" what does 1=1 do?
    Code:
    strSQL = "SELECT * MyTable WHERE (1=1)"
    If Rep <> "" Then
    strSQL = strSQL & " AND (Rep='" & Rep & "')"
    End If
    If Customer <> "" Then
    strSQL = strSQL & " AND (Customer='" & Customer & "')"
    End If
    By looking at my code, can you please tell how I can improve my coding in order to do the stuff I want to do.

  • #4
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    <<
    In your example, "select * mytable where (1=1)" what does 1=1 do?
    >>

    Well actually I just edited my above post to now include the word FROM in the SQL statement. So the code now looks like this:

    ' Get posted form vars.
    Rep = Request.Form("Rep")
    Customer = Request.Form("Customer")

    ' Set strSQL.
    strSQL = "SELECT * FROM MyTable WHERE (1=1)"
    If Rep <> "" Then
    strSQL = strSQL & " AND (Rep='" & Rep & "')"
    End If
    If Customer <> "" Then
    strSQL = strSQL & " AND (Customer='" & Customer & "')"
    End If

    Response.Write "strSQLWhere: " & strSQLWhere

    And the (1=1) above is there as a placeholder (which doesn't affect the results since it is always true) as the SQL statement possibly may use AND with criteria after it or possibly the SQL statement may not have anything after it. For example a SQL statement without the (1=1) like this would not work: SELECT * FROM MyTable WHERE AND Customer='API'
    Last edited by Bullschmidt; 07-09-2004 at 12:14 AM.
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  • #5
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,032
    Thanks
    0
    Thanked 248 Times in 245 Posts
    Quote Originally Posted by Bullschmidt
    And the (1=1) above is there as a placeholder (which doesn't affect the results since it is always true) as the SQL statement possibly may use AND with criteria after it or possibly the SQL statement may not have anything after it. For example a SQL statement without the (1=1) like this would not work: SELECT * FROM MyTable WHERE AND Customer='API'
    You can solve it without 1=1 like this:
    Code:
    dim whereClause
    ' Set strSQL.
    strSQL = "SELECT * FROM MyTable WHERE "
    If Rep <> "" Then
      whereClause="(Rep='" & Rep & "')"
    End If
    If Customer <> "" Then
       if whereClause <> "" then
          whereClause = whereClause & " AND (Customer='" & Customer & "')"
       else
           whereClause = "(Customer='" & Customer & "')"
       end if
    End If
    strSQL = strSQL & whereClause

  • #6
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    glenngv,

    I have to change only my sql statement, the rest stays the same, right? That is the Select Statement because how will the problem know to pull all records based on 'Receptionist' or sql statement provided by you and Bullschmidt does all that I want to do? Please explain. Thanks.

  • #7
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,032
    Thanks
    0
    Thanked 248 Times in 245 Posts
    Yes, the rest stays the same. Just test it, I just simplified Bullschmidt's script.

  • #8
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thankyou. I will test it and see.

  • #9
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried testing my search page, and it is not doing what I want it to do. That is if I select 'Receptionist' then it should display all the results related to 'Receptionist' - Customer Service Assistant, Front Desk Clerk, etc from the database. What it does right now, if the chosen title is receptionist from the drop down list from the first page, then it shows 'No record found for receptionist' even if customer service assistant, or front desk clerk are there in the database.

  • #10
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,032
    Thanks
    0
    Thanked 248 Times in 245 Posts
    Response.write the SQL statement to see if it's correct as you expect it and then try running it in the Query Analyzer to see if there will be records retrieved.

    strSQL = strSQL & whereClause
    response.write strSQL

    That's how you debug database-driven codes.

  • #11
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did response.write strsql and got the response as title='receptionist'. Database used MS Access.

  • #12
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,032
    Thanks
    0
    Thanked 248 Times in 245 Posts
    Can you post the whole SQL statement? And did you try executing it in the SQL View of Access to verify that it retrieves records?

  • #13
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    <%

    Dim MyConn, SQL, RS loc, title, A,B, whereclause

    A = "Receptionist"
    B = "Data Entry"

    loc= request.form("loc")
    title = request.form("title")

    Select Case title
    case "A"
    A = "Receptionist"
    A = "Front Desk Assistant"
    A = "Customer Service Assistant"
    case "B"
    B = "Data Entry"
    B = "Computer Operator"
    End Select



    SQL = "SELECT * FROM MyTable WHERE "
    If title <> "" Then
    whereClause="(title='" & title & "')"
    End If
    If loc <> "" Then
    if whereClause <> "" then
    whereClause = whereClause & " AND (loc='" & loc & "')"
    else
    whereClause = "(loc='" & loc & "')"
    end if
    End If
    SQL = SQL & whereClause

    'database connection
    Set MyConn=Server.CreateObject("ADODB.Connection")
    MyConn.Open "MyConnection"

    Set RS = Server.Createobject("Adodb.Recordset")

    Set RS=MyConn.Execute (sql)

    'display of records
    Response.Write "<center><table border=""0"" width='50%'>"


    If RS.EOF Then
    Response.write "No records were found for : "
    Response.write"<font color=""cc3300"">"
    Response.write loc & "," & title
    Response.write"</font>"
    Else
    Do While Not RS.EOF
    Response.write"<tr bgcolor=silver >"
    Response.write "<td >" & RS("loc") & "</td>"
    Response.write"<td >" & RS("title") & "</td>"
    Response.write"</tr>"



    RS.Movenext

    Loop
    RS.Close
    Response.write "</table>"
    End If
    MyConn.Close
    Set RS = Nothing
    Set MyConn = Nothing
    %>

  • #14
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can someone please help? I need desperate help. If someone can come forward and check my coding and tell what I am missing to implement this search? I am trying to bring all the related fields from the database by assigning a value for each title.

  • #15
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    can someone take a look at my code and let me know what may be wrong? I don't know why it is not displaying as specified? Is there a problem with Select Statement or the SQL Statement? Thanks.


  •  
    Page 1 of 4 123 ... LastLast

    Posting Permissions

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