Drop-down box binding

[Guest]

Can somebody tell me what's wrong with this?

I am trying to bind a drop-down box to a table (states). Thanks,

//binding for the State drop-down

SqlConnection cStates = new SqlConnection
(ConfigurationSettings.AppSettings["SqlConnectionString"]);

cStates.Open();
string strTable = "GEM.states";
string strWhere = "";

strWhere = " WHERE stateid=" + lstState.SelectedItem.Value;

string strState = "SELECT statename, stateprefix as State FROM " +
strTable + strWhere + " ORDER BY statename";

adapter.Fill(ds,strTable);
lstState.DataSource = ds.Tables[strTable].DefaultView;
lstState.DataBind();

 

[Dave Sexton]

Hi Antonio,

If lstState is a System.Web.UI.WebControls.DropDownList then you have to set
the lstState.DataTextField and lstState.DataValueField properties. In this
case you could also set the lstState.DataMember to the name of your table
and set the lstState.DataSource to reference the DataSet that contains the
table.

There are some things I must mention as well:

1. You don't have to bind directly to the DefaultView. The DefaultView will
be used by the DropDownList automatically for binding.

2. Wrap the code that uses your cStates connection, except for the Open()
method, in a Try...Finally block and call cStates.Close() in the finally
block to ensure that your connection is closed in the case that an Exception
is thrown.

3 Use a stored procedure or a parameterized query for your SQL. Right now
your code is blatantly open to SQL-injection attacks and you might gain
performance using a stored procedure.

4. Use a strong-Typed DataSet. In VS.NET use the server explorer to browse
to your database. In Vs.NET 2005 you can use the database explorer. Add a
new file to your project, selecting the DataSet type from the new file
dialog. Drag a table from either the server explorer or the database
explorer to your empty DataSet in the designer.

HTH

 

[Guest]

Thank you, Dave. I am new to .NET. I'll try it and hopefully, I won't post
it again..

Antonio

Hi Antonio,

If lstState is a System.Web.UI.WebControls.DropDownList then you have to set
the lstState.DataTextField and lstState.DataValueField properties. In this
case you could also set the lstState.DataMember to the name of your table
and set the lstState.DataSource to reference the DataSet that contains the
table.

There are some things I must mention as well:

1. You don't have to bind directly to the DefaultView. The DefaultView will
be used by the DropDownList automatically for binding.

2. Wrap the code that uses your cStates connection, except for the Open()
method, in a Try...Finally block and call cStates.Close() in the finally
block to ensure that your connection is closed in the case that an Exception
is thrown.

3 Use a stored procedure or a parameterized query for your SQL. Right now
your code is blatantly open to SQL-injection attacks and you might gain
performance using a stored procedure.

4. Use a strong-Typed DataSet. In VS.NET use the server explorer to browse
to your database. In Vs.NET 2005 you can use the database explorer. Add a
new file to your project, selecting the DataSet type from the new file
dialog. Drag a table from either the server explorer or the database
explorer to your empty DataSet in the designer.

HTH

Can somebody tell me what's wrong with this?

I am trying to bind a drop-down box to a table (states). Thanks,

//binding for the State drop-down

SqlConnection cStates = new SqlConnection
(ConfigurationSettings.AppSettings["SqlConnectionString"]);

cStates.Open();
string strTable = "GEM.states";
string strWhere = "";

strWhere = " WHERE stateid=" + lstState.SelectedItem.Value;

string strState = "SELECT statename, stateprefix as State FROM " +
strTable + strWhere + " ORDER BY statename";

adapter.Fill(ds,strTable);
lstState.DataSource = ds.Tables[strTable].DefaultView;
lstState.DataBind();