This demonstration will populate a ASP.NET checkbox control, check the matching records saved in a database and then save any changes made by the user
back to the database

          'This first populates a checkboxlist control
with a list of records 
          'then loops back through the control
and checks the checkboxes which have been saved to the database 
          Public
          Sub BindAlertsList() 'This
creates the list of checkboxes for the alert events types Dim strSQLConn As SqlConnection Dim cmd As SqlCommand Dim objReader As SqlDataReader Dim list_id AsInteger = CInt(lbllist_id.Text)
strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
cmd = New SqlCommand("SELECT ' '
+ [AlertEvent] AS AlertEvent, [alert_id] FROM SavedCompanyListAlertValues ORDER BY
AlertEvent", strSQLConn) strSQLConn.Open() cblAlertTypes.DataSource =
cmd.ExecuteReader(CommandBehavior.CloseConnection) cblAlertTypes.DataTextField = "AlertEvent" cblAlertTypes.DataValueField
= "alert_id" cblAlertTypes.DataBind() 'Dim
cmd As SqlCommmand cmd = New SqlCommand("SELECT
* FROM SavedCompanyListAlertTypes WHERE list_id=" & list_id, strSQLConn)
strSQLConn.Open() objReader = cmd.ExecuteReader() While objReader.Read() Dim currentCheckBox As ListItem
= cblAlertTypes.Items.FindByValue(objReader("alert_id").ToString()) IfNot (currentCheckBox IsNothing) Then currentCheckBox.Selected
= TrueEndIfEndWhile strSQLConn.Close() EndSub'This
saves the checked records in the database Sub SaveAlerts_Click(ByVal sender AsObject, ByVal e As System.EventArgs) 'The
first step in the save alerts process is to delete all of the exising ones for the
selected company list DeleteAlertTypes() EndSubSub DeleteAlertTypes() 'The
first step in the save alerts process is to delete all of the exising ones for the
selected company listDim strSQLConn As SqlConnection Dim cmd As SqlCommand Dim SqlText AsStringDim intlist_id AsInteger = CInt(lbllist_id.Text)
strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
SqlText = "p_SavedCompanyList_Delete_AlertTypes" cmd
= New SqlCommand(SqlText) cmd.Connection = strSQLConn cmd.CommandType
= CommandType.StoredProcedure 'input parameters for the sproc cmd.Parameters.Add("@list_id",
SqlDbType.Int).Value = intlist_id Try strSQLConn.Open()
cmd.Connection = strSQLConn cmd.ExecuteNonQuery() Finally strSQLConn.Close() EndTry'Now
that the existing alert types are deleted, we can add the new ones SaveAlertTypes() EndSubSub SaveAlertTypes() Dim strSQLConn As SqlConnection Dim cmd As SqlCommand Dim SqlText AsStringDim intlist_id AsInteger = CInt(lbllist_id.Text) 'Make
the lblError label invisible in case the user is resubmitting lblError.Visible
= False strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
SqlText = "p_SavedCompanyList_Save_AlertTypes"Try strSQLConn.Open() ForEach Item As ListItem In cblAlertTypes.Items If (Item.Selected) Then cmd
= New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLConn 'input parameters for the sproc cmd.Parameters.Add("@list_id",
SqlDbType.Int).Value = intlist_id cmd.Parameters.Add("@alertevents",
SqlDbType.Int).Value = Item.Value cmd.ExecuteNonQuery() EndIfNextFinally strSQLConn.Close()
lblSaveAlerts.Visible = TrueEndTryEndSub <asp:CheckboxList
ID="cblAlertTypes" runat="server" CssClass="smalltextNoUnderline" BorderStyle="Solid" BorderWidth="2px">
</asp:CheckboxList> CREATE PROCEDURE [dbo].[p_SavedCompanyList_Delete_AlertTypes]
@list_id int AS BEGIN SET NOCOUNT ON;
DELETE FROM SavedCompanyListAlertTypes WHERE list_id = @list_id END CREATE
PROCEDURE p_SavedCompanyList_Save_AlertTypes @list_id int, @alertevents int AS BEGIN
INSERT INTO SavedCompanyListAlertTypes (list_id, alertevents) VALUES (@list_id, @alertevents) END GO 

A very nice little free tool from RedGate software which gives you the ability to search through all of your SQL objects.  Examples from RedGate on how it is useful include:

You want to rename one of your table columns but aren't sure what stored procedures reference it. Using SQL Search, you can search for the column name and find all the stored procedures where it is used.

Finding anything in the SSMS object tree requires a lot of clicking. Using SQL Search, you can press the shortcut combo, start typing the name, and jump right there.

You need to find stored procedures you’ve not yet finished writing. Using SQL Search, you can search for stored procedures containing the text 'TODO'.

You are a DBA, and developers keep using 'SELECT *' in their views and stored procedures. You want to find all these and replace them with a correct list of columns to improve performance and prevent future bugs. Using SQL Search, you can look for 'SELECT *' in the text of stored procedures and views.

You can download the free tool here.

          
            

<
          asp:HyperLinkField
          HeaderText
          =""
          Text
          ="View"
          DataNavigateUrlFields
          ="list_id,ListName"
          DataNavigateUrlFormatString
          ="list.view.aspx?list_id={0}&ListName={1}"
          >
          </
          asp:HyperLinkField
          >
        

Here’s a simple query to find the Nth Maximum and Minimum values in a SQL Server table column using the Row_Number() function. We will find the 2nd highest and 3rd lowest values in the column.

DECLARE @tmp TABLE(id integer, amount integer)
INSERT INTO @tmp values(4, 9543)
INSERT INTO @tmp values(6, 34)
INSERT INTO @tmp values(3, 54)
INSERT INTO @tmp values(2, 6632)
INSERT INTO @tmp values(5, 645)
INSERT INTO @tmp values(1, 1115)
INSERT INTO @tmp values(7, 345)

-- FIND Nth Maximum value SELECT
id, amount FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount DESC) AS highest
FROM @tmp) as x WHERE highest = 2 -- FIND Nth Minimum value SELECT
id, amount FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount ASC) AS lowest
FROM @tmp) as x WHERE lowest = 3

To get an accurate value of all the rows in a SQL Server table, use DBCC UPDATEUSAGE. Here’s a query that first uses DBCC UPDATEUSAGE and then count all the rows in all the tables of a database using the undocumented stored procedure sp_msForEachTable.

          DECLARE @DynSQL NVARCHAR(255) SET @DynSQL
= 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'EXEC(@DynSQL) EXEC sp_msForEachTable'SELECT
PARSENAME(''?'', 1) as TableName,COUNT(*)
as NumberOfRows FROM ?'

Here’s a query that converts a Date to a String in SQL Server:

          DECLARE @Dt as DateTime

SET @Dt = '2010-02-22 11:45:17'SELECTCONVERT(CHAR(8),
@Dt, 112)+ REPLACE(CONVERT(CHAR(8),
@Dt, 114), ':', '')

In the query shown above, the style value 112 gives an output of yymmdd and a style value 114, gives an output of hh:mi:ss:mmm(24h). To display the milliseconds too, change Char(8) to Char(12).

Here is a working demo of this code.

<%@ Page Language="VB" %>
<!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> <meta http-equiv="Content-Type" content="text/html;
charset=utf-8" /> <title>jQuery Image Slideshow</title>
<script src="http://code.jquery.com/jquery-1.4.1-vsdoc.js" type="text/javascript"></script>
<script type="text/javascript"> $(function()
{ var imgs = [ '../images/1.jpg','../images/2.jpg','../images/3.jpg','../images/4.jpg']; var
cnt = imgs.length; var $imageSlide = $('img[id$=imageSlide]'); // set the
image control to the last image $imageSlide.attr('src',
imgs[cnt - 1]); setInterval(Slider, 4000); function Slider()
{ $imageSlide.fadeOut("slow", function ()
{ $(this).attr('src', imgs[(imgs.length++) % cnt]).fadeIn("slow"); });
} }); </script> </head> <body> <form runat="server" id="form1">
<div class="smallDiv">
<h2>Image Slide Show - Image Changes Every 4 Seconds</h2><br />
<asp:Image ID="imageSlide" runat="server"class="imgdiv" />
</div> </form> </body> </html>

Here is a working demo of this example.

<%@ Page Language="VB" %>
<!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> <meta http-equiv="Content-Type" content="text/html;
charset=utf-8" /> <title>jQuery Alpha Characters Only</title>
<script src="http://code.jquery.com/jquery-1.4.1-vsdoc.js" type="text/javascript"></script>
<script type="text/javascript"> $(function
() { $(".gv input.rate").bind('keyup
blur', function (e) { if (this.value.match(/[^a-zA-Z
]/g)) { this.value = this.value.replace(/[^a-zA-Z
]/g, ''); } }); }); </script> </head> <body>
<form runat="server" id="form1">
<asp:SqlDataSource id="srcMovies" ConnectionString="<%$
ConnectionStrings:MyDatabase %>" SelectCommand="SELECT
Id,Title,Director FROM Movies" Runat="server" />
<h3>Using a Gridview</h3> <div class="tableDiv">
<asp:GridView id="grdMovies" DataSourceID="srcMovies" DataKeyNames="Id" AutoGenerateColumns="false" Runat="server"class="gv">
<Columns> <asp:BoundField DataField="Id" HeaderText="Id" />
<asp:BoundField DataField="Title" HeaderText="Title" />
<asp:TemplateField HeaderText="Comments">
<ItemTemplate> <asp:TextBox id="txtComments" runat="server" MaxLength="25"class="rate" />
</ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>
<p id="para"></p> </div> </form>
</body> </html>

Here is a working demo on how this works.

          <%@ Page Language="VB" %>
          <!
          DOCTYPE
          html
          PUBLIC
          "-//W3C//DTD
XHTML 1.0 Transitional//EN"
          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
          >
          <
          script
          runat
          ="server"
          > Sub
grdMovies_PreRender(ByVal sender As Object, ByVal e As EventArgs) If grdMovies.Rows.Count
> 0 Then grdMovies.UseAccessibleHeader = True 'Adding the <thead> and <tbody>
elements grdMovies.HeaderRow.TableSection = TableRowSection.TableHeader End If End
Sub </script> <html
xmlns="http://www.w3.org/1999/xhtml"> <head>
<meta http-equiv="Content-Type" content="text/html;
charset=utf-8" /> <title>jQuery Gridview Cell Contents</title>
<style type="text/css"> .highlite { background-color:Gray;
} </style> <script src="http://code.jquery.com/jquery-1.4.1-vsdoc.js" type="text/javascript"></script>
<script type="text/javascript"> $(function()
{ $(".gv > tbody > tr:not(:has(table, th))")
.css("cursor", "pointer")
.click(function(e) { $(".gv td").removeClass("highlite"); var $cell
= $(e.target).closest("td"); $cell.addClass('highlite'); var $currentCellText
= $cell.text(); var $leftCellText = $cell.prev().text(); var $rightCellText
= $cell.next().text(); var $colIndex = $cell.parent().children().index($cell); var $colName
= $cell.closest("table").find('th:eq(' +
$colIndex + ')').text(); $("#para").empty().append("<b>Current
Cell Text: </b>"+ $currentCellText + "<br/>")
.append("<b>Text to Left of Clicked Cell: </b>"+
$leftCellText + "<br/>") .append("<b>Text
to Right of Clicked Cell: </b>" + $rightCellText + "<br/>")
.append("<b>Column Name of Clicked Cell: </b>" +
$colName) }); }); </script></head><body><formrunat="server"id="form1"><asp:SqlDataSourceid="srcMovies"ConnectionString="<%$
ConnectionStrings:MyDatabase %>"SelectCommand="SELECT
Id,Title,Director FROM Movies"Runat="server"/><h3>Using
a Gridview</h3><divclass="tableDiv"><asp:GridViewid="grdMovies"DataSourceID="srcMovies"DataKeyNames="Id"OnPreRender="grdMovies_PreRender"Runat="server"class="gv"/><pid="para"></p></div></form></body></html>

We want to select only those rows which are inside the TBODY:

$(".gv > tbody > tr:not(:has(table,
th))") 

We have used ‘e.target’ to find out the element that was clicked. This object is cached in the ‘cell’ variable:

var $cell = $(e.target).closest("td"); 

The closest() as given in the jQuery documentation, “works by first looking at the current element to see if it matches the specified expression, if so it just returns the element itself. If it doesn't match then it will continue to traverse up the document, parent by parent, until an element is found that matches the
specified expression.”

With the help of the ‘cell’ variable, we can use the DOM tree traversal methods like prev() and next(), to retrieve the value of the immediate ‘preceding’ and ‘following’ elements, respectively.

var $leftCellText = $cell.prev().text();
var $rightCellText = $cell.next().text(); 

Similarly the column header text is retrieved using the code shown below.

var $colIndex = $cell.parent().children().index($cell);
var $colName = $cell.closest("table").find('th:eq(' +
$colIndex + ')').text(); 

As shown above, after retrieving the column index, we use the closest() method to traverse up the DOM, parent by parent until we find the table element. The Header text is then selected using:

('th:eq(' + $colIndex + ')').text()

Public Function GeneratePassword(ByVal PwdLength As Integer) As String
    Dim _allowedChars As String = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789"
    Dim rndNum As New Random()
    Dim chars(PwdLength - 1) As Char
    Dim strLength As Integer = _allowedChars.Length
    For i As Integer = 0 To PwdLength - 1
        chars(i) = _allowedChars.Chars(CInt(Fix((_allowedChars.Length) * rndNum.NextDouble())))
    Next i
    Return New String(chars)
End Function

Here are some simple T-SQL examples  I came across that I wasn’t aware of.

          Select * from tblTest Where TestName Like'[ABC]%' (Returnallrowsof name startwith A
/ B / C) Select * from tblTest Where TestName Like'[^ABC]%' (Returnallrowsof name notstartwith A and B and C) Select * into #test from tblTest
(Createtemporarytable #test and insert all records from tblTest) Select db_name()
(shows the database name which you are working on) Select@@Servername (Shows
name of the server)

Many of the data controls used by ASP.NET support built-in table sorting but they use a lot of resources.  Even using the ASP.NET AJAX controls and UpdatePanels can be a little clumsy. 

A better solution is to use the jQuery plugin tablesorter. Here are a couple of examples on how to use this plugin with both a Gridview and a Listview.

First, here is a working demo of the code below.

          <%@ Page Language="VB" %>
          <!
          DOCTYPE
          html
          PUBLIC
          "-//W3C//DTD
XHTML 1.0 Transitional//EN"
          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
          >
          <
          script
          runat
          ="server"
          > 'By
default the gridview does not include the <thead> and <tbody> tags Sub
grdMovies_PreRender(ByVal sender As Object, ByVal e As EventArgs) If grdMovies.Rows.Count
> 0 Then grdMovies.UseAccessibleHeader = True 'Adding the <thead> and <tbody>
elements grdMovies.HeaderRow.TableSection = TableRowSection.TableHeader End If End
Sub </script> <html
xmlns="http://www.w3.org/1999/xhtml"> <head>
<meta http-equiv="Content-Type" content="text/html;
charset=utf-8" /> <title>jQuery Table Sorter</title> <script
src="http://code.jquery.com/jquery-1.4.1-vsdoc.js" type="text/javascript"></script>
<script src="jquery/jquery.tablesorter.min.js" type="text/javascript"></script>
<script type="text/javascript"> $(function ()
{ // make the Listview table sortable $("#moviesTable").tablesorter(); //
make the Gridview table sortable $("#grdMovies").tablesorter();
}); </script></head><body><formrunat="server"id="form1"><asp:SqlDataSourceid="srcMovies"ConnectionString="<%$
ConnectionStrings:xxxxx %>"SelectCommand="SELECT
Id,Title,Director FROM Movies"Runat="server"/><h3>Using
a Listview</h3><asp:ListViewID="lstMovies"DataSourceID="srcMovies"runat="server"><LayoutTemplate><tableid="moviesTable"><thead><tr><thtitle="Sort
by Id">Id</th><thtitle="Sort
by Title">Title</th><thtitle="Sort
by Director">Director</th></tr></thead><tbody><asp:PlaceHolderID="ItemPlaceholder"runat="server"/></tbody></table></LayoutTemplate><ItemTemplate><tr><td><%#
Eval("Id") %></td><td><%#
Eval("Title")%></td><td><%#
Eval("Director")%></td></tr></ItemTemplate></asp:ListView><h3>Using
a Gridview</h3><asp:GridViewid="grdMovies"DataSourceID="srcMovies"DataKeyNames="Id"OnPreRender="grdMovies_PreRender"Runat="server"/></form></body></html>

By default the Gridview does not use these tags to create its table and often times you will need these elements in client-side scripts:

<table id="myTable"> <thead>
<tr> <th>Last Name</th> <th>First Name</th> <th>Email</th>
<th>Due</th> <tbody> <tr> <td>Smith</td> <td>John</td>
<td>jsmith@gmail.com</td> <td>$50.00</td> </tr> </tobdy>
</table> 

The easiest way to achieve is is to use the page Pre_Render event:

          protected
          void gv_PreRender(object sender,
EventArgs e) { if (gv.Rows.Count > 0) { //Replacing
<td> with <th> - just in case gv.UseAccessibleHeader = true; //Adding
the <thead> and <tbody> elements gv.HeaderRow.TableSection = TableRowSection.TableHeader; //This
line can be put if you also have footer, it will add <tfoot> element //If
you don't have footer, remove it gv.FooterRow.TableSection = TableRowSection.TableFooter;
} }

Here are some examples of stored procedures on how to do this

http://slashgeek.com/?p=105