Post reply

Executing another Sproc within CLR Sproc.....

  • April 14, 2008 at 3:14 am

    #189097

    First time writting a CLR sproc and need a littel help. I am using the clr to loop thru a record set, I am also relatively new to VB.net.. Anyway, I am opening a recordset and looping thru it and validating certain conditions. One of those conditions is whether the State abbrevation is a valid State... I have a sproc written in T-SQL that does that just fine against my State table.

    First off let me state that I have no error handling in the CLR sproc. However the debug window in VS is giving this error

    A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

    The State Valid proc returns a 0 or 1...

    Here is how I am calling it.

    Dim command As SqlCommand

    Dim strReturn As String

    Using conn1 As New SqlConnection("context connection=true")

    command = New SqlCommand("sproc_ValidateStateAbbr", conn1)

    command.CommandType = CommandType.StoredProcedure

    command.Parameters.AddWithValue("@State_Abbr", strDbtrState)

    strReturn = command.ExecuteNonQuery.ToString

    '*************************************************************

    '* If the Return from the proc is 0 then the state is not good

    '*************************************************************

    If strReturn = "0" Then

    blnFailedValidation = True

    End If

    End Using

    It fails on this line

    strReturn = command.ExecuteNonQuery.ToString

    I really just need to know how to execute another stored procedure from within a CLR stored procedure...

    Thanks in advance.

    Tim

  • April 15, 2008 at 8:19 am

    #802605

    Add this line:

    conn1.Open()

    Just before ExecuteNonQuery.

    The context connection is, of course, already open, but in this case you are opening the SqlConnection object (allowing it to connect to the already open context connection).

    Help for SqlCommand.ExecuteNonQuery doesn't include InvalidOperationException. This is an oversight. It is mentioned in IDbCommand.ExecuteNonQuery help and states that it may be thrown when the connection does not exist or is not open.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply