Tuesday, August 30, 2011

Split CSV numbers into Table in SQL Server

--- From string to table

CREATE FUNCTION [dbo].[ConvertCsvToNumbers]
(
  @String AS VARCHAR(8000)
)
RETURNS
  @Numbers TABLE (Number INT)
AS
BEGIN
  SELECT @String =
    LTRIM(
      RTRIM(
        REPLACE(
          ISNULL(@String, ''), '  ' /* tab */, ' ')))
  IF (LEN(@String) = 0)
    RETURN
  DECLARE @StartIdx       INT
  DECLARE @NextIdx        INT
  DECLARE @TokenLength    INT
  DECLARE @Token          VARCHAR(16)
  SELECT  @StartIdx       = 0
  SELECT  @NextIdx        = 1
  WHILE @NextIdx > 0
  BEGIN
    SELECT @NextIdx = CHARINDEX(',', @String, @StartIdx + 1)
    SELECT @TokenLength =
      CASE WHEN @NextIdx > 0 THEN @NextIdx
      ELSE LEN(@String) + 1
    END - @StartIdx - 1
    SELECT @Token =
      LTRIM(
        RTRIM(
          SUBSTRING(@String, @StartIdx + 1, @TokenLength)))
    IF LEN(@Token) > 0
      INSERT
        @Numbers(Number)
      VALUES
        (CAST(@Token AS INT))
    SELECT @StartIdx = @NextIdx
  END
  RETURN
END
----From Table values to string
declare @RoleIds varchar(max)
select @RoleIds = coalesce(@RoleIds + ',','') + convert(varchar, nRoleID)
       from    EmpRole
       WHERE  sEmployeeID =10

ref:http://alekdavis.blogspot.com/2009/04/convert-string-to-table-in-sql.html

Wednesday, August 24, 2011

Getting Ajax autocompleteextender selected value

1) create data source as  List<string>();
 List<string> custList = new List<string>();
            string custItem = string.Empty;
            while (dr.Read())
            {
                custItem=AutoCompleteExtender.CreateAutoCompleteItem(dr[0].ToString(),dr[1].ToString());
                custList.Add(custItem);

            }
2)In the Asp.net page
 <script type="text/javascript">
    function ace1_itemSelected(sender, e)
    {
        var hdCustID = $get('<%= hdEmpID.ClientID %>');
        hdCustID.value = e.get_value();
    }
    </script>
 <asp:TextBox ID="txtEmpName" AutoPostBack="true" AutoComplete="off" runat="server"
                    OnTextChanged="txtEmpName_TextChanged" />
                <cc1:AutoCompleteExtender ID="ace1" TargetControlID="txtEmpName" ServiceMethod="GetSuggestions"
                    MinimumPrefixLength="1" OnClientItemSelected="ace1_itemSelected" FirstRowSelected="true"
                    runat="server" />
                <asp:HiddenField ID="hdEmpID" runat="server" />
3) the the code behind 
   String selectedEmpID =  hdEmpID.Value;

Thats it! 

Ref: http://forums.asp.net/t/1608031.aspx/1?AutoCompleteExtender+Selected+Value+Feature

Tuesday, August 23, 2011

Dropdown in GridView

1. Setting the value of dropdown in row databound
// Retrieve the underlying data item. In this example
// the underlying data item is a DataRowView object.
DataRowView rowView = (DataRowView)e.Row.DataItem;

// Retrieve the state value for the current row.
String state = rowView["state"].ToString();

// Retrieve the DropDownList control from the current row.
DropDownList list = (DropDownList)e.Row.FindControl("StatesList");

2.How can you access and display the row index of a gridview item as the command argument

 commandargument='<%#DataBinder.Eval(Container, "DataItemIndex")%>'



Ref:
1. Setting value of dropdown

2. For all operations

Friday, August 19, 2011

ASP.NET: Recursive FindControl & Extension Methods


Ref: 1.ASP.NET: Recursive FindControl & Extension Methods


Joining Three or More Tables


Example query :
SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;

Thursday, August 4, 2011

Tip/Trick: Show Header and Footer of GridView when no Data returned.

reference : 1) http://geekswithblogs.net/dotNETvinz/archive/2009/03/11/tiptrick-show-header-and-footer-of-gridview-when-no-data.aspx

2)http://stackoverflow.com/questions/3437581/show-gridview-footer-on-empty-grid

To Access Footer when no Grid Data
when grid source has no data, you have the row from emptydataTemplate which is not placed in normal Rows collection but is nested in Control(0) of gridview (which is a ChildTable that comes from System.Web.Ui.Controls and it is a
System.Web.UI.WebControls.Table

in these situations you can get the gridviewRow this way:
vb code
dim dr as GridViewRow= gridview1.Controls(0).Controls(0)
c# code
GridViewRow dr= gridview1.Controls[0].Controls[0];

Devops links

  Build Versioning in Azure DevOps Pipelines