SQL Question: UPDATE not working in VB.NET

Discussion in '.NET' started by gordonta, Mar 23, 2011.

  1. gordonta

    gordonta

    Joined:
    Mar 23, 2009
    Messages:
    8
    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!

    Code:
         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
     
    gordonta, Mar 23, 2011
    #1
    1. Advertising

  2. gordonta

    jlateehs

    Joined:
    Mar 17, 2011
    Messages:
    3
    is the ParentID a string or text value in your database or an int? What error are you getting when you debug?
     
    jlateehs, Mar 23, 2011
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. someone

    Update to SQL Server is NOT Working

    someone, Jul 7, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    343
    someone
    Jul 8, 2003
  2. William \(Bill\) Vaughn
    Replies:
    0
    Views:
    454
    William \(Bill\) Vaughn
    Aug 21, 2003
  3. David Browne
    Replies:
    0
    Views:
    464
    David Browne
    Aug 21, 2003
  4. JeffQ888
    Replies:
    0
    Views:
    192
    JeffQ888
    May 23, 2006
  5. David
    Replies:
    4
    Views:
    156
    Bob Barrows [MVP]
    Sep 28, 2006
Loading...

Share This Page