ASP and Ajax Question

ReportingAnalyst · Nov 16, 2006

I am not sure if this is the right place to ask this question. I am learning AJAX and interaction with ASP.

I am working on the Northwind database. I first show a select box with all the customers name.

Code:

<form>
Select a Customer:
<select name="customers" onchange="showCustomer(this.value)">
<%
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CustomerID, CompanyName FROM Customers order by CompanyName"

rs.open sSQL, Conn
%>
<option VALUE="Select">Select Customers</option>
<%
While Not rs.EOF
%>
	<option VALUE="<%=rs("CustomerID")%>"><%=rs("CompanyName")%></option>
<%
rs.MoveNext
wend

rs.close
set rs = nothing
%>
</select>
</form>

Now the Javascript submits the CustomerID to the same page:

Code:

<%
Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=DBServer;Initial Catalog=Northwind;"

%>
<html>
<head>
<script src="selectcustomer.js"></script>
</head>

<body>

<form>
Select a Customer:
<select name="customers" onchange="showCustomer(this.value)">
<%
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CustomerID, CompanyName FROM Customers order by CompanyName"

rs.open sSQL, Conn
%>
<%
While Not rs.EOF
%>
	<option VALUE="<%=rs("CustomerID")%>"><%=rs("CompanyName")%></option>
<%
rs.MoveNext
wend

rs.close
set rs = nothing
%>
</select>
</form>
<p>
<div id="txtHint"><b>Customer info will be listed here.</b>
<%

'Display the Customer Details.

sql="SELECT * FROM CUSTOMERS WHERE CUSTOMERID="
sql= sql & request.querystring("q")
Response.Write sql
Response.end


set rs1 = Server.CreateObject("ADODB.recordset")
rs1.Open sql, conn

response.write("<table>")
do until rs1.EOF
for each x in rs1.Fields
	response.write("<tr><td><b>" & x.name & "</b></td>")
	response.write("<td>" & x.value & "</td></tr>")
next
rs.MoveNext
loop

response.write("</table>")

%>

</div>
</p>

</body>
</html>

My question here is despite my select showing the 1st customer name, the query shows nothing for the querystring because I have not yet selected any value from the selectbox.

I get an error:

Incorrent syntax near =

It seems that it is not grabbing the 1st select box value into the SQL statement. How can I do that on load of the page?

Secondly, when I click on on a value of the select button, another select box is built right under it. Any ideas why I have a duplicate existence of the same select box?

Thanks.

ReportingAnalyst · Nov 16, 2006

I figured that there should be 3 pages

One page to show the customers from the Northwind database. (Customers.asp)

2nd Page to show the details of the selected Customer (GetCustomer.asp)

Javascript page - SelectCustomer.js

Customers.asp

Code:

<%
Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=DBServer;Initial Catalog=Northwind;"

%>
<html>
<head>
<script src="selectcustomer.js"></script>
</head>

<body>

<form name="Customers" id="Customers" action="">
Select a Customer:
<select name="customers" onchange="showCustomer(this.value)">
<%
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CustomerID, CompanyName FROM Customers order by CompanyName"

rs.open sSQL, Conn

If not rs.EOF then
	    aCustomers = rs.GetRows()

		'Close the Recordset object
		rs.Close
		'Delete the Recordset Object
		Set rs = Nothing

		'Declare Constants for the above SQL columns for better readability
		'Use these Constants instead of referring to the array numeric indexes
		Const c_CustomerID = 0
		Const c_CompanyName = 1

		Dim iRows

		For iRows = 0 to UBound(aCustomers, 2)
%>
			<option VALUE="<%=aCustomers(c_CustomerID,iRows)%>"><%=aCustomers(c_CompanyName,iRows)%></option>
<%
		q = aCustomers(c_CustomerID, iRows)
		%>
		<%
		Next
		%>
		<input type="text" name="CustID" id="CustID" value="<%=q%>">
		<%
End If
%>
</select>
</form>
<p>
<div id="txtHint"><b>Customer info will be listed here.</b>

</div>
</p>

</body>
</html>

GetCustomer.asp

Code:

<%
Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=DBServer;Initial Catalog=Northwind;"

%>
<%

sql="SELECT * FROM CUSTOMERS WHERE CUSTOMERID='"
sql= sql & request.querystring("q") & "'"
'Response.Write sql
'Response.end


set rs1 = Server.CreateObject("ADODB.recordset")
rs1.Open sql, conn

response.write("<table>")
do until rs1.EOF
for each x in rs1.Fields
	response.write("<tr><td><b>" & x.name & "</b></td>")
	response.write("<td>" & x.value & "</td></tr>")
next
rs1.MoveNext
loop

response.write("</table>")

%>

showCustomer.js

Code:

var xmlHttp

function showCustomer(str)
{ 
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
} 

var url="getCustomer.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 || xmlHttp.readyState=="complete")
{ 
document.getElementById("txtHint").innerHTML=xmlHttp.responseText 
} 
} 

function GetXmlHttpObject()
{ 
var objXMLHttp=null
if (window.XMLHttpRequest) //Safari, Mozilla browers
{
	objXMLHttp=new XMLHttpRequest()
}
else if (window.ActiveXObject) //IE browsers
{
	objXMLHttp=new ActiveXObject("Microsoft.XMLHTTP")
}
return objXMLHttp
}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP and Ajax Question

ReportingAnalyst

MIS

ReportingAnalyst

MIS

Similar threads

Part and Inventory Search

Sponsor