GridView wont delete or update

I have had this problem before but it turned out to be dodgy SQL created by the wizard. Doesn't seem to be the case this time.

The following does a postback but makes no changes.

1<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"2DeleteCommand="DELETE FROM [tblSubRegions] WHERE [SubRegionID] = ?"3InsertCommand="INSERT INTO [tblSubRegions] ([SubRegionID], [RegionID], [SubRegionName]) VALUES (?, ?, ?)"4ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"5SelectCommand="SELECT tblSubRegions.SubRegionID, tblSubRegions.RegionID, tblSubRegions.SubRegionName, tblRegions.RegionName FROM (tblSubRegions INNER JOIN tblRegions ON tblSubRegions.RegionID = tblRegions.RegionID) WHERE (tblSubRegions.RegionID = ?) ORDER BY tblSubRegions.SubRegionName"6UpdateCommand="UPDATE [tblSubRegions] SET [RegionID] = ?, [SubRegionName] = ? WHERE [SubRegionID] = ?">78<DeleteParameters>9 <asp:Parameter Name="SubRegionID" Type="Int32" />10</DeleteParameters>1112<UpdateParameters>13<asp:Parameter Name="RegionID" Type="Int32" />14<asp:Parameter Name="SubRegionName" Type="String" />15<asp:Parameter Name="SubRegionID" Type="Int32" />16</UpdateParameters>1718<SelectParameters>19<asp:ControlParameter ControlID="dropRegions" Name="RegionID" PropertyName="SelectedValue" Type="Int32" />20</SelectParameters>2122<InsertParameters>23<asp:Parameter Name="SubRegionID" Type="Int32" />24<asp:Parameter Name="RegionID" Type="Int32" />25<asp:Parameter Name="SubRegionName" Type="String" />26</InsertParameters>2728</asp:SqlDataSource>29303132<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"33ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"34SelectCommand="SELECT [RegionID], [RegionName] FROM [tblRegions]">3536</asp:SqlDataSource>37383940<asp:DropDownList id="dropStates" runat="server" OnSelectedIndexChanged="dropStates_SelectedIndexChanged" AutoPostBack="True">41</asp:DropDownList>4243<asp:DropDownList id="dropRegions" runat="server" OnSelectedIndexChanged="dropRegions_SelectedIndexChanged" AutoPostBack="True">44</asp:DropDownList>45464748 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"49 AutoGenerateColumns="False" EnableViewState=false Width="100%" DataSourceID="SqlDataSource1">50 <Columns>51 <asp:TemplateField HeaderText="SubRegionName" SortExpression="SubRegionName">52 <EditItemTemplate>53 <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"54 DataTextField="RegionName" DataValueField="RegionID" SelectedValue='<%# Bind("RegionID") %>'>55 </asp:DropDownList>56 </EditItemTemplate>57 <ItemTemplate>58 <asp:Label ID="Label1" runat="server" Text='<%# Bind("SubRegionName") %>'></asp:Label>59 </ItemTemplate>60 </asp:TemplateField>61 <asp:BoundField DataField="RegionName" HeaderText="RegionName" SortExpression="RegionName" />62 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />63 </Columns>64 </asp:GridView>

Thanks in advance.

Shaun

You need to set DataKeyNames="yourprimarykey"

gridview edit/delete

In gridview the edit and delete links appear where they belong and on edit the proper expanded edit bar appears allowing me to make changes, but when I click update it closes the edit bar and returns me to my original position without posting the changes.

The same thing happens when I attempt to delete a record.

The database is a single table and I assume the connection string is OK as evidenced by the fact the data is displayed correctly. Edit and delete were selected from the drop-down options menu on the gridview as opposed the inside the properties box.

To really drive me off the deep end, if I create a new site and data base following all the same procedures, I have a 50/50 chance that it will work properly, and I promise I haven't been drinking...yet.

Can anyone shed any light on what's happening here? It feels like I'm missing a step somewhere, but can't figure out where.

Many thanks!

What application are you using to edit your data? What are you doing to view your data in a grid?

In SQL Server 2005 Management Studio, you can right click on the table in Object Explorer and then select "Open Table" in the context menu to bring up an editable grid. You should be able to modify your data directly in the grid.

Hope this helps,
Steve

|||You might need to check the privileges for the account using this way, as it is better to edit or delete the table rows using Queyr editior with TSQL statements.|||

Hi,

I am facing a problem with a Editable grid view.

We have overridden the create row method in gridview to make it multi row editble by setting the rowstate property to edit on the selected row indexes.

We are having dropdowns in edit mode. Till it goes to edit mode it works fine but the after that when click on any button .it displays the "Failed to load view state Error" The controls used to load view state does not match.....

this error only happenes when we use a drop down and dynamically add the dropdown values when on row_created event of datagrid.

Any help would be appriciated.

Regards,

Alok

gridview edit/delete

In gridview the edit and delete links appear where they belong and on edit the proper expanded edit bar appears allowing me to make changes, but when I click update it closes the edit bar and returns me to my original position without posting the changes.

The same thing happens when I attempt to delete a record.

The database is a single table and I assume the connection string is OK as evidenced by the fact the data is displayed correctly. Edit and delete were selected from the drop-down options menu on the gridview as opposed the inside the properties box.

To really drive me off the deep end, if I create a new site and data base following all the same procedures, I have a 50/50 chance that it will work properly, and I promise I haven't been drinking...yet.

Can anyone shed any light on what's happening here? It feels like I'm missing a step somewhere, but can't figure out where.

Many thanks!

What application are you using to edit your data? What are you doing to view your data in a grid?

In SQL Server 2005 Management Studio, you can right click on the table in Object Explorer and then select "Open Table" in the context menu to bring up an editable grid. You should be able to modify your data directly in the grid.

Hope this helps,
Steve

|||You might need to check the privileges for the account using this way, as it is better to edit or delete the table rows using Queyr editior with TSQL statements.|||

Hi,

I am facing a problem with a Editable grid view.

We have overridden the create row method in gridview to make it multi row editble by setting the rowstate property to edit on the selected row indexes.

We are having dropdowns in edit mode. Till it goes to edit mode it works fine but the after that when click on any button .it displays the "Failed to load view state Error" The controls used to load view state does not match.....

this error only happenes when we use a drop down and dynamically add the dropdown values when on row_created event of datagrid.

Any help would be appriciated.

Regards,

Alok

GridView delete function

This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource:

 <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString%>" SelectCommand="SELECT * FROM [Index] WHERE (Type LIKE '%' + @.SearchText2 + '%') OR (Product LIKE '%' + @.SearchText2 + '%') OR (Version LIKE '%' + @.SearchText2 + '%') OR (Binder LIKE '%' + @.SearchText2 + '%') OR (Language LIKE '%' + @.SearchText2 + '%') OR (CDName LIKE '%' + @.SearchText2 + '%') OR (Details LIKE '%' + @.SearchText2 + '%') OR (ISOLink LIKE '%' + @.SearchText2 + '%')" DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.original_ID" UpdateCommand="UPDATE [Index] SET Type = @.Type, Product = @.Product , Version = @.Version, Binder = @.Binder, Language = @.Language, CDName = @.CDName, Details = @.Details, ISOLink = @.ISOLink WHERE ID = @.ID"> <SelectParameters> <asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Type" /> <asp:Parameter Name="Product" /> <asp:Parameter Name="Version" /> <asp:Parameter Name="Binder" /> <asp:Parameter Name="Language" /> <asp:Parameter Name="CDName" /> <asp:Parameter Name="Details" /> <asp:Parameter Name="ISOLink" /> </UpdateParameters> </asp:SqlDataSource>

It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@.ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc isdifferent from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.

-Brandan

Hello

What if you add a semicolumn after @.original_ID ? like this"DELETE FROM [Index] WHERE [ID] = @.original_ID;"

|||

Is the ID column your table's primary key? If yes, you need to make sure that the ID is set in your GridView's DadaKeyNames and you should change your DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> and DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.ID"

If you cannot make this work, please post your GridView part here and if you can list all your columns' name instead of a * in your SELECT statement, that would be great. Thanks.

|||

If it doesn't fixes it it might just be the DataKeyNames field of the gridview that need to be set to ID .

|||

**RESOLVED**

It was definitely the DataKeyNames. I had recreated the gridview so many times I forgot to put it back in. thanks ya'll.

Gridview delete behavior change from beta2 in VWD express

I've not been able to test this yet in full VS2005 (MS is screwed up with orders for some reason. Has anyone heard this one. All orders for less than quantity=5 were being rejected). So, instead of waiting for it I installed VWD Express. Now, the project had been built in VWD express beta 2, so no big change there.
Several gridview controls in the project had <Delete> enabled. Just <Delete> mind you. Nothing else for command buttons. They all worked fine with delete queries that use gridview.selectedvalue as the parameter. Every single one stopped working since I've converted from beta 2 to RTM!
Here's what I determined. The delete will only work if the row in the grid is selected first!. Otherwise <selectedvalue> is null when you click on <Delete>.
Is this a bug or what?
I'd be happy to supply more details. I am certain others will report this, but I have yet to find a post here or in any blogs out there that reproduce this problem in the RTM of .NET 2.0

On looking more closely at my earlier implementation of <Delete> I realize that the ParameterSource for the key in the grid is actually set to None in dotnet 2.0 beta 2. And it works fine. It seems to just automatically pick up the grid <datakey> value, even when that is a composite key.
My struggles to make <Delete> work again took me down the road of modifying the parameter values which is how I came to my earlier conclusion. Suffice it to say, that <Delete> in the gridview worked very automatically in beta 2. And now, no matter how much I try, the <Delete> function does not work at all.
I will keep playing with it, to see if there is some workaround other than coding things all over again to do deletes in a much more manual way.
Also, I've definitively reproduced my results by moving a working page back and forth from a .NET 2.0 beta 2 and .NET 2.0 RTM environment. Both in a development machine and W2003 server environments. As soon as I take the working page with <Delete> in the gridview from beta 2 to rtm, the <Delete> feature fails.
Anybody else that has a chance to check this out please let me know if you can reproduce this problem.

|||Hate to admit it, but I overlooked an old post here that gave me the hint I needed. Anyway, looking at delete query with SQLprofiler made it very clear. Removing the parameter for the <Delete> and changing the delete query to use the grids datakey field corrected things. Still, I have to compare this asp file to my old beta 2 file and see what happened when I copied it over and used it on the .NET 2 RTM development system. I am sure the behavior changed when I transitioned the project to the rtm version. Hope this saves others some head scatching...

Grid view-cant update or delete

I put a grid view on a web form ,when I run it -the SELECT, EDIT works

the UPDATE,DELETE makes an error although I use the sama data,I added the error :

Anyone can help?

Server Error in '/CrystalReportsWebSite1' Application.

---

The data types text and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): The data types text and nvarchar are incompatible in the equal to operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140

Hey,

What does those update/delete stored procedures look like? It seems like it may be an issue with the query.

|||I'm guessing he has a text field, and he told it to use optimistic concurrency or (CompareAllValues), which doesn't work with text fields.