Help Needed Regarding DataSet

[Eradicate]

Hi, im having this problem. I have a DropDownList with a list of values. It consist of AdminNumbers. I manually added a All option in the dropdownlist. When i click all it suppose to display all the AdminNumbers's details In a datagrid. Im trying to use a Foreach loop make this thing work. But the problem lies in here. Below is the code(The part which executes the "All" selection in the dropdownlist).


foreach(ListItem li in AdminList.Items)
{
string tryy;
tryy = li.Value.ToString();
SqlCommand cmd = sqlConnection1.CreateCommand();
cmd.CommandText = "SELECT * FROM AttendanceAdmin.atten WHERE AdminNo=@AdminNo";

cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@AdminNo", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
cmd.Parameters["@DateFrom"].Value = Date1tb.Text.ToString();
cmd.Parameters["@AdminNo"].Value = tryy;
cmd.Parameters["@DateTo"].Value = Date2tb.Text.ToString();

SqlDataAdapter sqladapter = new SqlDataAdapter();
sqladapter.SelectCommand = cmd;
DataSet myDataSet = new DataSet();
sqlConnection1.Open();
sqladapter.Fill(myDataSet, "atten");
sqlConnection1.Close();
DataGrid5.DataSource = myDataSet;
DataGrid5.DataBind();
}


The above code is the one i tried out. But it doesn't work. It resets the dataset each time it loops so the output is always the last value in the dropdownlist. Can anyone give me a tip or two, to make the dataset stacks the values it receives from the SELECT statement. Then when the loop ended, it binds the dataset to the datagrid to display the result.

[Andy Tacker]

You create an event for your dropdownlist, selection changed/ committed.

the you check whether the selected text is "-all-"
then you change your sQL command...

string m_szSQLCmd = null
if(selectedText=="all")
{
m_szSQLCmd = "select * From MyTable";
}
else
{
m_szSQLCmd = "select * From MyTable Where FieldValue = " + selectedText;
}

SqlCommand cmd = sqlConnection1.CreateCommand();
cmd.CommandText = m_szSQLCmd ;
SqlDataAdapter sqladapter = new SqlDataAdapter();
sqladapter.SelectCommand = cmd;
DataSet myDataSet = new DataSet();
sqlConnection1.Open();
sqladapter.Fill(myDataSet, "atten");
sqlConnection1.Close();
DataGrid5.DataSource = myDataSet;
DataGrid5.DataBind();

[Eradicate]

From the code i see. It only loops once and therefore display only one admin number's details? correct me if im wrong. don't understand some parts of the code u entered. Thanks for ur reply though appreciate it :)

[Andy Tacker]

this line of code:
m_szSQLCmd = "select * From MyTable";
fetches all data from your table...

this line of code:
m_szSQLCmd = "select * From MyTable Where FieldValue = " + selectedText;
fetches all data from your table for the admin you selected in the dropdownlist...

[Eradicate]

But i would like to display multiple records in one go. For example my dropdownlist have "5 items" and a "all". When i click all, it will display all the 5 records and not solely the one i selected.

[Andy Tacker]

Eradicate!
did you try to do it?
Please read my earlier post, which has two sentences...

read them and write your code...
execute it and then post your result here...

You have to try yourself... If you are willing to learn, you should be able to catch the idea and go ahead with your work. not that you should look for someone to do it for you... I am right?

Please give it a try. go through the posts again... It has all the information and logic that you need...

Good Luck...

[Eradicate]

I've did try out the code you given me. I've edited it to suit my program. But the result is isn't what i wanted. It display the whole database if -All- is selected. What i wanted and needed is when -All- is selected, it ONLY displays the admin number's details IN the DropDownList of admin numbers and not the whole database.

Therefore i needed a foreach loop, to loop the SQL query of the database for each admin number in the dropdownlist. After query, it adds the result into a dataset (Stacks the query results in the dataset depend on number of admin numbers in the dropdownlist) Then bind it into a datagrid.

I know how it could be done but i lack the programming skill to actually write the code out. Therefore im seeking help for experience programmers. I didn't really expect others to write the code for me and i just paste it into my code and be a happy man. An example of how it could be done would be enough. Im willing to learn and trying to learn, if i don't even bother i won't even be asking question on how the code work and what does the code means etc.. thanks for your time in replying to my post though.. i really appreciate it.

Below is the code which you gave me, i edited it a little in order to make it run.


private void AdminList_SelectedIndexChanged(object sender, System.EventArgs e)
{
if(AdminList.SelectedValue=="All")
{
SqlCommand cmd = sqlConnection1.CreateCommand();
cmd.CommandText = "SELECT * FROM AttendanceAdmin.atten";
SqlDataAdapter sqladapter = new SqlDataAdapter();
sqladapter.SelectCommand = cmd;
DataSet myDataSet = new DataSet();
sqlConnection1.Open();
sqladapter.Fill(myDataSet, "atten");
sqlConnection1.Close();
DataGrid5.DataSource = myDataSet;
DataGrid5.DataBind();
}
else
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT * FROM AttendanceAdmin.atten WHERE AdminNo =");
builder.Append("'");
builder.Append(AdminList.SelectedValue.ToString());
builder.Append("'");
SqlCommand cmd = new SqlCommand( builder.ToString(), sqlConnection1);
SqlDataAdapter sqladapter = new SqlDataAdapter();
sqladapter.SelectCommand = cmd;
DataSet myDataSet = new DataSet();
sqlConnection1.Open();
sqladapter.Fill(myDataSet, "atten");
sqlConnection1.Close();
DataGrid5.DataSource = myDataSet;
DataGrid5.DataBind();
}

}

[Andy Tacker]

Change * to AdminNo in your select statement.

[Eradicate]

It doesn't run anymore after i change '*' to AdminNo. It says a certain field name is not present.

[Eradicate]

Thanks Andy, i found a way to solve my problem. Thanks for your time and effort in replying. I really appreciate it.

[Andy Tacker]

nice.

post your solution for others, may be someone would find it useful...