Add Dynamicaly Row in GridView asp.net

Use the following Link..

http://geekswithblogs.net/dotNETvinz/archive/2009/06/04/adding-dynamic-rows-in-gridview-with-textboxes.aspx

Insert Into Select Query Sample - Use for Insert Multiple Transaction Entries

'Use for Insert Multiple Transaction Entries

INSERT INTO abd_emp_degree
(deg_emp_userid,
deg_name,
deg_display,
deg_addedby,
deg_addedon)
SELECT 'AW999000',
VALUE,
'Y',
'AW999000',
Getdate()
FROM dbo.Split('CC,PS', ',')

Create proerties and Declare variable through MsSql Store Procedure , thanks to PC PATEL

-- =============================================                   
-- Author:  PC PATEL                   
-- Create date: 5-Aug-2011                   
-- Description: Used when updating routes                   
-- =============================================                   
CREATE PROCEDURE Usp_makepropertiesvb @TableName VARCHAR(max) 
AS 
  BEGIN 
      DECLARE @Temp VARCHAR(max) 
      DECLARE @Temp1 VARCHAR(max) 
       
      DECLARE @Variables varchar(max) 
      set @Variables = '' 
      DECLARE @Properties varchar(max) 
      set @Properties = '' 
       
      DECLARE CUR1 CURSOR forward_only FOR 
        SELECT i_s.COLUMN_NAME, 
               CASE i_s.DATA_TYPE 
                 WHEN 'smallint' THEN 'Integer' 
                 WHEN 'int' THEN 'Integer' 
                 WHEN 'varchar' THEN 'String' 
                 WHEN 'char' THEN 'String' 
                 WHEN 'datetime' THEN 'Date' 
               END AS DATA_TYPE 
        FROM   INFORMATION_SCHEMA.COLUMNS AS i_s 
               LEFT OUTER JOIN sys.extended_properties AS s 
                 ON s.major_id = Object_id(i_s.TABLE_SCHEMA + '.' + i_s.TABLE_NAME) 
                    AND s.minor_id = i_s.ORDINAL_POSITION 
                    AND s.name = 'MS_Description' 
        WHERE  ( Objectproperty(Object_id(i_s.TABLE_SCHEMA + '.' + i_s.TABLE_NAME), 'IsMsShipped') = 0 ) 
               AND ( i_s.TABLE_NAME = @TableName ) 
        ORDER  BY i_s.ORDINAL_POSITION 
        FOR READ ONLY 
 
      OPEN CUR1 
 
      FETCH NEXT FROM CUR1 INTO @Temp, @Temp1 
 
      WHILE @@FETCH_STATUS = 0 
        BEGIN 
            SET @Variables += 'Private _' + @Temp + ' As ' + @Temp1 + CHAR(10) 
 
            set @Properties += 'Public Property ' + @Temp + '() As ' + @Temp1 + CHAR(10) 
 
            set @Properties += 'Get' + CHAR(10) 
 
            set @Properties += 'Return _' + @Temp + CHAR(10) 
 
            set @Properties += 'End Get' + CHAR(10) 
 
            set @Properties += 'Set(ByVal value As ' + @Temp1 + ')' + CHAR(10) 
 
            set @Properties += '_' + @Temp + ' = value' + CHAR(10) 
 
            set @Properties += 'End Set' + CHAR(10) 
 
            set @Properties += 'End Property' + CHAR(10) 
 
            FETCH NEXT FROM CUR1 INTO @Temp, @Temp1 
        END 
 
      CLOSE CUR1 
 
      DEALLOCATE CUR1 
       
      print '#Region " Variables "' 
      print @Variables 
      print '#End Region' 
      print '#Region " Properties "' 
      print @Properties 
      print '#End Region' 
  END  

How to select table column name and datatype in Ms Sql

 SELECT i_s.table_name
       AS
       [Table Name],
       i_s.column_name
       AS [Column Name],
       i_s.data_type
       AS [Data Type],
       Isnull(Isnull(i_s.character_maximum_length, i_s.numeric_precision), '')
       AS
       [Max Length],
       Isnull(s.VALUE, '')
       AS DESCRIPTION
FROM   information_schema.columns AS i_s
       LEFT OUTER JOIN sys.extended_properties AS s
         ON s.major_id = Object_id(i_s.table_schema + '.' + i_s.table_name)
            AND s.minor_id = i_s.ordinal_position
            AND s.name = 'MS_Description'
WHERE  ( Objectproperty(Object_id(i_s.table_schema + '.' + i_s.table_name),
                'IsMsShipped'
         ) = 0 )
       AND ( i_s.table_name = 'v_QTW_EMPProfile' )
ORDER  BY [Table Name],
          i_s.ordinal_position 

How to Get Treeview selected node in javascript

To start with I have added a simple ASP.Net TreeView and a button on my ASPX Page

<div>

    <asp:TreeView ID="TreeView1" runat="server">

        <SelectedNodeStyle ForeColor="Black" />

    </asp:TreeView>

</div>

<asp:Button ID="Button1" runat="server" Text="GetSelectedNode" OnClientClick="return GetSelectedNode();" />

Now here’s the script that will get the reference of the TreeView selected node and also extract its Text and Value part.

<script type="text/javascript">

function GetSelectedNode() {

    var treeViewData = window["<%=TreeView1.ClientID%>" + "_Data"];

    if (treeViewData.selectedNodeID.value != "") {

        var selectedNode = document.getElementById(treeViewData.selectedNodeID.value);

        var value = selectedNode.href.substring(selectedNode.href.indexOf(",") + 3, selectedNode.href.length - 2);

        var text = selectedNode.innerHTML;

        alert("Text: " + text + "\r\n" + "Value: " + value);

    } else {

        alert("No node selected.")

    }

    return false;

}

</script

On Page Load In Vb.net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Try    
If Not IsPostBack Then                                                  MakeTree1()
      tvMenu.CollapseAll()    End If
Catch ex As ExceptionEnd Try
  lblMsg.ForeColor = Drawing.Color.Red
  lblMsg.Text = "Error on page."

 End Sub

Simple Cursor in Sql Sqerver/ How to use cursor in MS Sql Server

ALTER PROCEDURE Usp_removrights @userList   VARCHAR(MAX),
                                @menu       SMALLINT,
                                @submenu    SMALLINT,
                                @subsubmenu SMALLINT
AS
  DECLARE @count INT

  SET @count=0

  DECLARE @user_no VARCHAR(MAX)
  DECLARE @user_id VARCHAR(MAX)

  BEGIN TRY
      --Find out the cursors that are opened and not close
      SELECT @count = COUNT(*)
      FROM   sys.Dm_exec_cursors(0)
      WHERE  is_open = 1
             AND name = 'cur_RemovRight'

      IF ( @count > 0 )
        BEGIN
            CLOSE cur_removright;

            DEALLOCATE cur_removright;
        END

      DECLARE cur_removright CURSOR FOR
        SELECT VALUE
        FROM
dbo.Split('00011 ,00261 , 01014 , 01019 ,  01022 ,  80011 ,  80012 ,  80013 ,  80014 ', ',')

    OPEN cur_removright

    FETCH NEXT FROM cur_removright INTO @user_no;

    WHILE @@FETCH_STATUS = 0
      BEGIN
          SELECT @user_id = muser_userid
          FROM   infoview
          WHERE  muser_empno = @user_no

          INSERT INTO userlevelrights1
                      (userid,
                       menu,
                       submenu,
                       subsubmenu)
          VALUES     (@user_id,
                      @menu,
                      NULL,
                      NULL)

          PRINT @user_id

          --Delete from userlevelrights1 where userid=@user_id
          FETCH NEXT FROM cur_removright INTO @user_no;
      END

    CLOSE cur_removright;

    DEALLOCATE cur_removright;
END TRY

  BEGIN CATCH
      INSERT INTO errorlog_sp
      SELECT Error_number()    AS errornumber,
             Error_severity()  AS errorseverity,
             Error_state()     AS errorstate,
             Error_procedure() AS errorprocedure,
             Error_line()      AS errorline,
             Error_message()   AS errormessage,
             Getdate()

      RAISERROR ('Error catch',
                 10,
                 1)
  END CATCH 

New Visual Studio Light Switch 2011 (addtional to visual studio for fast Development)

How to store/upload video in Sql Database table in Asp.net

use the following link.....

http://www.codeproject.com/KB/aspnet/VideoUploaderControl.aspx




Create a table in a SQL Server database that will store the file data, file name, and file size.
ID Int
Video varbinary(MAX)
Video_Name nvarchar(50)
Video_Size bigint

In the control, add a FileUpload control, a Button, a Label.
Add the control to your page.

Code for Upload File

using System.IO;
using System.Data.SqlClient;

public partial class UploadVideo : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    byte[] buffer;
    //this is the array of bytes which will hold the data (file)

    SqlConnection connection;
    protected void ButtonUpload_Click(object sender, EventArgs e)
    {
        //check the file

        if (FileUpload1.HasFile && FileUpload1.PostedFile != null 
            && FileUpload1.PostedFile.FileName != "")
        {
            HttpPostedFile file = FileUpload1.PostedFile;
            //retrieve the HttpPostedFile object

            buffer = new byte[file.ContentLength];
            int bytesReaded = file.InputStream.Read(buffer, 0, 
                              FileUpload1.PostedFile.ContentLength);
            //the HttpPostedFile has InputStream porperty (using System.IO;)
            //which can read the stream to the buffer object,
            //the first parameter is the array of bytes to store in,
            //the second parameter is the zero index (of specific byte)
            //where to start storing in the buffer,
            //the third parameter is the number of bytes 
            //you want to read (do u care about this?)

            if (bytesReaded > 0)
            {
                try
                {
                    string connectionString = 
                      ConfigurationManager.ConnectionStrings[
                      "uploadConnectionString"].ConnectionString;
                    connection = new SqlConnection(connectionString);
                    SqlCommand cmd = new SqlCommand
                    ("INSERT INTO Videos (Video, Video_Name, Video_Size)" + 
                     " VALUES (@video, @videoName, @videoSize)", connection);
                    cmd.Parameters.Add("@video", 
                        SqlDbType.VarBinary, buffer.Length).Value = buffer;
                    cmd.Parameters.Add("@videoName", 
                        SqlDbType.NVarChar).Value = FileUpload1.FileName;
                    cmd.Parameters.Add("@videoSize", 
                        SqlDbType.BigInt).Value = file.ContentLength;
                    using (connection)
                    {
                        connection.Open();
                        int i = cmd.ExecuteNonQuery();
                        Label1.Text = "uploaded, " + i.ToString() + " rows affected";
                    }
                }
                catch (Exception ex)
                {
                    Label1.Text = ex.Message.ToString();
                }
            }

        }
        else
        {
            Label1.Text = "Choose a valid video file";
        }
    }
}
//create a sqlcommand object passing the query and the sqlconnection object
//when declaring the parameters you have to be sure 
//you have set the type of video column to varbinary(MAX)

How to select the data and show it on your page

How to select the data and show it on your page
The problem here is that we have to set the src property of the player control, but our file exists in a database, so we need a handler to read the bytes in the database.. The handler idea is awesome! You can call it like: "Handler.ashx?ID=1", and in the handler code, read the video column where the ID column = QueryString["id"].

using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public class VideoHandler : IHttpHandler 
{
    
    public void ProcessRequest (HttpContext context) 
    {
        string connectionString = 
          ConfigurationManager.ConnectionStrings[
          "uploadConnectionString"].ConnectionString;

        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("SELECT Video, Video_Name" + 
                         " FROM Videos WHERE ID = @id", connection);
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = 
                           context.Request.QueryString["id"];
        try
        {
            connection.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    context.Response.ContentType = reader["Video_Name"].ToString();
                    context.Response.BinaryWrite((byte[])reader["Video"]);
                }
            }
        }
        finally
        {
            connection.Close();
        }
    }
 
    public bool IsReusable 
    {
        get {
            return false;
        }
    }
}

OK.. how do we show the video?! You can show the video in an ASP.NET Data control. Well, I did an example on the Repeater control. You have to read the data from the SQL Server with a SQL adapter and bind the data source to the Repeater control. Well, here you can specify which videos to select in the datasource..

private DataTable GetSpecificVideo(object i)
//pass the id of the video
{
    string connectionString = 
      ConfigurationManager.ConnectionStrings[
      "uploadConnectionString"].ConnectionString;
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT Video, ID " + 
                             "FROM Videos WHERE ID = @id", connectionString);
    adapter.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = (int)i;
    DataTable table = new DataTable();
    adapter.Fill(table);
    return table;
}
protected void ButtonShowVideo_Click(object sender, EventArgs e)
{
    Repeater1.DataSource = GetSpecificVideo(2);
    //the video id (2 is example)

    Repeater1.DataBind();
}

Now, its time for the player control.. In the Repeater (source view), add an ItemTemplate, and set the URL value parameter of the player control to <'%# "VideoHandler.ashx?id=" + Eval("ID") %'>. The ID is the name of the ID column of the data source that the Repeater binds to.

<asp:Button ID="ButtonShowVideo" runat="server" 
   onclick="ButtonShowVideo_Click" Text="Show Video" />

    <asp:Repeater ID="Repeater1" runat="server">
        <ItemTemplate>
            <object id="player" 
                       classid="clsid:6BF52A52-394A-11D3-B153-00C04F79FAA6" 
                       height="170" width="300">
                <param name="url" 
                  value='<%# "VideoHandler.ashx?id=" + Eval("ID") %>'/>
                <param name="showcontrols" value="true" />
                <param name="autostart" value="true" />
            </object>
        </ItemTemplate>
    </asp:Repeater>

Cross Apply Sample

sELECT distinct a.Loc_Icardallotter, LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM Mlocationwisealloter a
cross apply ( select loc_fullname +' , ' as [text()]
from Mlocationwisealloter b inner join mlocation on loc_code = b.loc_unitcd where b.loc_moduleno = a.loc_moduleno and b.Loc_Icardallotter = a.Loc_Icardallotter
order by loc_fullname
FOR XML PATH(''))el (EmpList)
WHERE (Loc_moduleno = 270)

How to Get splited string from value

SELECT SUBSTRING(REPLACE(InsuFromDate,'/',''), 0, CHARINDEX('/',InsuFromDate)) AS a,
SUBSTRING(REPLACE(InsuFromDate,'/',''), CHARINDEX('/',InsuFromDate), CHARINDEX('/',InsuFromDate)) AS b
from MHCControlParameterCar