I have a GridView posting what is in my database and I have the
DeleteCommand working as I want it, however, the Update Command is not updating. The if statement in mySQLDataSource "says" it's updating, but that is not the case. I have also tried adding in DeleteMessages.DataBind() thinking that maybe it needed to be rebound. I'm using a MySQL database if that helps.

Here is the aspx:

<%@ Page Language="vb" AutoEventWireup="false" MasterPageFile="~/Layout/TemplateForm.Master" CodeBehind="Trash.aspx.vb" Inherits="Messenger_Project.Trash" 
    title="Trash Message" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">

    <br /><div align="center"><asp:Label ID="lblStatus" runat="server" ForeColor="Red"></asp:Label></div> <br />
    
	<asp:GridView ID="DeleteMessages"       runat="server"      AllowSorting="True" AutoGenerateColumns="False"
                  DataKeyNames="studentID"  CellPadding="4"     ForeColor="#333333"
                  AllowPaging="True"        DataSourceID="mySQLDataSource" Width="629px"   style="margin-right: 6px">    
                  <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />                  
<Columns>


<asp:BoundField DataField="id"          HeaderText="id"         InsertVisible="False" ReadOnly="True" SortExpression="id" />
<asp:BoundField DataField="studentID"   HeaderText="studentID"  SortExpression="studentID" />
<asp:BoundField DataField="message"     HeaderText="message"    SortExpression="message" />
<asp:BoundField DataField="department"  HeaderText="department" SortExpression="department" ReadOnly="True" />

    <asp:TemplateField ShowHeader="False" HeaderText="Modify Cell">
        <ItemTemplate>
            <asp:Button ID="btnEdit" runat="server"  CausesValidation="True" CommandName="Edit" Text="Edit" />&nbsp;
            <asp:Button ID="btnDelete" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" OnClientClick="return confirm('Are you sure? This is permanent and can not be reversed!');" />
        </ItemTemplate>
        <EditItemTemplate>
            <asp:Button ID="btnUpdate" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />&nbsp;
            <asp:Button ID="btnCancel" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />
        </EditItemTemplate>
    </asp:TemplateField>


</Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>

    <asp:SqlDataSource ID="mySQLDataSource" runat="server"
        ConnectionString="<%$ ConnectionStrings:messengerConnectionString %>"  
        ProviderName="<%$ ConnectionStrings:messengerConnectionString.ProviderName %>" 
        SelectCommand="SELECT * FROM messenger"
        UpdateCommand="UPDATE messenger SET studentID = ?studentID, message = ?message WHERE (id = ?id)"
        DeleteCommand="DELETE FROM messenger WHERE id = ?id AND studentID = ?studentID AND message = ?message AND department = ?department" 
        OnDeleted="mySQLDataSource_Deleted" 
        OnUpdated="mySQLDataSource_Updated"
        ConflictDetection="CompareAllValues" 
        InsertCommand="INSERT INTO messenger(studentID, message) VALUES ?studentID, ?message" >
    <DeleteParameters>
        <asp:Parameter Name="original_id" Type="String" />
        <asp:Parameter Name="original_studentID" Type="String" />
        <asp:Parameter Name="original_message" Type="String" />
        <asp:Parameter Name="original_department" Type="String" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="id" Type="String" />
        <asp:Parameter Name="studentID" Type="String" />
        <asp:Parameter Name="message" Type="String" />
        <asp:Parameter Name="department" Type="String" />
    </UpdateParameters>    
        <InsertParameters>
            <asp:Parameter Name="studentID" Type="String" />
            <asp:Parameter Name="message" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
</asp:Content>

Here is the codebehind file.

Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient

Partial Public Class Trash
    Inherits System.Web.UI.Page


    Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Clear the label
        lblStatus.Text = Nothing
    End Sub

    'Delete a Row
    Protected Sub mySQLDataSource_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
        lblStatus.ForeColor = Drawing.Color.Red
        If e.Exception Is Nothing Then
            If e.AffectedRows = 1 Then
                lblStatus.ForeColor = Drawing.Color.Green
                lblStatus.Text = "Deleted Successfully"
                DeleteMessages.DataBind()
                Exit Sub
            Else
                lblStatus.Text = "Error during deletion"
                Exit Sub
            End If
        Else
            lblStatus.Text = "Error during deletion"
            e.ExceptionHandled = True
        End If
    End Sub

    'Update Information
    Protected Sub mySQLDataSource_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)

        lblStatus.ForeColor = Drawing.Color.Red
        If e.Exception Is Nothing Then
            If e.AffectedRows = 1 Then
                lblStatus.ForeColor = Drawing.Color.Green
                lblStatus.Text = "Updated Successfully"
                Exit Sub
            Else
                lblStatus.Text = "Updating not successfull"
                Exit Sub
            End If
        Else
            lblStatus.Text = "Update failed"
            e.ExceptionHandled = True
        End If
    End Sub

End Class

Why do your update and delete statements have different where clauses? Shouldn't they be the same?

UpdateCommand="UPDATE messenger SET studentID = ?studentID, message = ?message WHERE (id = ?id)"    
    
DeleteCommand="DELETE FROM messenger WHERE id = ?id AND studentID = ?studentID AND message = ?message AND department = ?department"

Sorry, not exactly sure. I've been teaching myself everything.
I'll change the sql statement around for that. The DeleteCommand should essentially mirror the UpdateCommand for the most part then?

Well if you're updating and individual record and you also want to delete that record then it stands to reason you would have the same where criteria to locate the record. As it is currently your update command could affect more records than your delete command.

Ok so I tested the UpdateCommand some more and I have the same issue still. I used the same criteria from the DeleteCommand.

Are there any other thoughts as to what is the issue? Through the query builder it works great, but not outside of it.

an me i have the same problem so can u give me codes for delete, update a record i am using asp.net and sql sever

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.