Category Archives: VB.NET

Standard

Posted by

Posted on

May 18, 2009

Posted under

ASP.NET, VB.NET

Comments

Leave a comment

LINQ – InActivating Records As Opposed to Deleting Them

Whilst there are always arguments for being able to delete records from a database, I have always preferred to InActivate my records as opposed to deleting them thus maintaining the referential integrity, history etc. With that in mind I thought I would pen a really quick post about how to do this using LINQ and VB.NET hopefully writing as little code as possible.

Solution

Consider the following (incredibly simplistic) Countries database table:

Column Name Data Type Details
ID INT Primary Key, Identity
Active BIT Not Null, Default 1
Name NVARCHAR(50) Not Null

Using Visual Studio 2008 we create a standard ASP.NET 3.5 web project called Example and add a Linq To SQL class as shown below:

linq-inactivate-01

Using the Server Explorer we simply drag the Countries table onto the design surface of the Example.dbml file as shown below:

linq-inactivate-02

We save that file and modify the Default.aspx file to include a simple Gridview and accompanying LinqDataSource:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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>JOATIT - Example</title>
</head>
<body>
<form id="frm" runat="server">
<div>
<asp:GridView ID="grvRecords" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" DataSourceID="ldsRecords">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:CheckBoxField DataField="Active" HeaderText="Active"
SortExpression="Active" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True"
ShowHeader="True" />
</Columns>
</asp:GridView>
<asp:LinqDataSource ID="ldsRecords" runat="server"
ContextTypeName="ExampleDataContext" EnableDelete="True" OrderBy="Name"
TableName="Countries">
</asp:LinqDataSource>
</div>
</form>
</body>
</html>

If we run this as it is the GridView will bind as shown below, but the Delete link will ACTUALLY delete the records from the database which is not what we want:

linq-inactivate-03

Instead what we want to is InActivate them when the Delete event is fired. To do this we simply open the Default.aspx.vb code-behind file and add the following custom event as shown below:

Protected Sub ldsRecords_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceDeleteEventArgs) Handles ldsRecords.Deleting
Dim _recordToBeDeleted As Country = CType(e.OriginalObject, Country)
Dim _db As New ExampleDataContext
Dim _specificRecord = (From _allRecords In _db.Countries _
Where _allRecords.ID = _recordToBeDeleted.ID _
Select _allRecords).Single
_specificRecord.Active = False
_db.SubmitChanges()
e.Cancel = True
End Sub

Now when we click the Delete link, our custom event fire and updates the record in question setting Active = 0, and cancels the Delete event.

Summary

More than happy to learn of a better way of doing this other than with a database trigger (which has its own advantages and disadvantages) so please, feedback (positive or negative) is always welcome.

Standard

Posted by

Posted on

December 31, 2007

Posted under

ASP.NET, VB.NET

Comments

2 Comments

ASP.NET Duration UserControl

Recently, I had need for a “duration control” that would allow users to view, edit and update a duration in hours, minutes and seconds. The result of the user selection needed to be stored as an integer in a database (as seconds) and then converted back to hour, minutes and seconds for display. In read only mode the duration needed to be displayed as a single label whilst in edit mode I wanted a reasonably foolproof interface for my users.

I decided to create a ASP.NET UserControl that consisted of, in edit mode, a textbox for the hours, and two dropdownlists for the minutes and seconds, whilst in read only mode, a label as shown below:

Read-Only Mode (used on a ASPX page):

Duration Control - View

Duration Control - View

Edit Mode (used on a ASPX page):

Duration Control - Edit

Duration Control - Edit

The code for this usercontrol is shown below:

HTML

<%@ Control Language="VB" AutoEventWireup="false" CodeFile="ctrlDuration.ascx.vb" Inherits="UserControls_ctrlDuration" %>
<asp:Panel ID="pnlDisplay" runat="server">
<asp:Label ID="lblDuration" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="pnlEdit" runat="server">
Hours:
<asp:TextBox ID="txtHours" runat="server" Text="00"></asp:TextBox>
Minutes:
<asp:DropDownList ID="ddlMinutes" runat="server">
<asp:ListItem Selected="True">0</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem>31</asp:ListItem>
<asp:ListItem>32</asp:ListItem>
<asp:ListItem>33</asp:ListItem>
<asp:ListItem>34</asp:ListItem>
<asp:ListItem>35</asp:ListItem>
<asp:ListItem>36</asp:ListItem>
<asp:ListItem>37</asp:ListItem>
<asp:ListItem>38</asp:ListItem>
<asp:ListItem>39</asp:ListItem>
<asp:ListItem>40</asp:ListItem>
<asp:ListItem>41</asp:ListItem>
<asp:ListItem>42</asp:ListItem>
<asp:ListItem>43</asp:ListItem>
<asp:ListItem>44</asp:ListItem>
<asp:ListItem>45</asp:ListItem>
<asp:ListItem>46</asp:ListItem>
<asp:ListItem>47</asp:ListItem>
<asp:ListItem>48</asp:ListItem>
<asp:ListItem>49</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>51</asp:ListItem>
<asp:ListItem>52</asp:ListItem>
<asp:ListItem>53</asp:ListItem>
<asp:ListItem>54</asp:ListItem>
<asp:ListItem>55</asp:ListItem>
<asp:ListItem>56</asp:ListItem>
<asp:ListItem>57</asp:ListItem>
<asp:ListItem>58</asp:ListItem>
<asp:ListItem>59</asp:ListItem>
</asp:DropDownList>
Seconds:
<asp:DropDownList ID="ddlSeconds" runat="server">
<asp:ListItem Selected="True">0</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem>31</asp:ListItem>
<asp:ListItem>32</asp:ListItem>
<asp:ListItem>33</asp:ListItem>
<asp:ListItem>34</asp:ListItem>
<asp:ListItem>35</asp:ListItem>
<asp:ListItem>36</asp:ListItem>
<asp:ListItem>37</asp:ListItem>
<asp:ListItem>38</asp:ListItem>
<asp:ListItem>39</asp:ListItem>
<asp:ListItem>40</asp:ListItem>
<asp:ListItem>41</asp:ListItem>
<asp:ListItem>42</asp:ListItem>
<asp:ListItem>43</asp:ListItem>
<asp:ListItem>44</asp:ListItem>
<asp:ListItem>45</asp:ListItem>
<asp:ListItem>46</asp:ListItem>
<asp:ListItem>47</asp:ListItem>
<asp:ListItem>48</asp:ListItem>
<asp:ListItem>49</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>51</asp:ListItem>
<asp:ListItem>52</asp:ListItem>
<asp:ListItem>53</asp:ListItem>
<asp:ListItem>54</asp:ListItem>
<asp:ListItem>55</asp:ListItem>
<asp:ListItem>56</asp:ListItem>
<asp:ListItem>57</asp:ListItem>
<asp:ListItem>58</asp:ListItem>
<asp:ListItem>59</asp:ListItem>
</asp:DropDownList>
<asp:CompareValidator ID="cvlDuration" runat="server"
ErrorMessage="Hours must be a whole number" ControlToValidate="txtHours"
Operator="GreaterThanEqual" Type="Integer" ValueToCompare="0">*</asp:CompareValidator>
</asp:Panel>

Code-Behind

Partial Class UserControls_ctrlDuration
Inherits System.Web.UI.UserControl
Public WriteOnly Property DisplayOnly() As Boolean
Set(ByVal value As Boolean)
If value Then
pnlDisplay.Visible = True
pnlEdit.Visible = False
Else
pnlDisplay.Visible = False
pnlEdit.Visible = True
End If
End Set
End Property
Public Property Duration() As Integer
Get
Return CInt(txtHours.Text * 3600) + CInt(ddlMinutes.SelectedValue * 60) + CInt(ddlSeconds.SelectedValue)
End Get
Set(ByVal value As Integer)
txtHours.Text = (value 3600)
ddlMinutes.SelectedValue = ((value 60) Mod 60)
ddlSeconds.SelectedValue = (value Mod 60)
lblDuration.Text = (value 3600) & " hours " & ((value 60) Mod 60) & " minutes " & (value Mod 60) & " seconds"
End Set
End Property
End Class

Then in the ASPX page that makes use of this usercontrol we need to register it:

<%@ Register src="ctrlDuration.ascx" tagname="ctrlDuration" tagprefix="uc1" %>

Finally on the page we can use it inside a formview control for example as shown below (omitting all the other datasource controls etc.):

In the ItemTemplate section we would use it in read only mode:

<uc1:ctrlDuration ID="ctrlResponse" runat="server" Duration='<%# Bind("iResponseDuration") %>' DisplayOnly="True" />

Whilst in the EditItemTemplate and InsertItemTemplate we would use it in edit mode:

<uc1:ctrlDuration ID="ctrlResponse" runat="server" Duration='<%# Bind("iResponseDuration") %>' DisplayOnly="False" />

Hope it may be of use. As always comments (positive and negative) always welcome.