abenage
asked on
Using xmlhttp to populate a dropdown list with text and value
I am very new to javascript and expecially xmlhttp.
I have been able to successfully implement a solution which retrieves data from an SQL DB as a user enter text into a text box. Basically, what is happening is the user is attempting to enter a company name, and as the company name is entered into the textbox, on each onkeyup event the DB is queried and returns the top 5 companies whose name matches the entered string.
This is working pretty well, except that the req.responseText is being applied to the innerHTML of a DIV. I am interested in populating a dropdown list with companyname as the text and company_id as the value. Ideally, this would be a dropdown list that you can enter text into at run time instead of entering the search criteria into a separate text box. As the user types, their entry stays at the top of the list and returned data populates the list.
Is this possible?
The javascript runs on an asp.net web form and uses another aspx helper file which builds the sql and runs the query onload. Here is how I am doing this:
<script type="text/javascript">
var req;
function Initialize()
{
try
{
req=new ActiveXObject("Msxml2.XMLH TTP");
}
catch(e)
{
try
{
req=new ActiveXObject("Microsoft.X MLHTTP");
}
catch(oc)
{
req=null;
}
}
if(!req&&typeof XMLHttpRequest!="undefined ")
{
req= new
XMLHttpRequest();
}
} function SendQuery(key)
{
//Here is the url of the helper page which builds and runs the SQL query
Initialize(); varurl="http://localhost/bdsearch/newpersonhelper.aspx?s=3&k="+key;
if(req!=null)
{
req.onreadystatechange = Process;
req.open("GET", varurl, true);
req.send(null);
}
}
function Process()
{
if (req.readyState == 4)
{
// only if "OK"
if (req.status == 200)
{
if(req.responseText=="")
HideDiv("autocomplete");
else
{
ShowDiv("autocomplete");
//This is where the returned req.responseText populates the div
//I would like to populate the dropdown here instead.
document.getElementById("a utocomplet e").innerH TML =req.responseText;
}
}
else
{
document.getElementById("a utocomplet e").innerH TML= "There was a problem retrieving data:<br>"+req.statusText;
}
}
}
function ShowDiv(divid)
{
if (document.layers) document.layers[divid].vis ibility="s how";
else document.getElementById(di vid).style .visibilit y="visible ";
}
function HideDiv(divid)
{
if (document.layers) document.layers[divid].vis ibility="h ide";
else document.getElementById(di vid).style .visibilit y="hidden" ;
}
function BodyLoad()
{
HideDiv("autocomplete");
document.form1.keyword.foc us();
}
</script>
<html>
<form>
INPUT id="keyword" onkeyup="SendQuery(this.va lue)" style="WIDTH: 500px" name="keyword" autocomplete="off">
</form>
</html>
and the codebehind of the helper aspx file (varurl="http://localhost/bdsearch/newpersonhelper.aspx?s=3&k="+key;):
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim keyword As String = Request("k")
Dim QueryScope As String = Request("s")
Dim sql As String
If keyword <> Nothing And keyword.Trim() <> "" Then
Select Case QueryScope
Case "1" ' Normal Query
sql = "select top 5 company, company_id from company where company like '" + keyword.Trim().Replace("'" , "''") + "%' order by company"
Case "2" 'anything before or after keyword
sql = "select top 5 company, company_id from company where company like '%" + keyword.Trim().Replace("'" , "''") + "%' order by company"
Case "3" ' anything before or after keyword and THE %Keyword%
sql = "select top 5 company, company_id from company where company like '" + keyword.Trim().Replace("'" , "''") + "%' or company like 'the " + keyword.Trim().Replace("'" , "''") + "%' order by company"
End Select
Dim conn As SqlConnection = New SqlConnection(Configuratio nSettings. AppSetting s("connect ionString" ))
conn.Open()
Dim dt As DataTable = New DataTable()
Dim command As SqlCommand = New SqlCommand(sql, conn)
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
adapter.Fill(dt)
conn.Close()
Dim row As DataRow
For Each row In dt.Rows
'Response.Write("<strong>" + row("Company").ToString() + "</strong> <br />")
Response.Write(row("Compan y").ToStri ng() + "<br />")
Next
End If
End Sub
Any ideas?
Thanks,
Aaron
I have been able to successfully implement a solution which retrieves data from an SQL DB as a user enter text into a text box. Basically, what is happening is the user is attempting to enter a company name, and as the company name is entered into the textbox, on each onkeyup event the DB is queried and returns the top 5 companies whose name matches the entered string.
This is working pretty well, except that the req.responseText is being applied to the innerHTML of a DIV. I am interested in populating a dropdown list with companyname as the text and company_id as the value. Ideally, this would be a dropdown list that you can enter text into at run time instead of entering the search criteria into a separate text box. As the user types, their entry stays at the top of the list and returned data populates the list.
Is this possible?
The javascript runs on an asp.net web form and uses another aspx helper file which builds the sql and runs the query onload. Here is how I am doing this:
<script type="text/javascript">
var req;
function Initialize()
{
try
{
req=new ActiveXObject("Msxml2.XMLH
}
catch(e)
{
try
{
req=new ActiveXObject("Microsoft.X
}
catch(oc)
{
req=null;
}
}
if(!req&&typeof XMLHttpRequest!="undefined
{
req= new
XMLHttpRequest();
}
} function SendQuery(key)
{
//Here is the url of the helper page which builds and runs the SQL query
Initialize(); varurl="http://localhost/bdsearch/newpersonhelper.aspx?s=3&k="+key;
if(req!=null)
{
req.onreadystatechange = Process;
req.open("GET", varurl, true);
req.send(null);
}
}
function Process()
{
if (req.readyState == 4)
{
// only if "OK"
if (req.status == 200)
{
if(req.responseText=="")
HideDiv("autocomplete");
else
{
ShowDiv("autocomplete");
//This is where the returned req.responseText populates the div
//I would like to populate the dropdown here instead.
document.getElementById("a
}
}
else
{
document.getElementById("a
}
}
}
function ShowDiv(divid)
{
if (document.layers) document.layers[divid].vis
else document.getElementById(di
}
function HideDiv(divid)
{
if (document.layers) document.layers[divid].vis
else document.getElementById(di
}
function BodyLoad()
{
HideDiv("autocomplete");
document.form1.keyword.foc
}
</script>
<html>
<form>
INPUT id="keyword" onkeyup="SendQuery(this.va
</form>
</html>
and the codebehind of the helper aspx file (varurl="http://localhost/bdsearch/newpersonhelper.aspx?s=3&k="+key;):
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim keyword As String = Request("k")
Dim QueryScope As String = Request("s")
Dim sql As String
If keyword <> Nothing And keyword.Trim() <> "" Then
Select Case QueryScope
Case "1" ' Normal Query
sql = "select top 5 company, company_id from company where company like '" + keyword.Trim().Replace("'"
Case "2" 'anything before or after keyword
sql = "select top 5 company, company_id from company where company like '%" + keyword.Trim().Replace("'"
Case "3" ' anything before or after keyword and THE %Keyword%
sql = "select top 5 company, company_id from company where company like '" + keyword.Trim().Replace("'"
End Select
Dim conn As SqlConnection = New SqlConnection(Configuratio
conn.Open()
Dim dt As DataTable = New DataTable()
Dim command As SqlCommand = New SqlCommand(sql, conn)
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
adapter.Fill(dt)
conn.Close()
Dim row As DataRow
For Each row In dt.Rows
'Response.Write("<strong>"
Response.Write(row("Compan
Next
End If
End Sub
Any ideas?
Thanks,
Aaron
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.