none
ADO OleDb command object with parameter fails and does not return any error. RRS feed

  • Question

  • I have an OleDb command object with one parameter which is an XML string.  When it executes I get nothing at all, that is, no error, no results, nothing in SQL profiler...nothing.  

    I've included a screenshot of the Watch, I can't see anything that is wrong.  In addition, the same string being used here as the parameter value works with out error when run in a Query as the Parameter value in EXEC spSLXInsertHistory Stored Proc in SQL Management Studio, so the Stored Procedure is working.  

    I've completely run out of ideas.  Similar command objects work without error when I'm using SQL Command objects, but I rarely use OleDB.  Could it be related to OleDb in some way.  

    Thanks for any suggestions. 

    Tim 


    Code is here.....

    Public Shared Function ExecuteSP_OleDB(ByVal storedprocedure As String, _
       ByVal cn_slx As OleDbConnection, ByVal ParamArray arrParam() As OleDbParameter) As Integer
            Dim cmd As New OleDbCommand
            cmd.Connection = cn_slx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = storedprocedure
            cmd.Parameters.Clear()
            Dim i As Integer = 0
            If arrParam IsNot Nothing Then
                For Each param As OleDbParameter In arrParam
                    Debug.Print(CStr(arrParam(i).Value))
                   
                    cmd.Parameters.Add(param)


                    i = i + 1
                Next
            End If
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                cn_slx.Dispose()


            End Try


        End Function

    Tim Conway

    Tuesday, March 27, 2012 5:08 PM

Answers

  • Here is the answer.  Don't use OLEDB if it's a Sage SalesLogix OLEDB Data provider.  Use regular old Microsoft SQL Server (SqlClient) if your putting together a .NET application and skip the OLEDB.  Turns out the OLEDB data provider used for Sage SalesLogix is specific to the application.  Likely a standard OLEDB connection would have worked, but I never tried as there was no need.  

    Thanks to everyone for your input.  

    Tim


    Tim Conway


    • Edited by tconway Thursday, March 29, 2012 9:45 PM
    • Marked as answer by tconway Thursday, March 29, 2012 9:45 PM
    Thursday, March 29, 2012 9:44 PM

All replies

  • Hi Tim,

    In my slight looking at your code, you put your procedure as a function (Public Shared Function ExecuteSP_OleDB) but no value returned from that function. It is better to use Public Shared Sub ExecuteSP_OleDB for that purpose, I mean no returning value.

    Any way pls check whether data has been inserted or not.

    Hope this help,

    Agung Gugiaji

    My application developer journals



    Wednesday, March 28, 2012 6:46 AM
  • I don't see anything obvious but I will assume that you have also looked at the cmd.Parameters collection and verified that all of the parameters are present. Also, they should be in the same order (ordinal position) as declared in the stored procedure, since ADO.NET does not support named parameters under OLEDB.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, March 28, 2012 12:21 PM
  • Here is the answer.  Don't use OLEDB if it's a Sage SalesLogix OLEDB Data provider.  Use regular old Microsoft SQL Server (SqlClient) if your putting together a .NET application and skip the OLEDB.  Turns out the OLEDB data provider used for Sage SalesLogix is specific to the application.  Likely a standard OLEDB connection would have worked, but I never tried as there was no need.  

    Thanks to everyone for your input.  

    Tim


    Tim Conway


    • Edited by tconway Thursday, March 29, 2012 9:45 PM
    • Marked as answer by tconway Thursday, March 29, 2012 9:45 PM
    Thursday, March 29, 2012 9:44 PM