Stone Coast Web Design Blog

Navigation

Search

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

| |

E-mail

Total Posts: 378
This Year: 6
This Month: 1
This Week: 0
Comments: 17

Wednesday, November 18, 2009

SQL Server RSS Reporter

Wednesday, November 18, 2009 9:11:53 PM (GMT Standard Time, UTC+00:00) ( SQL )

RSS Reporter is a very simple tool and that is the beauty of it. In addition of providing one of the best ways to monitor SQL Server jobs it provides something even cooler – it allows you to write any T-SQL query you want and with one click the results of your query are streamed in an elegant, standard RSS feed that you can subscribe to from any device.

http://www.xsqlsoftware.com/Product/Sql_Server_Rss_Reporter.aspx

Comments [0] | |

Monday, November 09, 2009

Search and Filter Dropdownlist

Monday, November 09, 2009 5:31:41 PM (GMT Standard Time, UTC+00:00) ( jQuery )

This is probably one of my all-time favorite jQuery examples. Here is a working demo.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Search and Filter a DropDownList</title>
    <script src="http://ajax.Microsoft.com/ajax/jquery/jquery-1.3.2.js" type="text/javascript"></script>
    
    <script type="text/javascript">
        $(function() {
            var $txt = $('input[id$=txtBox1]');
            var $ddl = $('select[id$=ddlCarModels]');
            var $items = $('select[id$=ddlCarModels] option');
 
            $txt.keyup(function() {
                searchDdl($txt.val());
            });
 
            function searchDdl(item) {
                $ddl.empty();
                var exp = new RegExp(item, "i");
                var arr = $.grep($items,
                    function(n) {
                        return exp.test($(n).text());
                    });
 
                if (arr.length > 0) {
                    countItemsFound(arr.length);
                    $.each(arr, function() {
                        $ddl.append(this);
                        $ddl.get(0).selectedIndex = 0;
                    }
                    );
                }
                else {
                    countItemsFound(arr.length);
                    $ddl.append("<option>No Items Found</option>");
                }
            }
 
            function countItemsFound(num) {
                $("#results").empty();
                if ($txt.val().length) {
                    $("#results").html(num + " items found");
                } 
            }
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2>Enter Text in the TextBox to Filter the DropDownList </h2>
        <br />
        Search Toyota models:<br />
        <asp:TextBox ID="txtBox1" runat="server" ToolTip="Enter Text Here"></asp:TextBox><br />
        <asp:DropDownList ID="ddlCarModels" runat="server" >
            <asp:ListItem Text="Tercel" Value="1"></asp:ListItem>
            <asp:ListItem Text="Camry" Value="2"></asp:ListItem>
            <asp:ListItem Text="Sienna" Value="3"></asp:ListItem>
            <asp:ListItem Text="Corolla" Value="4"></asp:ListItem>
            <asp:ListItem Text="Lexus" Value="5"></asp:ListItem>
            <asp:ListItem Text="Tundra" Value="6"></asp:ListItem>
            <asp:ListItem Text="Yaris" Value="7"></asp:ListItem>
            <asp:ListItem Text="Tacoma" Value="8"></asp:ListItem>
            <asp:ListItem Text="RAV4" Value="9"></asp:ListItem>
            <asp:ListItem Text="Highlander" Value="10"></asp:ListItem>
            <asp:ListItem Text="Land Cruiser" Value="11"></asp:ListItem>
            <asp:ListItem Text="Venza" Value="12"></asp:ListItem>
        </asp:DropDownList>
        <br />
        <p id="results"></p>
        <br /><br />
        Tip: Items get filtered as characters are entered in the textbox.
        Search is not case-sensitive
    </div>
 
    </form>
</body>
</html>

Comments [0] | |

Friday, November 06, 2009

Updating Multiple Fields and Rows in a Repeater

Friday, November 06, 2009 1:43:05 AM (GMT Standard Time, UTC+00:00) ( ASP.NET | SQL )

This is an interesting example useful for many different applications. Suppose you create a repeater containing multiple rows and each field contains various types of controls (textboxes, dropdownlists, checkboxes etc.) and you want to update every field in every row with a single button click. The repeater control has no inherent update capabilities but it can be done.

Sub btnUpdateProductDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs)
      
        Dim id_fieldSelected As Integer
        Dim ProductSelected As Integer
        Dim FeatureSelected As Integer
        Dim SectionSelected As Integer
        Dim createdDateSelected As DateTime
        Dim createdBySelected As String = lblEnteredBy.Text
        Dim IsActiveSelected As Boolean
        Dim ProductLabelSelected As Integer
        
        Dim dataItem As RepeaterItem
        For Each dataItem In rptFieldsInProducts.Items
            
            id_fieldSelected = CType(dataItem.FindControl("lblId_field"), Label).Text
            ProductSelected = CType(dataItem.FindControl("ddlProducts"), DropDownList).SelectedValue
            FeatureSelected = CType(dataItem.FindControl("ddlFeatures"), DropDownList).SelectedValue
            SectionSelected = CType(dataItem.FindControl("ddlSections"), DropDownList).SelectedValue
            createdDateSelected = CType(dataItem.FindControl("lblCreatedDate"), Label).Text
            IsActiveSelected = CType(dataItem.FindControl("chkIsActive"), CheckBox).Checked
            ProductLabelSelected = CType(dataItem.FindControl("ddlProductLabel"), DropDownList).SelectedValue   
            
            'Updates the repeater row
            'The stored procedure that updates this needs these parameter
            'id_field, id_product, id_ProductFeature, id_ProductSection, createdDate, createdBy, IsActive, id_ProductLabel
            
            UpdateProductDetails(id_fieldSelected, ProductSelected, FeatureSelected, SectionSelected, createdDateSelected, _
createdBySelected, IsActiveSelected, ProductLabelSelected)
        Next
    End Sub
    
    Sub UpdateProductDetails(ByVal id_fieldSelected As Integer, ByVal ProductSelected As Integer, ByVal FeatureSelected As Integer, _
                             ByVal SectionSelected As Integer, ByVal createdDateSelected As DateTime, ByVal createdBySelected As String, _
                             ByVal IsActiveSelected As Boolean, ByVal ProductLabelSelected As Integer)
        
        Dim strConnection As SqlConnection
        Dim strSql As String
        Dim cmd As SqlCommand
        
        strConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString_FieldInventory").ConnectionString)
        strSql = "proc_InsertFieldsInProducts"
        cmd = New SqlCommand(strSql)
        cmd.CommandType = CommandType.StoredProcedure
        
        cmd.Parameters.AddWithValue("@id_field", SqlDbType.Int).Value = id_fieldSelected
        cmd.Parameters.AddWithValue("@id_Product", SqlDbType.Int).Value = ProductSelected
        cmd.Parameters.AddWithValue("@id_ProductFeature", SqlDbType.Int).Value = FeatureSelected
        cmd.Parameters.AddWithValue("@id_ProductSection", SqlDbType.Int).Value = SectionSelected
        cmd.Parameters.AddWithValue("@createdDate", SqlDbType.DateTime).Value = createdDateSelected
        cmd.Parameters.AddWithValue("@createdBy", SqlDbType.VarChar).Value = createdBySelected
        cmd.Parameters.AddWithValue("@IsActive", SqlDbType.Bit).Value = IsActiveSelected
        cmd.Parameters.AddWithValue("@id_ProductLabel", SqlDbType.Int).Value = ProductLabelSelected
        cmd.Parameters.AddWithValue("@batchCreatedDate", SqlDbType.DateTime).Value = System.DateTime.Now 

        
        Try
            
           strConnection.Open()
            cmd.Connection = strConnection
            cmd.ExecuteNonQuery()
        Finally
            strConnection.Close()
        End Try
    End Sub

Here is the code for the repeater used for the code above

<asp:SqlDataSource ID="dsProductName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductName), id_Product FROM Products 
WHERE ProductName IS NOT NULL ORDER BY ProductName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsFeatureName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(FeatureName), id_ProductFeature FROM ProductFeatures 
WHERE FeatureName IS NOT NULL ORDER BY FeatureName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsSectionName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(SectionName), id_ProductSection FROM ProductSections 
WHERE SectionName IS NOT NULL ORDER BY SectionName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsProductLabel" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductLabel), id_ProductLabel FROM ProductLabels 
WHERE ProductLabel IS NOT NULL ORDER BY ProductLabel">
</asp:SqlDataSource>

<asp:Repeater id="rptFieldsInProducts" runat="server" OnItemDataBound="ddlProducts_ItemDataBound">
      <HeaderTemplate>
         <fieldset style="width: 900px">
         <legend><h4 class="pageHeader">Product Details</h4></legend>
         <table width="900px" Cellpadding="3" CellSpacing="1" border="0">
             <tr>
                <td class="fieldDetailLabel" width="15%">Product</td>
                <td class="fieldDetailLabel" width="15%">Feature</td>
                <td class="fieldDetailLabel" width="15%">Section</td>                
                <td class="fieldDetailLabel" width="15%">Label</td>
                <td class="fieldDetailLabel" width="15%">Is Active?</td>
            </tr>                  
      </HeaderTemplate>
      <ItemTemplate>                   
             <tr>
               
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlProducts" Runat="Server"
                    DataSourceID="dsProductName"
                    DataTextField="ProductName" 
                    DataValueField="id_Product"
                    CssClass="fieldControl" />
               </td>
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlFeatures" Runat="Server"
                    DataSourceID="dsFeatureName"
                    DataTextField="FeatureName" 
                    DataValueField="id_ProductFeature"
                    CssClass="fieldControl" />
               </td>      
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlSections" Runat="Server"
                    DataSourceID="dsSectionName"
                    DataTextField="SectionName" 
                    DataValueField="id_ProductSection"
                    CssClass="fieldControl" />
               </td>                                            
               <td>
               <asp:DropDownList id="ddlProductLabel" Runat="Server"
                    DataSourceID="dsProductLabel"
                    DataTextField="ProductLabel" 
                    DataValueField="id_ProductLabel"
                    CssClass="fieldControl" />
               </td>
               <td><asp:Checkbox ID="chkIsActive" runat="server" Checked='<%# Eval("IsActive") %>' /></td>
               <td><asp:Label ID="lblId_Field" runat="server" Text='<%# Eval("id_field") %>' class="displayNone" />
               <asp:Label ID="lblCreatedDate" runat="server" Text='<%# Eval("createdDate") %>' class="displayNone" />
               <asp:Label ID="lblCreatedBy" runat="server" Text='<%# Eval("createdBy") %>' class="displayNone" /></td>           
            </tr>
      </ItemTemplate>
      <FooterTemplate>
         <tr>
             <td colspan="5" align="center"><asp:Button ID="btnUpdateProductDetails" Text="Update Product Details" 
CssClass="button" runat="server" OnClick="btnUpdateProductDetails_Click"/></td>
         </tr>           
          </table>         
      </FooterTemplate>
   </asp:Repeater>

Comments [0] | |

Wednesday, November 04, 2009

Setting the Selected Item In a Dropdownlist Inside a Repeater

Wednesday, November 04, 2009 4:22:52 PM (GMT Standard Time, UTC+00:00) ( ASP.NET )

I have a dropdownlist inside of a repeater control and I need to make the selected item in the dropdown list the same selected item from the database.

Here’s how I did it.

Protected Sub rptProducts_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)
        Dim ddlProducts As DropDownList = e.Item.FindControl("ddlProducts")
        If Not e.Item.FindControl("ddlProducts") Is Nothing Then
            ddlProducts.Items.FindByValue(CType(System.Web.UI.DataBinder.Eval(e.Item.DataItem, "id_product"), Object)).Selected = True
        End If    
End Sub 

<asp:SqlDataSource ID="dsProductName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductName), id_Product FROM Products 
WHERE ProductName IS NOT NULL ORDER BY ProductName">
</asp:SqlDataSource>


<asp:Repeater id="rptFieldsInProducts" runat="server" OnItemDataBound="ddlProducts_ItemDataBound">      
      <ItemTemplate>                   
            
               <asp:DropDownList id="ddlProducts" Runat="Server"
                    DataSourceID="dsProductName"
                    DataTextField="ProductName" 
                    DataValueField="id_Product"
                    CssClass="fieldControl" /> 
             
      </ItemTemplate>      
</asp:Repeater>

The solution runs into problems if there aren’t any records to show for the Products section and fails with the error Conversion from type 'DBNull' to type 'String' is not valid.

Protected Sub rptProducts_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)
        Dim ddlProducts As DropDownList = e.Item.FindControl("ddlProducts")
        Dim temp As Object

        If Not e.Item.FindControl("ddlProducts") Is Nothing Then
            temp = CType(System.Web.UI.DataBinder.Eval(e.Item.DataItem, "id_Product"), Object)
            If Not IsDBNull(temp) Then
                ddlProducts.Items.FindByValue(temp.ToString).Selected = True
            End If
        End If
    End Sub

Comments [0] | |

Tuesday, November 03, 2009

How to Connect to a SQL 2005 Server When You Are Completely Locked Out

Tuesday, November 03, 2009 1:35:54 PM (GMT Standard Time, UTC+00:00) ( SQL )

This is from the article: http://www.sqlservercentral.com/articles/Administration/68271/

You have forgotten (or do not have) the "sa" password
The builtin\administrators account has been removed for security reasons
You cannot connect to the SQL Server instance

First off you need to shut down all the SQL Server related services. This can be done manually or you can use the batch file below. Either way you need to ensure that all SQL Server services are stopped. If you do the manual process see Image 1, which is a screen shot of the services you need to stop.

If you use this batch file, copy it to your editor like Notepad and save is as a batch file with the .bat extension. If you are stopping an instance you will have to modify the file.

@echo off
cls
echo.*****************************************
echo.**** Shutting Down SQL2005 Services ****
echo.*****************************************
echo.
echo. Shutting Down Integration Services...
net stop "SQL Server Integration Services"
echo.
echo. Shutting Down Full Text Search...
net stop "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Agent...
net stop "SQL Server Agent (MSSQLSERVER)"
echo. 
echo. Shutting Down Analysis Services..
net stop "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Shutting Down Reporting Services...
net stop "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Browser...
net stop "SQL Server Browser"
echo.
echo. Shutting Down SQL Server...
net stop "SQL Server (MSSQLSERVER)"
echo.
echo. 
echo. To stop the SQL Server Brower use this command: net stop "SQL Server Browser"
echo.
echo.
echo.**** Shut Down Completed ****
echo.

The service window should look like this once completed. See Image 2. Now you can open a command line window by clicking on START then RUN finally type: cmd, which will open a new window. From here you execute the .bat file you saved.

Once executed you will see that all the SQL Server services have stopped. If not, stop anything that might still be running. This step is important because if any other SQL Server services are still running you will not be able to connect when SQL Server is started in single user mode.

Now you need to start just the SQL Server in single user mode. In the command line window change to the directory that has SQL Server installed. In my configuration it's here on the C: drive:

cd\program files\microsoft sql server\mssql.1\mssql\binn

This location may be different if you are accessing an instance.

Type the following in the command window for the default instance

sqlservr.exe -m

For a named instance type

sqlservr.exe -m -s <instance name>

Once this is executing you will see screen scroll and then stop. This process is not hung just waiting as SQL Server is now running in single user mode.

Next open another command line window (cmd) and now you can use the sqlcmd tool included with SQL 2005 installation. In this new window type the following and hit enter.

sqlcmd -E

A new prompt will appear. The buildin\administrators account needs to be created and added to the proper role. The following is what you would type in the new command line window:

create login [builtin\administrators] from windows
go
exec sp_addsrvrolemember [builtin\administrators], [sysadmin]
go
shutdown
go

The first line will create a login called builtin\administrators as per the one created within the OS. GO command executes the previous command. Now you add this login to the sysadmin role and finally you shut down the SQL Server that is running in single user mode. Note the first command line window exits the execution of SQL Server and returns the prompt. You may now close this window.

PS. Below is a batch file you can use to start up all SQL Server related servers. I use both to stop and start SQL Server services as needed. If you have stopping an instance you will have to modify the file.

@echo off
cls
echo.---------------------------------------
echo.---- Starting Up SQL2005 Services ----
echo.---------------------------------------
echo.
echo. Starting Up SQL Server...
net start "SQL Server (MSSQLSERVER)"
echo.
echo. Starting Up SQL Agent...
net start "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Starting Up Integration Services...
net start "SQL Server Integration Services"
echo.
echo. Starting Up Full Text Search...
net start "SQL Server FullText Search (MSSQLSERVER)"
echo. 
echo. Starting Up Analysis Services..
net start "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Starting Up Reporting Services...
net start "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Starting Up SQL Browser...
echo. 
echo. To start the SQL Server Brower use this command: net start "SQL Server Browser"
echo.
echo.---- SQL 2005 Servers Start-Up Completed ----
echo.

Comments [0] | |

Stone Coast Web Design Blog

Mostly .NET Development

Navigation

Search

Categories

On this page

Archive

Blogroll