1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

ASP SQL Help-Hierarchies & Nested Sets (Celko)

Discussion in 'C#' started by rdx73, Feb 3, 2007.

  1. #1
    Hi Everyone:

    I am working with a hierarchial database. I chose to use Celko's nested set theory to keep track of my tblCategories table, which has the following structure:

    ID ParentID Name Lft Rgt
    1 0 All Categories 1 2

    I am confident that I have created the delete function correctly and that the Lft and Rgt values are being updated with the right numbers. However, I am having difficulty writing the query for inserting records into the tblCategories table.

    I am using Celko's work sited here: http ://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427

    Item #5 of the article says that the SQL to insert a record is:

    DECLARE right_most_sibling INTEGER;

    SET right_most_sibling
    = (SELECT rgt
    FROM Personnel
    WHERE emp = :your_boss);

    UPDATE Personnel
    SET lft = CASE WHEN lft > right_most_sibling
    THEN lft + 2
    ELSE lft END,
    rgt = CASE WHEN rgt >= right_most_sibling
    THEN rgt + 2
    ELSE rgt END
    WHERE rgt >= right_most_sibling;

    INSERT INTO Personnel (emp, lft, rgt)
    VALUES ('New Guy', right_most_sibling,
    (right_most_sibling + 1))
    How do I translate this to a Classic ASP query? Here is what I have so far:

    Set objRS = GetData("SELECT * FROM tblCategories WHERE Name = '" & request.form("Name") & "'")
    Do while not objRS.eof
    right_most_sibling = objRS("Rgt")

    strSQL = "Update tblCategories SET Lft = CASE WHEN Lft > right_most_sibling THEN Lft + 2 ELSE Lft END,Rgt = CASE WHEN Rgt >= right_most_sibling THEN Rgt + 2 ELSE Rgt END WHERE Rgt >= right_most_sibling"
    set strSQL = nothing
    strSQL = "INSERT INTO tblCategories(Lft, Rgt, ParentID,Name,Description,Keywords,SortOrder,ThumbUrl,Alt,ImageUrl) VALUES ('" & SQLSafe(right_most_sibling) & "','" & SQLSafe(right_most_sibling + 1) & "','" & SQLSafe(Request("txtParentID")) & "','" & SQLSafe(Request("txtName")) & "','" & SQLSafe(Request("txtDescription")) & "','" & SQLSafe(Request("txtKeywords")) & "','" & SQLSafe(Request("txtSortOrder")) & "','" & SQLSafe(Request("txtThumbUrl")) & "','" & SQLSafe(Request("txtAlt")) & "','" & SQLSafe(Request("txtImageUrl")) & "')"
    set strSQL = nothing

    When I use the above code, I get this error when trying to add a record:

    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression 'CASE WHEN Lft > right_most_sibling THEN Lft + 2 ELSE Lft END'.

    /chosonWCv1.0/controls/datafunctions.asp, line 306

    I know it is a problem with my SQL, but how do I fix it? Any help much appreciated. I am using Access right now, but when development on my project is over, I will migrate to mySQL. But for right now, Access is easier to update on the fly.
    rdx73, Feb 3, 2007 IP
  2. TasteOfPower

    TasteOfPower Peon

    Likes Received:
    Best Answers:
    Trophy Points:
    dont hit me w/so much info LOL....

    can you break this question up into a few much smaller posts?
    TasteOfPower, Feb 4, 2007 IP