CODEWITHSUNDEEP
c#sql-serverselect
pfinferno
pfinferno

Reputation: 1945

SELECT statement not returning anything

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.

Upvotes: 1

Views: 83

Answers (1)

Yacoub Massad
Yacoub Massad

Reputation: 27861

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";

Upvotes: 5

Related Questions