Consulta Combo

Fabio AVILA · December 6, 2012

Ola amigos ,

Estou a realizar um sistema de busca atraves de um combo porem estou encontrando 2 dificuldades

1ª é se tiver na tabela SÃO PAULO e SÃO CAETANO quano realizo a busca por SÃO PAULO ele traz também SÃO CAETANO..

2ª se tiver na tabela 3 vezes o nome SÃO PAULO ele mostra na combo SÃO PAULO três ou seja eu gostaria de agrupar e aparecener somente 1 vez

Abaixo meu cod.

<%

Dim strURL     

Dim cnnSearch 
Dim rstSearch  
Dim strDBPath  

Dim strSQL    
Dim strSearch  
inicio = Timer

'#Função que tira todos os acentos das palavras
function TiraAcento(StrAcento)
 for i = 1 to len(StrAcento) 
  Letra = mid(StrAcento, i, 1)
  Select Case Letra
   Case "á","Á","à","À","ã","Ã","â","Â","â","ä","Ä"
   Letra = "A"
   Case "é","É","ê","Ê","Ë","ë","È","è"
   Letra = "E"
   Case "í","Í","ï","Ï","Ì","ì"
   Letra = "I"
   Case "ó","Ó","ô","Ô","õ","Õ","ö","Ö","ò","Ò"
   Letra = "O"
   Case "ú","Ú","Ù","ù","ú","û","ü","Ü","Û"
   Letra = "U"
   Case "ç","Ç"
   Letra = "C"
   Case "não"
   Letra = "N"
  End Select
  texto = texto & Letra
 next
 TiraAcento = texto
end function 


strURL = Request.ServerVariables("URL")

strSearch = Request.QueryString("search")

%>

<%  
if request.queryString("at")= "" then

    db = Server.MapPath("noticias.mdb")
    Set cnn = Server.CreateObject("adodb.Connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& db &";"

    SQL="SELECT * FROM Horarios ORDER by Cidade"
    SET rs = Server.CreateObject("Adodb.recordset")
    rs.Open SQL,cnn 
    if not rs.eof then
        lista = rs.getrows()
        total = ubound(lista,2) 
    end if
    rs.close 
    set rs = nothing
    %><title>:.Gestor de Liga :. </title>
<body bgcolor="#FFFFFF">
<link href="estilos.css" rel="stylesheet" type="text/css" />


<table width="141" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#F2FFF2">
  <tr>
    <td bgcolor="#FFFFFF" width="812">
    <table width="738" border="0" cellpadding="2" style="border-collapse: collapse" height="1">
<tr>
          <td bgcolor="#FFFFFF" height="4" colspan="2" width="890">
            <p align="center">
            <b>
                      <font face="Tahoma" style="font-size: 12pt">
                      <img border="0" src="tp_equipes.jpg" align="left"></font></b></td>
        </tr>            
<tr>
          <td bgcolor="#FFFFFF" valign="middle" height="1" width="569">
            <form action="<%= strURL %>" method="get">
              <div align="left"> 
                <b><font face="Arial" style="font-size: 8pt">  Informe a Cidade </font>
                </b> 
                <span style="font-size: 8pt"> 
                <font face="Arial" color="#008000">
                 <br>
                </font>
                </span>
                    <select name="search" class="select"  Onchange="atualizaLanc(this.value);">
                        <option value="">selecionar...
                        
                        <%
                        for t=0 to total
                            response.write "<option value=" & lista(2,t) & ">" & lista(2,t) & "</option>"
                        next%>
                    </select><font face="Arial" color="#008000"><input name="submit" type="submit" value="busca" style="font-size: 8 pt; font-weight: bold; background-color: #1D2447; color:#FFFFFF; background-image:url('fundo.jpg')" /><span style="font-size: 8pt">
                </span></font>
              </div>
            </form>
            <p align="center"> 
                <font face="Arial" style="font-size: 8pt"> 
                <%
If strSearch <> "" Then
    
           strDBPath = Server.MapPath("Noticias.mdb")
        
Const adUseClient = 3
    
    Set cnnSearch = Server.CreateObject("ADODB.Connection")
    
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
cnnSearch.CursorLocation = adUseClient

strSQL = "SELECT Equipe, Bairro,Cidade,id " _
        & "FROM Horarios " _
        & "WHERE Cidade LIKE '%" & TiraAcento(Replace(strSearch, "'", "''")) & "%' " _
        & "OR Cidade LIKE '%" & TiraAcento(Replace(strSearch, "'", "''")) & "%' " _
        & "ORDER BY Equipe;"

    Set rstSearch = cnnSearch.Execute(strSQL)
intContador = rstSearch.RecordCount



    %> </font>
            </p></td>
          <td bgcolor="#FFFFFF" valign="top" height="1" width="317">
            <p align="center">
            <font face="Arial" style="font-size: 8pt"><a title="Imprimir" href="a">
            &lt;script language="JavaScript1.2">
<!--
function DoPrinting(){
if (!window.print){
alert("Use o Netscape  ou Internet Explorer \n nas versões 4.0 ou superior!")
return
}
window.print()
}
//-->
            </script>
            </a>
            </font></td>
        </tr>
      </table>
      <p align="left">
        <font face="Century Gothic" style="font-size: 8pt"><b>                                                                        <font color="#6E9F4D">         </font><font color="#1D2447">       ( Foram encontradas <%=intContador%> 
        ocorrências )</font></b></font></p>
      <div align="center" style="width: 502; height: 246"> 
        <table width="759" border="0" cellpadding="4" cellspacing="0" bgcolor="#FFFFFF" style="border-collapse: collapse" bordercolor="#111111">
<tr> 
            <th width="39" bgcolor="#1D2447" align="left" style="border-bottom-style: none; border-bottom-width: medium" bordercolor="#99B5C0"><strong>
            <font face="Arial" style="font-size: 8pt" color="#FFFFFF">Cod</font></strong></th>
            <th width="235" bgcolor="#1D2447" align="left" style="border-bottom-style: none; border-bottom-width: medium" bordercolor="#99B5C0">
            <strong><font face="Arial" style="font-size: 8pt" color="#FFFFFF">
            Equipe</font></strong></th>
            <th width="211" bgcolor="#1D2447" align="left" style="border-bottom-style: none; border-bottom-width: medium" bordercolor="#99B5C0">
            <strong><font face="Arial" style="font-size: 8pt" color="#FFFFFF">
            Bairro</font></strong></th>
            <th width="211" bgcolor="#1D2447" align="left" style="border-bottom-style: none; border-bottom-width: medium" bordercolor="#99B5C0">
            <strong><font face="Arial" style="font-size: 8pt" color="#FFFFFF">
            Cidade</font></strong></th>
            <th width="15" bgcolor="#1D2447" align="left" style="border-bottom-style: none; border-bottom-width: medium">
            <font face="Arial" style="font-size: 8pt" color="#FFFFFF">
            Deletar</font></th>
            <th width="15" bgcolor="#1D2447" align="left" style="border-bottom-style: none; border-bottom-width: medium">
            <font face="Arial" style="font-size: 8pt" color="#FFFFFF">
            Editar</font></th>
            <th width="1" bgcolor="#FFFFFF" align="left" style="border-bottom-style: none; border-bottom-width: medium" bordercolor="#99B5C0"> </th>
          </tr>

          
          <%
      
    Do While Not rstSearch.EOF
        %><font face="Century Gothic" style="font-size: 8pt"> </font>
          <tr>
          <%
          
              id = Trim(rstSearch.Fields("id"))
                  Response.Write "<TR style='cursor=hand;' id="& id&" onclick=""consultarClick('"&id&"')""; class='"&fundo&"' "
        Response.Write " onmouseover=""this.className='inputMouse';"" "
        Response.Write " onmouseout=""this.className='"&fundo&"';"">"
        %><font face="Century Gothic" style="font-size: 8pt"> </font> 
            <td width="39" align="left" style="border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium; border-bottom-style:solid; border-bottom-width:1" bordercolor="#6E9F4D" bgcolor="#EBEBEB"><font size="1" face="Arial"><%= rstSearch.Fields("id").Value %></font></td>
            <td width="235" align="left" style="border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium; border-bottom-style:solid; border-bottom-width:1" bordercolor="#6E9F4D" bgcolor="#EBEBEB"><font size="1" face="Arial"><%= rstSearch.Fields("Equipe").Value %></font></td>
            <td width="211" align="left" style="border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium; border-bottom-style:solid; border-bottom-width:1" bordercolor="#6E9F4D" bgcolor="#EBEBEB"><font size="1" face="Arial"><%= rstSearch.Fields("Bairro").Value %></font></td>
            <td width="211" align="left" style="border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium; border-bottom-style:solid; border-bottom-width:1" bordercolor="#6E9F4D" bgcolor="#EBEBEB"><font size="1" face="Arial"><%= rstSearch.Fields("Cidade").Value %></font></td>
            <td bgcolor="#EBEBEB" width="15" align="left" style="border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium; border-bottom-style:solid; border-bottom-width:1" bordercolor="#6E9F4D">
              <p align="center">
              <a href="deletaequipe.asp?id=<%=id%>">
              <img border="0" src="excluir.gif"></a></td>
            <td bgcolor="#EBEBEB" width="15" align="left" style="border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium; border-bottom-style:solid; border-bottom-width:1" bordercolor="#6E9F4D">
              <p align="center">
              <a href="altera_equipes.asp?acao=V&id=<%=id%>">
              <img border="0" src="edita.gif"></a></td>
            <td width="1" align="left" style="border-style: none; border-width: medium; " bordercolor="#6E9F4D">
               </td>
          </tr>
          <%

        rstSearch.MoveNext
    Loop
    %><font face="Century Gothic" style="font-size: 8pt"> </font>
        </table>
        <p align="left"> <p align="left"><b>
        <font face="Century Gothic" style="font-size: 8pt" color="#1D2447">Sua busca foi processada em 
        <%response.write FormatNumber( Timer - inicio, 2 )%>
        segundos.</font><font face="Century Gothic" style="font-size: 8pt" color="#6E9F4D"><br>
        </font></b>
        <font face="Century Gothic" style="font-size: 8pt">
        <%
    
    rstSearch.Close
    Set rstSearch = Nothing
    cnnSearch.Close
    Set cnnSearch = Nothing
End If

%>

Abraços

Marcelo_2 · December 7, 2012

cara.. d boa.. tuas duvidas saum d SQL.. enfim..

questão1:

correto; veja tua sql:

"SELECT Equipe, Bairro,Cidade,id " _
        & "FROM Horarios " _
        & "WHERE Cidade LIKE '%" & TiraAcento(Replace(strSearch, "'", "''")) & "%' " _
        & "OR Cidade LIKE '%" & TiraAcento(Replace(strSearch, "'", "''")) & "%' " _
        & "ORDER BY Equipe;"

pesquise sobre operador OR; aqui, por exemplo:

http://www.devmedia.com.br/introducao-ao-s...s-logicos/16902

questão 2:

pesquise sobre GROUP BY ou DISTINCT

Fabio AVILA · December 9, 2012

Ola Marcelo ,

Tentei usar na combom assim :

[b]SQL="SELECT DISTINCT * FROM Horarios ORDER by Cidade"[/b]

Porem ele fica do mesmo jeito busca todos os nomes da mesma cidade ...

Marcelo_2 · December 10, 2012

cara.. d boa.. seria legal você melhorar teus conhecimentos d SQL.. vai por mim..

mas se eu entendi bem teu problema:

1. select p/ criar a combo:

select distinct cidade from tabela

ou

select cidade from tabela group by cidade

2. select p/ exibir dados da cidade selecionada - declara uma variavel q é a opção selecionada na combo; ahe:

select dados from tabela where cidade=variavel q é a opção selecionada na combo

Edited December 10, 2012 by Marcelo_2

Sign In

Consulta Combo

Question

Fabio AVILA

Link to comment

Share on other sites

3 answers to this question

Recommended Posts

Marcelo_2

Link to comment

Share on other sites

Fabio AVILA

Link to comment

Share on other sites

Marcelo_2

Link to comment

Share on other sites

Join the conversation

Forum Statistics

Browse

Activity