Problem in populating the GridView in ASP.NET(C#)

Question

I'm trying to populate a Gridview with results from loop. But I'm getting only last result in the loop.
I think GridView is being overwritten on every time the for loop is being executed.

Can you people help me to remove this problem please.

for (int j = 0; j < i; j++)
{
    Label1.Text += fipath[j];
    Label1.Text += "-------------";
    SqlConnection conn = new SqlConnection("Server=ILLUMINATI;" + "Database=DB;Integrated Security= true");
    SqlCommand comm = new SqlCommand("Select * from FileUpload where UploadedBy='" + NAME + "' AND FilePath='" + fipath[j] + "'", conn);

    try
    {
        conn.Open();
        SqlDataReader rdr = comm.ExecuteReader();
        if (Role.Equals("admin"))
        {
            GridView1.DataSource = rdr;
            GridView1.DataBind();
        }
        rdr.Close();
    }
    catch
    {
        conn.Close();
    }
}

Alex Aza · Accepted Answer · 2011-06-11 17:14:29Z

There is more than one problem with this code:

seems like if Role== "admin" you don't need to query db at all
DataSource of the grid is overridden on every loop iteration, this is why you see only the last value.
use parameters for SqlCommand to prevent SQL injection.
don't run string concatenation in the loop. Use StringBuilder instead
use using for your connection. The code is cleaner this way.

The fix could look like this:

if (Role != "admin")
    return;

var dataTable = new DataTable();
var stringBuilder = new StringBuilder();
using (var connection = new SqlConnection("Server=ILLUMINATI;" + "Database=DB;Integrated Security= true"))
using (var command = connection.CreateCommand())
{
    connection.Open();
    command.CommandText = "Select * from FileUpload where UploadedBy = @UploadedBy AND FilePath = @FilePath";
    command.Parameters.AddWithValue("UploadedBy", NAME);
    var filPathParameter = command.Parameters.Add("FilePath", SqlDbType.VarChar);
    for (int j = 0; j < i; j++)
    {
        stringBuilder.Append(fipath[j]);
        stringBuilder.Append("-------------");
        filPathParameter.Value = fipath[j];
        dataTable.Load(command.ExecuteReader(), LoadOption.PreserveChanges);
    }
}
Label1.Text += stringBuilder.ToString();
GridView1.DataSource = dataTable;
GridView1.DataBind();

Also, I don't know how many elements your normal loop is. If it is one or two and you have appropriate indexes in FileUpload table then it is ok to leave as is. However, if you need to do the for many times you should consider switching to a single query instead

For example:

var filePathes = string.Join(",", fipath.Select(arg => "'" + arg + "'"));
var command = "Select * from FileUpload where UploadedBy = @UploadedBy AND FilePath in (" + filePathes + ")";

This query is SQL injection prone. And has a 2100 elements limit in MS SQL.

There is more than one way to approach this. Depends on your DBMS and requirements.

Community · Accepted Answer · 2017-05-23 11:59:25Z

2

Use the in clause in SQL Query and pass the list of ID's in FilePath

SqlCommand comm = new SqlCommand("Select * from FileUpload where UploadedBy='" + NAME 
 + "' AND FilePath in (" + listOfIDs + ")", conn);

Check out these URLs that are related to the use of in clause.

Techniques for In-Clause and SQL Server

Parameterizing a SQL IN clause?

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Jun 11, 2011 at 16:56

Muhammad Akhtar

52.2k37 gold badges142 silver badges191 bronze badges

3 Comments

Naresh Over a year ago

It looks correct.But for some reason it's not giving any result.

Naresh Over a year ago

I removed the for loop and given fipath instead of fipath[j].It's printing nothing.

Muhammad Akhtar Over a year ago

You have to give list of Id's in parameter like... in(1,2,3,4) if you have SQL server, simply verify the query there and copy that query in SQL command. Select * from FileUpload where uploadedby='' and Filepath(1,3,4,5)

Patrick · Accepted Answer · 2011-06-11 16:52:49Z

1

Create a list or BindingSource outside the loop, bind that to your gridview and then add all records to that list or source.

The problem with your current approach is that you are overwriting the records pulled from the database with a new datasource each time, so as you stated, only the last one is "set", and the older assignments are disposed of.

answered Jun 11, 2011 at 16:52

Patrick

18k6 gold badges73 silver badges85 bronze badges

2 Comments

Naresh Over a year ago

How to add items to the list Dynamically.Can you show me please.

Patrick Over a year ago

@ILLUMINATI7590: Use the Read method from IDataReader (your rdr instance) and add an object to a bindingsource for instance using the Add method.

Collectives™ on Stack Overflow

Problem in populating the GridView in ASP.NET(C#)

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related