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 491 Views

Grid

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

7 Answers, 1 is accepted

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

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">

<html xmlns="http://www.w3.org/1999/xhtml">

<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 '?'.

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

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

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="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: " ", 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"></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;
}
}

?>