how to delete a record in a database from a grid

7 posts, 0 answers
  1. David
    David avatar
    81 posts
    Member since:
    May 2011

    Posted 12 May 2011 Link to this post

    Hi everyone,


    I have a very stupid question. How do you delete a record in a grid and also delete this record from the database. I read a lots of examples, but they only delete the item from the grid. I read a interesting example where they were using a SessionDataSource, but my IDE (visual studio with Telerik, of course) didn't recognize the tag. I was thinking about simply had a column with the primary key and take the primary key to make a delete statement, but I don't want to show my primary key (cause it is a guid and it's not really pretty to show). So, is to possible to have a hidden column in a grid. If yes, just tell me how and my problem is solved. 


    Thank you in advance for your answer.  


    David
  2. Gimmik
    Gimmik avatar
    170 posts
    Member since:
    May 2011

    Posted 12 May 2011 Link to this post

    Hi David,

    There are no stupid questions! There are several ways to do this depending on your setup. If you're using a declarative datasource in your ASPX page, deleting a row is trivial. The SQL delete statement is actually stored as part of the ASP SqlDataSource, so you need not display all the key columns. Telerik has a great document that shows how to accomplish.

    Here is the document:
    http://www.telerik.com/help/aspnet-ajax/grid-automatic-datasource-operations.html

    Notice the column from OrderID isn't displayed, but is still bound to the RadGrid, and is also used as a parameter to the delete statement.

    <telerik:GridBoundColumn DataField="OrderID" HeaderText="Order ID" ReadOnly="True"
            UniqueName="OrderID" Display="False" />

    Hope this helps!
    -Gimmik
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. David
    David avatar
    81 posts
    Member since:
    May 2011

    Posted 13 May 2011 Link to this post

    Thank you, it seem to be pretty usefull
  5. David
    David avatar
    81 posts
    Member since:
    May 2011

    Posted 13 May 2011 Link to this post

    Hi again

    I read the documentation page and I tried to run the example that was on the page, but it doesn't. I guess it is missing a little something, but I don't know what. The only thing I change is the accessDataSource for a SqlDataSource, but according to what I read, it doesn't suppose to make a difference. In any case, there is my complete code (there is no code-behind) :

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Default" %>
     
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
     
    <head runat="server">
        <title></title>
        <telerik:RadStyleSheetManager id="RadStyleSheetManager1" runat="server" />
    </head>
    <body>
        <form id="form1" runat="server">
        <telerik:RadScriptManager ID="RadScriptManager1" runat="server">
            <Scripts>
                <%--Needed for JavaScript IntelliSense in VS2010--%>
                <%--For VS2008 replace RadScriptManager with ScriptManager--%>
                <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js" />
                <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js" />
                <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js" />
            </Scripts>
        </telerik:RadScriptManager>
        <script type="text/javascript">
            //Put your JavaScript code here.
        </script>
        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        </telerik:RadAjaxManager>
     
        <telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" PageSize="5" Skin="Hay"
          DataSourceID="sqlDataSource1" AllowAutomaticInserts="True" AllowAutomaticUpdates="True"
          AllowAutomaticDeletes="True">
          <MasterTableView EditMode="InPlace" CommandItemDisplay="Bottom" DataSourceID="sqlDataSource1"
            DataKeyNames="OrderID" AutoGenerateColumns="False">
            <EditFormSettings>
              <EditColumn UniqueName="EditCommandColumn1" />
              <PopUpSettings ScrollBars="None" />
            </EditFormSettings>
            <Columns>
              <telerik:GridEditCommandColumn />
              <telerik:GridButtonColumn CommandName="Delete" Text="Delete" UniqueName="DeleteColumn" />
              <telerik:GridBoundColumn DataField="OrderID" HeaderText="Order ID" ReadOnly="True"
                UniqueName="OrderID" />
              <telerik:GridBoundColumn DataField="CustomerID" HeaderText="Customer ID" UniqueName="CustomerID" />
              <telerik:GridBoundColumn DataField="EmployeeID" HeaderText="Employee ID" UniqueName="EmployeeID">
              </telerik:GridBoundColumn>
              <telerik:GridDateTimeColumn DataField="OrderDate" HeaderText="Order Date" UniqueName="OrderDate"
                PickerType="DatePicker" />
              <telerik:GridBoundColumn DataField="ShipName" HeaderText="Shipping Name" UniqueName="ShipName" />
            </Columns>
          </MasterTableView></telerik:RadGrid>
           
          <asp:SqlDataSource ID="sqlDataSource1"
            runat="server" SelectCommand="SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [ShipName] FROM [Orders]"
                           DeleteCommand="DELETE from [ORDERS] WHERE [OrderID] = ?"
                           ConnectionString="<%$ ConnectionStrings:ConnectionString %>">
            <UpdateParameters>
              <asp:Parameter Name="CustomerID" Type="String" />
              <asp:Parameter Name="EmployeeID" Type="Int32" />
              <asp:Parameter Name="OrderDate" Type="DateTime" />
              <asp:Parameter Name="OrderID" Type="Int32" />
              <asp:Parameter Name="ShipName" Type="String" />
            </UpdateParameters>
            <InsertParameters>
              <asp:Parameter Name="OrderID" Type="Int32" />
              <asp:Parameter Name="CustomerID" Type="String" />
              <asp:Parameter Name="EmployeeID" Type="Int32" />
              <asp:Parameter Name="OrderDate" Type="DateTime" />
              <asp:Parameter Name="ShipName" Type="String" />
            </InsertParameters>
            <DeleteParameters>
              <asp:Parameter Name="OrderID" Type="Int32" />
            </DeleteParameters>
          </asp:SqlDataSource>
     
        </form>
    </body>
    </html>
    So, anybody can tell me what I'm doing wrong ? 
    Thank you.

    Ps: my problem is that when I try to delete a row, a error page appears and says " Incorrect syntax near '?'.
  6. Gimmik
    Gimmik avatar
    170 posts
    Member since:
    May 2011

    Posted 13 May 2011 Link to this post

    Hi David,

    You are very very close. I think the issue here might be a slight ambiguity in Telerik's documentation. The "?" in the DeleteCommand isn't part of a well formed SQL statement. You will need to reference the parameter passed to the SqlDataSource, i.e. "@OrderID".

    However, that still won't work because of the way the NorthWind sample database is designed. (Try and once - just for fun!) Basically, the [Order Details] table has a foreign key [OrderID] that references the [Orders] table. So you will be unable to delete an order without first deleting the details. This is actually a good thing because otherwise you would break your referential integrity and orphan your Order Details record i.e. you'll have details for an order that no longer exists!

    Anyway, you can just delete the order details first, then delete the order. Here's the new DeleteCommand:

    DeleteCommand="DELETE from [Order Details] WHERE [OrderID] = @OrderID;DELETE from [Orders] WHERE [OrderID] = @OrderID"

    I'll leave it as an exercise for you to implement updates. Check the telerik documentation I sent before - but pay special attention to this property on the SqlDataSource. This one took me a while when I first tried this.

    OldValuesParameterFormatString="original_{0}"


    Hope this helps,
    -Gimmik
  7. David
    David avatar
    81 posts
    Member since:
    May 2011

    Posted 13 May 2011 Link to this post

    Thanks Gimmik

    This time, I try it and everything work fine for real. Really, a big thank you. You're awesome
  8. Elliott
    Elliott avatar
    391 posts
    Member since:
    May 2010

    Posted 13 May 2011 Link to this post

    not a stupid question

    a good idea is to wire up a confirm in javascript to the delete button on the grid

    another way is to respond to the Delete Command in code
    <telerik:GridButtonColumn UniqueName="DeleteColumn" CommandName="Delete" Text="Delete" />       
    Protected Sub gvStores_ItemCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs) Handles gvStores.ItemCommand
        Dim gdItem As GridDataItem = Nothing
        Select Case e.CommandName
            Case "Delete"
                gdItem = DirectCast(e.Item, GridDataItem)
                DeleteStores(gdItem)
        End Select
    End Sub
     
    Private Sub DeleteStores(ByVal gdItem As GridDataItem)
        Dim StoreNumber As Int64
        Dim ws As CommonFunctions
     
        StoreNumber = CInt(gdItem.OwnerTableView.DataKeyValues(gdItem.ItemIndex)("StoreNumber"))
        ws = New CommonFunctions
        ws.DeleteStore(StoreNumber)
    End Sub
    Imports System.Data
    Imports System.Net.Mail
    Imports System.IO
    Imports System.Xml
    Imports System.Data.SqlClient
    Imports Microsoft.VisualBasic
    Imports System.Diagnostics
    Imports System.Reflection

    Public Class CommonFunctions
          Dim strConn as String = "connection string foes here"

    Public
    Sub DeleteStore(ByVal StoreNumber As Int64)
        Dim conn As SqlConnection = Nothing
        Dim cmd As SqlCommand = Nothing
        Dim paramStores As SqlParameter = Nothing
     
        conn = New SqlConnection(strConn)
        cmd = New SqlCommand("delete_store", conn)
        cmd.CommandType = CommandType.StoredProcedure
        paramStores = New SqlParameter("@StoreNumber", SqlDbType.BigInt)
        paramStores.Value = StoreNumber
        cmd.Parameters.Add(paramStores)
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            SendErrorEmail(ex.Message)
        Finally
            conn.Close()
            cmd = Nothing
            conn = Nothing
        End Try
    End Sub
    End Class
    CREATE PROCEDURE [dbo].[delete_store]
        @StoreNumber BIGINT
    AS
    BEGIN
        DELETE FROM ShowStores
        WHERE StoreNumber=@StoreNumber
    END
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017