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

Embedded Code and Inline Server Tags

Ref: http://weblogs.asp.net/ahmedmoosa/archive/2010/10/06/embedded-code-and-inline-server-tags.aspx

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

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

Convert VB.NET to C# - A free code conversion tool

Convert VB.NET to C# - A free code conversion tool

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;

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