DataTable dt = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindRepeater();
                RepeaterMenu.DataSource = dt;
                RepeaterMenu.DataBind();
            }
        }

        public DataTable BindRepeater()
        {
            dt = new Select()
                    .From<CmsMenu>()
                    .Where(CmsMenu.PublishedColumn)
                    .IsEqualTo(true)
                    .And(CmsMenu.FooterMenuColumn)
                    .IsEqualTo(true)
                    .OrderAsc("sortOrder")
                    .ExecuteDataSet().Tables[0];

            return dt;
        }

Always forgetting this one!

int sortOrder;

//a sort order has been entered
if (Int32.TryParse(txtSortOrder.Text, out sortOrder))
    { }
     else
   {
       sortOrder = 0;
   }

I have spent the last day trying to find a solution to what turns out to be a very common problem with the ASP.NET treeview control. Basically, I am pulling my data out of SQL and binding it to my treeview to use as navigation through a series of categories. My problem was that when I click a category (i’m redirecting to another page based on the category selected) the treeview loses all state and loads a fresh. Oh, my treeview control is in a user control btw, not in a master page.

The solution I came up with (couldn’t find a solution on the web that would work for me, i looked at the recursion method among many others, but for whatever reason it wouldn’t work. Maybe because the control is a user control and not directly within a master page??)  is rather a simple one, but it works! I’m sure there maybe more efficient and better ways of doing it, but like I said, this one works for me!

So this is what I do…
When a node is clicked I get the valuepath of the node via the SelectedNodeChanged event. I store this value in a session variable. When the resultant page loads I then use the OnPreRender method which is, and I quote;

"The PreRender event is raised just before the page is about to render its
contents. This is the last chance to modify a page output before it is sent
to the browser".

In this method I check to see if the session for the valuepath has any data. If it does I check to see if it has a separator. If there is that means we are more than one level deep into our treeview else if not, we are at the root level. If we are more than one level deep I throw the value into an array. I then basically loop through the array writing out the TreeView1.Findnode method as I go. So we are left with something like this;

TreeView1.FindNode("6").Expand();
TreeView1.FindNode("6/11").Expand();
TreeView1.FindNode("6/11/15").Expand();

That’s basically it. The code is below.

ASP.NET

<asp:TreeView ID="TreeView1" NodeWrap="true" ExpandDepth="0" runat="server"     OnTreeNodePopulate="TreeView1_TreeNodePopulate"
    PathSeparator="/" ImageSet="MSDN" CssClass="categoryMenu" OnDataBound="TreeView1_DataBound"
    OnSelectedNodeChanged="TreeView1_SelectedNodeChanged" ShowCheckBoxes="None"     SelectedNodeStyle-CssClass="categorySelected">
                <ParentNodeStyle Font-Bold="False" CssClass="navigationLink" />
                <HoverNodeStyle Font-Underline="True" ForeColor="" />
                <NodeStyle NodeSpacing="0px" VerticalPadding="0px" /></asp:TreeView>

 

C#

protected override void OnPreRender(EventArgs e)
    {
        if (Session["SelectedNodeValuePath"] != null)
        {
            //the selected node value is something like 6/11
            string nodeValuePath = Session["SelectedNodeValuePath"].ToString();

            //get the position of the first /
            int firstSeparator = nodeValuePath.IndexOf("/");

            if (nodeValuePath.IndexOf("/") > 0)
            {
                //define the character that separates
                char[] separator = { '/' };

                //split the string
                string[] array = nodeValuePath.Split(separator);

                StringBuilder path = new StringBuilder();
                int noArrayElements = array.Length;

                // if you select the third node down in a tree, the array will write out the following
                // TreeView1.FindNode("6").Expand();
                // TreeView1.FindNode("6/11").Expand();
                // TreeView1.FindNode("6/11/15").Expand();

                for (int i = 1; i < array.Length; i++)
                {
                    // if there is only one element in my array there is then only 1 node to expand.
                    if (noArrayElements == 1)
                    {
                        path.Append(array[i - 1]);
                        TreeView1.FindNode(path.ToString()).Expand();
                    }
                    // if there is more than one element i need to add the separator to the node path and then expand the node
                    if (noArrayElements > 1)
                    {
                        if (i == 1)
                        {
                            // don't prefix searator if first node
                            path.Append(array[i - 1]);
                            TreeView1.FindNode(path.ToString()).Expand();

                        }
                        else
                        {
                            // prefix separator for subsequent nodes
                            path.Append("/");
                            path.Append(array[i - 1]);
                            TreeView1.FindNode(path.ToString()).Expand();
                        }
                    }
                }

            }
            TreeView1.FindNode(Session["SelectedNodeValuePath"].ToString()).Select();

        }
    }

protected void TreeView1_SelectedNodeChanged(object sender, EventArgs e)
    {
        //Get the value of the selected node
        int catID;
        catID = Convert.ToInt32(TreeView1.SelectedValue);
        string valuePath = TreeView1.SelectedNode.ValuePath;
        Session["selectedValue"] = catID;
        Session["SelectedNodeValuePath"] = valuePath;

        //Get the name of the selected category
        Category c = new Category(catID);
        string itemUrl = c.CatName;

        //pass the name through our clean URL class for URLrewriting
        itemUrl = CleanURLRewrite.cleanURL(itemUrl);

        //Check if we're rewriting urls and redirect accordingly
        if (System.Convert.ToBoolean((ConfigurationManager.AppSettings["EnableURLrewrite"])) == true)
        {
            Response.Redirect(ConfigurationManager.AppSettings["cartDirectory"].ToString() + "cat/" + catID + "/" + itemUrl + ".aspx", false);
        }
        else
        {
            Response.Redirect(ConfigurationManager.AppSettings["cartDirectory"].ToString() + "Items.aspx?category=" + catID.ToString(), false);
        }
    }

SELECT name INTO #tables from sys.objects where type = 'U'

while (SELECT count(1) FROM #tables) > 0

begin

declare @sql varchar(max)

declare @tbl varchar(255)

SELECT top 1 @tbl = name FROM #tables

SET @sql = 'drop table ' + @tbl

exec(@sql)

DELETE FROM #tables where name = @tbl

end

DROP TABLE #tables

Sort and page the results

public SortDirection SortDir
    {
        get
        {
            if (ViewState["sortDirection"] == null)
            {
                ViewState["sortDirection"] = SortDirection.Ascending;
            } return (SortDirection)ViewState["sortDirection"];
        }
        set
        {
            ViewState["sortDirection"] = value;
        }
    }

    DataSet ds = new DataSet();
    DataView dv = new DataView();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
            GridView1.DataSource = dv;
            GridView1.DataBind();
        }
    }

    private DataView BindGrid()
    {
        ds = new Query(AnthemWeb.DAL.Item.Schema).ExecuteDataSet();

        if (ViewState["sortExpr"] != null)
        {
            dv = new DataView(ds.Tables[0]);
            dv.Sort = (string)ViewState["sortExpr"];
        }
        else
        {
            dv = ds.Tables[0].DefaultView;
        }

        return dv;
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataSource = BindGrid();
        GridView1.DataBind();
    }

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        string stExp = e.SortExpression;
        string stDir = string.Empty;
        if (SortDir == SortDirection.Ascending)
        {
            SortDir = SortDirection.Descending;
            stDir = "DESC";
        }
        else
        {
            SortDir = SortDirection.Ascending;
            stDir = "ASC";
        }

        ViewState["sortExpr"] = e.SortExpression + " " + stDir;
        GridView1.DataSource = BindGrid();
        GridView1.DataBind();
    }

    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        int selectedRowIndex;
        selectedRowIndex = GridView1.SelectedIndex;
        GridViewRow row = GridView1.Rows[selectedRowIndex];
        string ID = row.Cells[0].Text;

        Response.Redirect("customer-details.aspx?ID=" + ID, false);
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        BindGrid();
    }

Started this week to use Subsonic to generate my DAL. I got this handy reference from their forum for doing the INSERTS etc…

  private void BindNames()
    {               
        AddBook.DLL.AddressBookCollection list = new AddBook.DLL.AddressBookCollection().OrderByAsc("Name").Load();
        this.ddNames.DataSource = list;
        this.ddNames.DataTextField = "Name";
        this.ddNames.DataValueField = "UserID";
        this.ddNames.DataBind();
    }
    protected void btnSelect_Click(object sender, EventArgs e)
    {
        AddBook.DLL.AddressBook ab = new AddBook.DLL.AddressBook(ddNames.SelectedValue);
        this.tbName.Text = ab.Name;
        this.tbSurname.Text = ab.Surname;
        this.tbAddress.Text = ab.Address;
        this.tbHomePhone.Text = ab.HomePhone;
        this.tbMobilePhone.Text = ab.MobilePhone;      
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        AddBook.DLL.AddressBook ab = new AddBook.DLL.AddressBook(ddNames.SelectedValue);
        AddBook.DLL.AddressBook.Delete(ab.UserID);
        BindNames();       
    }
   

protected void btnUpdate_Click(object sender, EventArgs e)
    {
        AddBook.DLL.AddressBook ab = new AddBook.DLL.AddressBook(ddNames.SelectedValue);
        ab.Name = tbName.Text;
        ab.Surname = tbSurname.Text;
        ab.Address = tbAddress.Text;
        ab.HomePhone = tbHomePhone.Text;
        ab.MobilePhone = tbMobilePhone.Text;
        ab.Save();
        BindNames();
    }

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        AddBook.DLL.AddressBook ab = new AddBook.DLL.AddressBook();
        ab.Name = tbName.Text;
        ab.Surname = tbSurname.Text;
        ab.Address = tbAddress.Text;
        ab.HomePhone = tbHomePhone.Text;
        ab.MobilePhone = tbMobilePhone.Text;
        ab.Save();
        BindNames();
    }

Example page = www.mysite.com/pickup-golding.aspx

C# code

string loadedPageName = Path.GetFileName(Page.Request.PhysicalPath);
        Response.Write(loadedPageName);

Result = pickup-golding.aspx

Got these very useful references from http://sqltutorials.blogspot.com/2007/06/sql-string-functions.html

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM
Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)
SELECT ASCII(’a') — Value = 97
SELECT ASCII(’b') — Value = 98
SELECT ASCII(’c') — Value = 99
SELECT ASCII(’A') — Value = 65
SELECT ASCII(’B') — Value = 66
SELECT ASCII(’C') — Value = 67
SELECT ASCII(’1′) — Value = 49
SELECT ASCII(’2′) — Value = 50
SELECT ASCII(’3′) — Value = 51
SELECT ASCII(’4′) — Value = 52
SELECT ASCII(’5′) — Value = 53
Example SQL String Function - SPACE
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT (’SQL’) + SPACE(0) + (’TUTORIALS’)
– Value = SQLTUTORIALS
SELECT (’SQL’) + SPACE(1) + (’TUTORIALS’)
– Value = SQL TUTORIALS
Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’)
– Value = 27
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’, 20)
– Value = 27
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’, 30)
– Value = 0 (Because the index is count from 30 and above)
Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( ’string1′ , ’string2′ , ’string3′ )
SELECT REPLACE(’All Function’ , ‘All’, ‘SQL’)
– Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( ’string’ [ , 'quote_character' ] )
SELECT QUOTENAME(’Sql[]String’)
– Value = [Sql[]]String]

Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 )
SELECT STUFF(’SqlTutorial’, 4, 6, ‘Function’)
– Value = SqlFunctional
SELECT STUFF(’GoodMorning’, 5, 3, ‘good’)
– Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer)
SELECT LEFT(’TravelYourself’, 6)
– Value = Travel
SELECT LEFT(’BeautyCentury’,6)
– Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT(’TravelYourself’, 6)
– Value = urself
SELECT RIGHT(’BeautyCentury’,6)
– Value = Century

Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)
SELECT REPLICATE(’Sql’, 2)
– Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING(’SQLServer’, 4, 3)
– Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string)
SELECT LEN(’SQLServer’)
– Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE(’SQLServer’)
– Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char)
SELECT UNICODE(’SqlServer’)
– Value = 83 (it take first character)
SELECT UNICODE(’S')
– Value = 83

Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER(’SQLServer’)
– Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string )
SELECT UPPER(’sqlserver’)
– Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(’ sqlserver’)
– Value = ’sqlserver’ (Remove left side space or blanks)
Example SQL String Function - RTRIM
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
SELECT RTRIM(’SqlServer ‘)
– Value = ‘SqlServer’ (Remove right side space or blanks)

The XML File

<client_order ordID="14" supplierID="000002">
  <item_order itoItemID="12" itoItemQty="1" itoItemPrice="35.99" itoTaxAmount="3.00" itemCode="LD3162" />
  <item_order itoItemID="3" itoItemQty="0" itoItemPrice="29.99" itoTaxAmount="3.00" itemCode="LD3278" />
  <item_order itoItemID="7" itoItemQty="0" itoItemPrice="31.99" itoTaxAmount="3.00" itemCode="LD3226" />
  <item_order itoItemID="10" itoItemQty="0" itoItemPrice="31.99" itoTaxAmount="3.00" itemCode="LD3226" />
  <item_order itoItemID="9" itoItemQty="0" itoItemPrice="29.99" itoTaxAmount="3.00" itemCode="LD3226" />
</client_order>

The CODE ;o)

//Get the XML
                    string responseXML = r["Response"].ToString();

                    try
                    {
                        XmlDocument contentXML = new XmlDocument();
                        contentXML.LoadXml(responseXML);

                        //Get the attribute values from the root node <client_order>
                        XmlNode client_orderNode = contentXML.DocumentElement.SelectSingleNode("//client_order");
                        string supplierID = client_orderNode.Attributes["supplierID"].Value.ToString();
                        string ordID = client_orderNode.Attributes["ordID"].Value.ToString();

                        string supplierName = CommonDBCommands.SelectValue(connectionString, "suppliers", "supplierName",
                            "supplierID", supplierID);

                        //GET the order id and supplier as recorded in the XML response.
                        Debug.Print("Supplier ID = " + supplierID);
                        Debug.Print("Order ID = " + ordID);

                        //read each element <item_order> in <client_order> and get the attribute values
                        foreach (XmlNode node in client_orderNode)
                        {
                            XmlElement item_order = (XmlElement)node;

                            string itemID = "";
                            string itemQty = "";
                            string itemPrice = "";
                            string itemTaxAmount = "";
                            string itemCode = "";

                            if (item_order.HasAttributes)
                            {
                                itemID = item_order.Attributes["itoItemID"].InnerText;
                                itemQty = item_order.Attributes["itoItemQty"].InnerText;
                                itemPrice = item_order.Attributes["itoItemPrice"].InnerText;
                                itemTaxAmount = item_order.Attributes["itoTaxAmount"].InnerText;
                                itemCode = item_order.Attributes["itemCode"].InnerText;

                                if (itemID == hItemID)
                                {
                                    DataRow row = dt.NewRow();
                                    row["SupplierName"] = supplierName;
                                    row["itemID"] = itemID;
                                    row["Qty"] = Convert.ToDecimal(itemQty);
                                    row["price"] = Convert.ToDecimal(itemPrice);
                                    row["taxAmount"] = Convert.ToDecimal(itemTaxAmount);
                                    row["itemCode"] = itemCode;

                                    dt.Rows.Add(row);
                                }
                            }

                            Debug.Print("item ID  = " + itemID);
                            Debug.Print("price  = $" + itemPrice);

                        }
                    }
                    catch (Exception ex)
                    {
                        Response.Write(ex.Message.ToString());
                    }

protected void LogOut()
    {
        Session.Abandon();
        string nextpage = "Logoutt.aspx";
        Response.Write("<script language=javascript>");

        Response.Write("{");
        Response.Write(" var Backlen=history.length;");

        Response.Write(" history.go(-Backlen);");
        Response.Write(" window.location.href='" + nextpage + "'; ");

        Response.Write("}");
        Response.Write("</script>");

    }