Import dataset from Excel

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~\\File\\" + HiddenField1.Value.ToString()) + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connectionString);
objConn.Open();
String strConString = "SELECT * FROM [Sheet1$]";
//where date = CDate('" + DateTime.Today.ToShortDateString() + "')";
OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ExcelData");


//GridView1.DataSource = objDataset1.Tables[0].DefaultView;
//GridView1.DataBind();

IsDate() function in C#

C# does not provide IsDate() function, but you can build one pretty easily:

private bool IsDate(string inputDate)
{
DateTime dt;
bool isdate = DateTime.TryParse(inputDate, out dt);
return isdate;
}

You can build other functions such as IsInteger() by using int.Parse() instead of DateTime.Parse().

Remove Extra Space from Text in Sql

select name,
replace(replace(replace(name,' ','<>'),'><',''),'<>',' ')
from qtemp/mydata


NAME REPLACE
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith

So how does it work? The innermost REPLACE changes all blanks to a less-than greater-than pair. So, if there are three spaces between Joe and Smith, the innermost REPLACE returns Joe<><><>Smith.

The middle REPLACE changes all greater-than less-than pairs to the empty string, which removes them. Joe<><><>Smith becomes Joe<>Smith.

The outer REPLACE changes all less-than greater-than pairs to a single blank. Joe<>Smith becomes Joe Smith. Clever!

You do not have to use the less-than and greater-than symbols. Any two characters that are not used in the field will work.

Removing Extra space from text using javascript

Remove spaces from input...


str1 = "someone @ somewhere . com"
str1 = str1.replace(/\s+/g,"");
document.write(str1);


Trim spaces from the beginning of a string


str2 = " This is a test!";
str2 = str2.replace(/^\s+/,"");
document.write(str2);


Trim spaces from the end of a string

str3 = "This is a test! ";
str3 = str3.replace(/\s+$/,"");
document.write(str3);


Remove extra spaces from within a string
and replace them with ONE space

str4 = "This is a test!";
str4 = str4.replace(/\s+/g," ");
document.write(str4);


Here's how to combine the last three
examples into one statement!

str5 = " This is a test! ";
str5 = str5.replace(/^\s+/,"").replace(/\s+$/,"").replace(/\s+/g," ");
document.write(str5);

N Level TreeView

CatagoryID ParentCatagoryID

1 --

2 --

4 2

5 2

6 1

7 --

8 4

9 2

<asp:TreeView ID="TreeView1" runat="server" OnTreeNodePopulate="TreeView_TreeNodePopulate" />

    protected void Page_Load(object sender, EventArgs e)
   {
       if (!IsPostBack)
       {
           PopulateRootLevel();
       }
   }

   private void PopulateRootLevel()
   {
       SqlConnection objConn = new SqlConnection(ConnectionString);
        SqlCommand objCommand = new SqlCommand("SELECT sc.CatagoryID, sc.ParentCatagoryID,(SELECT COUNT(*) FROM yourtable WHERE (ParentCatagoryID= sc.CategoryID)) AS childnodecount FROM yourtable AS sc", objConn);
        SqlDataAdapter da = new SqlDataAdapter(objCommand);DataTable dt = new DataTable();
        da.Fill(dt);        PopulateNodes(dt, TreeView1.Nodes);
   }

   private void PopulateNodes(DataTable dt, TreeNodeCollection nodes)
   {
       foreach (DataRow dr in dt.Rows)
       {
           TreeNode tn = new TreeNode();
           tn.Value = dr["CatagoryId"].ToString();
           tn.Text = dr["CatagoryId"].ToString();
           nodes.Add(tn);
           //If node has child nodes, then enable on-demand populating
           tn.PopulateOnDemand = ((int)(dr["childnodecount"]) > 0);
       }
   }

   protected void TreeView_TreeNodePopulate(object sender, TreeNodeEventArgs e)
   {
       int ParentCatagoryID= Int32.Parse(e.Node.Value);
       PopulateSubLevel(ParentCatagoryID, e.Node);
   }

   private void PopulateSubLevel(int ParentCatagoryID, TreeNode parentNode)
   {
       //Your sublevel Datatable ie. dtSub
       PopulateNodes(dtSub, parentNode.ChildNodes);
   }