can't run stored procedure

[jonnyreed]

Hi Guys

I’m new to this forum thing.

I come from a VB6 background and am trying to get a VB.NET application to execute an Oracle Stored Procedure using ODBC connection. I keep getting the error message Invalid SQL statement. I know the stored procedure works as it is currently being used in a VB6 application so it must be the way I’m calling it. The code I am using is as follows:

Dim conn As New System.Data.Odbc.OdbcConnection
Dim ds As New DataSet
Try

conn.ConnectionString = "DSN=ABCD;uid=jonny;pwd=jonny"

conn.Open()
Dim mycmd As New OdbcCommand
mycmd.Connection = conn
mycmd.CommandText = "jonny"
mycmd.CommandType = CommandType.StoredProcedure
mycmd.ExecuteReader()

Catch x As Exception
MessageBox.Show(x.Message.ToString)
End Try

Has anyone any ideas what could be wrong.

Any help or suggestions would be greatly appreciated.

Thanks

Jonny

[Sawamu]

Probably you are facing a problem with the mycmd.ExecuteReader().

If you just want to run the stored procedure, use the ExecuteNonQuery.

But if you want to use the ExecuteReader to read row by row, get it to a variable, use it and close it at the end.

Like:

Dim sqrReturn As SqlDataReader

sqrReturn = mycmd.ExecuteReader()

Then you will need to perform a loop for read each value and when there will be no more values, close the reader.

Hope this helps...

[jonnyreed]

Hi Sawamu

Thanks for your reply.

I tried this and still get the same error message "Invalid SQL". I know the SQL is fine as it works with VB6.

Can you post any code that you use to execute an Oracle stored procedure.

Your help is much appreciated.

Thanks

jonnyreed

[CyberHawke]

There would be a significant problem with using the SqlDataReader when working with Odbc Connections and Command Objects.

Try executing it against an OdbcDataReader and see if that fixes your problem.

Dim conn As New System.Data.Odbc.OdbcConnection
    Dim ds As New DataSet ' You don't need this if you are using a reader
    Dim rdr As System.Data.Odbc.OdbcDataReader
    Dim mycmd As New OdbcCommand
    Try
        conn.ConnectionString = "DSN=CO51D;uid=aasc;pwd=aasc"
        conn.Open()
        mycmd.Connection = conn
        mycmd.CommandText = "jonny"
        mycmd.CommandType = CommandType.StoredProcedure
        rdr = mycmd.ExecuteReader()
    Catch x As Exception
        MessageBox.Show(x.Message.ToString)
    End Try

[jonnyreed]

CyberHawke

Still no joy with this. I'm still getting the same error message "Invalid SQL Statement".

I am connecting to an Oracle 9i database. Do I maybe need someting enabled on the database side?

It is working fine with VB6.

I can get the stored procedure to work with OracleClient but when you use this you have to specify the server name and I want to just use the DSN.

Any other ideas would be much appreciated.

Many thanks

jonnyreed

[CyberHawke]

Just a personal observation but ODBC is such an antiquated technology that even though it works under .NET, it has issues. Microsoft even has disdain for it's use, I had a link here but seem to have lost it. There are some problems associated with the Microsoft Oracle ODBC Driver and OLEDB Provider which are worth checking out here: INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider and although the OLEDB provider is mentioned, I have personally found it to be more stable. Having said that, I do recognize that there are some envrionments out there that are "married" to ODBC and if you really need to stick with the technology then I recommend performing a thorough search of MSDN for key words like "odbc .net oracle stored procedures" probably others too, but these are good starts.

Sorry I can't be of much help at the moment, but perhaps when my Oracle DBA comes in, I can have a talk with him.

[jonnyreed]

Thanks for your help CyberHawke.

I'll continue to investigate but I think I'll have to use the OracleClient and just specify the server name.

Thanks again

Jonnyreed