CODEWITHSUNDEEP
c#.net
SanamShaikh
SanamShaikh

Reputation: 1799

Datatable Select rows using 'IN' clause C#.net

string selectedLeaveTypes = "";
  foreach (ListItem item in chkLeaveType.Items)
      {
         if (item.Selected)
         {
          selectedLeaveTypes = selectedLeaveTypes + "'" + item.Value + "',";
         }
      }
 selectedLeaveTypes = "(" +selectedLeaveTypes+ ")";

 DataRow[] rows = dt.Select("LeaveType IN '" + selectedLeaveTypes + "'");

Syntax error: The items following the IN keyword must be separated by commas and be enclosed in parentheses.

Upvotes: 1

Views: 14988

Answers (2)

Steve
Steve

Reputation: 216303

You have added the selectedLeaveType inside single quotes. Remove them

 DataRow[] rows = dt.Select("LeaveType IN " + selectedLeaveTypes);

The single quotes transform your whole variable value in a single big literal string.

Also you need to remove the final comma in the variable

selectedLeaveTypes = selectedLeaveTypes.TrimEnd(new char[] {','});

I also suggest you to remove that big string concatenation and use a List<string> with a final string.Join to build the IN clause

 List<string> values = new List<string>()
 foreach (ListItem item in chkLeaveType.Items)
 {
     if (item.Selected)
        values.Add("'" + item.Value + "'");
 }
 DataRow[] rows = null;

 // Consider that you should also handle the no selection status
 if(values.Count == 0)
    rows = dt.Select();   // ??
 else
    rows = dt.Select("LeaveType IN (" + 
                  string.Join(",", values.ToArray() + ")");

Upvotes: 7

NetMage
NetMage

Reputation: 26917

var selectedLeaveTypes = "("+String.Join(",", chkLeaveType.Items.Where(item => item.Selected).Select(item => $"'{item.Value}'").ToArray())+")";

DataRow[] rows = dt.Select($"LeaveType IN {selectedLeaveTypes}");

Upvotes: 0

Related Questions