#1
  1. Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jul 2005
    Location
    Sydney, Australia
    Posts
    213
    Rep Power
    97

    Auto Suggest Form


    Hi Guys

    I just posted this as a reply to this query , and thought I would share it here also

    OK, here we go:

    This is the main page, with a small form, and includes the javascript (further down this post) that talks to the ASP script on the server (even further down this post) to get the suggestions
    ASP Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <html>
    <head>
    <script src="clienthint.js"></script> 
    </head>
    <body>
     
    <form> 
    Enter Word:
    <input type="text" id="txt1"
    onkeyup="showHint(this.value)">
    </form>
     
    <p>Suggestions: <span id="txtHint"></span></p> 
     
    </body>
    </html>


    This is the javascript:
    JavaScript Code:
    var xmlHttp
     
    function showHint(str)
    {
    if (str.length==0)
      { 
      document.getElementById("txtHint").innerHTML="";
      return;
      }
    xmlHttp=GetXmlHttpObject()
    if (xmlHttp==null)
      {
      alert ("Your browser does not support AJAX!");
      return;
      } 
    var url="gethint.asp";
    url=url+"?q="+str;
    url=url+"&sid="+Math.random();
    xmlHttp.onreadystatechange=stateChanged;
    xmlHttp.open("GET",url,true);
    xmlHttp.send(null);
    } 
     
    function stateChanged() 
    { 
    if (xmlHttp.readyState==4)
    { 
    document.getElementById("txtHint").innerHTML=xmlHt  tp.responseText;
    }
    }
     
    function GetXmlHttpObject()
    {
    var xmlHttp=null;
    try
      {
      // Firefox, Opera 8.0+, Safari
      xmlHttp=new XMLHttpRequest();
      }
    catch (e)
      {
      // Internet Explorer
      try
        {
        xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
        }
      catch (e)
        {
        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
        }
      }
    return xmlHttp;
    }

    and this is the ASP script on the server:
    ASP Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <%
    response.expires=-1
    Dim rsWords
    Dim rsWords_numRows
    Dim q
    Dim hint
    q=ucase(request.querystring("q"))
    hint=""
    Set rsWords = Server.CreateObject("ADODB.Recordset")
    rsWords.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db_hint_words.mdb")
    rsWords.Source = "SELECT *  FROM TBL_WORDS  WHERE (word LIKE'" + q + "%') ORDER BY WORD"
    rsWords.CursorType = 2
    rsWords.CursorLocation = 2
    rsWords.LockType = 3
    rsWords.Open()
    rsWords_numRows = 0
     
    If Not rsWords.EOF Then
    	Do While Not rsWords.EOF
    		If trim(hint) = "" Then
    			hint = rsWords("word")
    		Else
    			hint = hint & " , " & rsWords("word")
    		End If
    		rsWords.MoveNext()
    	Loop
    End If
    if trim(hint)="" then 
      response.write("no suggestion")
    else
      response.write(hint)
    end if
     
    rsWords.Close()
    Set rsWords = Nothing
    %>

    This one talks to the access database to get any words that start with whatever is the form field - and this is done each time the content of that field change (each and every keystroke)

    I have attached a ZIP file of this code with an Access DB, containing a list of Railway Station names in Sydney, Australia (300+ entries, so a good sample of the code can be run ).

    If you would like to see this in action, you can view it on my development server at http://dev.unimaetrix.com/hint/clienthint.asp

    The layout of the page is very simple as it is ony a sample of the scripts in action.

    Let me know if you need any further assistance , or if you find any errors in my code.
    Attached Files
    Alex - Lucky Number 13

    Did I do something that helped you? - please click the scales and help my rep
  2. #2
  3. Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jul 2005
    Location
    Sydney, Australia
    Posts
    213
    Rep Power
    97
    In the thread mentioned in the above post, there was a query about making the suggestions clickable, so here is some more code for that purpose:

    Clickable suggestion that populate the text box
    I haven't got the autosubmit to work yet tho

    ASP Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <html>
    <head>
    <script src="clienthint.js"></script> 
    </head>
    <body>
    <%
    'this displays the value of the textbox after the form is submitted
    If trim(Request("txt1")) <> "" Then
    	Response.Write "You entered:"
    	Response.Write "<b>" & Request("txt1") & "</b><br /><br />"
    End If
    %>
    <form name="form1" action="clienthint.asp" method="post">
    Enter Word:
    <input type="text" name="txt1" id="txt1" onKeyUp="showHint(this.value,'txt1','form1',true)"  >
    <input type="submit" name="submit" value="submit">
    </form>
    <p>Suggestions: <span id="txtHint"></span></p> 
    </body>
    </html>


    JavaScript Code:
    var xmlHttp
     
    function showHint(str, box, thisForm, autoSubmit)
    {
    if (str.length==0)
      { 
      document.getElementById("txtHint").innerHTML="";
      return;
      }
    xmlHttp=GetXmlHttpObject()
    if (xmlHttp==null)
      {
      alert ("Your browser does not support AJAX!");
      return;
      } 
    var url="gethint.asp";
    url=url+"?q="+str;
    url=url+"&b="+box;
    url=url+"&f="+thisForm;
    url=url+"&a="+autoSubmit;
    url=url+"&sid="+Math.random();
    xmlHttp.onreadystatechange=stateChanged;
    xmlHttp.open("GET",url,true);
    xmlHttp.send(null);
    } 
     
    function stateChanged() 
    { 
    if (xmlHttp.readyState==4)
    { 
    document.getElementById("txtHint").innerHTML=xmlHt  tp.responseText;
    }
    }
     
    function GetXmlHttpObject()
    {
    var xmlHttp=null;
    try
      {
      // Firefox, Opera 8.0+, Safari
      xmlHttp=new XMLHttpRequest();
      }
    catch (e)
      {
      // Internet Explorer
      try
        {
        xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
        }
      catch (e)
        {
        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
        }
      }
    return xmlHttp;
    }
     
    //this function allows for Clickable suggestions
    function setTextBox(thisText,thisBox,thisForm,autoSubmit){
    	document.getElementById(thisBox).value = thisText
    	//this autoSubmits the form after a suggestion is clicked - it is not working <img src="https://images.devshed.com/faf/smilies/frown.gif" border="0" alt="" title="Frown" class="inlineimg" />
    	//if(autoSubmit=='true'){
    	//	alert(thisForm);
    	//	document.getElementById(thisForm).submit();
    	//}
    }


    ASP Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <%
    response.expires=-1
    Dim rsWords
    Dim rsWords_numRows
    Dim q
    Dim b
    Dim hint
    q=ucase(request.querystring("q"))
    b=(request.querystring("b"))
    f=(request.querystring("f"))
    a=(request.querystring("a"))
    hint=""
    Set rsWords = Server.CreateObject("ADODB.Recordset")
    rsWords.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db_hint_words.mdb")
    rsWords.Source = "SELECT *  FROM TBL_WORDS  WHERE (word LIKE'" + q + "%') ORDER BY WORD"
    rsWords.CursorType = 2
    rsWords.CursorLocation = 2
    rsWords.LockType = 3
    rsWords.Open()
    rsWords_numRows = 0
     
    If Not rsWords.EOF Then
    	Do While Not rsWords.EOF
    		If trim(hint) = "" Then
    			hint = "<a href=""javascript:setTextBox('" & rsWords("word") & "','" & b & "','" & f & "','" & a & "');"">" & rsWords("word") & "</a>"
    		Else
    			hint = hint & " , <a href=""javascript:setTextBox('" & rsWords("word") & "','" & b & "','" & f & "','" & a & "');"">" & rsWords("word") & "</a>"
    		End If
    		rsWords.MoveNext()
    	Loop
    End If
    if trim(hint)="" then 
      response.write("no suggestion")
    else
      response.write(hint)
    end if
     
    rsWords.Close()
    Set rsWords = Nothing
    %>


    New Zip attached of this version

    You can also see it working at : http://dev.unimaetrix.com/hint2/clienthint.asp

    I'll continue to try to get the autosubmit when a suggestion is clicked - if I succeed, I'll post the function for that here also
    Attached Files
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    797
    Rep Power
    34

    autosuggest


    Do have a modified version of this code that would keep the suggested words in the textbox (from the databse field) so you can tab through or click on and then submit to your page.

    Thanks!
  6. #4
  7. Expert Learner
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Feb 2005
    Location
    Wisconsin
    Posts
    1,909
    Rep Power
    101
    Rather than using onkeyup straight, I'd recommend using something like the following function, which dramatically reduces the number of AJAX calls, and is still as (if not more) effective for the client.

    Usage:
    Code:
    <input type="text" name="q" onkeyup="search_delay(this)" />
    Code:
    function search_delay(element) {
    	// Create a function to get the search results
    	var func = function() { getBox(document.getElementById('ab_q').value, 'RhapArtist', 10); };
    
    	// Check to see if there is already a timeout and if so...
    	// ...cancel it and create a new one
    	if ( element.zid ) {
    		clearTimeout(element.zid);
    	}
    	element.zid = setTimeout(func,500);
    }
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    797
    Rep Power
    34
    I am not able to get it to work. Am I suppose to but the following code in the bottom of the clienthint.js file?

    Code:
    function search_delay(element) {
    	// Create a function to get the search results
    	var func = function() { getBox(document.getElementById('ab_q').value, 'RhapArtist', 10); };
    
    	// Check to see if there is already a timeout and if so...
    	// ...cancel it and create a new one
    	if ( element.zid ) {
    		clearTimeout(element.zid);
    	}
    	element.zid = setTimeout(func,500);
    }
    Last edited by phoenixaz; July 4th, 2008 at 02:33 AM.
  10. #6
  11. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    797
    Rep Power
    34
    I think I am having a brain cramp because I can't seem to substitute my sql connection with your access database connection.

    Here is my sql connection. Can you suggest how I make the changes?

    Code:
    set objConn = server.CreateObject("adodb.connection")
    objConn.Open application("my_ConnectionString")
    
    
    SQL = "select * from table1 WHERE (studentname LIKE'" + q + "%') order by studentname"
    
    Set objRS = objConn.Execute(SQL)
    I tried changing objrs to rswords, but that didn't work. And when you had rsWords("word") I changed that to rsWords("studentname")

    Any suggestions would be appreciated.

    Thanks
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    797
    Rep Power
    34
    I was able to get it to work, but had to comment out 5 lines of code. Any thoughts?

    Code:
    set objConn = server.CreateObject("adodb.connection")
    objConn.Open application("My_ConnectionString")
    strSQL = "SELECT studentname  FROM table1 WHERE (studentname LIKE'" + q + "%') ORDER BY studentname"
    Set rsWords = objConn.Execute(strSQL)
    
    
    'rsWords.CursorType = 2
    'rsWords.CursorLocation = 2
    'rsWords.LockType = 3
    'rsWords.Open()
    'rsWords_numRows = 0
  14. #8
  15. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    797
    Rep Power
    34
    You did a great job on coding gethint2.zip files.

    If I want to add another field and pass it to the form, how can I capture the StudentID in the form?

    Code:
    If trim(hint) = "" Then
    			hint = "<a href=""javascript:setTextBox('" & rsWords("studentname") & " (" & rswords ("studentid")& ")" & "','" & b & "','" & f & "','" & a & "');"">" & rsWords("studentname") & " (" & rswords ("studentid")& ")" &  "</a>"
    		Else
    			hint = hint & " , <a href=""javascript:setTextBox('" & rsWords("studentname") & " (" & rswords ("studentid")& ")" & "','" & b & "','" & f & "','" & a & "');"">" & rsWords("studentname") & " (" & rswords ("studentid")& ")" & "</a>"
    		End If
    That code works fine and the results would be something like this....

    John Doe (1411) , John Doe (3288) , John Doe (3432)

    So I have 3 different students with the same name. So when I click on the one I want, it puts ....

    John Doe (3288) in the text box.

    So if I now click on SUBMIT, I want to put in the form the studentname and ALSO the studentid so I can use that in my sql string.


    Code:
    <form name="form1" action="clienthint.asp" method="post">
    Student Name:
    <input type="text" name="studentname" id="txt1" onKeyUp="showHint(this.value,'txt1','form1',true)">
    <input type="submit" name="submit" value="submit">
    </form>
    <p><span id="txtHint"></span></p>
    I appreciate your suggestions.

    Thanks!
  16. #9
  17. Expert Learner
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Feb 2005
    Location
    Wisconsin
    Posts
    1,909
    Rep Power
    101
    Originally Posted by phoenixaz
    I am not able to get it to work. Am I suppose to but the following code in the bottom of the clienthint.js file?

    Code:
    function search_delay(element) {
    	// Create a function to get the search results
    	var func = function() { getBox(document.getElementById('ab_q').value, 'RhapArtist', 10); };
    
    	// Check to see if there is already a timeout and if so...
    	// ...cancel it and create a new one
    	if ( element.zid ) {
    		clearTimeout(element.zid);
    	}
    	element.zid = setTimeout(func,500);
    }
    That code can go anywhere, as long as it's not contained within any other function or class. It can be used globally.
  18. #10
  19. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    797
    Rep Power
    34

    Last name search


    Is it possible to do a search with your autosuggestion for the last name when the name field contains both first, last and sometimes middle.

    Example: Let's say that there are 2 names,

    John Doe Smitih , John Smith

    If you type.... John, both names show up in the suggest list.

    If you type.... John D, then only John Doe Smith shows and not John Smith.

    I think there is a way to do a split, but I am not sure how to do it.

    Thanks
    Last edited by phoenixaz; July 7th, 2008 at 07:55 PM.
  20. #11
  21. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2008
    Posts
    1
    Rep Power
    0

    any progress on autosubmit?


    Dear alexk13,
    thanx for the nice piece of code.

    do you have any progress on autosubmit? would be great if you did.
  22. #12
  23. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2010
    Posts
    1
    Rep Power
    0

    Google search style auto sugggest


    Hi,
    You guys are great. Is there a way that codes in "gethint2.zip" be modified in such a way that they behave like "Google Search style auto suggest".

    Thanks
    Salan
  24. #13
  25. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2011
    Posts
    1
    Rep Power
    0

    SQL injection


    I noticed that we're taking q from a querystring and building a SQL query with it directly. This can be exploited by a hacker.

    Might I suggest replacing:
    q=ucase(request.querystring("q"))

    with
    q=Replace(ucase(request.querystring("q")),"'","''" )

    Stace

    Comments on this post

    • Shadow Wizard agrees : Thanks.
  26. #14
  27. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2012
    Posts
    1
    Rep Power
    0

    New Twist On Same These


    This is a great script which after re-editing it quite a bit I was able to put it to good use on tradesearchuk (dot) co (dot) uk

    Many thanks for posting and please feel free to view the site home page where you will see 3 filter boxes doing a splendid job.

    If any members want to know how this was acheived, please let me know as happy to help.
  28. #15
  29. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Hi, Can anyone help ? This code is great, I am using the clickable version that puts the values into the textbox. However, is it possible to list the suggestions in a box underneath the text box e.g. like a dropdown box rather than across the page ?

    How can I do this ?

Similar Threads

  1. Open form not adding record and use an add button on form
    By Rhinogirl in forum Microsoft Access Help
    Replies: 1
    Last Post: August 2nd, 2006, 09:28 AM
  2. Replies: 0
    Last Post: July 17th, 2006, 08:47 AM
  3. Form auto submit
    By Midnight-Toker in forum HTML, JavaScript And CSS Help
    Replies: 28
    Last Post: June 1st, 2005, 03:10 AM
  4. Help with a 'Quick Add' Form
    By Bombaamn in forum Microsoft Access Help
    Replies: 1
    Last Post: June 24th, 2004, 05:45 PM
  5. how to auto refresh form page?
    By michelle_bcp in forum ASP Development
    Replies: 1
    Last Post: November 27th, 2003, 12:03 PM

IMN logo majestic logo threadwatch logo seochat tools logo