[C#] An error occurred while attempting to update the row

Miguel61 · March 17, 2011, 9:44pm

Hi there.

I can do it update records in the GridView.

This is the error:
An error occurred while attempting to update the row.

Why?

Default4.aspx:


<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Odbc" %>
<%@ Import Namespace="System.Configuration" %>

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

<script runat="server">

public void Page_Load (Object sender, EventArgs e)
{
    OdbcConnection myConnectionString = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString);
    myConnectionString.Open();    
    myConnectionString.Close();     
 }

protected void GridView1_RowUpdated(Object sender, GridViewUpdatedEventArgs e)
{

    if (e.Exception != null)
    {
        Message.Text = "Row updated successfully.";
   
    }
    else
    {
        e.ExceptionHandled = true;
        Message.Text = "An error occurred while attempting to update the row.";
    
    }

}

protected void GridView1_RowCancelingEdit(Object sender, GridViewCancelEditEventArgs e)
{
    Message.Text = "";
}

protected void GridView1_RowEditing(Object sender, GridViewEditEventArgs e)
{
    Message.Text = "";
}



</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>

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

<asp:label id="Message" forecolor="Red" runat="server"/>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnMySQL %>" 
    ProviderName="<%$ ConnectionStrings:ConnMySQL.ProviderName %>" 
    DataSourceMode="DataSet"
    SelectCommand="SELECT * FROM tbl_login" 
    UpdateCommand="UPDATE tbl_login SET name=@nome, c=@c WHERE ID=@original_ID">

<UpdateParameters>
<asp:Parameter Type="String" Name="name" />
<asp:Parameter Type="String" Name="c" />
</UpdateParameters>

</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
    AllowSorting="True" PageSize="20" DataSourceID="SqlDataSource1" 
    DataKeyNames="ID" AutoGenerateColumns="False" AutoGenerateEditButton="False"
    onrowupdated="GridView1_RowUpdated"
    onrowcancelingedit="GridView1_RowCancelingEdit"
    onrowediting="GridView1_RowEditing">
    
<PagerStyle HorizontalAlign="Left" />

    <Columns>

    <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="true" />
    <asp:BoundField DataField="Email" HeaderText="Email" HtmlEncode="False" DataFormatString="<a href='mailto:{0}'>{0}</a>" />
    <asp:BoundField DataField="name" HeaderText="name" />
    <asp:BoundField DataField="c" HeaderText="c" />
    <asp:CommandField ShowHeader="true" HeaderText="Edit" ShowEditButton="true" />

        <asp:TemplateField>        
            <ItemTemplate>
            </ItemTemplate>                
        </asp:TemplateField>

    </Columns>

<PagerSettings Position="TopAndBottom"
 FirstPageText="First page"
 LastPageText="Last page"
 Mode="NextPreviousFirstLast" />
<PagerSettings />

</asp:GridView>


</div>
</form>

</body>
</html>

Jason__C · March 18, 2011, 2:01am

Is this all of your code? If not, you have a long way to go before you can utilize the Grids Row_Update event handler, to check for a successful update

Miguel61 · March 18, 2011, 8:55am

Thanks Sir.

Yes this is all code of the my aspx page.

I not understand u suggestion, I’m sorry.

pinch · March 18, 2011, 12:20pm

Put in a breakpoint and check the actual message that is passed with the exception (e.Exception). That should give you a clue.

Miguel61 · March 18, 2011, 1:44pm

Thanks Sir.

I try this:

protected void GridView1_RowUpdated(Object sender, GridViewUpdatedEventArgs e)

    {

    if (e.Exception != null)
    {
        Message.Text = "Row updated successfully.";
   
    }
    else
    {
        e.ExceptionHandled = true;
        Message.Text = e.Exception.Message;
    
    }

}

But I have this error:

Source=App_Web_rkhwabmm
StackTrace:
in ASP.default4_aspx.GridView1_RowUpdated(Object sender, GridViewUpdatedEventArgs e) in c:\Inetpub\wwwroot\_aspx\Default4.aspx:line 28
in System.Web.UI.WebControls.GridView.OnRowUpdated(GridViewUpdatedEventArgs e)
in System.Web.UI.WebControls.GridView.HandleUpdateCallback(Int32 affectedRows, Exception ex)
in System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback)
in System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation)
in System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
in System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e)
in System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
in System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e)
in System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
in System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e)
in System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
in System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
in System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
in System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
in System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:

Jason__C · March 18, 2011, 1:51pm

Misspell?


UpdateCommand="UPDATE tbl_login SET name=@nome, c=@c WHERE ID=@original_ID">

@nome…should be…@name?

Shot in the Dark

Miguel61 · March 18, 2011, 1:53pm

Thanks Sir.
I have correct, but not change.

pinch · March 18, 2011, 8:00pm

To avoid getting the exception, and properly printing out the message, you’ll need to put your code in a try-catch block. Or, as I suggested earlier, put a breakpoint and read the message that way.

Serenarules · March 18, 2011, 8:52pm

Just an observation, but you call Open(), immediately followed by Close() on your DbConnection in Page_Load(). You realise this will leave your connection closed for any following code?

Miguel61 · March 18, 2011, 10:46pm

Thanks everyone.

I’m here to learn and all suggestions are well accepted.

I have this problem with records updates and not find the solution.

I learning from the book: «ASP.NET 4 with C# and VB», author Bill Evjen, Scott Hanselman, Devin Rader.

Jason__C · March 19, 2011, 9:45am

Geez, I didn’t even see that. lol. OP, have you tried downloading the code from the website and running it directly on your machine? That should solve the problem, in case you made an error writing it by yourself, if you did.

Miguel61 · March 20, 2011, 10:27pm

Thanks all.

This is the error:


ERROR [42000] [MySQL][ODBC 5.1 Driver]
[mysqld-5.1.44-community]
You have an error in your SQL syntax; 
check the manual that corresponds to 
your MySQL server version for the right 
syntax to use near 'name, c='NewValueofC'
c WHERE ID='myEmail@MyEmail.com'ID' at line 1

Can u help me?

imaginekitty · March 20, 2011, 10:35pm

MySQL, huh?

I didn’t see your connection string.

NightStalker · March 21, 2011, 5:26am

I would suggest using the MySql Connector as apposed to your Odbc connection. Also, try changing the @'s to ? in your update string.

Miguel61 · March 21, 2011, 1:17pm

Thanks Sir.

Now you can delete, but you can not update…


<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Odbc" %>
<%@ Import Namespace="System.Configuration" %>

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

<script runat="server">
public void Page_Load (Object sender, EventArgs e)
{
    OdbcConnection myConnectionString = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString);
    myConnectionString.Open();    
    myConnectionString.Close();     
 }

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>

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

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnMySQL %>" 
    ProviderName="<%$ ConnectionStrings:ConnMySQL.ProviderName %>" 
    DataSourceMode="DataSet"
    SelectCommand="SELECT * FROM _fmbtlam_copy" 
    UpdateCommand="UPDATE _fmbtlam_copy SET email=? WHERE ID=?"
    DeleteCommand="DELETE FROM _fmbtlam_copy WHERE ID=?">

<UpdateParameters>
<asp:Parameter Name="email" Type="String" Direction="Input" />
</UpdateParameters>

<DeleteParameters>
<asp:Parameter Name="ID" Type="Int32" Direction="Input" />
</DeleteParameters>

</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" 
    DataSourceID="SqlDataSource1"
    AllowSorting="True" 
    AllowPaging="True" 
    PageSize="20" 
    DataKeyNames="ID">

<PagerStyle HorizontalAlign="Left" />
    <Columns>

    <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="true" />

    <asp:CommandField ShowHeader="true" HeaderText="Edit" ShowEditButton="true" />
    <asp:CommandField ShowHeader="true" HeaderText="Delete" ShowDeleteButton="true" />

    <asp:TemplateField>        
    <ItemTemplate>
    </ItemTemplate>                
    </asp:TemplateField>

    </Columns>

<PagerSettings Position="TopAndBottom" FirstPageText="First"
 LastPageText="Last" Mode="NextPreviousFirstLast" />
<PagerSettings />

</asp:GridView>
</div>
</form>

</body>
</html>

web.config


<?xml version="1.0"?>

<configuration>
	<connectionStrings>
		<add name="ConnMySQL" connectionString="Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=_mydb;uid=root;pwd=KKKKKK;option=3;" providerName="System.Data.Odbc" />
		<add name="ApplicationServices" connectionString="data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
	</connectionStrings>
	<system.web>
		<compilation debug="true" targetFramework="4.0"/>
		<authentication mode="Forms">
			<forms loginUrl="~/Account/Login.aspx" timeout="2880"/>
		</authentication>
		<membership>
			<providers>
				<clear/>
				<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/>
			</providers>
		</membership>
		<profile>
			<providers>
				<clear/>
				<add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/>
			</providers>
		</profile>
		<roleManager enabled="false">
			<providers>
				<clear/>
				<add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/"/>
				<add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/"/>
			</providers>
		</roleManager>
	</system.web>
	<system.webServer>
		<modules runAllManagedModulesForAllRequests="true"/>
	</system.webServer>
</configuration>

NightStalker · March 21, 2011, 6:43pm

Try just replacing the @'s with ?'s. Like this:

UpdateCommand=“UPDATE tbl_login SET name=?name, c=?c WHERE ID=?original_ID”

Miguel61 · March 22, 2011, 7:56am

Thanks all for ur cooperation.

I fixed the problem, now all working in my new page NET.