«

[gordonta]

I really hope I'm simply missing a syntax rule somewhere, but after extensive debugging I'm left with no other option but to beg you SQL Gurus out there.

My issue is that the UPDATE command, that increments the number of children a tree node has simply will not work. Everything else works fine, just no updating children. Please, I do know how messy the code is right now, clean up comes after functionality in my book

Thanks for your help!

     Try
            con.Open()
            Dim objcmd0 = New OleDb.OleDbCommand("SELECT id FROM SM_Tree WHERE sm_name = 'Situation'", con)
            Dim parentId As Integer = CInt(objcmd0.ExecuteScalar())
            Dim objCmd = New OleDb.OleDbCommand("INSERT INTO SM_Tree (parentId, sm_name, sm_data, children) VALUES (" & parentId.ToString & ", 'Event', '" & eventTextBox.Text & "', 0)", con)
            objCmd.ExecuteNonQuery()
            Dim objCmd2 = New OleDb.OleDbCommand("SELECT @@Identity", con)
            Dim id As Integer = CInt(objCmd2.ExecuteScalar())
            Dim objCmd4 = New OleDb.OleDbCommand("SELECT children FROM SM_Tree WHERE id = " & parentId.ToString & "", con)
            Dim oldChildrenValue As Integer = CInt(objCmd4.ExecuteScalar())
            oldChildrenValue += 1
            Dim sql As String = "UPDATE SM_Tree SET children = " + oldChildrenValue.ToString + " WHERE id = " + parentId.ToString
            Dim objCmd3 = New OleDb.OleDbCommand(sql, con)
            objCmd2.ExecuteNonQuery()
            con.Close()
            con = Nothing
        Catch ex As Exception
            con.Close()
            con = Nothing
            Server.Transfer("~/ErrorPage.aspx?errorMessage=" + ex.ToString)
        End Try

[jlateehs]

is the ParentID a string or text value in your database or an int? What error are you getting when you debug?