Deleting and Updating from Gridview

Hi,

I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @.TransactionIDAND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid) "

It gives the error that Quantityavailable is not a SET type?

Thanks if you can suggest a remedy!

Jon

jbear123:

UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid

That is not valid syntax for an UPDATE statement.

Try this

UPDATE itemsSET Quantityavailable = Quantityavailable + 1, numtaken = numtaken - 1WHERE ItemId = @.ItemId

|||

Thanks!

Deleting a row using a Stored Procedure from a GridView

I am trying to do something that I would think is simple. I have a stored procedure used for deleting a record, and I want to call it from the "Delete" command of a Delete button on a GridView. This incredible simple SP accepts one value, the unique record ID to delete the record like this:

CREATE PROCEDURE usp_DeleteBox/* ******************************************* Delete a record using the Passed ID.********************************************** */(@.pIDas int =Null)ASDELETE FROM [Boxes]WHERE ID = @.pID

When I configured the data source for the GridView, I selected the "Delete" tab and selected my Stored Procedure from the list. As mentioned on another post I saw here, I set the "DataKeyNames" property of the GridView to my id field (called "ID", naturally).

When I click the Delete button on a row, I get this error message: "Procedure or function usp_DeleteBox has too many arguments specified." If I leave the "DataKeyNames" property empty, it does nothing when I click delete.

Can someone tell me the correct way to configure this? I am sure I am missing something obvious, and I would appreciate any suggestions. Thank you!

Do you have any other columns in the DataKeyNames field? Any DeleteParameters on the DataSource? Can you show the GridView and DataSource code ?

|||

Thanks for your quick reply! I have no other colums in the DataKeyNames field. Here is the code you mentioned:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource_Boxes" Font-Names="Arial" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="BoxNumber" HeaderText="BoxNumber" SortExpression="BoxNumber" /> <asp:BoundField DataField="PalletNumber" HeaderText="PalletNumber" SortExpression="PalletNumber" /> <asp:BoundField DataField="DestName" HeaderText="DestName" SortExpression="DestName" /> <asp:BoundField DataField="DestCode" HeaderText="DestCode" SortExpression="DestCode" /> <asp:CommandField ButtonType="Button" ShowDeleteButton="True" /> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#999999" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView>

 <asp:SqlDataSource ID="SqlDataSource_Boxes" runat="server" ConnectionString="<%$ ConnectionStrings:BoxTrackSite.My.MySettings.ShippingConnStr%>" SelectCommand="SELECT dbo.Boxes.ID, dbo.Boxes.BoxNumber, dbo.Pallets.PalletNumber, dbo.Destinations.DestName, dbo.Destinations.DestCode FROM dbo.Boxes INNER JOIN dbo.Pallets ON dbo.Boxes.PalletID = dbo.Pallets.ID INNER JOIN dbo.Destinations ON dbo.Boxes.DestID = dbo.Destinations.ID" DeleteCommand="usp_DeleteBox" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="pID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>

I know the SqlDataSource has a DeleteParameter (I don't know how that got there). If I removed that, it does nothing. Any further suggestions? Thanks again for your help.|||

I found a solution, but it wasn't as clean as I would have liked. Following the details inthis post, I found that I was indeed passing two parameters "pID" & "ID" when the SqlDataSource.Deleting event fired. So, as the other people mentioned, I changed my SP variable to be the same name as the identity column in my database ("ID") - and this worked.

It's frustrating that this is the behaviour. The controls should support methods for doing things as I was trying to do it originally. Some developers do not have the "luxury" (or permission) to modify existing SP's.

DeleteCommand and UpdateCommand

I have added a gridview and under its properties i have changed AutoGenerateDeleteButton and AutoGenerateEditButton to true.

The problem is... when I try and delete a record, I get this error: Deleting is not supported by data source 'SqlDataSource1' unless DeleteCommand is specified

If I try and update.. i get the error: Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.

Can someone please help me out... maybe with some code... or a link to a tutorial..

Thanks

Canning

When you configure your datasource, you need to choose "Advanced". On the Advanced page, you need to check "Generate Insert,Update and Delete commands". This will generate attributes in your sqldatasource that allow for these things to happen. Make sure you have a primary key in your table set.|||

Hi,

I have the same problem. I tried to change the advanced setting as you specified. However, it is not allowing me to do so. The check boxes are greyed out.

Any suggestions?

thanks..

|||Greyed out usually means there is no primary key on the table that the control can recognize.|||

Also,

I do see where it says on the advanced section that "You must have all primary key fields selected for this option to be enabled." Im not sure what that means though. I have every column in the table selected to display. That does not help though. Can you tell me where I find the primary key fields so that I can "Select" them?

thanks...

|||

I do see where it says on the advanced section that "You must have all primary key fields selected for this option to be enabled." Im not sure what that means though. I have every column in the table selected to display. That does not help though. Can you tell me where I find the primary key fields so that I can "Select" them?

thanks...

|||

I was able to figure this out...thanks...and sorry for posting that twice.

Thanks for the direction.

|||

Hi,

I have the same problem that DavidatWork. Can you send me the direction too?

|||I have the same problem however in my table I specify a primary key. I still have the options disabled. Any help?|||

WEN I tried to change the advanced setting OPTION However, it is not allowing TO DO THAT The check boxes ARE UNCHECKED IE GREYED OUT SO HOW CAN I DO UPDATE AND DELETE IN MY TABLE.hOW TO ADD PRIMARY KEY ON THE TABLE

|||

hoW TO ADD PRIMARY KEY IN TABLE QUERY I HAVENT FOUND ANY OPTION REGD PRIMARY KEY CAN U SUGGEST ME

tHANK U

bHANUPRIYA

|||

do this:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

(it's not the same as having an identity column).

|||

i have the same problem can anyone tell me how to resolve it?