ASP.NET foreach looping only displaying last value of gridview

Question

I am trying to select specific persons with their ID by using ASP.NET Gridview, but only the last userID reads. Let's say I have ID's 3, 4, and 6, and only ID number 6 does not appear in the Gridview table but 3 and 4 appear. They should not appear even 3 and 4 because I am running the sql script as: WHERE NOT EmployeeId = " + userID.

foreach (int userID in userIDList)
{ 

    string cs = ConfigurationManager.ConnectionStrings["MYDB"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {                
        SqlCommand cmd = new SqlCommand("SELECT FirstName, LastName FROM Employee WHERE NOT EmployeeId = " + userID, con);

        con.Open();
        GridView4.DataSource = cmd.ExecuteReader();
        GridView4.DataBind();
    }            
}

Steve · Accepted Answer · 2016-03-04T22:29:36.793

0

Your query is inside the loop. At each loop you execute the query and bind the grid with the result of the query REPLACING the previous result.
Of course you end up with the last result only.

But you don't need any kind of loop, just use the IN clause that allows you to specify a list of values to filter your results.

Change your code to

string cs = ConfigurationManager.ConnectionStrings["MYDB"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
     string inClause = string.Join(",", userIDList);
     SqlCommand cmd = new SqlCommand(@"SELECT FirstName, LastName 
           FROM Employee WHERE EmployeeId NOT IN(" + inClause + ")", con);
     con.Open();
     GridView4.DataSource = cmd.ExecuteReader();
     GridView4.DataBind();
}

EDIT
It seems that you have some kind of unexplicable error in the string.Join call. Not clear what is causing it because, as far as I know, you should be able to pass a List<int> as second parameter to string.Join. However, you could force your List of integers to become an array of strings with this code

 string inClause = string.Join(",", userIDList
                                    .Select(x => x.ToString())
                                    .ToArray()

edited Mar 04 '16 at 22:29

answered Mar 04 '16 at 21:58

Steve

213,761
22
232
286

thank you for Quick respons , but I'am getting error " The call is ambigouse between the following methods or properities : 'string.Join(string, params string[]) and 'string.Join(string, params object[]) ... – Jonas Willander Mar 04 '16 at 22:07
What is the type of userIDList? I assumed it to be a `List` – Steve Mar 04 '16 at 22:12
I'am declaring as private List userIDList = new List(); – Jonas Willander Mar 04 '16 at 22:14
That's weird, I have no such errors when passing a List to the string.Join method. Doing some checks now.... – Steve Mar 04 '16 at 22:17
Try with this syntax _string.Join(",", userIDList.Select(x => x.ToString()).ToArray());_ but the error is inexplicable. As far as I know you could pass the List as second parameter to string.Join in this Framework version. – Steve Mar 04 '16 at 22:24
just a calculation problem. Where there is not Id's to retriv it says : Incorrect syntax near ')'. I mean I have a bunch of imployyes If they are not in userIDlist then I want show all in gridview. But right now there is something wrong.. becouse if list is empty then it's not selecting all employyes. – Jonas Willander Mar 04 '16 at 22:35
You should prepare two different strings for your SqlCommand, one as the current one and another without the WHERE clause (and all the following test) IE: _if(userIDList.Count == 0) ..... else ....._ if it is not clear, please post a new question – Steve Mar 04 '16 at 22:41
A final note. Remember that concatenating strings like I have done here is considered a bad practice for the risk of [Sql Injection](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). In this specific case, I think that there isn't a big problem, because we are creating a string from a list of integers while [parametrizing an IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) is complex. – Steve Mar 04 '16 at 22:57

ASP.NET foreach looping only displaying last value of gridview

1 Answers1