Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts

    Question Question - ASP + Ajax

    Question - ASP + Ajax

    Hey!

    Not sure if this is the correct section to post this.

    Well, I have a table in my sql server 2000 database

    ***************
    tblAddress
    ----------------------------
    City_Name (varchar)
    Venue_Name (varchar)
    *******************

    The "City_Name" column contains all the name of the cities in my country and each city has multiple venues.

    Now I have 2 drop-down boxes in my asp page. The first drop-down is already listed with all the cities which are in the "City_Name" column.

    What I now want is, when I select a city name from the first drop-down, the second drop down should be populated with all the venue names of that city (of course without refreshing the page)

    I know this can be done with ajax.

    I got few codes while googling but none of them are working

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Hi Cancer --

    Here are a couple of pointers for you...

    1) AJAX code (.js file, attached) - This file has the basics for connection a page to AJAX. There are a couple of field names that you will need to change, all pre- and post-fixed by <<< and >>> respectively. These fields need to be named the same as your form fields.

    2) Create an ASP page that queries your database and returns the results. Again, attached is a .asp (in txt format) so that you can get an idea of what needs to be done. I used DIVs instead of dropdowns, but the majority of the work has been done for you.

    3) In you ASP code on the FIRST DROPDOWN BOX. Put in an onchange="javascript:setSearch('<<<FLDVALUE>>>'). This will call the AJAX file based on the <<<FLDVALUE>>> given by the dropdown.

    I've given you the basics in these 2 files. You will need to do some code changes and alter it to fit your needs, but it should be enough to get you going.

    Please let me know if you need any other help with this!
    Attached Files Attached Files

  • #3
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts

    Thumbs up

    Hey Daemonspyre,

    First of all thanx for your help.

    I have attached the modified version of your code. Unfortunately it does not work. Can u plz check what is wrong in it?


    Thanx


    PS: the connect.asp is not included for security reasons
    Attached Files Attached Files

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts

    This Is A Really Long Post

    Hi Cancer --

    It didn't work for a few reasons, 1) because you didn't have the same DIV names that I did, as you needed to change the DIV tags to JS Option tags; 2) you need to check the SQL; and 3) Basic HTML issues. I am going to post the code below for 2 reasons: 1) so that others can see/check the work, and 2) so that I can explain it a little better.

    First off - Are you using Firefox? If not, START NOW!! (if you are, skip to the next section - for those of you reading this that are not using FF, Firefox has a really cool tool called FIREBUG (download it at https://addons.mozilla.org) that allows you to see the headers and responses to all AJAX calls as well as any Javascript errors. This is REALLY important in AJAX debugging.).

    Now - the code:

    AJAX.js

    Code:
    var xmlHttp
    
    function setSearch(str) {
    	document.myform.txtVenue.value = str;   //This is the value posted back by your form
    }
    
    function showmyfunction(str)
    { 
    	xmlHttp=GetXmlHttpObject();
    	if (xmlHttp==null)
    		{
    		alert ("Your browser does not support AJAX!");
    		return;
    		} 
    	var url="index.asp?q="+str+"&sid="+Math.random();  //This line posts the AJAX call to your ASP page
    	xmlHttp.onreadystatechange=stateChanged;
    	xmlHttp.open("GET",url,true);
    	xmlHttp.send(null);
    }
    
    function stateChanged() 
    { 
    	var test = document.myform.txtCity.value;
    	if (xmlHttp.readyState==4)
    	{
    		var select = document.getElementsByName("txtVenue");
    		
    		select.innerHTML = xmlHttp.responseText;
    		
    		results.appendChild(select);
    		return results;
    	}
    }
    
    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;
    }
    If you look at the original attachment, you will see that in the stateChanged() function it looks a little different. This is because I wanted my information posted to a DIV, you want your AJAX postback to go to a SELECT.


    Second - getVenueNames.asp

    Code:
    <!--#include file="connect.asp"-->
    
    <%
    response.expires = -1
    '
    ' REALLY IMPORTANT YOU CHECK THE SQL CODE HERE!!!
    ' Since I don't have access to your table structure, I can't say this is right. 
    ' You want to make sure this is the information you want to pull before starting any other code edits.
    '
    sql = "SELECT Venue_ID,Venue_Name FROM tblAddress WHERE City_Name LIKE '" & request.querystring("q") & "'"
    '
    'CHECK YOUR OBJECT NAMES. rs = ADODB.Recordset, conn = ADODB.connection. Is this the same for you? If not, you need to change it.
    ' 
    rs.open sql,conn
    '
    if rs.eof then
    	'Since you don't have this tag, there is no reason to have the line below.
    	'response.write("<div class=""autoDiv"">No Venues match your request</div>")
    else
    	'Again, check your object names in the rs.GetRows()
    	arrVenueNames = rs.GetRows()
    	iRowMax = ubound(arrVenueNames,2)
    	rs.close
    	'
    	response.write("<script language=""javascript"">" & vbCrLf)				'puts the script tag in + carriage return
    	'
    	for x=0 to iRowMax
    		response.write("document.myform.txtVenue.options[" & x & "] = new Option('" & arrVenueNames(1,x) & "', '" & arrVenueNames(0,x) & "');" & vbCrLf)
    	next
    	'
    	response.write("</script>")
    end if
    %>
    You will notice again that the code has changed from the original attachments.

    AGAIN, IT IS REALLY, REALLY IMPORTANT TO CHECK THE SQL!!

    You wanted a SELECT, so the JS has to change in order to add OPTIONS to your SELECT. See the for...next loop code. Remember,
    Code:
    document.formname.fieldname.options[i] = new Option('txt string','txt value');
    Lastly - index.asp

    Code:
    <html>
    <head>
    <script language="javascript" type="text/javascript" src="ajax.js"></script>
    </head>
    <body>
    <!--#include file="connect.asp"-->
    <form name="myform">
      <table width="50%" border="0" cellpadding="5" cellspacing="5">
        <tr>
          <td align="center"><select name="txtCity" id="txtCity" onchange="javascript:setSearch('txtCity')">
    	  <% set rsrecords = conn.execute("SELECT * from PRS_Master_Venue")
    	  
    	  do until rsrecords.eof
    	  %>
    	  <option><%=rsrecords("Venue_City")%></option>
    	  <%
    	  rsrecords.movenext
    	  loop
    	  %>
            </select>      </td>
        </tr>
        <tr>
          <td align="center"><select name="txtVenue">
    	  <option></option>
          </select></td>
        </tr>
      </table>
    </form>
    </body>
    </html>
    I know that some of the changes above are trivial (like adding HTML/BODY tags), but it's important that you have a well-structured document so that you can be sure it's not browser issues with code misinterpretation.

    There are a couple of issues here. I will go into them one by one...
    Code:
    set rsrecords = conn.execute("SELECT * from PRS_Master_Venue")
    Pet peeve (and SQL Query optimization) - Do you really need a SELECT *? Would you not do well to do a "SELECT Venue_City_ID,Venue_City FROM PRS_Master_Venue" instead? No need to pull more data from the database than needed.

    Code:
    <option><%=rsrecords("Venue_City")%></option>
    So how does AJAX know what the option value is in your JS code to pass it to your ASP Page? Change it to:

    Code:
    <option value="<%= rsrecords("Venue_City_ID")%>"><%=rsrecords("Venue_City")%></option>
    Lastly, and this is for the die-hard ASP coders out there that would say the same thing: Don't jump in and out of ASP all the time. You can combine basically this entire page into ASP and not have so much work on the server.

    See below:

    Code:
    <script src="ajax.js" language="javascript"></script>
    <!--#include file="connect.asp"-->
    <form name="myform">
      <table width="50%" border="0" cellpadding="5" cellspacing="5">
        <tr>
          <td align="center">
            <select name="txtCity" id="txtCity" onchange="javascript:setSearch('txtCity');">
    	  <% 
    	  set rsrecords = conn.execute("SELECT * from PRS_Master_Venue")
    	  '
    	  do until rsrecords.eof
    	  '
    	  	response.write("		<option value=""" & rsrecords("Venue_City_ID") & """>" & rsrecords("Venue_City") & "</option>")
    	  '
    	  rsrecords.movenext
    	  loop
    	  %>
            </select>
          </td>
        </tr>
        <tr>
          <td align="center"><select name="txtVenue">
          </select></td>
        </tr>
      </table>
    </form>
    !!! IMPORTANT !!!

    Since we are posting the Venue_City_ID back to the AJAX, the AJAX.js and getVenueNames.asp need to change accordingly. If you want it to post the Names themselves, you will need to do some REPLACE() functions to remove things like spaces and single quotes.

    I would be willing to help more, but table structures and data would be needed, and I don't think you want to post those.

    Play with it a little. Best way to learn to swim is to jump in the deep end. If you need help with the JS, go to http://www.w3schools.com/jsref (W3Schools Javascript Reference) or just post your issues here.

    Have a good one, and good luck!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •