SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast Mangofreak's Avatar
    Join Date
    Aug 2004
    Location
    Toronto
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL/ASP Query problem

    help please.

    I have dynamic menu that lists cities where member contracting companies of my organization are based. I select the city and a result page should only list the Contractors for that city. I have tried many combination on my query but I have only been successfull listing the whole db.

    How can I indicate the value (city) in my query for it to list only the records for that city.

    My last query attempt is below, please any help is greatly appreciated:

    SELECT cityID, cityName, UserID, companyName, companyAddress, cityContractors, postal, phone
    FROM cityContractors, users

    In case more details are needed:

    My db has 2 tables:
    1. Users
    2. cityContractors

    both tables are related by a column named cityNames on the Users table.

    thanks

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try thsi code:

    Dim rsCity
    if (Request.QueryString("City") <> "") then rsCity = Request.QueryString("City")

    "SELECT cityID, cityName, UserID, companyName, companyAddress, cityContractors, postal, phone FROM cityContractors WHERE cityName LIKE '%" + Replace(rsCity, "'", "''") + "%' Order by cityName ASC"


    www.ex-designz.net
    Dexter

  3. #3
    SitePoint Enthusiast Mangofreak's Avatar
    Join Date
    Aug 2004
    Location
    Toronto
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply Dexter. Unfortunately I was not able to make it work.

    To create my queries I'm using DreamweaverMX. I hope it is not a problem.

    So far I can get the results showing the records on my db. However, I still cannot isolate the results to a specific city.

    What I want to understand is how do I identify the value on my menu Select (it could be 1 of 238 possible values).
    Thanks for your help!!

    This is the code on my search page:

    //top of page
    <%
    Dim rsContractorSearch
    Dim rsContractorSearch_numRows

    Set rsContractorSearch = Server.CreateObject("ADODB.Recordset")
    rsContractorSearch.ActiveConnection = MM_connContractorSearch1_STRING
    rsContractorSearch.Source = "SELECT * FROM cityContractors ORDER BY cityName ASC"
    rsContractorSearch.CursorType = 0
    rsContractorSearch.CursorLocation = 2
    rsContractorSearch.LockType = 1
    rsContractorSearch.Open()

    rsContractorSearch_numRows = 0
    %>

    //on the body
    <form action="contractor_results.asp" method="get" name="Searchform">
    <table width="100%" border="0" cellpadding="6" cellspacing="0" id="search">
    <tr>
    <td width="25">&nbsp;</td>
    <td class="subHeader">&nbsp;</td>
    <td class="subHeader">Select a city from the list below: </td>
    </tr>
    <tr>
    <td width="25">&nbsp;</td>
    <td class="formTitle"></td>
    <td class="formField"><select name="citymenu">
    <%
    While (NOT rsContractorSearch.EOF)
    %>
    <option value="<%=(rsContractorSearch.Fields.Item("cityName").Value)%>" <%If (Not isNull((rsContractorSearch.Fields.Item("cityID").Value))) Then If (CStr(rsContractorSearch.Fields.Item("cityName").Value) = CStr((rsContractorSearch.Fields.Item("cityID").Value))) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(rsContractorSearch.Fields.Item("cityName").Value)%></option>
    <%
    rsContractorSearch.MoveNext()
    Wend
    If (rsContractorSearch.CursorType > 0) Then
    rsContractorSearch.MoveFirst
    Else
    rsContractorSearch.Requery
    End If
    %>


    </select>
    <input type="submit" name="Submit" value="go"></td>


    Quote Originally Posted by dexterz
    Try thsi code:

    Dim rsCity
    if (Request.QueryString("City") <> "") then rsCity = Request.QueryString("City")

    "SELECT cityID, cityName, UserID, companyName, companyAddress, cityContractors, postal, phone FROM cityContractors WHERE cityName LIKE '%" + Replace(rsCity, "'", "''") + "%' Order by cityName ASC"


    www.ex-designz.net
    Dexter

    My results' page code is the following:

    //top of the page
    <%
    Dim rsContractorResults_citymenu
    if (Request.QueryString("cityID") <> "") then rsContractorResults = Request.QueryString("cityName")
    Dim rsContractorResults
    Dim rsContractorResults_numRows
    Set rsContractorResults = Server.CreateObject("ADODB.Recordset")
    rsContractorResults.ActiveConnection = MM_connContractorSearch1_STRING
    rsContractorResults.Source = "SELECT cityID, cityName, UserID, companyName, companyAddress, cityContractors, postal, phone FROM cityContractors, users WHERE cityName = '"& citymenu &"' ORDER BY cityID"

    rsContractorResults.CursorType = 0
    rsContractorResults.CursorLocation = 2
    rsContractorResults.LockType = 1
    rsContractorResults.Open()

    rsContractorResults_numRows = 0
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index

    Repeat1__numRows = 25
    Repeat1__index = 0
    rsContractorResults_numRows = rsContractorResults_numRows + Repeat1__numRows
    %>

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't know you're using Form Post Method.

    Change Request.Form to Request.Form

    Dim rsContractorResults_citymenu
    if (Request.Form("cityName") <> "") then rsContractorResults_citymenu = Request.Form("cityName")
    Dim rsContractorResults
    Dim rsContractorResults_numRows
    Set rsContractorResults = Server.CreateObject("ADODB.Recordset")
    rsContractorResults.ActiveConnection = MM_connContractorSearch1_STRING
    rsContractorResults.Source = "SELECT cityID, cityName, UserID, companyName, companyAddress, cityContractors, postal, phone FROM cityContractors, users WHERE cityName = LIKE '%" + Replace(rsContractorResults_citymenu, "'", "''") + "%' Order by cityName"

    Or try this in your SQL.
    "SELECT cityID, cityName, UserID, companyName, companyAddress, cityContractors, postal, phone FROM cityContractors, users WHERE cityName = LIKE '%" + Replace(rsContractorResults_citymenu, "'", "''") + "%' AND cityContractors.ID = users.ID"

    www.ex-designz.net
    Dexter

  5. #5
    SitePoint Enthusiast Mangofreak's Avatar
    Join Date
    Aug 2004
    Location
    Toronto
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As you may have noticed. I'm fairly new to this tornado of code. I have tried so many combinations of code that I have no idea where I'm at right now. If it is ok. with you. Can I post a basic code of the results page and we build it from there. It would be easier to copy code, but I would like to crack this problem. Please let me know.

    Quote Originally Posted by dexterz
    I didn't know you're using Form Post Method.

    Change Request.Form to Request.Form

    Dim rsContractorResults_citymenu
    if (Request.Form("cityName") <> "") then rsContractorResults_citymenu = Request.Form("cityName")
    Dim rsContractorResults
    Dim rsContractorResults_numRows
    Set rsContractorResults = Server.CreateObject("ADODB.Recordset")
    rsContractorResults.ActiveConnection = MM_connContractorSearch1_STRING
    rsContractorResults.Source = "SELECT cityID, cityName, UserID, companyName, companyAddress, cityContractors, postal, phone FROM cityContractors, users WHERE cityName = LIKE '%" + Replace(rsContractorResults_citymenu, "'", "''") + "%' Order by cityName"

    This should work

    www.ex-designz.net
    Dexter

  6. #6
    Guru Bullschmidt's Avatar
    Join Date
    Apr 2002
    Location
    USA
    Posts
    524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And (as I notice no one else has posted an answer in the past week) perhaps this slightly different approach may hopefully give you some ideas. It's got a downloadable sample and does retain values after the post back:

    Classic ASP Design Tips - Dependent Listboxes
    http://www.bullschmidt.com/devtip-de...tlistboxes.asp
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips


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
  •