Home ›
ASP.NET Forums » General ASP.NET » Data Presentation Controls » Gridview - Multiple-step OLE DB operation generated errors. Check each OLE DB status value

Gridview - Multiple-step OLE DB operation generated errors. Check each OLE DB status value

Last post 06-27-2009 6:03 AM by hans_v. 4 replies.

Sort Posts:

  • Gridview - Multiple-step OLE DB operation generated errors. Check each OLE DB status value

    06-26-2009, 3:57 AM
    • Member
      10 point Member
    • shahid.majeed
    • Member since 10-07-2008, 8:24 AM
    • Lahore
    • Posts 69

    Hi:

        i have a grid view control at my page nothing else. i configure the datasource of the gridview and enable delete,edit command. when i run the page its shows the data in the gridview perfectly. but after editing the grid its did not save the data back in the database. i am searching on the net about the but i did not geting anything which solve my problem


    Eroor Message

    Multiple-step OLE DB operation generated errors. Check each OLE DB status value


    Aspx Page Code:

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                DataKeyNames="Sr#" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                    <asp:BoundField DataField="Sr#" HeaderText="Sr#" InsertVisible="False" 
                        ReadOnly="True" SortExpression="Sr#" />
                    <asp:BoundField DataField="Category" HeaderText="Category" 
                        SortExpression="Category" />
                    <asp:BoundField DataField="CDate" HeaderText="CDate" SortExpression="CDate" />
                    <asp:BoundField DataField="CDescription" HeaderText="CDescription" 
                        SortExpression="CDescription" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:ParklandsConnectionString %>" 
                DeleteCommand="DELETE FROM [Competitions] WHERE [Sr#] = @column1" 
                InsertCommand="INSERT INTO [Competitions] ([Category], [CDate], [CDescription]) VALUES (@Category, @CDate, @CDescription)" 
                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
                SelectCommand="SELECT * FROM [Competitions]" 
                UpdateCommand="UPDATE [Competitions] SET [Category] = @Category, [CDate] = @CDate, [CDescription] = @CDescription WHERE [Sr#] = @column1">
                <DeleteParameters>
                    <asp:Parameter Name="column1" Type="Int64" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:Parameter Name="Category" Type="String" />
                    <asp:Parameter Name="CDate" Type="String" />
                    <asp:Parameter Name="CDescription" Type="String" />
                    <asp:Parameter Name="column1" Type="Int64" />
                </UpdateParameters>
                <InsertParameters>
                    <asp:Parameter Name="Category" Type="String" />
                    <asp:Parameter Name="CDate" Type="String" />
                    <asp:Parameter Name="CDescription" Type="String" />
                </InsertParameters>
            </asp:SqlDataSource>
        
        </div>
        </form>
    </body>
    </html>

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">

    <head runat="server">

        <title></title>

    </head>

    <body>

        <form id="form1" runat="server">

        <div>

        

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 

                DataKeyNames="Sr#" DataSourceID="SqlDataSource1">

                <Columns>

                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

                    <asp:BoundField DataField="Sr#" HeaderText="Sr#" InsertVisible="False" 

                        ReadOnly="True" SortExpression="Sr#" />

                    <asp:BoundField DataField="Category" HeaderText="Category" 

                        SortExpression="Category" />

                    <asp:BoundField DataField="CDate" HeaderText="CDate" SortExpression="CDate" />

                    <asp:BoundField DataField="CDescription" HeaderText="CDescription" 

                        SortExpression="CDescription" />

                </Columns>

            </asp:GridView>

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

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

                DeleteCommand="DELETE FROM [Competitions] WHERE [Sr#] = @column1" 

                InsertCommand="INSERT INTO [Competitions] ([Category], [CDate], [CDescription]) VALUES (@Category, @CDate, @CDescription)" 

                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 

                SelectCommand="SELECT * FROM [Competitions]" 

                UpdateCommand="UPDATE [Competitions] SET [Category] = @Category, [CDate] = @CDate, [CDescription] = @CDescription WHERE [Sr#] = @column1">

                <DeleteParameters>

                    <asp:Parameter Name="column1" Type="Int64" />

                </DeleteParameters>

                <UpdateParameters>

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

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

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

                    <asp:Parameter Name="column1" Type="Int64" />

                </UpdateParameters>

                <InsertParameters>

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

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

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

                </InsertParameters>

            </asp:SqlDataSource>

        

        </div>

        </form>

    </body>

    </html>

    Database Table

    Table had only 4 Fields. Sr# Int Primary key, other field simple text field. 

    i am waiting the answer of my problem from you all. 

    Thanks In Advance

    Regards,
    Shahid Majeed
    Email: shahid.majeed@gmail.com

  • Re: Gridview - Multiple-step OLE DB operation generated errors. Check each OLE DB status value

    06-26-2009, 11:24 AM
    Answer
    • Contributor
      7,023 point Contributor
    • superguppie
    • Member since 05-19-2009, 11:42 AM
    • Posts 1,240
    You are using @column1 in the where of the UpdateCommand. However, the code as you show it does not set the value of that parameter. That will make the update fail.
    SqlDataSource can make parameters for you when doing an update. To get it to do so, don't give it parameters explicitly. In this case, you can take out the first 3 UpdateParameters without problem.

    However, for key values things are a bit different. These values are passed to SqlDataSource in a different way. You can use them as parameters by setting OldValuesFormatString on SqlDataSource. My favorite value for it is "Key_{0}".
    Then you can use the key fields as parameters in your UpdateCommand. The name is formed by putting the field name in the format-string and prefixing it with an @. In your case that would be @Key_Sr#.
    Use that instead of @column1, and it should work. This removes the need for the parameter column1, leaving the UpdateParameters collection empty. Best take it out completely.
    And since the same goes for DeleteCommand, replace @column1 by @Key_Sr# and take out the DeleteParameters collection as well.
    Superguppie.

    Please remember to click “Mark as Answer” on the post that helps you.
    This can be beneficial to other community members reading the thread.

    When all you've got is a Hammer,
    Every Problem looks like a Nail.
    Michael Swain.

  • Re: Gridview - Multiple-step OLE DB operation generated errors. Check each OLE DB status value

    06-26-2009, 11:36 AM
    • Star
      9,008 point Star
    • hans_v
    • Member since 01-29-2007, 4:03 PM
    • Posts 1,551

    superguppie:
    You can use them as parameters by setting OldValuesFormatString on SqlDataSource.
     

    There's no need to set the OldValuesFormatString, this only applies if ConflictDetection is set to CompareAllValues.

    The only thing that wrong here is that the parameter names should match the names of the bound fields, so replace @colums1 by @Sr# and that's it

  • Re: Gridview - Multiple-step OLE DB operation generated errors. Check each OLE DB status value

    06-27-2009, 2:20 AM
    • Member
      10 point Member
    • shahid.majeed
    • Member since 10-07-2008, 8:24 AM
    • Lahore
    • Posts 69

    sorry dear when i look in my code there is only question mark instead of field name.

    here is the code

     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 

                DataKeyNames="Sr#" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                    <asp:BoundField DataField="Sr#" HeaderText="Sr#" InsertVisible="False" 
                        ReadOnly="True" SortExpression="Sr#" />
                    <asp:BoundField DataField="Category" HeaderText="Category" 
                        SortExpression="Category" />
                    <asp:BoundField DataField="CDate" HeaderText="CDate" SortExpression="CDate" />
                    <asp:BoundField DataField="CDescription" HeaderText="CDescription" 
                        SortExpression="CDescription" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                DeleteCommand="DELETE FROM [Competitions] WHERE [Sr#] = ?" 
                InsertCommand="INSERT INTO [Competitions] ([Sr#], [Category], [CDate], [CDescription]) VALUES (?, ?, ?, ?)" 
                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
                SelectCommand="SELECT * FROM [Competitions]" 
                UpdateCommand="UPDATE [Competitions] SET [Category] = ?, [CDate] = ?, [CDescription] = ? WHERE [Sr#] = ?">
                <DeleteParameters>
                    <asp:Parameter Name="column1" Type="Int64" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:Parameter Name="Category" Type="String" />
                    <asp:Parameter Name="CDate" Type="String" />
                    <asp:Parameter Name="CDescription" Type="String" />
                    <asp:Parameter Name="column1" Type="Int64" />
                </UpdateParameters>
                <InsertParameters>
                    <asp:Parameter Name="column1" Type="Int64" />
                    <asp:Parameter Name="Category" Type="String" />
                    <asp:Parameter Name="CDate" Type="String" />
                    <asp:Parameter Name="CDescription" Type="String" />
                </InsertParameters>
            </asp:SqlDataSource>

     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 

                DataKeyNames="Sr#" DataSourceID="SqlDataSource1">

                <Columns>

                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

                    <asp:BoundField DataField="Sr#" HeaderText="Sr#" InsertVisible="False" 

                        ReadOnly="True" SortExpression="Sr#" />

                    <asp:BoundField DataField="Category" HeaderText="Category" 

                        SortExpression="Category" />

                    <asp:BoundField DataField="CDate" HeaderText="CDate" SortExpression="CDate" />

                    <asp:BoundField DataField="CDescription" HeaderText="CDescription" 

                        SortExpression="CDescription" />

                </Columns>

            </asp:GridView>

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

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

                DeleteCommand="DELETE FROM [Competitions] WHERE [Sr#] = ?" 

                InsertCommand="INSERT INTO [Competitions] ([Sr#], [Category], [CDate], [CDescription]) VALUES (?, ?, ?, ?)" 

                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 

                SelectCommand="SELECT * FROM [Competitions]" 

                UpdateCommand="UPDATE [Competitions] SET [Category] = ?, [CDate] = ?, [CDescription] = ? WHERE [Sr#] = ?">

                <DeleteParameters>

                    <asp:Parameter Name="column1" Type="Int64" />

                </DeleteParameters>

                <UpdateParameters>

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

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

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

                    <asp:Parameter Name="column1" Type="Int64" />

                </UpdateParameters>

                <InsertParameters>

                    <asp:Parameter Name="column1" Type="Int64" />

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

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

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

                </InsertParameters>

            </asp:SqlDataSource>


    so please let me know what i do know

    Regards,
    Shahid Majeed
    Email: shahid.majeed@gmail.com

  • Re: Gridview - Multiple-step OLE DB operation generated errors. Check each OLE DB status value

    06-27-2009, 6:03 AM
    Answer
    • Star
      9,008 point Star
    • hans_v
    • Member since 01-29-2007, 4:03 PM
    • Posts 1,551

     The questionmarks in the SQL are not the problem. OleDb parameters are recognized by their position anyway, so that's not the problem. But how would ASP.NEt possibly know that parameter named column1 (in the updateparameters collection) should be bound to boundfield named Sr#????

    So you should changes the Parameter Named column1 in the Update Paremeter  collections:

    <asp:Parameter Name="Sr#" Type="Int64" />

    This applies for the delete parameter also!

    By the way, you changed your insertcommand.  The InsertVisible porperty is set to false (which makes me believe it is a autogenerated field), so how could this be included in the insert command?
Page 1 of 1 (5 items)
RSS Available

Contact | Advertise | Issue Management by Axosoft's OnTime | Running IIS7
All Rights Reserved. | Terms of Use | Trademarks | Privacy Statement
© 2009 Microsoft Corporation.