ExecuteNonQuery() Issue

[ingbat]

Can you take a look at this code and see if there is anything obviously wrong with it. It’s just a simple update query to a backend Sybase DB, but it’s not updating and not returning any errors. My updates to an SQL DB are working, so I’m wondering if it’s a Sybase thing…?? The connection to the DB is working as I’ve already retrieved the data for processing in the first place. I’m using Visual Studio 2008. Thanks very much…

<code>
Public Function UpdateDap(ByVal ScanLog As Integer, ByVal WFID As Integer) As String
Dim myOdbcConnection As OdbcConnection
Dim myOdbcCommand As OdbcCommand
Dim ra As Integer
myOdbcConnection = New OdbcConnection(DAPConnectionString)
If WFID <> -1 Then
Try
myOdbcConnection.Open()
myOdbcCommand = New OdbcCommand("sp_set_ew_workflowid", myOdbcConnection)
myOdbcCommand.CommandType = CommandType.StoredProcedure
myOdbcCommand.Parameters.Add(New OdbcParameter("@tranId", OdbcType.Int, InputString)).Direction = ParameterDirection.Input
myOdbcCommand.Parameters.Add(New OdbcParameter("@ewId", OdbcType.Int)).Direction = ParameterDirection.Input
myOdbcCommand.Parameters("@tranId").Value = ScanLog
myOdbcCommand.Parameters("@ewId").Value = WFID
myOdbcCommand.CommandTimeout = 0
ra = myOdbcCommand.ExecuteNonQuery()
myOdbcConnection.Close() : myOdbcConnection.Dispose()
Catch ex As Exception
Log("UpdateDap", ex.Message, w)
myOdbcConnection.Close()
End Try
End If
Return (ra)
End Function
</code>

[kvprajapati]

Please check your stored-procedue.

Public Function UpdateDap(ByVal ScanLog As Integer, ByVal WFID As Integer) As String
        Dim myOdbcConnection As OdbcConnection
        Dim myOdbcCommand As OdbcCommand
        Dim ra As Integer
        myOdbcConnection = New OdbcConnection()
        If WFID <> -1 Then
            Try
                myOdbcConnection.Open()
                myOdbcCommand = New OdbcCommand("sp_set_ew_workflowid", myOdbcConnection)
                myOdbcCommand.CommandType = CommandType.StoredProcedure
                myOdbcCommand.Parameters.AddWithValue("@transId", ScanLog)
                myOdbcCommand.Parameters.AddWithValue("@ewId", WFID)
                myOdbcCommand.CommandTimeout = 0
                ra = myOdbcCommand.ExecuteNonQuery()
                myOdbcConnection.Close() : myOdbcConnection.Dispose()
            Catch ex As Exception
                Log("UpdateDap", ex.Message, w)
                myOdbcConnection.Close()
            End Try
        End If
        Return (ra)
    End Function

[ingbat]

The stored procedure is fine. I can Execute it from Interactive SQL using the same paramaters and the DB is updated.

[TomW]

Have you tried stepping through the code in debug mode to make sure the event is actually firing with the expected parameter values and also that "ra" is returning a number stating whether or not any records were actually updated or not?

[ingbat]

Yes I have, ra is returning 0.

[ingbat]

This is resolved. Changed the code to this and got rid of the paramater stuff
<code>
Dim MySQL as string
MySQL = "execute sp_set_ew_workflowid " & ScanLog & ", " & WFID
myOdbcCommand = New OdbcCommand(MySQL, myOdbcConnection)
myOdbcCommand.CommandType = CommandType.Text
myOdbcCommand.CommandTimeout = 0
ra = myOdbcCommand.ExecuteNonQuery()
</code>

The other way should have worked to, but if I have to do it this way, I'm a happy camper