Solved

Update on GridView TemplateField results in Null values for SQLDataSource Parameters

Posted on 2009-03-29
11
1,270 Views
Last Modified: 2012-05-06
Hello,

I am trying to customize a gridview's layout by converting the default boundfields to templatefields, and then altering the default converted templatefields' layout.

The problem is, that when I customize the converted gridview templatefields and try to use the update command, the command fails with the following error: "Cannot insert the value NULL into column 'VideoTitle', table 'dbo.UserVideos'; column does not allow nulls. UPDATE fails. The statement has been terminated."

The delete command, however, works fine in my modified Gridview.

In the code below, GridView1 works fine for both Update and Delete Commands.  GridView2 is the one that will allow a delete, but fails when updating with the error described above.

Thanks a bunch if you have any ideas!

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"  DataKeyNames="UserVideoId"

                AllowSorting="True" AutoGenerateColumns="False" 

                DataSourceID="SdsUserVideos" GridLines="Horizontal" BorderWidth="0px" Width="100%">

                <Columns>

                    <asp:TemplateField HeaderText="Action" ShowHeader="False">

                        <EditItemTemplate>

                            <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" 

                                CommandName="Update" Text="Update"></asp:LinkButton>

                            &nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 

                                CommandName="Cancel" Text="Cancel"></asp:LinkButton>

                        </EditItemTemplate>

                        <ItemTemplate>

                            <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" 

                                CommandName="Edit" Text="Edit"></asp:LinkButton>

                            &nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 

                                CommandName="Delete" Text="Delete"></asp:LinkButton>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Title" SortExpression="VideoTitle">

                        <EditItemTemplate>

                            <asp:TextBox ID="TextasdfBox1" runat="server" Text='<%# Bind("VideoTitle") %>'></asp:TextBox>

                        </EditItemTemplate>

                        <ItemTemplate>

                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("VideoTitle") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Video Url" SortExpression="VideoUrl">

                        <EditItemTemplate>

                            <asp:TextBox ID="TeasdfxtBox3" runat="server" Text='<%# Bind("VideoUrl") %>'></asp:TextBox>

                        </EditItemTemplate>

                        <ItemTemplate>

                            <asp:Label ID="Label3" runat="server" Text='<%# Bind("VideoUrl") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Description" SortExpression="VideoDescription">

                        <EditItemTemplate>

                            <asp:TextBox ID="TextasdfasdfBox2" runat="server" 

                                Text='<%# Bind("VideoDescription") %>'></asp:TextBox>

                        </EditItemTemplate>

                        <ItemTemplate>

                            <asp:Label ID="Label2" runat="server" Text='<%# Bind("VideoDescription") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                </Columns>

            </asp:GridView>

<br /><br />

<br /><br />

<br /><br />

<asp:GridView ID="GridView2" runat="server" AllowPaging="True"                         DataKeyNames="UserVideoId" 

                        AllowSorting="True" AutoGenerateColumns="False" PageSize="4"

                        DataSourceID="SdsUserVideos" GridLines="Horizontal" BorderWidth="0px"

                         Width="100%">

                        <Columns>

                            <asp:TemplateField>

                                <HeaderTemplate>

                                    <asp:Table ID="TblUserVideosHeader" Width="300px" BorderWidth="0" CellPadding="2" CellSpacing="0" runat="server">

                                        <asp:TableRow>

                                            <asp:TableCell Width="30px">&nbsp;</asp:TableCell>

                                            <asp:TableCell Width="130px"><asp:LinkButton ID="LbtSortVideoTitle" CommandName="Sort" CommandArgument="VideoTitle" Text="Title" runat="server"></asp:LinkButton></asp:TableCell>

                                            <asp:TableCell Width="140px"><asp:LinkButton ID="LbtSortVideoUrl" CommandName="Sort" CommandArgument="VideoUrl" Text="URL" runat="server"></asp:LinkButton></asp:TableCell>

                                        </asp:TableRow>                                        

                                    </asp:Table>

                                </HeaderTemplate>

                                <ItemTemplate>

                                    <asp:Table ID="TblUserVideosItems" Width="300px" BorderWidth="0" CellPadding="2" CellSpacing="0" runat="server">

                                        <asp:TableRow>

                                            <asp:TableCell Width="30px" RowSpan="2">

                                                <asp:LinkButton ID="LbtEditItem" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>

                                                <br />

                                                <asp:LinkButton ID="LbtDeleteItem" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton>                                            

                                            </asp:TableCell>

                                            <asp:TableCell Width="130px">

                                                <asp:Label ID="LblVideoTitleItem" Text='<%# Bind("VideoTitle") %>' runat="server"></asp:Label>

                                            </asp:TableCell>

                                            <asp:TableCell Width="140px">

                                                <asp:Label ID="LblVideoUrlItem" Text='<%# Bind("VideoUrl") %>' runat="server"></asp:Label>                                            

                                            </asp:TableCell>                                            

                                        </asp:TableRow>

                                        <asp:TableRow>

                                            <asp:TableCell Width="270px" ColumnSpan="2">

                                                <asp:Label ID="LblVideoDescriptionItem" Text='<%# Bind("VideoDescription") %>' runat="server"></asp:Label>

                                            </asp:TableCell>

                                        </asp:TableRow>

                                    </asp:Table>

                                </ItemTemplate>

                                <EditItemTemplate>

                                    <asp:Table ID="TblUserVideosEdit" Width="300px" BorderWidth="0" CellPadding="2" CellSpacing="0" runat="server">

                                        <asp:TableRow>

                                            <asp:TableCell Width="30px" RowSpan="2">

                                                <asp:LinkButton ID="LbtUpdateItem" runat="server" CausesValidation="False" CommandName="Update" Text="Update"></asp:LinkButton>

                                                <br />

                                                <asp:LinkButton ID="LbtCancelItem" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>                                            

                                            </asp:TableCell>

                                            <asp:TableCell Width="130px">

                                                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("VideoTitle") %>'></asp:TextBox>                                               

                                            </asp:TableCell>

                                            <asp:TableCell Width="140px">

                                                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("VideoUrl") %>'></asp:TextBox>                             

                                            </asp:TableCell>                                            

                                        </asp:TableRow>

                                        <asp:TableRow>

                                            <asp:TableCell Width="270px" ColumnSpan="2">

                                                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("VideoDescription") %>'></asp:TextBox>

                                            </asp:TableCell>

                                        </asp:TableRow>

                                    </asp:Table>

                                </EditItemTemplate>

                            </asp:TemplateField>

                        </Columns>

                    </asp:GridView>

            

            <asp:SqlDataSource ID="SdsUserVideos" runat="server" 

                ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>" 

                DeleteCommand="DeleteUserVideo" DeleteCommandType="StoredProcedure" 

                SelectCommand="GetUserVideosByUserId" SelectCommandType="StoredProcedure" 

                UpdateCommand="UpdateUserVideo" UpdateCommandType="StoredProcedure">

                <SelectParameters>

                    <asp:SessionParameter DefaultValue="-1" Name="userId" SessionField="UserId" Type="Int32" />

                </SelectParameters>

                <DeleteParameters>

                    <asp:Parameter Name="userVideoId" Type="Int32" />

                </DeleteParameters>

                <UpdateParameters>

                    <asp:Parameter Name="userVideoId" Type="Int32" />

                    <asp:Parameter Name="videoTitle" Type="String" />

                    <asp:Parameter Name="videoUrl" Type="String" />

                    <asp:Parameter Name="videoDescription" Type="String" />

                </UpdateParameters>

            </asp:SqlDataSource>

Open in new window

0
Comment
Question by:suchandsuch
  • 6
  • 5
11 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24017431
"Oh, joy, more code to look through (d'oh)"  *BIG GRIN*

What fields are you talking about?  What parameters are you working with?
0
 
LVL 1

Author Comment

by:suchandsuch
ID: 24017605

Well, I included the code so that there was something concrete to refer to - that's the idea right?  If I've misused the code-snippet feature, please let me know and I'll correct my postings - maybe there is a misunderstanding there.

I'm not sure what you mean by what fields am I talking about, and what parameters I am working with - but here's my best stab at those questions:

When I create a generic gridview and link it up to the sqldatasource that is at the bottom of the above code snippet, it, by default, uses BoundFields for the three db table columns listed in the code above (VideoTitle, VideoUrl, VideoDescription).  When I click the smartmenu for the gridview, and click on each of these columns/fields and then click on "convert this field to a templatefield" I get the code that you see in GridView1 in the code snippet above. Working with GridView1, everything works as it should, updates update, deletes delete etc.  

GridView2 is just a reorganized version of GridView1; GridView2's arrangement is different - it only uses one templatefield with multiple sections (HeaderTemplate, ItemTemplate, EditItemTemplate) for all three of the db columns/fields being used in the gridview.  GridView2, however, unlike GridView1, gives me this error when I click update (from clicking edit, changing data, then clicking update): "Cannot insert the value NULL into column 'VideoTitle', table 'dbo.UserVideos'; column does not allow nulls. UPDATE fails. The statement has been terminated."

So something in my reorganization of GridView1 is causing GridView2 to fail - this is the problem that I don't understand and need help with.

The sproc parameters are all varchars, and their db definitions do not allow nulls - which is why I am getting the error quoted above - but that isn't the problem - the problem is that GridView2 for some reason is passing null values for the parameters, when it should be passing data from the textboxes that the Bind('somefield') method appears in.

Please let me know if this doesn't address your questions, and I'll take another stab at them - thank you.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24018631
Other people may find code to be useful, but I just find it to be noise that I need to filter.  I prefer a more surgical approach, where I can determine the need, and then just get an important part to highlight the issue.  

With that said, you could show me just the HTML for the SqlDataSource, so I can see how the parameters are defined for those templated fields.
0
 
LVL 1

Author Comment

by:suchandsuch
ID: 24018769
Sure thing, thanks for giving it some thought.

I believe that the code below is what you're looking for - please let me know if it is not.

The thing is though, that I'm afraid that I'm mis-using the TemplateField, or something like that - so the above code may be helpful - I'm putting all three db data columns into one TemplateField, so that I can render the GridView visually the way that I want.  Maybe that's the problem?  I'm not sure - I only run into the problem when trying to edit then update a row.

Thanks again
            <asp:SqlDataSource ID="SdsUserVideos" runat="server" 

                ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>" 

                DeleteCommand="DeleteUserVideo" DeleteCommandType="StoredProcedure" 

                SelectCommand="GetUserVideosByUserId" SelectCommandType="StoredProcedure" 

                UpdateCommand="UpdateUserVideo" UpdateCommandType="StoredProcedure">

                <SelectParameters>

                    <asp:SessionParameter DefaultValue="-1" Name="userId" SessionField="UserId" Type="Int32" />

                </SelectParameters>

                <DeleteParameters>

                    <asp:Parameter Name="userVideoId" Type="Int32" />

                </DeleteParameters>

                <UpdateParameters>

                    <asp:Parameter Name="userVideoId" Type="Int32" />

                    <asp:Parameter Name="videoTitle" Type="String" />

                    <asp:Parameter Name="videoUrl" Type="String" />

                    <asp:Parameter Name="videoDescription" Type="String" />

                </UpdateParameters>

            </asp:SqlDataSource>

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24024543
Are all the values null?

Is this one of the TemplateFields?

                     <asp:TemplateField HeaderText="Title" SortExpression="VideoTitle">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextasdfBox1" runat="server" Text='<%# Bind("VideoTitle") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("VideoTitle") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>

I would have thought that the problem is that the binding method that you are using is a one-way binding, but I doubt that is the problem...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:suchandsuch
ID: 24028543
Yes, but only when using GridView2 in the example given in the first post - in GridView1 in the first post, all of the values (not null) are provided.

The code in your post above is one of the templatefields, but from GridView1, the gridview that works - the gridiview that doesn't work, GridView2, has only one templatefield, with all three of the columns inside it.

The code below is excerpted from the GridView2 in the first post.

Thanks for continuing to think about this.  My guess is that I only have one templatefield, with all of the columns in it, for GridView2, whereas GridView1 has a separate templatefield for each column in the gridview. Just a guess, but that's the only difference between the two gridviews that I can think would affect anything.  My question in that case would still be, why?  Are Templatefields and db columns intended to be one-to-one?



<asp:TemplateField>

                                <HeaderTemplate>

                                    <asp:Table ID="TblUserVideosHeader" Width="300px" BorderWidth="0" CellPadding="2" CellSpacing="0" runat="server">

                                        <asp:TableRow>

                                            <asp:TableCell Width="30px">&nbsp;</asp:TableCell>

                                            <asp:TableCell Width="130px"><asp:LinkButton ID="LbtSortVideoTitle" CommandName="Sort" CommandArgument="VideoTitle" Text="Title" runat="server"></asp:LinkButton></asp:TableCell>

                                            <asp:TableCell Width="140px"><asp:LinkButton ID="LbtSortVideoUrl" CommandName="Sort" CommandArgument="VideoUrl" Text="URL" runat="server"></asp:LinkButton></asp:TableCell>

                                        </asp:TableRow>                                        

                                    </asp:Table>

                                </HeaderTemplate>

                                <ItemTemplate>

                                    <asp:Table ID="TblUserVideosItems" Width="300px" BorderWidth="0" CellPadding="2" CellSpacing="0" runat="server">

                                        <asp:TableRow>

                                            <asp:TableCell Width="30px" RowSpan="2">

                                                <asp:LinkButton ID="LbtEditItem" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>

                                                <br />

                                                <asp:LinkButton ID="LbtDeleteItem" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton>                                            

                                            </asp:TableCell>

                                            <asp:TableCell Width="130px">

                                                <asp:Label ID="LblVideoTitleItem" Text='<%# Bind("VideoTitle") %>' runat="server"></asp:Label>

                                            </asp:TableCell>

                                            <asp:TableCell Width="140px">

                                                <asp:Label ID="LblVideoUrlItem" Text='<%# Bind("VideoUrl") %>' runat="server"></asp:Label>                                            

                                            </asp:TableCell>                                            

                                        </asp:TableRow>

                                        <asp:TableRow>

                                            <asp:TableCell Width="270px" ColumnSpan="2">

                                                <asp:Label ID="LblVideoDescriptionItem" Text='<%# Bind("VideoDescription") %>' runat="server"></asp:Label>

                                            </asp:TableCell>

                                        </asp:TableRow>

                                    </asp:Table>

                                </ItemTemplate>

                                <EditItemTemplate>

                                    <asp:Table ID="TblUserVideosEdit" Width="300px" BorderWidth="0" CellPadding="2" CellSpacing="0" runat="server">

                                        <asp:TableRow>

                                            <asp:TableCell Width="30px" RowSpan="2">

                                                <asp:LinkButton ID="LbtUpdateItem" runat="server" CausesValidation="False" CommandName="Update" Text="Update"></asp:LinkButton>

                                                <br />

                                                <asp:LinkButton ID="LbtCancelItem" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>                                            

                                            </asp:TableCell>

                                            <asp:TableCell Width="130px">

                                                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("VideoTitle") %>'></asp:TextBox>                                               

                                            </asp:TableCell>

                                            <asp:TableCell Width="140px">

                                                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("VideoUrl") %>'></asp:TextBox>                             

                                            </asp:TableCell>                                            

                                        </asp:TableRow>

                                        <asp:TableRow>

                                            <asp:TableCell Width="270px" ColumnSpan="2">

                                                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("VideoDescription") %>'></asp:TextBox>

                                            </asp:TableCell>

                                        </asp:TableRow>

                                    </asp:Table>

                                </EditItemTemplate>

                            </asp:TemplateField>

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24030960
Hmmm...maybe this is a problem because you are re-binding the GridView when the page posts back...
0
 
LVL 1

Author Comment

by:suchandsuch
ID: 24031069
Well, the update button is likely causing a rebinding on the post-back, but I'm not doing any re-binding programmatically.

Here's just a general question if I may: can 2 or more db column/fields be databound ( bind('colName') ) within a single TemplateField?  Will the update command know which fields should be bound to which sqldatasource parameters if there is only one TemplateField?

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24034843
I don't think that the problem is having multiple bindings in one TemplateField.  I believe that the problem is in matching up the values to the SqlDataSource parameters.

We can check the values:

    Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
        Dim view As DataView = CType(Me.SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
    End Sub
0
 
LVL 1

Author Comment

by:suchandsuch
ID: 24086394
Ok thanks, I will try out checking the values as you suggest above and report back.
0
 
LVL 1

Accepted Solution

by:
suchandsuch earned 0 total points
ID: 24519917
Moderators, I can not find a solution to this issue - should it just be deleted?  I've moved on and have approached the problem differently.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calling stored proc in EDMX 11 23
Run time Error 4 34
IIS issues - Convert to Application - APS.NET v4.0 3 23
ASP.net show message while code is running 3 8
Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now