asked on

Gridview - Enable Inserting

In ASP.NET I click checkbox that says "Generate INSERT, UPDATE, and DELETE statements."

In my gridview, I see 'Enable Editing, Enable Deleting, etc.
The only thing I don't see is 'Enable Insert'. Am I doing something wrong. How can I enable insert?

Thanks.

Espavo

You can only "Insert" records once you already have a record displaying in the GridView (and you need to add the "insert fields" into the footer template...
Here's an example of how I do it...
Espavo

<asp:GridView ID="ProcuctGridView" runat="server" AllowPaging="True" 
            AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" 
            DataKeyNames="pID" DataSourceID="ProdODS" ForeColor="#333333" 
            GridLines="None" ShowFooter="True" PageSize="50">
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <Columns>
                <asp:TemplateField HeaderText="Product Name" SortExpression="pName">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("pName") %>' 
                            Width="250px"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                            ControlToValidate="TextBox1" Display="None" 
                            ErrorMessage="Required Field" SetFocusOnError="True" 
                            ValidationGroup="gvEdit">*</asp:RequiredFieldValidator>
                        <cc1:ValidatorCalloutExtender ID="RequiredFieldValidator1_ValidatorCalloutExtender" 
                            runat="server" Enabled="True" TargetControlID="RequiredFieldValidator1">
                        </cc1:ValidatorCalloutExtender>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("pName") %>'></asp:Label>
                    </ItemTemplate>
                     <FooterTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Width="250px"></asp:TextBox>
                          <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                            ControlToValidate="TextBox1" Display="None" 
                            ErrorMessage="Required Field" SetFocusOnError="True" 
                            ValidationGroup="gvFooter">*</asp:RequiredFieldValidator>
                        <cc1:ValidatorCalloutExtender ID="RequiredFieldValidator1_ValidatorCalloutExtender" 
                            runat="server" Enabled="True" TargetControlID="RequiredFieldValidator1">
                        </cc1:ValidatorCalloutExtender>
                   </FooterTemplate>
               </asp:TemplateField>
                <asp:TemplateField HeaderText="Abr" SortExpression="pAbr">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("pAbr") %>' 
                            Width="150px"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 
                            ControlToValidate="TextBox2" Display="None" 
                            ErrorMessage="Required Field" SetFocusOnError="True" 
                            ValidationGroup="gvEdit">*</asp:RequiredFieldValidator>
                        <cc1:ValidatorCalloutExtender ID="RequiredFieldValidator2_ValidatorCalloutExtender" 
                            runat="server" Enabled="True" TargetControlID="RequiredFieldValidator2">
                        </cc1:ValidatorCalloutExtender>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("pAbr") %>'></asp:Label>
                    </ItemTemplate>
                     <FooterTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Width="150px"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 
                            ControlToValidate="TextBox2" Display="None" 
                            ErrorMessage="Required Field" SetFocusOnError="True" 
                            ValidationGroup="gvFooter">*</asp:RequiredFieldValidator>
                        <cc1:ValidatorCalloutExtender ID="RequiredFieldValidator2_ValidatorCalloutExtender" 
                            runat="server" Enabled="True" TargetControlID="RequiredFieldValidator2">
                        </cc1:ValidatorCalloutExtender>
                    </FooterTemplate>
               </asp:TemplateField>
                <asp:TemplateField ShowHeader="False">
                    <EditItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" ValidationGroup="gvEdit"
                            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>
                    </ItemTemplate>
                     <FooterTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" 
                            CommandName="Insert" Text="Insert" OnClick="Insert_Click" ValidationGroup="gvFooter"></asp:LinkButton>
                        &nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 
                            CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                    </FooterTemplate>
               </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="Black" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#999999" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        </asp:GridView>


    Protected Sub Insert_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim ProdAdp As New SQLDataSetTableAdapters.ProductsTableAdapter
        Dim pName As String = CType(ProcuctGridView.FooterRow.FindControl("TextBox1"), TextBox).Text
        Dim pAbr As String = CType(ProcuctGridView.FooterRow.FindControl("TextBox2"), TextBox).Text
        ProdAdp.InsertIfNotExists(pName, pAbr)
        ProcuctGridView.DataBind()
    End Sub

Open in new window

pschrama

Gridview doesn't support Inserting, you'll have to add that support yourself.

Here's one example to do this:
http://fredrik.nsquared2.com/viewpost.aspx?PostID=155

lazyberezovsky

See here good example:
http://www.c-sharpcorner.com/UploadFile/Art%20Scott/GridViewInsertANewRow09012006165053PM/GridViewInsertANewRow.aspx

Isaac

ASKER

I found something very similar to what I want and I followed the steps exactly but nothing happens when I click "insert". I don't even get errors.

http://www.dotnetbips.com/articles/c1e0ca90-5f5d-47aa-a739-492b562e810a.aspx

                                                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                                                    CellPadding="4" DataKeyNames="ID_KEY" DataSourceID="SqlDataSource1" 
                                                    ForeColor="#333333" GridLines="None" AllowSorting="True" 
                                                    AllowPaging="True">
                                                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                                                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                                                    <Columns>
                                                        <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" />
                                                        <asp:BoundField DataField="DUE_DATE" HeaderText="DUE_DATE" 
                                                            SortExpression="DUE_DATE" />
                                                        <asp:BoundField DataField="DESCRIPTION" HeaderText="DESCRIPTION" 
                                                            SortExpression="DESCRIPTION" />
                                                        <asp:BoundField DataField="ID_KEY" HeaderText="ID_KEY" ReadOnly="True" 
                                                            SortExpression="ID_KEY" Visible="False" />
                                                        <asp:ButtonField CommandName="Insert" Text="Insert" />
                                                    </Columns>
                                                    <EditRowStyle BackColor="#999999" />
                                                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                                                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                                                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                                                    <EmptyDataTemplate>
                                                        <asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource1" 
                                                            DefaultMode="Insert" Height="50px" Width="125px">
                                                        </asp:DetailsView>
                                                    </EmptyDataTemplate>
                                                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                                                </asp:GridView>
                                                    <br />
                                                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                                                        ConnectionString="<%$ ConnectionStrings:oracleConnectionString %>" 
                                                        ProviderName="<%$ ConnectionStrings:oracleConnectionString.ProviderName %>"    
                                                    SelectCommand="SELECT &quot;DUE_DATE&quot;, &quot;DESCRIPTION&quot;, &quot;ID_KEY&quot; FROM &quot;OGC_DATE_DUE_INFO_TBL&quot; WHERE (&quot;CASE_NUMBER&quot; = :CASE_NUMBER)" 
                                                    DeleteCommand="DELETE FROM &quot;OGC_DATE_DUE_INFO_TBL&quot; WHERE &quot;ID_KEY&quot; = :ID_KEY" 
                                                    InsertCommand="INSERT INTO &quot;OGC_DATE_DUE_INFO_TBL&quot; (&quot;DUE_DATE&quot;, &quot;DESCRIPTION&quot;, &quot;ID_KEY&quot;) VALUES (:DUE_DATE, :DESCRIPTION, :ID_KEY)"                                                     
                                                    UpdateCommand="UPDATE &quot;OGC_DATE_DUE_INFO_TBL&quot; SET &quot;DUE_DATE&quot; = :DUE_DATE, &quot;DESCRIPTION&quot; = :DESCRIPTION WHERE &quot;ID_KEY&quot; = :ID_KEY">
                                                        <SelectParameters>
                                                            <asp:ControlParameter ControlID="ed_caseNumber" Name="CASE_NUMBER" 
                                                                PropertyName="Text" Type="String" />
                                                        </SelectParameters>
                                                        <DeleteParameters>
                                                            <asp:Parameter Name="ID_KEY" Type="Decimal" />
                                                        </DeleteParameters>
                                                        <InsertParameters>
                                                            <asp:Parameter Name="DUE_DATE" Type="DateTime" />
                                                            <asp:Parameter Name="DESCRIPTION" Type="String" />
                                                            <asp:Parameter Name="ID_KEY" Type="Decimal" />
                                                        </InsertParameters>
                                                        <UpdateParameters>
                                                            <asp:Parameter Name="DUE_DATE" Type="DateTime" />
                                                            <asp:Parameter Name="DESCRIPTION" Type="String" />
                                                            <asp:Parameter Name="ID_KEY" Type="Decimal" />
                                                        </UpdateParameters>
                                                    </asp:SqlDataSource>

Open in new window

Espavo

Is this an exact paste of the example?
I see a lot of "& q u o t ; " (spaces between to demonstrate) that WILL crash the script...
Are you running the GridView in an UpdatePanel? (remove the Update Panel and you'll see the errors...)
Espavo

Isaac

ASKER

No. That's what my code created in VS as I followed the example.

Also, I'm not running in an updatePanel. I am in a tabcontainer though. Maybe that's the problem.

Espavo

Do you have any records displaying in your GridView?

Isaac

ASKER

Yes I do.

Espavo

Where did you get the example from? (My reason for asking is that a GridView doesn't support Insert... which is why I do the Footer work-around on it... so it's strange to see that someone has it working...)

Isaac

ASKER

I got it from here.
http://www.dotnetbips.com/articles/c1e0ca90-5f5d-47aa-a739-492b562e810a.aspx

Isaac

ASKER

I applied your idea to my code but when I run it, my button does not show up.

what am I doing wrong?

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                                                    CellPadding="4" DataKeyNames="ID_KEY" DataSourceID="SqlDataSource1" 
                                                    ForeColor="#333333" GridLines="None" AllowSorting="True">
                                                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                                                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                                                    <Columns>
                                                        <asp:CommandField ShowEditButton="True" ShowSelectButton="True" />
                                                        <asp:BoundField DataField="DUE_DATE" HeaderText="DUE_DATE" 
                                                            SortExpression="DUE_DATE" />
                                                        <asp:BoundField DataField="DESCRIPTION" HeaderText="DESCRIPTION" 
                                                            SortExpression="DESCRIPTION" />
                                                        <asp:BoundField DataField="ID_KEY" HeaderText="ID_KEY" ReadOnly="True" 
                                                            SortExpression="ID_KEY" Visible="False" />
                                                         <asp:TemplateField ShowHeader="False">
                                                            <FooterTemplate>
                                                                <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="false" CommandName="Insert" Text="Insert" OnClick="Insert_Click"></asp:LinkButton>
                                                            </FooterTemplate>
                                                         </asp:TemplateField>
                                                    </Columns>
                                                    <EditRowStyle BackColor="#999999" />
                                                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                                                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                                                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                                                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                                                </asp:GridView>

Open in new window

ASKER CERTIFIED SOLUTION

Espavo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Isaac

ASKER

I used what you have and I added what's below. I get no errors but it does'nt work either.

What am I doing wrong?
Thx.

    public void insert_click(object sender, EventArgs e)
    {
        TextBox dueDate = GridView1.FooterRow.FindControl("CustomerIDTextBox") as TextBox;        
        TextBox description = GridView1.FooterRow.FindControl("CompanyNameTextBox") as TextBox;

        SqlDataSource1.InsertParameters["DUE_DATE"].DefaultValue = dueDate.Text;
        SqlDataSource1.InsertParameters["DESCRIPTION"].DefaultValue = description.Text;

        SqlDataSource1.Insert();
    }

Open in new window

Espavo

Well, to start off with, I see that you have three input parameters... (but you are only passing two...)

        <InsertParameters>
                                                            <asp:Parameter Name="DUE_DATE" Type="DateTime" />
                                                            <asp:Parameter Name="DESCRIPTION" Type="String" />
                                                            <asp:Parameter Name="ID_KEY" Type="Decimal" />
                                                        </InsertParameters>

Open in new window

Isaac

ASKER

Well, ID_KEY is just my primary key. My backend is ORACLE so how I do I call the sequence that I have already created to increment ID_KEY?

Isaac

ASKER

Also, where would I paste your <inputParameter> code? Right after the grid, in the grid?

Isaac

ASKER

Now I get this error

System.NullReferenceException was unhandled by user code
Message="Object reference not set to an instance of an object."
Source="App_Web_qw2lacs-"
StackTrace:
at edit_case.Insert_Click(Object sender, EventArgs e) in d:\My Documents\Visual Studio 2008\WebSites\OGC\edit_case.aspx.cs:line 484
at System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e)
at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:

points to this line: SqlDataSource1.InsertParameters["DUE_DATE"].DefaultValue = dueDate.Text;

    public void Insert_Click(object sender, EventArgs e)
    {
        TextBox dueDate = GridView1.FooterRow.FindControl("txtDue_Date") as TextBox;        
        TextBox description = GridView1.FooterRow.FindControl("txtDescription") as TextBox;

        SqlDataSource1.InsertParameters["DUE_DATE"].DefaultValue = dueDate.Text;      
        SqlDataSource1.InsertParameters["DESCRIPTION"].DefaultValue = description.Text;

        SqlDataSource1.Insert();
    }

Open in new window

chrisgreaves

The gridview is not designed for inserting. The detailsview control is the one to use.
Chris