Hi jovceka,
Refer below sample.
Database
For this sample I have used of NorthWind database that you can download using the link given below.
Download Northwind Database
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
var selectedText = '';
MakeAjaxCall(selectedText);
$("#ddlCountry").change(function () {
var selectedText = $(this).find("option:selected").text();
MakeAjaxCall(selectedText);
});
});
function MakeAjaxCall(selectedText) {
$.ajax({
type: "POST",
url: "CS.aspx/GetData",
data: '{search:"' + selectedText + '"}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var table = $('#tblCustomers');
$(table).find("tr:gt(0)").remove();
var rows = "";
for (var i = 0; i < response.d.length; i++) {
var CustomerID = response.d[i].CustomerID;
var CompanyName = response.d[i].CompanyName;
var City = response.d[i].City;
var Country = response.d[i].Country;
rows += "<tr><td>" + CustomerID +
"</td><td>" + CompanyName +
"</td><td>" + City +
"</td><td>" + Country +
"</td></tr>";
}
table.append(rows);
}, failure: function (response) {
alert(response.responseText);
}, error: function (response) {
alert(response.responseText);
}
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:DropDownList runat="server" ID="ddlCountry">
<asp:ListItem Value="Select" Text="Select"></asp:ListItem>
</asp:DropDownList>
<br />
<table id="tblCustomers" class="tblCustomers" cellpadding="2" cellspacing="0" border="1">
<tr>
<th>
<b>CustomerId </b>
</th>
<th>
<b>CompanyName </b>
</th>
<th>
<b>City </b>
</th>
<th>
<b>Country </b>
</th>
</tr>
</table>
</form>
</body>
</html>
Namespaces
C#
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
VB.Net
Imports System.Data.SqlClient
Imports System.Web.Services
Imports System.Data
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId,Country FROM Customers", con))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
ddlCountry.DataSource = dt;
ddlCountry.DataTextField = "Country";
ddlCountry.DataValueField = "CustomerId";
ddlCountry.DataBind();
}
}
}
}
}
[WebMethod]
public static List<Customer> GetData(string search)
{
List<Customer> customers = new List<Customer>();
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT TOP 5 * FROM Customers WHERE Country = @Country OR @Country IS NULL";
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
if (!string.IsNullOrEmpty(search))
{
cmd.Parameters.AddWithValue("@Country", search);
}
else
{
cmd.Parameters.AddWithValue("@Country", (object)DBNull.Value);
}
cmd.Connection = con;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
customers.Add(new Customer
{
CustomerID = sdr["CustomerId"].ToString(),
CompanyName = sdr["CompanyName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString(),
});
}
con.Close();
}
return customers;
}
public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId,Country FROM Customers", con)
Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
da.Fill(dt)
ddlCountry.DataSource = dt
ddlCountry.DataTextField = "Country"
ddlCountry.DataValueField = "CustomerId"
ddlCountry.DataBind()
End Using
End Using
End Using
End If
End Sub
<WebMethod()>
Public Shared Function GetData(ByVal search As String) As List(Of Customer)
Dim customers As List(Of Customer) = New List(Of Customer)()
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT TOP 5 * FROM Customers WHERE Country = @Country OR @Country IS NULL"
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
If Not String.IsNullOrEmpty(search) Then
cmd.Parameters.AddWithValue("@Country", search)
Else
cmd.Parameters.AddWithValue("@Country", CObj(DBNull.Value))
End If
cmd.Connection = con
con.Open()
Dim sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
customers.Add(New Customer With {
.CustomerID = sdr("CustomerId").ToString(),
.CompanyName = sdr("CompanyName").ToString(),
.City = sdr("City").ToString(),
.Country = sdr("Country").ToString()
})
End While
con.Close()
End Using
Return customers
End Function
Public Class Customer
Public Property CustomerID As String
Public Property CompanyName As String
Public Property City As String
Public Property Country As String
End Class
Screenshot
