Gridview Insert parameter problem

Who is online? 0 guests and 1 members

home » forums » asp.net topics » data access » Gridview Insert parameter problem

Gridview Insert parameter problem

Posts under the topic: Gridview Insert parameter problem

Posted: 7/14/2011

Mike Riemer

360 points Lurker

Joined on: 6/5/2011
Posts: 51

I am trying to compile a list into a gridviews from selections made from a detailsview. The problem is that the primary key Is Identity value is getting inserted to each and every field. Here is a screen shot:

Here is my code. what do I have to do to get each one of those fields from my details view into my gridview correctly?

<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" 
            DeleteMethod="Delete" InsertMethod="Insert" 
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" 
            TypeName="Testbed.DataSet2TableAdapters.ShoppingListTableAdapter" 
            UpdateMethod="Update">
            <DeleteParameters>
                <asp:Parameter Name="Original_SListId" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:ControlParameter ControlID="DetailsView1" Name="StoreName" 
                     PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="DetailsView1" Name="Coupons" 
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="DetailsView1" Name="ItemName" 
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="DetailsView1" Name="ReatilPrice" 
                    PropertyName="SelectedValue" Type="Decimal" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="Coupons" Type="String" />
                <asp:Parameter Name="ItemName" Type="String" />
                <asp:Parameter Name="ReatilPrice" Type="Decimal" />
                <asp:Parameter Name="Original_SListId" Type="Int32" />
            </UpdateParameters>
        </asp:ObjectDataSource>

Posted: 7/14/2011

Raghav Khunger

16793 points Guru

Joined on: 4/19/2009
Posts: 490

Please show some code to fill your details view and gridview. Create some sample code to fill it.

[<a href="https://mvp.support.microsoft.com/profile/Raghav">ASP.NET MVP</a>] [<a href="https://www.microsoftcommunitycontributor.com">Microsoft® Community Contributor</a>] [<a href="http://www.dzone.com/aboutmvb">Dzone MVB</a>] [<a href="https://twitter.com/#!/RaghavKhunger">Follow me on Twitter</a>] [<a href="http://codeasp.net/wiki/view/10/how-to-ask-questions-on-forums">How to ask questions on forums</a>] "Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous "Success is simply a matter of luck. Ask any failure." Anonymous

Posted: 7/14/2011

Mike Riemer

360 points Lurker

Joined on: 6/5/2011
Posts: 51

Here's the complete code:

    <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" 
            DataKeyNames="ItemId" DataSourceID="SqlDataSource1" Height="50px" 
            Width="125px" AllowPaging="True">
            
            <Fields>
                <asp:BoundField DataField="ItemId" HeaderText="ItemId" InsertVisible="False" 
                    ReadOnly="True" SortExpression="ItemId" />
                <asp:BoundField DataField="ItemName" HeaderText="ItemName" 
                    SortExpression="ItemName" />
                <asp:BoundField DataField="StoreName" HeaderText="StoreName" 
                    SortExpression="StoreName" />
                <asp:BoundField DataField="Coupons" HeaderText="Coupons" 
                    SortExpression="Coupons" />
                <asp:BoundField DataField="RetailPrice" HeaderText="RetailPrice" 
                    SortExpression="RetailPrice" />
                <asp:BoundField DataField="FinalValuePrice" HeaderText="FinalValuePrice" 
                    SortExpression="FinalValuePrice" />
                <asp:BoundField DataField="ClerkName" HeaderText="ClerkName" 
                    SortExpression="ClerkName" />
                <asp:CommandField ShowSelectButton="true" />
            </Fields>
        </asp:DetailsView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionStringtestData %>" 
            SelectCommand="SELECT * FROM [CouponBot]" 
            ConflictDetection="CompareAllValues" 
            DeleteCommand="DELETE FROM [CouponBot] WHERE [ItemId] = @original_ItemId AND [ItemName] = @original_ItemName AND [StoreName] = @original_StoreName AND [Coupons] = @original_Coupons AND [RetailPrice] = @original_RetailPrice AND (([FinalValuePrice] = @original_FinalValuePrice) OR ([FinalValuePrice] IS NULL AND @original_FinalValuePrice IS NULL)) AND [ClerkName] = @original_ClerkName" 
            InsertCommand="INSERT INTO [CouponBot] ([ItemName], [StoreName], [Coupons], [RetailPrice], [FinalValuePrice], [ClerkName]) VALUES (@ItemName, @StoreName, @Coupons, @RetailPrice, @FinalValuePrice, @ClerkName)" 
            OldValuesParameterFormatString="original_{0}" 
            UpdateCommand="UPDATE [CouponBot] SET [ItemName] = @ItemName, [StoreName] = @StoreName, [Coupons] = @Coupons, [RetailPrice] = @RetailPrice, [FinalValuePrice] = @FinalValuePrice, [ClerkName] = @ClerkName WHERE [ItemId] = @original_ItemId AND [ItemName] = @original_ItemName AND [StoreName] = @original_StoreName AND [Coupons] = @original_Coupons AND [RetailPrice] = @original_RetailPrice AND (([FinalValuePrice] = @original_FinalValuePrice) OR ([FinalValuePrice] IS NULL AND @original_FinalValuePrice IS NULL)) AND [ClerkName] = @original_ClerkName">
            <DeleteParameters>
                <asp:Parameter Name="original_ItemId" Type="Int32" />
                <asp:Parameter Name="original_ItemName" Type="String" />
                <asp:Parameter Name="original_StoreName" Type="String" />
                <asp:Parameter Name="original_Coupons" Type="String" />
                <asp:Parameter Name="original_RetailPrice" Type="Decimal" />
                <asp:Parameter Name="original_FinalValuePrice" Type="Decimal" />
                <asp:Parameter Name="original_ClerkName" Type="String" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="ItemName" Type="String" />
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="Coupons" Type="String" />
                <asp:Parameter Name="RetailPrice" Type="Decimal" />
                <asp:Parameter Name="FinalValuePrice" Type="Decimal" />
                <asp:Parameter Name="ClerkName" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="ItemName" Type="String" />
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="Coupons" Type="String" />
                <asp:Parameter Name="RetailPrice" Type="Decimal" />
                <asp:Parameter Name="FinalValuePrice" Type="Decimal" />
                <asp:Parameter Name="ClerkName" Type="String" />
                <asp:Parameter Name="original_ItemId" Type="Int32" />
                <asp:Parameter Name="original_ItemName" Type="String" />
                <asp:Parameter Name="original_StoreName" Type="String" />
                <asp:Parameter Name="original_Coupons" Type="String" />
                <asp:Parameter Name="original_RetailPrice" Type="Decimal" />
                <asp:Parameter Name="original_FinalValuePrice" Type="Decimal" />
                <asp:Parameter Name="original_ClerkName" Type="String" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" 
            DeleteMethod="Delete" InsertMethod="Insert" 
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" 
            TypeName="Testbed.DataSet2TableAdapters.ShoppingListTableAdapter" 
            UpdateMethod="Update">
            <DeleteParameters>
                <asp:Parameter Name="Original_SListId" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:ControlParameter ControlID="DetailsView1" Name="StoreName" 
                     PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="DetailsView1" Name="Coupons" 
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="DetailsView1" Name="ItemName" 
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="DetailsView1" Name="ReatilPrice" 
                    PropertyName="SelectedValue" Type="Decimal" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="Coupons" Type="String" />
                <asp:Parameter Name="ItemName" Type="String" />
                <asp:Parameter Name="ReatilPrice" Type="Decimal" />
                <asp:Parameter Name="Original_SListId" Type="Int32" />
            </UpdateParameters>
        </asp:ObjectDataSource>
        <br />
        <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
        <br />
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataKeyNames="SListId" DataSourceID="ObjectDataSource1">
            <Columns>
                <asp:BoundField DataField="SListId" HeaderText="SListId" InsertVisible="False" 
                    ReadOnly="True" SortExpression="SListId" />
                <asp:BoundField DataField="StoreName" HeaderText="StoreName" 
                    SortExpression="StoreName" />
                <asp:BoundField DataField="Coupons" HeaderText="Coupons" 
                    SortExpression="Coupons" />
                <asp:BoundField DataField="ItemName" HeaderText="ItemName" 
                    SortExpression="ItemName" />
                <asp:BoundField DataField="ReatilPrice" HeaderText="ReatilPrice" 
                    SortExpression="ReatilPrice" />
            </Columns>
        </asp:GridView>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
            DeleteMethod="Delete" InsertMethod="Insert" 
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" 
            TypeName="Testbed.DataSet2TableAdapters.ShoppingListTableAdapter" 
            UpdateMethod="Update">
            <DeleteParameters>
                <asp:Parameter Name="Original_SListId" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="Coupons" Type="String" />
                <asp:Parameter Name="ItemName" Type="String" />
                <asp:Parameter Name="ReatilPrice" Type="Decimal" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="Coupons" Type="String" />
                <asp:Parameter Name="ItemName" Type="String" />
                <asp:Parameter Name="ReatilPrice" Type="Decimal" />
                <asp:Parameter Name="Original_SListId" Type="Int32" />
            </UpdateParameters>
        </asp:ObjectDataSource>

Posted: 7/14/2011

Raghav Khunger

16793 points Guru

Joined on: 4/19/2009
Posts: 490

I have mentioned to show some dummy data to fill your grids. It will be helpful to fix your issue.

Posted: 7/14/2011

Mike Riemer

360 points Lurker

Joined on: 6/5/2011
Posts: 51

ItemId Int Is Identity yes

ItemName nvarchar(50) hot dogs, chili, bread, jello

StoreName nvarchar(50) Walmart, Target, Jewel, Dominicks

Coupons nvarchar(max) RP 6/26 $2.00 off, PG 5/30 $3.00 off, coupons.com, facebook.com

RetailPrice smallmoney 2.00, 3.00, 4.00, 1.00

FinalValuePrice smallmoney .50, 0, 1.00, .75

Here is some sample data and types.

Thanks.

Posted: 7/15/2011

Mike Riemer

360 points Lurker

Joined on: 6/5/2011
Posts: 51

I figured out that the problem lies in my INSERT query. I am trying to INSERT into my ShoppingList table, the ItemName, StoreName, Coupons,and RetailPrice from the CouponBot table where the ItemId is equal to the selected value of the detailsview. Is this possible. This is the query I tried but it didn't work:

INSERT INTO [ShoppingList] ([ItemName], [StoreName], [Coupons], [RetailPrice])
FROM [CouponBot]
WHERE ItemId =(@ItemId)

but I got :

Incorrect syntax near the keyword 'FROM'.

Is what I am trying to do possible?

What changes do I need to make to my INSERT query?

Thanks

Posted: 7/15/2011

Raghav Khunger

16793 points Guru

Joined on: 4/19/2009
Posts: 490

The query should be like:

INSERT INTO [ShoppingList] ([ItemName], [StoreName], [Coupons], [RetailPrice])
SELECT [ItemName], [StoreName], [Coupons], [RetailPrice]
FROM [CouponBot]
WHERE ItemId =@ItemId

Posted: 7/16/2011

Mike Riemer

360 points Lurker

Joined on: 6/5/2011
Posts: 51

This works fine Raghav but I have another wrench to throw into the mix. I also need to insert the authenticated user name into the ShoppingList table at the same time. I set up a nonvisible TextBox that gets the authenticated UserName in code behind:

protected void Page_Load(object sender, EventArgs e)
        {
            if (User.Identity.IsAuthenticated)
                TextBox1.Text = User.Identity.Name;
            else
                TextBox1.Text = "No user identity available.";

        }

How can I do this. When I tried I got an exception that states the # of insert columns must match the number of select columns. I am sure there is a way to do this, but it eludes me.

thanks

Posted: 7/17/2011

Raghav Khunger

16793 points Guru

Joined on: 4/19/2009
Posts: 490

You can go like this then:

INSERT INTO [ShoppingList] ([ItemName], [StoreName], [Coupons], [RetailPrice],[UserName])
SELECT [ItemName], [StoreName], [Coupons], [RetailPrice],@UserName
FROM [CouponBot]
WHERE ItemId =@ItemId

Page 1 of 1 (9 items)