How does Gridview Update work?

[JimmyFo]

I'm having a problem. I have a stored procedure for selecting items like this:

SELECT
		intMilestoneID [ID],
		strMilestoneName [Name],
		strMilestoneDescription [Desc],
		intMilestoneOrder [MSOrder],

And so on. See how it gives aliases for the data.

I bind it to a gridview like so:

<asp:GridView ID="grid_milestone"
            DataSourceID="DSProject"
            AutoGenerateColumns="false"
            AutoGenerateEditButton="true" runat="server">
            <Columns>
                <asp:BoundField DataField="ID" />
                <asp:BoundField DataField="Name" />
            </Columns>
        </asp:GridView>code]

My datasource works great for selecting:

[code<asp:SqlDataSource ID="DSProject" ConnectionString="<%$ AppSettings:SQLConnection1 %>"
        SelectCommand="uspSELECT_MILESTONE"
        SelectCommandType="StoredProcedure"
        UpdateCommand="uspUPDATE_MILESTONE"
        UpdateCommandType="StoredProcedure"
        CancelSelectOnNullParameter="true"
        runat="server">
            <SelectParameters>
                <asp:QueryStringParameter QueryStringField="project" Name="p_PROJECTID" />
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Name="p_MILESTONEID" />
                <asp:Parameter Name="p_MILESTONENAME" />
            </UpdateParameters>
        </asp:SqlDataSource>

But not so well for updating. My update stored procedure is like this:

REATE PROCEDURE uspUPDATE_MILESTONE 
	-- Add the parameters for the stored procedure here
	@p_MILESTONEID int = null,
	@p_MILESTONENAME varchar(50) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE tblProjectMilestone
	SET
		strMilestoneName = @p_MILESTONENAME
	WHERE
		intMilestoneID = @p_MILESTONEID
END

Where am I going wrong? I get a "too many arguments specified" error, and I can look at the profiler and see that I'm passing in Name, ID, p_MILESTONENAME, p_MILESTONEID. Can't I rename the parameters in the datasource? Is there another way? The stored procedures should look like that with those parameter names, so I don't know what else I can do.

Thanks!
James

 

[jmeckley]

I don't think you need to set the update parameters within the SQLDataSource since it's bound to a GridView. I would recommend configuring your GridView like this: (instead of displaying the primary key)

<asp:GridView ID="grid_milestone"
              DataSourceID="DSProject" 
              AutoGenerateColumns="false"
              AutoGenerateEditButton="true"
              runat="server"
              DataKeyNames="ID">
   <Columns>
      <asp:BoundField DataField="Name" />
   </Columns>
</asp:GridView>
<asp:SqlDataSource ID="DSProject"
                   ConnectionString="<%$ AppSettings:SQLConnection1 %>"
                   SelectCommand="uspSELECT_MILESTONE" SelectCommandType="StoredProcedure"
                   UpdateCommand="uspUPDATE_MILESTONE"
                   UpdateCommandType="StoredProcedure"
                   CancelSelectOnNullParameter="true"
                   runat="server">
   <SelectParameters>
      <asp:QueryStringParameter QueryStringField="project" Name="p_PROJECTID" />
   </SelectParameters>
</asp:SqlDataSource>

you shouldn't need any code behind for this setup.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JimmyFo]

Thanks for the input - when I remove the specification, I get this:

@ID is not a parameter for procedure uspUPDATE_MILESTONE

I believe the parameter is passed directly as the datafield from the gridview - hence, it is named ID so it passes @ID as a parameter. Changing the stored procedures is a big deal as it break conformity for the whole application so far - is there a way to pass the same value as a different name, i.e. make @ID become @p_PORTFOLIOID? Thanks!

 

[jmeckley]

I would suggest creating an event handler for the OnUpdating event for the SqlDataSource.
rename the existing paremeters which match the stored procs arguments this assumes parameters are in the correct order.

it would look something along the lines of

private void OnSqlUpdating(Object source, SqlDataSourceCommandEventArgs e) 
{
   e.Command.Parameters[0].Name = "p_MILESTONEID";
   e.Command.Parameters[1].Name = "p_MILESTONENAME";
}

Not sure, but maybe you could reference the Parameter by name instead of index.

private void OnSqlUpdating(Object source, SqlDataSourceCommandEventArgs e) 
{
   e.Command.Parameters["ID"].Name = "p_MILESTONEID";
   e.Command.Parameters["Name"].Name = "p_MILESTONENAME";
}

Jason Meckley
Programmer
Specialty Bakers, Inc.