This is a migrated thread and some comments may be shown as answers.

how to delete a record in a database from a grid

7 Answers 443 Views
Grid
This is a migrated thread and some comments may be shown as answers.
David
Top achievements
Rank 1
David asked on 12 May 2011, 08:00 PM
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

7 Answers, 1 is accepted

Sort by
0
Gimmik
Top achievements
Rank 1
answered on 12 May 2011, 10:29 PM
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
0
David
Top achievements
Rank 1
answered on 13 May 2011, 02:43 PM
Thank you, it seem to be pretty usefull
0
David
Top achievements
Rank 1
answered on 13 May 2011, 03:40 PM
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 '?'.
0
Gimmik
Top achievements
Rank 1
answered on 13 May 2011, 04:36 PM
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
0
David
Top achievements
Rank 1
answered on 13 May 2011, 04:44 PM
Thanks Gimmik

This time, I try it and everything work fine for real. Really, a big thank you. You're awesome
0
Elliott
Top achievements
Rank 2
answered on 13 May 2011, 04:44 PM
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
0
iqra
Top achievements
Rank 1
answered on 10 Jul 2017, 12:25 PM

my delete button and update not working but in php can u please tell me 

here is my grid code

<!DOCTYPE html>
<html>
<head>
<title></title>
<link rel="stylesheet" href="styles/kendo.common.min.css" />
<link rel="stylesheet" href="styles/kendo.default.min.css" />
<link rel="stylesheet" href="styles/kendo.default.mobile.min.css" />

<script src="js/jquery.min.js"></script>
<script src="js/kendo.all.min.js"></script>
</head>

<!--<body>-->
<!--<div id="example">-->
<!-- <div id="grid"></div>-->
<!---->
<!-- <script>-->
<!-- $(document).ready(function () {-->
<!--// var crudServiceBaseUrl = "https://demos.telerik.com/kendo-ui/service",-->
<!-- dataSource = new kendo.data.DataSource({-->
<!-- transport: {-->
<!-- read: {-->
<!-- url: "data/employees.php" ,-->
<!-- dataType: "json"-->
<!-- },-->
<!-- update: {-->
<!-- url: "data/employees.php",-->
<!-- dataType: "json"-->
<!-- },-->
<!-- destroy: {-->
<!-- url: "data/employees.php",-->
<!-- dataType: "json"-->
<!-- },-->
<!-- create: {-->
<!-- url: "data/employees.php",-->
<!-- dataType: "json"-->
<!-- },-->
<!-- parameterMap: function(options, operation) {-->
<!-- if (operation !== "read" && options.models) {-->
<!-- return {models: kendo.stringify(options.models)};-->
<!-- }-->
<!-- }-->
<!-- },-->
<!-- batch: true,-->
<!-- pageSize: 20,-->
<!-- schema: {-->
<!---->
<!-- model: {-->
<!-- id: "ProductID",-->
<!-- fields: {-->
<!-- ProductID: { editable: false, nullable: true },-->
<!-- ProductName: { validation: { required: true } },-->
<!-- UnitPrice: { type: "number", validation: { required: true, min: 1} },-->
<!-- Discontinued: { type: "boolean" },-->
<!-- UnitsInStock: { type: "number", validation: { min: 0, required: true } }-->
<!-- }-->
<!-- }-->
<!-- }-->
<!-- });-->
<!---->
<!-- $("#grid").kendoGrid({-->
<!-- dataSource: dataSource,-->
<!-- pageable: true,-->
<!-- height: 550,-->
<!-- toolbar: ["create"],-->
<!-- columns: [-->
<!-- "ProductName",-->
<!-- { field: "UnitPrice", title: "Unit Price", format: "{0:c}", width: "120px" },-->
<!-- { field: "UnitsInStock", title:"Units In Stock", width: "120px" },-->
<!-- { field: "Discontinued", width: "120px", editor: customBoolEditor },-->
<!-- { command: ["edit", "destroy"], title: "&nbsp;", width: "250px" }],-->
<!-- editable: "inline"-->
<!-- });-->
<!-- });-->
<!---->
<!-- function customBoolEditor(container, options) {-->
<!-- $('<input class="k-checkbox" type="checkbox" name="Discontinued" data-type="boolean" data-bind="checked:Discontinued">').appendTo(container);-->
<!-- $('<label class="k-checkbox-label">&#8203;</label>').appendTo(container);-->
<!-- }-->
<!-- </script>-->
<!--</div>-->
<!---->
<!---->
<!--</body>-->

<body> <div id="grid"></div> <script>
$(function() {
$("#grid").kendoGrid({
dataSource: {
transport: {
read: {
url: "data/employees.php" ,
dataType: "json"

},
update: {
url: "data/employees.php",
dataType: "json",
type: "POST"
},
destroy: {
url: "data/employees.php",
dataType: "json",
type: "POST"
},
create: {
url: "data/employees.php",
dataType: "json",
type: "POST"
},

},

batch: true,
pageSize: 20,
schema: {
data: "data",
model: {
id: "productid",
fields: {
ProductID: { editable: false, nullable: true },
ProductName: { validation: { required: true } },
UnitPrice: { type: "number", validation: { required: true, min: 1} },
Discontinued: { type: "boolean" },
UnitsInStock: { type: "number", validation: { min: 0, required: true } }
}
}
}

},
pageable: true,
height: 550,
toolbar: ["create"],
columns: [
{ field: "productname" },
{ field: "unitprice" },
{ field: "unitsinstock" }, { field: "discontinued" },
{ command: ["edit", "destroy"], title: "&nbsp;", width: "250px" }],
editable: "inline"

});
// function customBoolEditor(container, options) {
// $('<input class="k-checkbox" type="checkbox" name="Discontinued" data-type="boolean" data-bind="checked:Discontinued">').appendTo(container);
// $('<label class="k-checkbox-label">&#8203;</label>').appendTo(container);
// }


//
});
</script> </body>

</html>

 

 and here is my php

 

<?php
$link = mysql_pconnect("localhost", "root", "") or die("Unable To Connect To Database Server");
mysql_select_db("pro") or die("Unable To Connect To Northwind");

header("Content-type: application/json");


$verb = $_SERVER["REQUEST_METHOD"];
// handle a GET

$arr = array();
$rs = mysql_query("SELECT productname, unitprice,unitsinstock,discontinued FROM products");

while ($obj = mysql_fetch_object($rs)) {
$arr[] = $obj;
}

// add the header line to specify that the content type is JSON

echo "{\"data\":" .json_encode($arr). "}";
?>

<?php


// handle a POST
if ($verb == "POST") {
$productid = mysql_real_escape_string($_POST["productid"]);
$productname = mysql_real_escape_string($_POST["productname"]);
$unitprice = mysql_real_escape_string($_POST["unitprice"]);
$unitsinstock = mysql_real_escape_string($_POST["unitsinstock"]);
$discontinued = mysql_real_escape_string($_POST["discontinued"]);


$rs = mysql_query("UPDATE products SET productname = '" .$productname ."',unitprice = '" .$unitprice ."',unitsinstock = '" .$unitsinstock ."',discontinued = '" .$discontinued ."' WHERE productid = '".$productid."'" );

if ($rs) {
echo json_encode($rs);
}
else {
header("HTTP/1.1 500 Internal Server Error");
echo "Update failed for EmployeeID: " .$employeeId;
}
}
//
////// insert

if ($verb == "POST") {
$productname = mysql_real_escape_string($_POST["productname"]);
$unitprice = mysql_real_escape_string($_POST["unitprice"]);
$unitsinstock = mysql_real_escape_string($_POST["unitsinstock"]);
$discontinued = mysql_real_escape_string($_POST["discontinued"]);
$productid = mysql_real_escape_string($_POST["productid"]);

$rs = mysql_query("insert into products (productname, unitprice,unitsinstock,discontinued) VALUES (111,'" .$productname ."','" . $unitprice ."','" . $unitsinstock ."','" . $discontinued ."')");

if ($rs) {
echo json_encode($rs);
}
else {
header("HTTP/1.1 500 Internal Server Error");
echo "inserted failed for EmployeeID: " .$employeeId;
}
}
////delete
//
//hidden('id', $Get_id['id']);
//$id = $Get_id['id'];
// handle a POST
if ($verb == "POST") {

// $productname = mysql_real_escape_string($_POST["productname"]);
// $unitprice = mysql_real_escape_string($_POST["unitprice"]);
// $unitsinstock = mysql_real_escape_string($_POST["unitsinstock"]);
// $discontinued = mysql_real_escape_string($_POST["discontinued"]);
$productid = mysql_real_escape_string($_POST["productid"]);


$rs = mysql_query("delete from products WHERE productid = ".$productid);

if ($rs) {
echo json_encode($rs);
}
else {
header("HTTP/1.1 500 Internal Server Error");
echo "deleted failed for productid: " .$productid;
}
}

?>

 

Tags
Grid
Asked by
David
Top achievements
Rank 1
Answers by
Gimmik
Top achievements
Rank 1
David
Top achievements
Rank 1
Elliott
Top achievements
Rank 2
iqra
Top achievements
Rank 1
Share this question
or