Busqueda en db
Hola! Tengo un sistema en asp que busca en la base de datos, mi problema es que cuando busco datos como "Fernando" o "recovery" no me muestra todos los datos que debería y me lo repite, si quieres echarle un vistazo esta es mi pagina: http://j.1asphost.com/fegasa/critica/search.asp
Intenta buscar "fernando" o "recovery" y veraz lo que te digo.
Aqui te mando el codigo que hace la busqueda:
<%
Dim strInputSearch 'Variable for the search word
Dim strCon 'Holds the string to connect to the db
Dim adoCon 'Database Connection Variable Object
Dim strSQL 'Holds the SQL query for the database
Dim rsSearch 'Holds the search recordset
'This is the variable that has the search word
strInputSearch = Request.Form("txtSearch")
'This makes it so people cant inject SQL code and/or cause some unwanted errors
strInputSearch = Replace(strInputSearch,"'", "''", 1, -1, 1)
'This sets the connection
Set adoCon = Server.CreateObject("ADODB.Connection")
'This is the connection string
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("sistemacd.mdb")
'Open the connection
adoCon.Open strCon
'Set the database connection
Set rsSearch = Server.CreateObject("ADODB.Recordset")
'This is the SQL statement for searching. You will need to change tblTable and Field to match your database (they are in red)
strSQL = "SELECT Id, Contenido, Nombre, caseid, ubicacion " _
& "FROM Importantes " _
& "WHERE Contenido LIKE '%" & StrInputSearch & "%' " _
& "OR Nombre LIKE '%" & StrInputSearch & "%' " _
& "ORDER BY Id;"
dim nombre, id, contenido, portacd, ubicacion
'Opens the database so we can get the results of the search
rsSearch.Open strSQL, adoCon
'If there are no matches then continue
If rsSearch.EOF Then
'Write out the message that there are no matches
Response.Write("There are no matches with your search")
'If there are matches, continue
Else
nombre = rssearch("nombre")
id = rssearch("id")
contenido = rssearch("contenido")
portacd = rssearch("caseid")
ubicacion = rssearch("ubicacion")
'Loop through the database to display all the matches
DO UNTIL rsSearch.EOF
'Write out the match found.
'You need to change Field to your database field you are search (its in red)
Response.Write("<table border='0' width='100%' id='table1' cellspacing='0'><tr><td width='11' style='border-left: 1px solid #808080; border-right: 1px solid #808080; border-top: 1px solid #808080; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px'> <p align='center'><i><font face='Arial' size='2'>" & id & "</font></i></td> <td width='96%'><font face='Arial' size='2'> Insertado por: <b>" & nombre & "</b></font></td></tr><tr> <td colspan='2' style='border-top: 1px solid #808080; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px'> <font color='#FFFFFF' style='font-size: 5pt'>a</font></td> </tr> <tr> <td colspan='2'><font face='Arial' size='2'>Contenido:</font></td> </tr> <tr> <td colspan='2' align='left' width='100%'><i><font face='Arial' size='2'>" & contenido & "</font></i><p> </td> </tr> <tr> <td colspan='2' style='border-top: 1px solid #808080; border-bottom: 1px solid #808080; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px'> <font face='Arial' size='2'>Esta en el portacd numero <b>"& portacd & "</b>, en la hoja <b>" & ubicacion & "</b></font></td> </tr></table>")
'Move to next line in database
rsSearch.MoveNext
'Continue looping through database to display all results found
Loop
End If
'Reset server objects
Set rsSearch = Nothing
adoCon.Close
Set adoCon = Nothing
%>
Intenta buscar "fernando" o "recovery" y veraz lo que te digo.
Aqui te mando el codigo que hace la busqueda:
<%
Dim strInputSearch 'Variable for the search word
Dim strCon 'Holds the string to connect to the db
Dim adoCon 'Database Connection Variable Object
Dim strSQL 'Holds the SQL query for the database
Dim rsSearch 'Holds the search recordset
'This is the variable that has the search word
strInputSearch = Request.Form("txtSearch")
'This makes it so people cant inject SQL code and/or cause some unwanted errors
strInputSearch = Replace(strInputSearch,"'", "''", 1, -1, 1)
'This sets the connection
Set adoCon = Server.CreateObject("ADODB.Connection")
'This is the connection string
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("sistemacd.mdb")
'Open the connection
adoCon.Open strCon
'Set the database connection
Set rsSearch = Server.CreateObject("ADODB.Recordset")
'This is the SQL statement for searching. You will need to change tblTable and Field to match your database (they are in red)
strSQL = "SELECT Id, Contenido, Nombre, caseid, ubicacion " _
& "FROM Importantes " _
& "WHERE Contenido LIKE '%" & StrInputSearch & "%' " _
& "OR Nombre LIKE '%" & StrInputSearch & "%' " _
& "ORDER BY Id;"
dim nombre, id, contenido, portacd, ubicacion
'Opens the database so we can get the results of the search
rsSearch.Open strSQL, adoCon
'If there are no matches then continue
If rsSearch.EOF Then
'Write out the message that there are no matches
Response.Write("There are no matches with your search")
'If there are matches, continue
Else
nombre = rssearch("nombre")
id = rssearch("id")
contenido = rssearch("contenido")
portacd = rssearch("caseid")
ubicacion = rssearch("ubicacion")
'Loop through the database to display all the matches
DO UNTIL rsSearch.EOF
'Write out the match found.
'You need to change Field to your database field you are search (its in red)
Response.Write("<table border='0' width='100%' id='table1' cellspacing='0'><tr><td width='11' style='border-left: 1px solid #808080; border-right: 1px solid #808080; border-top: 1px solid #808080; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px'> <p align='center'><i><font face='Arial' size='2'>" & id & "</font></i></td> <td width='96%'><font face='Arial' size='2'> Insertado por: <b>" & nombre & "</b></font></td></tr><tr> <td colspan='2' style='border-top: 1px solid #808080; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px'> <font color='#FFFFFF' style='font-size: 5pt'>a</font></td> </tr> <tr> <td colspan='2'><font face='Arial' size='2'>Contenido:</font></td> </tr> <tr> <td colspan='2' align='left' width='100%'><i><font face='Arial' size='2'>" & contenido & "</font></i><p> </td> </tr> <tr> <td colspan='2' style='border-top: 1px solid #808080; border-bottom: 1px solid #808080; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px'> <font face='Arial' size='2'>Esta en el portacd numero <b>"& portacd & "</b>, en la hoja <b>" & ubicacion & "</b></font></td> </tr></table>")
'Move to next line in database
rsSearch.MoveNext
'Continue looping through database to display all results found
Loop
End If
'Reset server objects
Set rsSearch = Nothing
adoCon.Close
Set adoCon = Nothing
%>
1 respuesta
Respuesta de arunchi
1