Access Database, ASP/C# Drop Down List populating a Drop Down List

[WickedFooker]

Ok this has sort of been asked before but also somewhat differently here. I have a regular drop down (not connected to a database) that just has the topics of subjects. This is a sample of what I have:

                   Please Select
                   English
                   Math
                   Social Studies
                   Science
                   History

I want to make it so that when it changes the next part will search and select teachers that teach that specific subject. I am using an Access Database for connection. I have pieced together some of what others did in an attempt to get it to work but no.

protected void ddlcourseType_SelectedIndexChanged(object sender, EventArgs e)
{
OleDbConnection dbConn = null;
OleDbCommand dbCmd;
OleDbDataReader dr;
String strConnection;
String strSQL;
string path = Server.MapPath("eAcademy_DB.mdb");

    string Teachable = Convert.ToString(ddlcourseType.SelectedValue);
    strConnection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + path;
    dbConn = new OleDbConnection(strConnection);
    dbConn.Open();
    strSQL = "select \* from tblEmployee where canTeach=" + Teachable;

    dbCmd = new OleDbCommand(strSQL, dbConn);
    DataSet ds = new DataSet();
            
    dbConn.Close();
    ddlTeacher.DataSource = ds;
    ddlTeacher.DataTextField = "emp\_ID";
    ddlTeacher.DataValueField = "emp\_ID";
    ddlTeacher.DataBind();
    ddlTeacher.Items.Insert(0, new ListItem("--Select--", "0"));
    if (ddlTeacher.SelectedValue == "0")
    {
        ddlTeacher.Items.Clear();
        ddlTeacher.Items.Insert(0, new ListItem("--Select--", "0"));
    }
}

I think what I really need to do is make a for loop and have it iterate for the amount of teachers that are in the list, and then have it populate the list, but not sure how exactly to do thi

[Richard Deeming]

WickedFooker wrote:

strSQL = "select * from tblEmployee where canTeach=" + Teachable;

Your first problem is SQL Injection[^]. Never use string concatenation to insert parameters into a query; use a parameterized query instead.

WickedFooker wrote:

dbCmd = new OleDbCommand(strSQL, dbConn); DataSet ds = new DataSet(); dbConn.Close();

Creating an OleDbCommand object isn't going to execute the query, let alone store the results in another variable. You could use ExecuteReader, iterate through the results, and add them to a DataTable, but it's much simpler to use a DataAdapter to do the work for you.

WickedFooker wrote:

if (ddlTeacher.SelectedValue == "0") {    ddlTeacher.Items.Clear();    ddlTeacher.Items.Insert(0, new ListItem("--Select--", "0")); }

Since you've just inserted an item with a value of "0" as the first item, this will most likely be the selected item. Even if your data-binding was working, this block of code will most likely throw away the results from the database and leave you with an empty list. Try something like this:

protected void ddlcourseType_SelectedIndexChanged(object sender, EventArgs e)
{
string path = Server.MapPath("eAcademy_DB.mdb");
string connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + path;
string commandText = "SELECT * FROM tblEmployee WHERE canTeach = ?";

var ds = new DataSet();

using (var connection = new OleDbConnection(connectionString))
using (var command = new OleDbCommand(commandText, connection))
{
// OleDbCommand uses positional, rather than named, parameters.
// The parameter name doesn't matter; only the position

[WickedFooker]

I just want to thank you for the help! This did the trick. I was able to populate the 2nd table with perhaps the smallest of changes.

string commandText = "SELECT * FROM tblEmployee WHERE canTeach = " + ddlcourseType.SelectedValue;

I removed the select on the bottom and instead put it in the page load since it is more needed for looks when the page loads. Thanks again for your help. I am certain I will be back for more :) REVISED: I see your warning about String concatenation. I will change it back. I went back to the way you originally posted it!

[cyber_addicted]

i think you are missin to fill your dataset your command object not executing use

DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(dbCmd)

adapter.Fill(ds);

after

dbCmd = new OleDbCommand(strSQL, dbConn);

then it fill dataset and your debug will show your dataset contain value.