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
– manish, Commented Nov 23, 2014 at 17:20
Also, be careful with that sql command look into sanitizing your parameters. — Ross Bush
– Ross Bush, Commented Nov 23, 2014 at 17:20

Sk Borhan Uddin · Accepted Answer · 2014-11-23 17:32:07Z

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

krivtom · Accepted Answer · 2014-11-23 17:20:18Z

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

Dgan · Accepted Answer · 2014-11-23 17:22:33Z

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, 2014 at 17:22

Dgan

10.3k1 gold badge33 silver badges51 bronze badges

3 Comments

krivtom Over a year ago

Are you sure IN ( @commaSepara ) works? I'm pretty sure SQL Server does not allow this

Dgan Over a year ago

@dotnetom Can you tell me what will be issue here...you can see here stackoverflow.com/questions/337704/parameterize-a-sql-in-clause

krivtom Over a year ago

Just run the above code (or similar one) and you will see for yourself

Collectives™ on Stack Overflow

How to bind datalist from code behind in asp.net

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related