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:

    BEGIN
    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))
    END;

    How do I translate this to a Classic ASP query? Here is what I have so far:

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

    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"
    ExecuteQuery(strSQL)
    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")) & "')"
    ExecuteQuery(strSQL)
    set strSQL = nothing
    CloseDB()
    %>


    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

    Messages:
    572
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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