[RESOLVED] Gridview update from stored procedure

[Samir Nagheenanajar]

How do you programatically update a row in a gridview using a stored procedure.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
For Each row As GridViewRow In Me.assignmentGridView.Rows
            Me.assignmentDataSource.Update()
            'where the update uses the parameters are defined below in .aspx file
        Next
EndSub

the above code does not do anything, but its the idea of what Im trying to do.

<asp:SqlDataSource ID="assignmentDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FCRscheduleConnectionString %>"
            SelectCommand="dayAssignment" SelectCommandType="StoredProcedure" UpdateCommand="dayAssignmentUpdate" UpdateCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="Calendar1" Name="date" PropertyName="SelectedDate"
                    Type="DateTime" />
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
                <asp:ControlParameter ControlID="Calendar1" Name="date" PropertyName="SelectedDate"
                    Type="DateTime" />
                <asp:ControlParameter ControlID="assignmentGridView" Name="fcrID" PropertyName="SelectedValue"
                    Type="Int32" />
                <asp:ControlParameter ControlID="assignmentGridView" Name="Site" PropertyName="SelectedValue"
                    Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>

[mendhak]

Step through your code - in the button1.click method, do a quickwatch on assignmentDataSource.UpdateParameters. Are these parameters available to you? Do they have values?

[dnanetwork]

http://aspspider.info/dnanetwork/Gri...e_Advance.aspx

try this link..might help..!

[Samir Nagheenanajar]

I think i might be on the right track. There are the correct number of assignmentDataSource.UpdateParameters, but im not sure how to view thier values. I tried just calling Me.assignmentGridView.UpdateRow(i, False) where i indexes the rows in the gridview. But this gives me an error

"Procedure or function dayAssignmentUpdate has too many arguments specified. "

assignmentDataSource.UpdateParameters.count is 3, and as you can see below dayAssignmentUpdate stored procedure has three parameters.

ALTER PROCEDURE [dbo].[dayAssignmentUpdate] 
	-- Add the parameters for the stored procedure here
	@fcrID as int, 
	@Site as int,
	@date as smalldatetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
	DELETE FROM [assignment] WHERE (assignment.FCR = @fcrID) AND (assignment.date = @date) AND NOT (@date = null)
END
IF NOT(@Site=13)
BEGIN
		INSERT INTO [assignment] (assignment.FCR, assignment.site, assignment.date) VALUES (@fcrID, @Site, @date) 
END

[mendhak]

OK so do those parameters have values against them?

[Samir Nagheenanajar]

Im not sure how to see thier values. Ive been trying to get into them in the watch and immediate window but like I said, Im not sure where to look.

[rjv_rnjn]

I do not see any return value from the SP. And you have

<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />

I'm not sure what do you need it for but try commenting that line out and see if everything works fine then that would be the offending code.

[Samir Nagheenanajar]

I do not see any return value from the SP. And you have

<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />

I'm not sure what do you need it for but try commenting that line out and see if everything works fine then that would be the offending code.

Yeah thats fixed. Sorry I should have remembered to update this when I updated my code. The parameter count for this data source is correct at 3, so Im really confused how to even troubleshoot this.

[Samir Nagheenanajar]

So as far as the problem with the number of arguments, I found this which solves that problem. I have 5 databound items in my gridview but was only passing 3 parameters to the update stored procedure. The first solution down solved this problem.
http://www.dotnetgoodies.com/article...specified.aspx

However I do not think the parameters are getting passed correctly. I am just trying to loop through each row in my gridview and update.

Dim i As Integer
        i = 1
        For Each row As GridViewRow In Me.assignmentGridView.Rows 
            Me.assignmentGridView.UpdateRow(i, False)
            i = i + 1
        Next

this now gives me an error:Cannot insert the value NULL into column 'FCR', table 'FCRschedule.dbo.assignment'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The parameters I am trying to pass are definitly not null, so Im assuming they are not being passed properly.

[mendhak]

OK, so it appears that FcrID is null, for whatever reason, which in turn indicates that the GridView's SelectedValue property is null. Is there a reason you're using GridView's SelectedValue and then updating each row?

[Samir Nagheenanajar]

So Ive had some progress. My page is working perfectly if the fcrID column is not hidden, when I hide it I get this error. Theres got to be a way to have this properly databind when the column is hidden. Any thoughts.

[rjv_rnjn]

When a column is hidden at server side, the HTML received by browser doesn't contain any definition for that column. So after a postback ASP.Net will always get a null value for row[column]. You will have to find a way to set a default value for fcrID in that case.
Maybe you can try setting style="visibility:hidden" for the column instead of doing a visible=false.

[Samir Nagheenanajar]

that gives me a parser error.

[rjv_rnjn]

How did you add the style property to the gridview column? I tried it like below and works as expected and I can see the column name and values in page source.

<asp:BoundField DataField="TestName" ItemStyle-Width="10%" ItemStyle-CssClass="test"  />

And the Css file has the class defined as

.test
{
	visibility:hidden;
}

[Samir Nagheenanajar]

Im just going to work around this by putting the invisible fcrID textbox in a visible column, which works for me. Thanks for all the help on this, you have no idea how much ive learned. I really wish I would have figured this stuff out a year or two ago, my old boss would have been much happier!

[mendhak]

Pfft, old people are never impressed