Results 1 to 7 of 7

Thread: DbCommand.ExecuteNonQuery Never Completes

  1. #1
    StoneCodeMonkey is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2009
    Posts
    56
    Reputation
    98

    DbCommand.ExecuteNonQuery Never Completes

    First things first, here is the code.
    Code:
        Public Shared Function ExecuteNonQuery(ByVal ConnectionString As String, ByVal factory As DbProviderFactory, ByVal sql As String) As Integer
    
            'When this function executes
            'factory = {Oracle.DataAccess.Client.OracleClientFactory}
            'sql = "update downtimearea set area = 'yyy' where downtimearea = '01'"
            Dim rowsAffected As Integer = -1
    
            'This works.
            Using conn As DbConnection = factory.CreateConnection
                conn.ConnectionString = ConnectionString
                conn.Open()
                If conn.State.Equals(ConnectionState.Open) Then
    
                    Try
                        'This works
                        Dim command As DbCommand = conn.CreateCommand
                        command.CommandText = sql
                        command.CommandType = CommandType.Text
    
                        '**********This blocks and never returns.
                        rowsAffected = command.ExecuteNonQuery()
                    Catch ex As Exception
                        '**********No exception is ever thrown.
                        Debug.Print(ex.Message)
                    End Try
    
                End If
            End Using
            Return rowsAffected
        End Function
    I am executing this code against a simple test database. I have a similar function that has the same signature, but returns a DataTable and it works fine. For the life of me I can not figure out why ExecuteNonQuery will not work. I have checked the database and the value is never updated. If I paste the sql into SQLDeveloper it executes just fine and the table is updated as expected.

    Any ideas?
    Last edited by StoneCodeMonkey; 10-03-2012 at 9:03 AM.

  2. #2
    IanRyder is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Sep 2012
    Location
    Healing, NE Lincs, UK
    Posts
    1,128
    Reputation
    742
    Hi,

    This comment is a bit of a stab in the dark since I have no experience with Oracle and you state that you have already tried this and it works but:-

    Is your SQL statement correct "update downtimearea set area = 'yyy' where downtimearea = '01'"

    From an SQL server point of view the structure of an sql update statement is:-

    Update <TableName> Set <FieldName> = <Value> where <FieldName> = <AnotherValue>

    Your SQL statement seems to suggest the structure:-

    Update <TableName> Set <FieldName> = <Value> where <TableName> = <AnotherValue>

    Notice the use of your table name in the selection element of the query?

    Of course you could have a field name which is the same as the table name but just seems a bit strange to me.

    Cheers,

    Ian

  3. #3
    StoneCodeMonkey is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2009
    Posts
    56
    Reputation
    98
    Unfortunately there is a column name that is also the table name, so it does add some confusion. Poor schema design.

    The SQL statement however is correct. I can execeute it from SQLDeveloper and the table updates as it should. Thanks for the stab.

  4. #4
    StoneCodeMonkey is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2009
    Posts
    56
    Reputation
    98
    Well I feel silly. The row in the table I was testing against had uncommited updates from my session in SQLDeveloper. It would seem that ODAC will wait as long as necessary for the row to be unlocked.

  5. #5
    IanRyder is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Sep 2012
    Location
    Healing, NE Lincs, UK
    Posts
    1,128
    Reputation
    742
    I take it you are sorted now then?

    I was just about to post another silly idea for you to try.

    If you are sorted then don't worry about it, we have all experienced silly issues that cause us to think there are bigger issues at work.

    Cheers,

    Ian

  6. #6
    StoneCodeMonkey is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2009
    Posts
    56
    Reputation
    98
    Quote Originally Posted by IanRyder View Post
    I take it you are sorted now then?

    I was just about to post another silly idea for you to try.

    If you are sorted then don't worry about it, we have all experienced silly issues that cause us to think there are bigger issues at work.

    Cheers,

    Ian
    Sorted. Yes. I understand the cause.

    However I should have been able to set command.CommandTimeout = n and handle the exception, but even the Timeout will not occur if the table or row is locked. Just need to figure how I want to handle this when it occurs. Its kind of weird, the thread hangs until the lock is remove, but .Net recognizes that the Timeout has been exceeded and throws the exception. I would have thought that "Timeout" would occur at the time interval set regardless of the reason. I suspect that the native call is synchronous and the thread just can't continue until it returns from the native code.

    Thanks

  7. #7
    tulasirn is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Jan 2017
    Posts
    1
    Reputation
    0
    Hey StoneCodeMonkey, Did you figure out how to handle the situation you described above? I'm running into the same problem and stuck.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •