SQL Query
-
I have a database that a user can search for using the begins with, exact match, or contains option. I did a request.querystring to pull in that information from the previous page where the user selects one of those options and types is what he is searching for. The problem is that I cannot figure out how to display the users result when they select to search by exact match, begins with, or contains. The following is the code that I used. Can anyone tell me what is wrong with it and give me some leads on how to get it fixed. Table Display <%Dim FieldHead, SearchMethod, TextBox, Table, DB, RS, strSql%> <%'Request the information from the previous page'%> <%FieldHead = Request.QueryString("fieldhead") SearchMethod = Request.QueryString("SrchMthd") TextBox = Request.QueryString("textbox") Table = Request.QueryString("Table") %> <%'search method if the user selects begins with%> <%If SearchMethod = "begin" Then%> <%Set DB = Server.CreateObject("ADODB.Connection") DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb") Set RS = Server.CreateObject("ADODB.Recordset") strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '" & TextBox & "%'" RS.Open (strSql), DB End IF %> <%'search method if the user selects contains%> <%If SearchMethod = "contain" Then%> <%Set DB = Server.CreateObject("ADODB.Connection") DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb") Set RS = Server.CreateObject("ADODB.Recordset") strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '%" & TextBox & "%'" RS.Open (strSql), DB End IF%> <%'search method if the user selects exact match%> <%If SearchMethod = "exact" Then%> <%Set DB = Server.CreateObject("ADODB.Connection") DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb") Set RS = Server.CreateObject("ADODB.Recordset") strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & = TextBox RS.Open (strSql), DB End IF%>
Ty
-
I have a database that a user can search for using the begins with, exact match, or contains option. I did a request.querystring to pull in that information from the previous page where the user selects one of those options and types is what he is searching for. The problem is that I cannot figure out how to display the users result when they select to search by exact match, begins with, or contains. The following is the code that I used. Can anyone tell me what is wrong with it and give me some leads on how to get it fixed. Table Display <%Dim FieldHead, SearchMethod, TextBox, Table, DB, RS, strSql%> <%'Request the information from the previous page'%> <%FieldHead = Request.QueryString("fieldhead") SearchMethod = Request.QueryString("SrchMthd") TextBox = Request.QueryString("textbox") Table = Request.QueryString("Table") %> <%'search method if the user selects begins with%> <%If SearchMethod = "begin" Then%> <%Set DB = Server.CreateObject("ADODB.Connection") DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb") Set RS = Server.CreateObject("ADODB.Recordset") strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '" & TextBox & "%'" RS.Open (strSql), DB End IF %> <%'search method if the user selects contains%> <%If SearchMethod = "contain" Then%> <%Set DB = Server.CreateObject("ADODB.Connection") DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb") Set RS = Server.CreateObject("ADODB.Recordset") strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '%" & TextBox & "%'" RS.Open (strSql), DB End IF%> <%'search method if the user selects exact match%> <%If SearchMethod = "exact" Then%> <%Set DB = Server.CreateObject("ADODB.Connection") DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb") Set RS = Server.CreateObject("ADODB.Recordset") strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & = TextBox RS.Open (strSql), DB End IF%>
Ty
Your code should use:
... " WHERE " & FieldHead & " LIKE '" & Replace(TextBox, "'", "''") & "%'Instead of:
... " WHERE " & FieldHead & " LIKE '" & TextBox & "%'"Otherwise it will break if the user enters a single-quote into his search value. The normal way to show the results is to iterate through each row and column in the result set. However, you might find the following faster:
'Open the recordset and render the data in HTML format. For Each objField In objRS.Fields Call Response.Write("<td nowrap><b>" & _ Server.HtmlEncode(objField.Name) & "</b></td>" & vbCRLF) Next Call Response.Write("</tr>" & vbCRLF) Call Response.Write("<tr>") If Not objRs.eof Then strData = objRS.GetString( , , "#<#/td#>##<#td#>#", _ "#<#/td#>##<#/tr#>##<#tr#>##<#td nowrap#>#", "#&#nbsp;") strData = Replace(Replace(Replace(strData, "#&#", Chr(1)), "&", "&"), Chr(1), "&") strData = Replace(Replace(Replace(strData, "#<#", Chr(1)), "<", "<"), Chr(1), "<") strData = Replace(Replace(Replace(strData, "#>#", Chr(1)), ">", ">"), Chr(1), ">") strData = "<td>" & Replace(Left(strData, Len(strData) - 15), "</tr>", "</tr>" & vbCRLF) Call Response.Write(strData) Else Call Response.Write("<td colspan=""" & objRs.Fields.Count & """>No records found.</td></tr>" & vbCRLF) End If Call Response.Write("</table>" & vbCRLF)The funny replace stuff is a quick way to get it to render each field as a separate HTML table cell. Andy