Deleting is not supported by data source 'SqlDataSource1' unless DeleteCommand is specified

Question

3.00/5 (3 votes)

See more:

Hi,

I am getting Error"Deleting is not supported by data source 'SqlDataSource1' unless DeleteCommand is specified."

pls. help.

The code is below.

<asp:GridView ID="GridView1" runat="server" align="center" AllowPaging="True" DataKeyNames="EmpNumber"
                                 AutoGenerateColumns="False" class="tabulardata" DataSourceID="SqlDataSource1" 
                                 HorizontalAlign="Center" Width="100%"  OnRowCommand="GridView1_RowCommand" 
                                 OnRowDataBound="GridView1_RowDataBound" OnRowDeleted="GridView1_RowDeleted" OnRowDeleting="GridView1_RowDeleting">
                                 <columns>
                                     <asp:TemplateField HeaderText="Sl.No" ItemStyle-HorizontalAlign="Center" 
                                         ItemStyle-Width="2%">
                                         <itemtemplate>
                                             <asp:Label ID="Sno" runat="server">
                                         </itemtemplate>
                                         <itemstyle horizontalalign="Center" />
                                     
                                     <asp:BoundField DataField="EmpNumber" HeaderText="User Name" Visible="false" 
                                         ItemStyle-HorizontalAlign="center" ReadOnly="true" SortExpression="EmpNumber">
                                         <itemstyle horizontalalign="Center" />
                                     
                                     <asp:BoundField DataField="Empname" HeaderText="User Name" 
                                         ItemStyle-HorizontalAlign="center" ReadOnly="true" SortExpression="Empname">
                                         <itemstyle horizontalalign="Center" width="14%" />
                                     
                                     <asp:BoundField DataField="UserType" HeaderText="User Type" 
                                         ItemStyle-HorizontalAlign="center" ReadOnly="true" SortExpression="UserType">
                                         <itemstyle horizontalalign="Center" width="5%" />
                                     
                                     <asp:TemplateField HeaderText="Access" ItemStyle-HorizontalAlign="Center">
                                         <itemtemplate>
                                             <asp:ImageButton ID="btnAccess" runat="server" CommandName="Delete" Height="20" 
                                                 ImageUrl="~/images/Access.gif" 
                                                 Width="20" />
                                         </itemtemplate>
                                         <itemstyle horizontalalign="Center" width="2%" />
                                     
                                     <asp:TemplateField HeaderText="Delete" ItemStyle-HorizontalAlign="Center">
                                         <itemtemplate>
                                             <asp:ImageButton ID="btnDelete" runat="server" CommandName="Delete" Height="20" 
                                                 ImageUrl="~/images/btn_Delete.gif" 
                                                 CommandArgument='<%# Eval("EmpNumber") %>' 
                                                  Width="20" />
                                         </itemtemplate>
                                         <itemstyle horizontalalign="Center" width="2%" />
                                     
                                 </columns>
                                 <alternatingrowstyle backcolor="#FFF5F5" />
                             
                        
 
 <asp:SqlDataSource ID="SqlDataSource1" runat="server"   
    ConnectionString="<%$ConnectionStrings:MPP%>"
    SelectCommand ="Select l.emp_number as EmpNumber,v.Emp_name+' '+v.emp_initial+' ('+l.emp_number+')' as Empname,
Case when UserType='A' then 'Administrator' when UserType='H' then 'HOD' when UserType='HA' then 'HOD Assistant' when UserType='HG' then 'HOD Group' Else 'Project Lead' END as UserType,
l.emp_number  from csLogin L inner join idpeapp.dbo.view_employee V on V.emp_number = l.emp_number order by l.UserType,v.Empname">

VB Code
--------

Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        'If e.CommandName = "Delete" Then
        ' get the EmpNumber of the clicked row
        Dim EmpNumber As Integer = Convert.ToInt32(e.CommandArgument)
        ' Delete the record 
        DeleteRecordByID(EmpNumber)
        'End If
    End Sub
    Private Sub DeleteRecordByID(ByVal EmpNumber As Integer)
        Dim StrQry As String
        ' Delete csLogin table Values for the empnumber
        Conn.Open()
        StrQry = ""
        StrQry = "delete from csLogin where emp_number ='" & EmpNumber & "'"
        Cmd.Connection = Conn
        Cmd.CommandText = StrQry
        Cmd.ExecuteNonQuery()
        Cmd.Dispose()
        'Delete csManpowerAccess table Values for the empnumber
        StrQry = ""
        StrQry = "Delete from csManpowerAccess where emp_number ='" & EmpNumber & "'"
        Cmd.Connection = Conn
        Cmd.CommandText = StrQry
        Cmd.ExecuteNonQuery()
        Cmd.Dispose()
        'Delete idpeapp.dbo.tblreportaccess table Values for the empnumber
        StrQry = ""
        If Session("Dept") = "CL" Then
            StrQry = "Delete from idpeapp.dbo.tblreportaccess where ename ='" & EmpNumber & "' and  report_name = 'Civil Manpower Allocation' "
        ElseIf Session("Dept") = "CM" Then
            StrQry = "Delete from idpeapp.dbo.tblreportaccess where ename ='" & EmpNumber & "' and  report_name = 'Commissioning Manpower Allocation' "
        ElseIf Session("Dept") = "EL" Then
            StrQry = "Delete from idpeapp.dbo.tblreportaccess where ename ='" & EmpNumber & "' and  report_name = 'Electrical Manpower Allocation' "
        ElseIf Session("Dept") = "EG" Then
            StrQry = "Delete from idpeapp.dbo.tblreportaccess where ename ='" & EmpNumber & "' and  report_name = 'Engineering Manpower Allocation' "
        ElseIf Session("Dept") = "EQ" Then
            StrQry = "Delete from idpeapp.dbo.tblreportaccess where ename ='" & EmpNumber & "' and  report_name = 'Equipment Manpower Allocation' "
        ElseIf Session("Dept") = "PQ" Then
            StrQry = "Delete from idpeapp.dbo.tblreportaccess where ename ='" & EmpNumber & "' and  report_name = 'Process Manpower Allocation' "
        ElseIf Session("Dept") = "TR" Then
            StrQry = "Delete from idpeapp.dbo.tblreportaccess where ename ='" & EmpNumber & "' and  report_name = 'Trac Manpower Allocation' "
        End If
        Cmd.Connection = Conn
        Cmd.CommandText = StrQry
        Cmd.ExecuteNonQuery()
        Cmd.Dispose()
        Conn.Close()
    End Sub

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        Dim sno As New Label
        Dim SrNo As Integer
        If e.Row.RowType = DataControlRowType.DataRow Then
            SrNo = (GridView1.PageIndex + 1) * 10 - 9
            sno = e.Row.FindControl("Sno")
            sno.Text = e.Row.RowIndex + SrNo
            sno.DataBind()
        End If
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim l As ImageButton = DirectCast(e.Row.FindControl("btnDelete"), ImageButton)
            l.Attributes.Add("onclick", "javascript:return " & "confirm('Are you sure you want to delete this record " & DataBinder.Eval(e.Row.DataItem, "EmpNumber") & "')")
        End If
    End Sub
Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs) Handles GridView1.RowDeleted
    End Sub
    Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
        Dim empnumber As Integer = CInt(GridView1.DataKeys(e.RowIndex).Value)
        DeleteRecordByID(empnumber)
        LblMessage.Text = "Employee Number Deleted"
        GridView1.DataBind()
    End Sub

Posted 22-Jun-11 19:10pm

gani7787

Updated 22-Jun-11 19:17pm

Prerak Patel

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prerak Patel · Answer 1 · 2011-06-22T20:28:00

You have used <asp:imagebutton id="btnDelete" runat="server" commandname="<b>Delete</b>" xmlns:asp="#unknown"></asp:imagebutton> which by default runs DeleteCommand in DataSource. You have provided just SelectCommand here.

To run DeleteRecordByID,
1. Change the command name <asp:imagebutton id="btnDelete" runat="server" commandname="<b>MyDelete</b>" xmlns:asp="#unknown"></asp:imagebutton>
2. In RowCommand, check for name and run your code.

If e.CommandName = "Delete" Then
  ' get the EmpNumber of the clicked row
  Dim EmpNumber As Integer = Convert.ToInt32(e.CommandArgument)
  ' Delete the record 
  DeleteRecordByID(EmpNumber)
End If

mehdifaster123 · Answer 2 · 2014-03-05T19:51:00

Solution 2

please change CommandName="delete" to CommandName="mydelete"
i sure solve your problem

Posted 5-Mar-14 19:51pm

mehdifaster123