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

this is obvious as for each iteration you're changing the DatasSource (DS) & re-binding it, instead of doing so you should assign the DS only once and bind to the same only once, and that should be outside for loop. — manish, Nov 23 '14 at 17:20
Also, be careful with that sql command look into sanitizing your parameters. — Ross Bush, Nov 23 '14 at 17:20

score 1 · Answer 1 · answered Nov 23 '14 at 17:20

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();

score 1 · Accepted Answer · answered Nov 23 '14 at 17:32

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...

score 0 · Answer 3 · answered Nov 23 '14 at 17:22

0

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();

answered Nov 23 '14 at 17:22

Dgan

10,077
1
29
51

2

Are you sure `IN ( @commaSepara )` works? I'm pretty sure SQL Server does not allow this – dotnetom Nov 23 '14 at 17:24
@dotnetom Can you tell me what will be issue here...you can see here http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause – Dgan Nov 23 '14 at 17:27
Just run the above code (or similar one) and you will see for yourself – dotnetom Nov 23 '14 at 17:37

How to bind datalist from code behind in asp.net

3 Answers3