SELECT statement not returning anything

Question

My issue is that the results are empty when executing the statement, even though when executing it in Microsoft's SQL server studio it works.

//This has two values in it (Ex: 4 and 2)
string[] arr2 = groupListValues.Split('-');

List<string> userID = new List<string>();

// Connect to the database
SqlConnection gconn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectinfohere"].ConnectionString);
gconn.Open();
SqlCommand command1 = new SqlCommand();
command1.Connection = gconn;
String sql = "SELECT ID FROM Users WHERE Group = @groupID";
command1.CommandText = sql;
command1.Parameters.Add(new SqlParameter("@groupID", ""));
SqlDataReader reader = command1.ExecuteReader();              

//issue is in this loop
foreach (string str in arr2)
{
    command1.Parameters["@groupID"].Value = str;
    while (reader.Read())
    {
        userID.Add(reader["ID"].ToString());
    }
}

Not sure what the issue is. The "ID" I'm getting in the SQL statement is of type bigint, could that cause an issue?

The reason I am setting the parameter inside the foreach loop is because, for each value in arr2 corresponds to a group that several users could be attached to. So I need to loop through that, get the users attached to each groupID, then add all their ID's to a list.

shouldn't you be setting the `groupID` parameter value before executing the reader? — Yacoub Massad, Jan 14 '16 at 16:05
well, the groupID needs to be set to each value in arr2. I'll edit my post to add in why. — pfinferno, Jan 14 '16 at 16:06
`command1.Parameters["@groupID"].Value = str;` what is the purpose of this line inside of the foreach loop.. — MethodMan, Jan 14 '16 at 16:06
The title of the question is misleading and the first two sentences are unnecessary. I think your real question is why is your query not working. — B. Clay Shannon-B. Crow Raven, Jan 14 '16 at 16:07
Try bringing `SqlDataReader reader = command1.ExecuteReader();` inside the loop after `command1.Parameters["@groupID"].Value = str;` — Yacoub Massad, Jan 14 '16 at 16:07
Yep that was it. Had to move the ExecuteReader() inside of the foreach loop. If you want to post this as an answer I'll mark it. Thanks! — pfinferno, Jan 14 '16 at 16:12
are you only returning a single Column of `GroupId` if so why not use a single line of code to return the DataTable into a List `List listGroupID = dt.AsEnumerable().ToList();` change your code to use the `.Fill()` method to fill a DataTable' then you can get at the `ItemArray` property — MethodMan, Jan 14 '16 at 16:13
if you want something more advanced the convert the `DataReader` into a `List` here is an example it's very straight foraward http://stackoverflow.com/questions/1464883/how-can-i-easily-convert-datareader-to-listt — MethodMan, Jan 14 '16 at 16:19

score 5 · Accepted Answer · answered Jan 14 '16 at 16:15

There are two problems with you code:

The first one is that you setting the @groupID parameter after you execute the reader. To fix it, execute the reader after you set the parameter value like this:

foreach (string str in arr2)
{
    command1.Parameters["@groupID"].Value = str;

    using(SqlDataReader reader = command1.ExecuteReader())
    {
        while (reader.Read())
        {
            userID.Add(reader["ID"].ToString());
        }
    }
}

The second problem is that Group is a reserved keyword in SQL, so you need to wrap it with square brackets like this:

String sql = "SELECT ID FROM Users WHERE [Group] = @groupID";

SELECT statement not returning anything

1 Answers1