Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

Glayaar — Wed, 31 Mar 2010 02:56:19 GMT

As a workaround, you will need to use a column name alias for such column in the SQL command in order for the table adapter generated parameter and WebGrid generated parameter to match.

Another suggestion, would be to convert the parameter to the expected value in the ISDS during deleting and editing server side event. Here is the snippet for the Deleting scenario.

ISDS Deleting server side event for # character:

protected void ISDataSource1_Deleting(object sender, ISNet.WebUI.DataSource.ISDataSourceMethodEventArgs e)
{
    e.InputParameters["_Original_Category_"] = e.InputParameters["Original_Category#"];
    e.InputParameters.Remove("Original_Category#");
}

ISDS Delete Parameter for # character:

<ISDataSource:ISDataSourceTable DeleteMethod="Delete" InsertMethod="Insert" 
    OldValuesParameterFormatString="Original_{0}" SelectMethod="GetData" 
    TableName="CategoriesPoundSign" 
    TypeName="CategoriesPoundSignTableAdapters.CategoriesPoundSignTableAdapter" 
    UpdateMethod="Update">
    <DeleteParameters>
        <asp:Parameter Name="_Original_Category_" Type="Int32" />
        <asp:Parameter Name="Original_Category#" Type="Int32" />
    </DeleteParameters>

Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

DJBadin — Tue, 30 Mar 2010 07:14:04 GMT

So, how do I get these types of tables to work with WebGrid?

Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

Glayaar — Tue, 30 Mar 2010 03:11:51 GMT

The cause of this issue is .NET TableAdapter limitation. The database field name will be converted in command parameter, since .NET parameter name will not accept # or - character, the parameter will be renamed, in your scenario from Original_Category# to _Original_Category_ and Category-Name to p1.

Similar behavior could also be observed if you are using ObjectDataSource and GridView.

Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

DJBadin — Mon, 29 Mar 2010 12:15:08 GMT

Column Names with Hyphens and Pound Signs cause "ISDataSource '...' Table [...] could not find a non-generic method '...' that has parameters: ..."

Why is this occurring?

I have include T-SQL to create 2 tables in the NorthWind Database, CategoriesPoundSign and CategoriesHyphen and provided 2 Test Cases.

USE [Northwind]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[CategoriesPoundSign](

[Category#] [int] IDENTITY(1,1) NOT NULL,

[CategoryName] [nvarchar](15) NOT NULL,

[Description] [ntext] NULL,

[Picture] [image] NULL,

CONSTRAINT [PK_CategoriesPoundSign] PRIMARY KEY CLUSTERED 

(

[Category#] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [Northwind]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[CategoriesHyphen](

[CategoryID] [int] IDENTITY(1,1) NOT NULL,

[Category-Name] [nvarchar](15) NOT NULL,

[Description] [ntext] NULL,

[Picture] [image] NULL,

CONSTRAINT [PK_CategoriesHyphen] PRIMARY KEY CLUSTERED 

(

[CategoryID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

TEST 1:

Create a DataSet (.xsd) for CategoriesPoundSign, CategoriesPoundSign

1. Select each row individually, do not select "*". This will generate:

SELECT Category#, CategoryName, Description, Picture FROM dbo.CategoriesPoundSign

2. When configuring the TableAdapter, the first step has has an Advanced Option Button in the Lower Left

- Click it and Check "Refresh the data table"

3. Make sure the AutoIncrement Values are correct

- Select Primary Key

Change following properties form -1

AutoIncrementSeed to 0
AutoIncrementStep to 1

Create a new .aspx page, CategoriesPoundSignISDataSource.aspx.

4. In Design Mode, add an ISDataSource

5. Configure DataSource

6. Select Schema CategoriesPoundSign

7. "Auto generate ISDataSource's table list from selected schema"

8. Add a WebGrid

9. Choose Data Source ISDataSource1

10. Allow Add New

11. Allow Editing

12. Allow Delete

13. Run the Page

14. Add a New Row with Values

- Category-Name = "Cat1"
- Description = "Desc1"

15. Refresh the Grid

16. Edit the Description Value to be "Desc1x"

17. Update the Row

18. The following error is generated:

"ISDataSource 'ISDataSource1' Table [CategoriesPoundSign] could not find a non-generic method 'Update' that has parameters: CategoryName, Description, Picture, _Original_Category_, Category_, Original_Category#."

Similar problems occur with the other Commands.

TEST 2:

Create a DataSet (.xsd) for CategoriesHyphen, CategoriesHyphen

19. Select each row individually, do not select "*". This will generate:

SELECT CategoryID, [Category-Name], Description, Picture FROM dbo.CategoriesHyphen

20. When configuring the TableAdapter, the first step has has an Advanced Option Button in the Lower Left

- Click it and Check "Refresh the data table"

21. Make sure the AutoIncrement Values are correct

- Select Primary Key

Change following properties form -1

AutoIncrementSeed to 0
AutoIncrementStep to 1

Create a new .aspx page, CategoriesHyphenISDataSource.aspx.

22. In Design Mode, add an ISDataSource

23. Configure DataSource

24. Select Schema CategoriesHyphen

25. "Auto generate ISDataSource's table list from selected schema"

26. Add a WebGrid

27. Choose Data Source ISDataSource1

28. Allow Add New

29. Allow Editing

30. Allow Delete

31. Run the Page

32. Add a New Row with Values

- Category-Name = "Cat2"
- Description = "Desc2"

32. The following error is generated:

"ISDataSource 'ISDataSource1' Table [CategoriesHyphen] could not find a non-generic method 'Insert' that has parameters: p1, Description, Picture, Category-Name."

Similar problems occur with the other Commands.

Intersoft Community - WebGrid Enterprise - Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors

Column Names with Hyphens and Pound Signs cause ISDataSource Command Errors