Intersoft Community - WebGrid Enterprise - Simple Example using asp:SqlDataSource or Select, Update using sprocs

Simple Example using asp:SqlDataSource or Select, Update using sprocs

yudi — Fri, 30 Sep 2011 03:31:33 GMT

I modify the spTestSelect stored procedure from following:

ALTER PROCEDURE [dbo].[spTestSelect]	
AS
BEGIN
	Select SupplierID, CompanyName, City  FROM Suppliers
END

into:

ALTER PROCEDURE [dbo].[spTestSelect]
	@City nvarchar(30)
AS
BEGIN
	Select SupplierID, CompanyName, City  FROM Suppliers
	WHERE City=@City
END

The new select stored procedure now has a parameter named City.

Next, the SqlDataSource1 control is configured into the following:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:SqlNorthwindConnectionString %>"
    SelectCommand="spTestSelect" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>

Please note that the select parameter is not declaratively defined inside the SQLDataSource control because the parameter will be added from code behind as shown in the following code snippet.

protected void Page_Load(object sender, EventArgs e)
{
    SqlDataSource1.SelectParameters.Add("City", System.Data.DbType.String, "Frankfurt");
}

Sorry for not directly provide you sample with the delete stored procedure, since I have no information about how you implement the hidden bound columns.

I believe that if the similar scenario for the select command works, the same approach will works for the delete operation.

Hope this helps.

*Note: you may modify the sample and send back to us for further investigation.

Simple Example using asp:SqlDataSource or Select, Update using sprocs

davidpendry — Thu, 29 Sep 2011 11:27:50 GMT

Thanks Yudi,

thankyou for the help..

I require some logic to be evaluated before a delete occurs, which requires some of the bound columns (hidden) to be passed into the delete sproc too. This is where the challenge arises as i currently cannot get the hidden columns to be passed in, even though they are in the delete element list (see below)

Could you please provide an example of this also.

David

<RootTable DataKeyField="MyId">
<Columns>
<ISWebGrid:WebGridColumn Caption="HiddenId" DataMember="HiddenId" DataType="System.Guid" Name="HiddenId" Width="100px" Visible="false" EditType="NoEdit" >
.....
......

<DeleteParameters>
<asp:Parameter Name="HiddenId" Type="String" />
</DeleteParameters>

Simple Example using asp:SqlDataSource or Select, Update using sprocs

yudi — Wed, 28 Sep 2011 09:19:20 GMT

I created a simple sample page of WebGrid where AllowAddNew; AllowDelete; and AllowEdit are enabled. The grid is bind to spTestSelect (stored procedure for select command); spTestInsert (stored procedure for insert command); spTestUpdate (stored procedure for update command); and spTestDelete (stored procedure for delete command) through an ASP.NET SQLDataSource control.

The data file, northwind.mdf, is available within the WebGridSamples project.

Please have the attached sample tested on your end and let us know whether it helps or not.

Simple Example using asp:SqlDataSource or Select, Update using sprocs

davidpendry — Tue, 27 Sep 2011 10:00:45 GMT

Hi,

I cant find anywhere a simple example of using your grid with a asp:SqlDataSource and updating with a stored procedure. I have been struggling to get this to work properly..

Thanks