DataTable.Select method in .NET 2.0

  • Thread starter Thread starter maciu
  • Start date Start date
M

maciu

Hi,
I have a problem with DataTable.Select method in Microsoft .NET 2.0.
I have a ListBox control, with DataSource pointing to a DataTable
"RolePermissions". ListBox has SelectionMode property set to
"Multiple".
I would like to enable User to Delete Multiple items with just one
click.
I am almost sure, that this worked in .NET 1.1.

I have a DataTable with column "permissionID" of type uniqueidentifier
Here is my Code:


// We will concatenate selected Guid's into one string
StringBuilder sbItemsSelected = new StringBuilder();

// for every Item ....
foreach (ListItem item in lbRolePermissions.Items)
{
// ... which is selected...
if (item.Selected == true)
{
// ...we add selected ID, and space after it
sbItemsSelected.Append("'");
sbItemsSelected.Append(item.Value);
sbItemsSelected.Append("' ");
}
}

/// We now have list of ID's, separated with spaces (with space at he
end)
string selectedItems = sbItemsSelected.ToString();
// remove last space
selectedItems = selectedItems.Trim();
// insert commas instead of spaces
selectedItems = selectedItems.Replace(" ", ",");
// get the table, to handle data
DataTable dtRolePermissions = Session["RolePermissions"] as DataTable;

/// select rows, which are of interest to us
********************************************************************
DataRow[] drsRolePermissions =
dtRolePermissions.Select("permissionID IN (" + selectedItems + ")");
*********************************************************************
/// delete them
foreach (DataRow drRolePer in drsRolePermissions)
{
drRolePer.Delete();
}

/// save new Table in Session variable
Session["RolePermissions"] = dtRolePermissions;
// bind the Table again
lbRolePermissions.DataSource = dtRolePermissions;
lbRolePermissions.DataBind();



The line inside "stars" is line, in which an exception occurs, the
exception Message is:
"Cannot perform '=' operation on System.Guid and System.String."

I thought, that exception can occur because the Items in listBox are
filtered with an expression, but that would be stupid of Microsoft not
to let such operations.

I am confused.... this really worked in .NET 1.1
I used it a lot in previous applications....
Is there some explanation to this fact ?
( apart from saying, that this is a bug ;-) )

Thank You in advance for help,
maciu
 
Hi,

maciu said:
Hi,
I have a problem with DataTable.Select method in Microsoft .NET 2.0.
I have a ListBox control, with DataSource pointing to a DataTable
"RolePermissions". ListBox has SelectionMode property set to
"Multiple".
I would like to enable User to Delete Multiple items with just one
click.
I am almost sure, that this worked in .NET 1.1.

I have a DataTable with column "permissionID" of type uniqueidentifier
Here is my Code:


// We will concatenate selected Guid's into one string
StringBuilder sbItemsSelected = new StringBuilder();

// for every Item ....
foreach (ListItem item in lbRolePermissions.Items)
{
// ... which is selected...
if (item.Selected == true)
{
// ...we add selected ID, and space after it
sbItemsSelected.Append("'");
sbItemsSelected.Append(item.Value);
sbItemsSelected.Append("' ");
}
}

/// We now have list of ID's, separated with spaces (with space at he
end)
string selectedItems = sbItemsSelected.ToString();
// remove last space
selectedItems = selectedItems.Trim();
// insert commas instead of spaces
selectedItems = selectedItems.Replace(" ", ",");
// get the table, to handle data
DataTable dtRolePermissions = Session["RolePermissions"] as DataTable;

/// select rows, which are of interest to us
********************************************************************
DataRow[] drsRolePermissions =
dtRolePermissions.Select("permissionID IN (" + selectedItems + ")");
*********************************************************************
/// delete them
foreach (DataRow drRolePer in drsRolePermissions)
{
drRolePer.Delete();
}

/// save new Table in Session variable
Session["RolePermissions"] = dtRolePermissions;
// bind the Table again
lbRolePermissions.DataSource = dtRolePermissions;
lbRolePermissions.DataBind();



The line inside "stars" is line, in which an exception occurs, the
exception Message is:
"Cannot perform '=' operation on System.Guid and System.String."

I thought, that exception can occur because the Items in listBox are
filtered with an expression, but that would be stupid of Microsoft not
to let such operations.

I am confused.... this really worked in .NET 1.1
Click to expand...

Doesn't seem to work in .NET 1.1 either. It works if you use = operator but
not with the IN operator.

In .NET1.1 you can convert each guid string into a guid :
"permissionID IN (Convert('"+SomeGuidStr+"', 'System.Guid'), ...)"

In .NET2.0 you can convert the guid field into a string :
"Convert(permissionID,'System.String') IN ('"+SomeGuidStr+"', ...)"

But i'm wondering if it wouldn't be easier if you use Find ?
Eg.

DataTable dtRolePermissions =
Session["RolePermissions"] as DataTable;

foreach (ListItem item in lbRolePermissions.Items)
{
if (item.Selected == true)
dtRolePermissions.Find( item.Value ).Delete();
}


HTH,
Greetings
 
First of all, thank You for Your reply,

Indeed, using Find method is much easier - simply haven't thought of
it, but when I tried to apply it to my scenario, I got an
MissingPrimaryKeyException.
I am not sure why...
This table is returned by the SqlDataAdapter.Fill() method. The
SelectCommand for this dataAdpater runs T-SQL select statement, which
retrieves table directly from DataBase (table has primary keys in
DataBase).

Any suggestions?

Greetings,
maciu
 
Hi,

maciu said:
First of all, thank You for Your reply,

Indeed, using Find method is much easier - simply haven't thought of
it, but when I tried to apply it to my scenario, I got an
MissingPrimaryKeyException.
Click to expand...

If you get that exception then there is no primary key
(DataTable.PrimaryKey).

For DataTable.Rows.Find to work, the permissionID column must be the primary
key.
I am not sure why...
Click to expand...

If the permissionID column in the DB is the primary key then it should be
applied to the DataTable when you use Adapter.Fill, try to set
SqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey before
you Fill.

Or you can set the pk yourself:
dtRolePermissions.PrimaryKey = new DataColumn[] {
dtRolePermissions.Columns["permissionID"] };

HTH,
Greetings
 
Back
Top