How to bind datalist from code behind in asp.net

Question

I want to bind datalist from code behind in asp.net

i am taking product id's from a list and selecting all products on their bases

Following is my code:

List<string> r_items_grid = (List<string>)Session["recent_items"];

for(int i=0; i < r_items_grid.Count; i++)
{

    OleDbCommand cmd_r_items= new OleDbCommand("SELECT product_id,product_name,product_price,product_image_1 from products where product_id="+ Convert.ToInt32( r_items_grid[i]),con); 
    r_items_reader=cmd_r_items.ExecuteReader();

    DataList3.DataSource = r_items_reader;
    DataList3.DataBind(); 
}

But i am only seeing last record in datalist

Answer 1

If I am not wrong you are trying put string list in a session. When you traversing the whole list each and every time you bind the datalist (DataList3) with a new string based on list index value. So it shows always the last string value of the list. If you want to fetch all data according your list, you might use this

List<string> r_items_grid = (List<string>)Session["recent_items"];

string items_id= string.Join(",", r_items_grid);// items_id may be like 1,2,3,4,5.

OleDbCommand cmd_r_items= new OleDbCommand("SELECT product_id,product_name,product_price,product_image_1 from products where product_id IN ("+ items_id + ")",con); 
r_items_reader=cmd_r_items.ExecuteReader();

DataList3.DataSource = r_items_reader;
DataList3.DataBind();

"SELECT product_id,product_name,product_price,product_image_1 from products where product_id IN (1,2,3,4,5...)" the query is used to fetch all data whose items_id is 1,2,3,4,5...

Answer 2

The issue is that you are selecting one record at a time, and then assigning it to a data source. When you do that the previous value there is overwritten. So you only see results from the last query. You can fix it by making a single request and then binding that to a list:

Below code works assuming that you know that Session["recent_items"] stores integers, if there is a chance that strings are stored there you run the risk of SQL injection.

List<string> r_items_grid = (List<string>)Session["recent_items"];

string ids = string.Join(",", r_items_grid);    // Here you get IDs in format 1,2,3,4,5 etc.

OleDbCommand cmd_r_items= new OleDbCommand("SELECT product_id,product_name,product_price,product_image_1 from products where product_id IN ("+ ids + ")",con); 
r_items_reader=cmd_r_items.ExecuteReader();

DataList3.DataSource = r_items_reader;
DataList3.DataBind(); 

Answer 3

There is No Need to For Loop

You can use comma separated List

string  commaSepara = String.Join(",", r_items_grid);

OleDbParameters commaSepara=new OleDbParameters("@commaSepara",SqlDbType.NVarchar,-1);
commaSepara.Value=commaSepara;
OleDbCommand cmd_r_items= new OleDbCommand(@"SELECT product_id,product_name,product_price,product_image_1 from products where product_id
 IN ( @commaSepara )",con); 


r_items_reader=cmd_r_items.ExecuteReader();

DataList3.DataSource = r_items_reader;
DataList3.DataBind();