yannis says:
Hello,
I am not at all familiar with jQuery, but I came across this (https://www.aspsnippets.com/Articles/Implement-jQuery-AutoComplete-TextBox-from-database-using-AJAX-PageMethods-in-ASPNet.aspx)and I implimented it in a project of mine where I am trying to create a pdf with data taken from an Access database, corresponding to a certain user input.
My question is how can i insert additional hidden fields so that I can retrieve additional data from the database and use it accordingly.
Please, I would apreciate a more elaborate answer since as I mentioned above I am not at all familiar with jQuery and AJAX.
You can add more hidden field in which you can save values on selection of autocomplete option. You just need to add your column name in select query list and then in success function split the values. On success used item variable where label and val property are used to show label text and value of selected option from autocomplete. Create more property and save values in it and access it in select and set accordingly
Refer the below code for your understanding and implement it according to your logic.
Here – keyword used to split the values and sets for string values from select list.
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js" type="text/javascript"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js" type="text/javascript"></script>
<link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css"
rel="Stylesheet" type="text/css" />
<script type="text/javascript">
$(function () {
$("[id$=txtSearch]").autocomplete({
source: function (request, response) {
$.ajax({
url: '<%=ResolveUrl("~/AutoCompleteWithMultiplecolumnNameinList.aspx/GetCustomerDetails") %>',
data: "{ 'prefix': '" + request.term + "'}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
response($.map(data.d, function (item) {
return {
label: item.split('-')[0],
val: item.split('-')[1],
contactName: item.split('-')[2],
contactTitle: item.split('-')[3],
address: item.split('-')[4],
city: item.split('-')[5],
region: item.split('-')[6],
country: item.split('-')[7],
}
}))
},
error: function (response) {
alert(response.responseText);
}, faliure: function (response) {
alert(response.responseText);
}
});
},
select: function (e, i) {
$("[id*=hfCustomerId]").val(i.item.val);
$("[id*=hfContactName]").val(i.item.contactName);
$("[id*=hfContactTitle]").val(i.item.contactTitle);
$("[id*=hfAddress]").val(i.item.address)
$("[id*=hfCity]").val(i.item.city);
$("[id*=hfRegion]").val(i.item.region);
$("[id*=hfCountry]").val(i.item.country);
},
minLength: 1
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
Enter Search
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:HiddenField ID="hfCustomerId" runat="server" />
<asp:HiddenField ID="hfCompanyName" runat="server" />
<asp:HiddenField ID="hfContactName" runat="server" />
<asp:HiddenField ID="hfContactTitle" runat="server" />
<asp:HiddenField ID="hfAddress" runat="server" />
<asp:HiddenField ID="hfCity" runat="server" />
<asp:HiddenField ID="hfRegion" runat="server" />
<asp:HiddenField ID="hfCountry" runat="server" />
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="Submit" />
</div>
</form>
</body>
</html>
C#
protected void Submit(object sender, EventArgs e)
{
string customerName = Request.Form[txtSearch.UniqueID];
string customerId = Request.Form[hfCustomerId.UniqueID];
string contactName = Request.Form[hfContactName.UniqueID];
string contactTitle = Request.Form[hfContactTitle.UniqueID];
string address = Request.Form[hfAddress.UniqueID];
string city = Request.Form[hfCity.UniqueID];
string region = Request.Form[hfRegion.UniqueID];
string country = Request.Form[hfCountry.UniqueID];
string customerDetails = string.Format("Name:{1}{0}Id:{2}{0}Contact Name:{3}{0}Contact Title:{4}{0}Address:{5}{0}City:{6}{0}Region:{7}{0}Country:{8}{0}", "\\n", customerName, customerId, contactName, contactTitle, address, city, region, country);
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + customerDetails + "');", true);
}
[WebMethod]
public static string[] GetCustomerDetails(string prefix)
{
List<string> customers = new List<string>();
string constr = ConfigurationManager.ConnectionStrings["ConStr1"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT CompanyName,CustomerID,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax FROM Customers WHERE ContactName like @SearchText + '%'";
cmd.Parameters.AddWithValue("@SearchText", prefix);
cmd.Connection = conn;
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
customers.Add(string.Format("{0}-{1}-{2}-{3}-{4}-{5}-{6}-{7}-{8}-{9}-{10}", sdr["CompanyName"], sdr["CustomerID"], sdr["ContactName"], sdr["ContactTitle"], sdr["Address"], sdr["City"], sdr["Region"], sdr["PostalCode"], sdr["Country"], sdr["Phone"], sdr["Fax"]));
}
conn.Close();
return customers.ToArray();
}
VB.Net
Protected Sub Submit(sender As Object, e As EventArgs)
Dim customerName As String = Request.Form(txtSearch.UniqueID)
Dim customerId As String = Request.Form(hfCustomerId.UniqueID)
Dim contactName As String = Request.Form(hfContactName.UniqueID)
Dim contactTitle As String = Request.Form(hfContactTitle.UniqueID)
Dim address As String = Request.Form(hfAddress.UniqueID)
Dim city As String = Request.Form(hfCity.UniqueID)
Dim region As String = Request.Form(hfRegion.UniqueID)
Dim country As String = Request.Form(hfCountry.UniqueID)
Dim customerDetails As String = String.Format("Name:{1}{0}Id:{2}{0}Contact Name:{3}{0}Contact Title:{4}{0}Address:{5}{0}City:{6}{0}Region:{7}{0}Country:{8}{0}", "\n", customerName, customerId, contactName, contactTitle, _
address, city, region, country)
ClientScript.RegisterStartupScript(Me.[GetType](), "alert", (Convert.ToString("alert('") & customerDetails) + "');", True)
End Sub
<WebMethod()>
Public Shared Function GetCustomerDetails(prefix As String) As String()
Dim customers As New List(Of String)()
Dim constr As String = ConfigurationManager.ConnectionStrings("ConStr1").ConnectionString
Dim conn As New SqlConnection(constr)
Dim cmd As New SqlCommand()
cmd.CommandText = "SELECT CompanyName,CustomerID,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax FROM Customers WHERE ContactName like @SearchText + '%'"
cmd.Parameters.AddWithValue("@SearchText", prefix)
cmd.Connection = conn
conn.Open()
Dim sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
customers.Add(String.Format("{0}-{1}-{2}-{3}-{4}-{5}-{6}-{7}-{8}-{9}-{10}", sdr("CompanyName"), sdr("CustomerID"), sdr("ContactName"), sdr("ContactTitle"), sdr("Address"), _
sdr("City"), sdr("Region"), sdr("PostalCode"), sdr("Country"), sdr("Phone"), sdr("Fax")))
End While
conn.Close()
Return customers.ToArray()
End Function
Screenshot
