Stone Coast Web Design Blog

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 As Integer = 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())
            If Not (currentCheckBox Is Nothing) Then
                currentCheckBox.Selected = True
            End If
        End While
        strSQLConn.Close()
End Sub 

'This saves the checked records in the database 

Sub SaveAlerts_Click(ByVal sender As Object, 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()
End Sub
    Sub DeleteAlertTypes()
        'The first step in the save alerts process is to delete all of the exising ones for the selected company list
        Dim strSQLConn As SqlConnection
        Dim cmd As SqlCommand
        Dim SqlText As String
        Dim intlist_id As Integer = 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()
        End Try
        'Now that the existing alert types are deleted, we can add the new ones
        SaveAlertTypes()
    End Sub 

Sub SaveAlertTypes()
        Dim strSQLConn As SqlConnection
        Dim cmd As SqlCommand
        Dim SqlText As String
        Dim intlist_id As Integer = 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()
            For Each 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()
                End If
            Next
        Finally
            strSQLConn.Close()
            lblSaveAlerts.Visible = True
        End Try
End Sub 

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

This article demonstrates how to view and edit records in a table using the GridView, EntityDataSource, and Validator controls.  The article also demonstrates how to generate an ADO.NET Entity Data Model to update and delete records in a table.

I’m trying to learn LINQ and came across this page of useful examples in VB

http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

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'
SELECT CONVERT(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>