Hi ukd,
I have created a sample. Check this example. Now please take its reference and correct your code.
For this sample I have used of NorthWind database that you can download using the link given below.
Download Northwind Database
First.aspx
<div>
City:
<asp:DropDownList runat="server" ID="ddlCity">
</asp:DropDownList>
Country:
<asp:DropDownList runat="server" ID="ddlCountry">
</asp:DropDownList>
<br />
<asp:Button Text="Transfer" OnClick="List_Click" runat="server" />
</div>
First.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateDropDownList(ddlCity, "City", "City", "SELECT DISTINCT City FROM Customers WHERE City <> ''");
PopulateDropDownList(ddlCountry, "Country", "Country", "SELECT DISTINCT Country FROM Customers WHERE Country <> ''");
}
}
protected void List_Click(object sender, EventArgs e)
{
if (ddlCountry.SelectedIndex > 0 && ddlCity.SelectedIndex > 0)
{
Server.Transfer("Extract.aspx?Country=" + ddlCountry.SelectedItem.Text + "&City=" + ddlCity.SelectedItem.Text);
}
}
private void PopulateDropDownList(DropDownList ddl, string dataTextField, string dataValueField, string query)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
con.Open();
DataSet ds = new DataSet();
da.Fill(ds);
ddl.DataTextField = dataTextField;
ddl.DataValueField = dataValueField;
ddl.DataSource = ds;
ddl.DataBind();
ddl.Items.Insert(0, new System.Web.UI.WebControls.ListItem("Select", "0"));
}
}
}
}
First.aspx.vb
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
PopulateDropDownList(ddlCity, "City", "City", "SELECT DISTINCT City FROM Customers WHERE City <> ''")
PopulateDropDownList(ddlCountry, "Country", "Country", "SELECT DISTINCT Country FROM Customers WHERE Country <> ''")
End If
End Sub
Protected Sub List_Click(ByVal sender As Object, ByVal e As EventArgs)
If ddlCountry.SelectedIndex > 0 AndAlso ddlCity.SelectedIndex > 0 Then
Server.Transfer("Extract.aspx?Country=" & ddlCountry.SelectedItem.Text & "&City=" + ddlCity.SelectedItem.Text)
End If
End Sub
Private Sub PopulateDropDownList(ByVal ddl As DropDownList, ByVal dataTextField As String, ByVal dataValueField As String, ByVal query As String)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query, con)
Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
con.Open()
Dim ds As DataSet = New DataSet()
da.Fill(ds)
ddl.DataTextField = dataTextField
ddl.DataValueField = dataValueField
ddl.DataSource = ds
ddl.DataBind()
ddl.Items.Insert(0, New System.Web.UI.WebControls.ListItem("Select", "0"))
End Using
End Using
End Using
End Sub
Extract.aspx
<div>
<table>
<tr>
<td>
ID:
</td>
<td>
<asp:TextBox runat="server" ID="txtCustomerID" />
</td>
</tr>
<tr>
<td>
Name:
</td>
<td>
<asp:TextBox runat="server" ID="txtxContactName" />
</td>
</tr>
<tr>
<td>
City:
</td>
<td>
<asp:TextBox runat="server" ID="txtxCity" />
</td>
</tr>
<tr>
<td>
Country:
</td>
<td>
<asp:TextBox runat="server" ID="txtCountry" />
</td>
</tr>
</table>
</div>
Extract.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(Request.QueryString["Country"]) && !string.IsNullOrEmpty(Request.QueryString["City"]))
{
string country = Request.QueryString["Country"];
string city = Request.QueryString["City"];
Response.Write(string.Format("Country is : <b>{0}</b> <br/> City is : <b>{1}</b>", country, city));
string str = "SELECT CustomerID,ContactName,City,Country FROM Customers WHERE City = '" + city + "' AND Country = '" + country + "'";
DataTable dt = GetDataTable(str);
if (dt.Rows.Count > 0)
{
txtCustomerID.Text = dt.Rows[0]["CustomerID"].ToString();
txtxContactName.Text = dt.Rows[0]["ContactName"].ToString();
txtxCity.Text = dt.Rows[0]["City"].ToString();
txtCountry.Text = dt.Rows[0]["Country"].ToString();
}
else
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No record found as per your criteria!')", true);
}
}
}
private DataTable GetDataTable(string query)
{
DataSet ds = new DataSet();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
con.Open();
da.Fill(ds);
}
}
}
return ds.Tables[0];
}
Extract.aspx.vb
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not String.IsNullOrEmpty(Request.QueryString("Country")) AndAlso Not String.IsNullOrEmpty(Request.QueryString("City")) Then
Dim country As String = Request.QueryString("Country")
Dim city As String = Request.QueryString("City")
Response.Write(String.Format("Country is : <b>{0}</b> <br/> City is : <b>{1}</b>", country, city))
Dim str As String = "SELECT CustomerID,ContactName,City,Country FROM Customers WHERE City = '" & city & "' AND Country = '" & country & "'"
Dim dt As DataTable = GetDataTable(str)
If dt.Rows.Count > 0 Then
txtCustomerID.Text = dt.Rows(0)("CustomerID").ToString()
txtxContactName.Text = dt.Rows(0)("ContactName").ToString()
txtxCity.Text = dt.Rows(0)("City").ToString()
txtCountry.Text = dt.Rows(0)("Country").ToString()
Else
ClientScript.RegisterClientScriptBlock(Me.[GetType](), "", "alert('No record found as per your criteria!')", True)
End If
End If
End Sub
Private Function GetDataTable(ByVal query As String) As DataTable
Dim ds As DataSet = New DataSet()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query, con)
Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
con.Open()
da.Fill(ds)
End Using
End Using
End Using
Return ds.Tables(0)
End Function
Screenshot
