Intersoft Community - WebGrid Enterprise - Very strange datasource behaviour

Very strange datasource behaviour

yudi — Thu, 13 Jan 2011 01:58:54 GMT

I have added and forwarded the correction into Task #885.
I’ll let you know when the enhancement is implemented.

Thank you.

Very strange datasource behaviour

staffan.eriksson@semita.se — Wed, 12 Jan 2011 03:42:22 GMT

Just a correction: all of the columns displayed in the WebGrid don't have to be in the update query of the stored procedure. I have many examples when they are not. BUT, all the update parameters also have to be in the query of the select stored procedure which of course makes it almost impossible to create a slimmed and optimized application and database.

Very strange datasource behaviour

yudi — Wed, 12 Jan 2011 01:31:04 GMT

Thank you very much for the update, so that I can continue the investigation and understand the issue better.

In the current build of WebGrid, all of the column displayed in WebGrid must be included in the update query of the stored procedure. Unlike the ASP.NET GridView control that the column can be set to be read-only in order to avoid “too many parameter” being sent automatically to the stored procedure.

I have forwarded this to WebGrid development team for enhancement. This enhancement is filed under Task #885. I’ll keep you updated with any news I heard from the team regarding Task #885.

Thank you and have a nice day.

Very strange datasource behaviour

staffan.eriksson@semita.se — Tue, 11 Jan 2011 05:21:33 GMT

My scenario is:

I have a select sp which shows many fields. I only allow update in the amount column and I'm using these update parameters:

<UpdateParameters>
<asp:SessionParameter Name="EstateID" SessionField="DropDownList5" DefaultValue="%" />
<asp:Parameter Name="AccountGroup" />
<asp:Parameter Name="Amount" />
<asp:SessionParameter Name="UserID" SessionField="UserID" />
</UpdateParameters>

With WebGrid 6.0 both EstateID and UserID have to be in the select sp otherwise I receive an error message like "The object reference hasn't been set to an instance of an object" (translated from Swedish to English). EditType = NoEdit for all columns except Amount.

With ASP.NET GridView EstateID and UserID doesn't have to be in the select sp and if a column is set to ReadOnly the value is not sent to the datasource when updating.

By the way, it looks like the error message from WebGrid is fired before the OnUpdating server-side event because when I put code in that event it's not run.

Very strange datasource behaviour

yudi — Tue, 11 Jan 2011 02:01:17 GMT

I carefully create a test page based on your information. I’m using Northwind.mdf database file (specifically “Suppliers” table) that available in WebGridSamples project and added two stored procedures.

“spTestSelect” stored procedure is the stored procedure that handles the select action. This select stored procedure has ten fields (“Supplier”, “CompanyName”, “ContactName”, “ContactTitle”, “Address”, “Region”, “PostalCode”, “Phone”, “Fax”, and “HomePage”). Six out of these fields are going to be displayed in WebGrid. Those fields are “SupplierID”, “CompanyName”, “ContactName”, “ContactTitle”, “Address”, and “Region”.

ALTER PROCEDURE [dbo].[spTestSelect]	
AS
BEGIN
	Select
		SupplierID, CompanyName, ContactName,
		ContactTitle, Address, Region, PostalCode,
		Phone, Fax, HomePage
	FROM
		Suppliers
END

“spTestUpdate” stored procedure is the stored procedure that handles the update action. On update, only one field, “CompanyName” field, is going to be saved. Besides “CompanyName” field, there will be another two parameters that will be added during update action. They are “City” field and “Country” field.

ALTER PROCEDURE [dbo].[spTestUpdate]
	@SupplierID int,
	@CompanyName nvarchar(30),
	@City nvarchar(30),
	@Country nvarchar(30)
AS
BEGIN
	UPDATE Suppliers 
	SET
		CompanyName = @CompanyName,
		City = @City,
		Country = @Country
	WHERE 
		SupplierID = @SupplierID
END

Since you said that the issue happens in SQLDataSource control which connected to WebGrid, then the first thing that I’d like to check is: which one of the controls, is it SQLDataSource or WebGrid, which may have caused the issue. So, the test began by using following approach:

SQLDataSource is connected to ASP.NET GridView. If the issue is reproducible using this scenario, then the problem may have caused by SQLDataSource control instead of WebGrid.

If the issue is not reproducible using the upper scenario, then proceed the test by connecting SQLDataSource control to WebGrid and we can assume that WebGrid is the control that may have caused the issue.

When using SQLDataSource to handle updates to an underlying database from ASP.NET GridView, it run across following message: Procedure or function spTestUpdate has too many arguments specified.

This usually means what it says, that the update command is trying to pass more parameters than the stored procedure is set up to handle. Since I getting this error, I decided to view the parameters being passed as they happen. A breakpoint is added in the SQLDataSource OnUpdating server-side event.

When the update event rose, I found out that there are eight parameters: @CompanyName, @City, @Country, @ContactName, @ContactTitle, @Address, @Region, @SupplierID.

Up until here, I think I should have reproduced the issue when updating depending on what is in the select stored procedure and when using parameters like session or control parameters. Please let us know if you have different scenario.

In my opinion, each datakeyname specified in the ASP.NET GridView control is automatically being sent to the stored procedure. So if we try to also define extra parameters, we will be passing too many.

My test comes to an end that the problem is caused by the SQLDataSource control instead of WebGrid.

Please kindly share with us if you don’t agree or have different result.

Very strange datasource behaviour

staffan.eriksson@semita.se — Mon, 10 Jan 2011 15:05:36 GMT

I have one stored procedure for select and another for update in a SqlDataSource connected to my WebGrid 6.0.

I have 10 fields in my select sp and I show 6 of them in my WebGrid. When saving I only want to save 1 of them and add 2 session parameters. This is not possible if those fields are not in the select sp and they have nothing to do with the select sp!

Can someone please test and change the behaviour of WebGrid 6.0 with stored procedures in a SqlDataSource? There are numerous of strange things going on there when updating depending on what is in the select sp and when using parameters like session or control parameters.