dynamic drop down list problem

[pmud]

Hi,

I have 2 drop down lists on an asp.Net page. The 1st contains alphabets.
When the user selects an alphabet frm the first list, the second drop down
list should be populated with names from the sql database, which begin with
that alphabet.

For this I used an sql data Adapter & created a data set. The user's
selection in 1st list is passed as parameter to the sql statement.

Please tell what is wrong with the following code?

public class WebForm3 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList ddlSubAgent;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected NEW.dsDdl dsDdl1;
protected System.Web.UI.WebControls.DropDownList ddlLetter;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.dsDdl1 = new NEW.dsDdl();
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).BeginInit();
this.ddlLetter.SelectedIndexChanged += new
System.EventHandler(this.ddlLetter_SelectedIndexCh anged);
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=TR;packet
size=4096;user id=sa;data source=TR;persist secu" +
"rity info=True;initial catalog=ALOG;password=aaa";
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"FormFields", new System.Data.Common.DataColumnMapping[] { new
System.Data.Common.DataColumnMapping("SubAgentName ", "SubAgentName")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT SubAgentName FROM FormFields
WHERE (SubAgentName LIKE @x)";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@x", System.Data.SqlDbType.VarChar, 30,
"SubAgentName"));
//
// dsDdl1
//
this.dsDdl1.DataSetName = "dsDdl";
this.dsDdl1.Locale = new System.Globalization.CultureInfo("en-US");
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).EndInit();

}
#endregion

private void ddlLetter_SelectedIndexChanged(object sender,
System.EventArgs e)
{

sqlDataAdapter1.GetFillParameters();
sqlDataAdapter1.Fill(dsDdl1);
ddlSubAgent.DataBind();
}
}
}

--
pmud

[Gopal \(FMS, Inc.\)]

Is there an error compiling or the page does not behave as expected?
--
Gopal Rangaswamy
Microsoft Certified Solutions Developer
FMS, Inc.
<http://www.fmsinc.com/consulting>
<http://www.fmsinc.com/dotnet/SourceBook/>

"pmud" <pm**@discussions.microsoft.com> wrote in message
news:0D**********************************@microsof t.com...

Hi,

I have 2 drop down lists on an asp.Net page. The 1st contains alphabets.
When the user selects an alphabet frm the first list, the second drop down
list should be populated with names from the sql database, which begin with that alphabet.

For this I used an sql data Adapter & created a data set. The user's
selection in 1st list is passed as parameter to the sql statement.

Please tell what is wrong with the following code?

public class WebForm3 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList ddlSubAgent;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected NEW.dsDdl dsDdl1;
protected System.Web.UI.WebControls.DropDownList ddlLetter;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.dsDdl1 = new NEW.dsDdl();
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).BeginInit();
this.ddlLetter.SelectedIndexChanged += new
System.EventHandler(this.ddlLetter_SelectedIndexCh anged);
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=TR;packet
size=4096;user id=sa;data source=TR;persist secu" +
"rity info=True;initial catalog=ALOG;password=aaa";
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"FormFields", new System.Data.Common.DataColumnMapping[] { new
System.Data.Common.DataColumnMapping("SubAgentName ", "SubAgentName")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT SubAgentName FROM FormFields
WHERE (SubAgentName LIKE @x)";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@x", System.Data.SqlDbType.VarChar, 30, "SubAgentName"));
//
// dsDdl1
//
this.dsDdl1.DataSetName = "dsDdl";
this.dsDdl1.Locale = new System.Globalization.CultureInfo("en-US");
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).EndInit();

}
#endregion

private void ddlLetter_SelectedIndexChanged(object sender,
System.EventArgs e)
{

sqlDataAdapter1.GetFillParameters();
sqlDataAdapter1.Fill(dsDdl1);
ddlSubAgent.DataBind();
}
}
}

--
pmud

[pmud]

Hi Gopal,

I solved that problem by using a data reader instead of data adapter. I
bound the second dropdown list to this data reader.

But I am facing problem with the SQL command to get values from the
Database.I am using the sqlCommand :
SELECT CustName FROM Table WHERE (CustName LIKE @x)

Here @x (the parameter) is the alphabet which the user selects from the
first dropdown list. Now, I want all CustNames which start with the letter
chosen by the user.

I tried the command with a % sign after the parameter like
SELECT CustName FROM Table WHERE (CustName LIKE @x%)
but it said Syntax is wrong. I even tried '@x*' and '@x%' , but nothing
seems to work.

What is the right syntax for this?

Thanks

"Gopal (FMS, Inc.)" wrote:

Is there an error compiling or the page does not behave as expected?
--
Gopal Rangaswamy
Microsoft Certified Solutions Developer
FMS, Inc.
<http://www.fmsinc.com/consulting>
<http://www.fmsinc.com/dotnet/SourceBook/>

"pmud" <pm**@discussions.microsoft.com> wrote in message
news:0D**********************************@microsof t.com...

Hi,

I have 2 drop down lists on an asp.Net page. The 1st contains alphabets.
When the user selects an alphabet frm the first list, the second drop down
list should be populated with names from the sql database, which begin

with

that alphabet.

For this I used an sql data Adapter & created a data set. The user's
selection in 1st list is passed as parameter to the sql statement.

Please tell what is wrong with the following code?

public class WebForm3 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList ddlSubAgent;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected NEW.dsDdl dsDdl1;
protected System.Web.UI.WebControls.DropDownList ddlLetter;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.dsDdl1 = new NEW.dsDdl();
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).BeginInit();
this.ddlLetter.SelectedIndexChanged += new
System.EventHandler(this.ddlLetter_SelectedIndexCh anged);
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=TR;packet
size=4096;user id=sa;data source=TR;persist secu" +
"rity info=True;initial catalog=ALOG;password=aaa";
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"FormFields", new System.Data.Common.DataColumnMapping[] { new
System.Data.Common.DataColumnMapping("SubAgentName ", "SubAgentName")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT SubAgentName FROM FormFields
WHERE (SubAgentName LIKE @x)";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@x", System.Data.SqlDbType.VarChar,

30,

"SubAgentName"));
//
// dsDdl1
//
this.dsDdl1.DataSetName = "dsDdl";
this.dsDdl1.Locale = new System.Globalization.CultureInfo("en-US");
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).EndInit();

}
#endregion

private void ddlLetter_SelectedIndexChanged(object sender,
System.EventArgs e)
{

sqlDataAdapter1.GetFillParameters();
sqlDataAdapter1.Fill(dsDdl1);
ddlSubAgent.DataBind();
}
}
}

--
pmud

[pmud]

Hi,

I found out the answer . It was simple. The corerct syntax is:
SELECT CustName FROM Table WHERE (CustName LIKE @x + '%')

Thanks

"pmud" wrote:

Hi Gopal,

I solved that problem by using a data reader instead of data adapter. I
bound the second dropdown list to this data reader.

But I am facing problem with the SQL command to get values from the
Database.I am using the sqlCommand :
SELECT CustName FROM Table WHERE (CustName LIKE @x)

Here @x (the parameter) is the alphabet which the user selects from the
first dropdown list. Now, I want all CustNames which start with the letter
chosen by the user.

I tried the command with a % sign after the parameter like
SELECT CustName FROM Table WHERE (CustName LIKE @x%)
but it said Syntax is wrong. I even tried '@x*' and '@x%' , but nothing
seems to work.

What is the right syntax for this?

Thanks

"Gopal (FMS, Inc.)" wrote:

Is there an error compiling or the page does not behave as expected?
--
Gopal Rangaswamy
Microsoft Certified Solutions Developer
FMS, Inc.
<http://www.fmsinc.com/consulting>
<http://www.fmsinc.com/dotnet/SourceBook/>

"pmud" <pm**@discussions.microsoft.com> wrote in message
news:0D**********************************@microsof t.com...

Hi,

I have 2 drop down lists on an asp.Net page. The 1st contains alphabets.
When the user selects an alphabet frm the first list, the second drop down
list should be populated with names from the sql database, which begin

with

that alphabet.

For this I used an sql data Adapter & created a data set. The user's
selection in 1st list is passed as parameter to the sql statement.

Please tell what is wrong with the following code?

public class WebForm3 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList ddlSubAgent;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected NEW.dsDdl dsDdl1;
protected System.Web.UI.WebControls.DropDownList ddlLetter;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.dsDdl1 = new NEW.dsDdl();
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).BeginInit();
this.ddlLetter.SelectedIndexChanged += new
System.EventHandler(this.ddlLetter_SelectedIndexCh anged);
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=TR;packet
size=4096;user id=sa;data source=TR;persist secu" +
"rity info=True;initial catalog=ALOG;password=aaa";
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"FormFields", new System.Data.Common.DataColumnMapping[] { new
System.Data.Common.DataColumnMapping("SubAgentName ", "SubAgentName")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT SubAgentName FROM FormFields
WHERE (SubAgentName LIKE @x)";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@x", System.Data.SqlDbType.VarChar,

30,

"SubAgentName"));
//
// dsDdl1
//
this.dsDdl1.DataSetName = "dsDdl";
this.dsDdl1.Locale = new System.Globalization.CultureInfo("en-US");
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.d sDdl1)).EndInit();

}
#endregion

private void ddlLetter_SelectedIndexChanged(object sender,
System.EventArgs e)
{

sqlDataAdapter1.GetFillParameters();
sqlDataAdapter1.Fill(dsDdl1);
ddlSubAgent.DataBind();
}
}
}

--
pmud