Below is some sample code. Create a new VB console application, add a reference to the Oracle.DataAccess.dll assembly, and add Oracle.DataAccess.Client to the Imported namespaces using the properties page for the project. Copy and paste the code into the .vb source file. Do you get any errors? Does it compile? Is there anything wrong with this code?
Module Module1
Sub Main()
' connection string -- change as necessary
Dim constr As String = "User Id=scott; " & _
"Password=tiger; " & _
"Data Source=orademo; " & _
"Enlist=false; " & _
"Pooling=false"
' will use below
Dim ds As New DataSet
Dim da As OracleDataAdapter
Dim con As OracleConnection
' open connection
con = New OracleConnection(constr)
con.Open()
' get a data adapter for the emp table
da = New OracleDataAdapter("select * from emp", con)
' get the schema information
da.FillSchema(ds, SchemaType.Source)
' get command builder from data adapter
Dim cb As New OracleCommandBuilder(da)
' set the insert command from the command builder
da.InsertCommand = cb.GetInsertCommand(True)
' simple prompt to keep console from closing when
' run from within the Visual Studio environment
Console.WriteLine("ENTER to continue...")
Console.ReadLine()
End Sub
End Module
If the code compiles, when you try to run it, do you get an error? If you get an error, does it indicate: "The DataAdapter.SelectCommand property needs to be initialized."
Hmm. Strange. It looks like the SelectCommand is being initialized right there in the OracleDataAdapter constructor: da = New OracleDataAdapter("select * from emp", con)
A quick check in the Oracle Data Provider for .NET documentation shows that the constructor for OracleDataAdapter can definitely take a string which represents the select command text and a connection object. Yet you may get an error stating that the SelectCommand property needs to be initialized.
If this code compiled fine and yet you get a runtime error, here's a pointer - add the following to the top of the source code file:
' Enable Option Strict checking
Option Strict On
(You can also change this via the project property page on the Compile tab)
Did anything change in the source code? In particular this part of the source code:
' set the insert command from the command builder
da.InsertCommand = cb.GetInsertCommand(True)
If Option Strict is not enabled, which happens to be the default, VB will try to perform an implicit conversion from one data type to another data type with no compile-time warning. However, at runtime the conversion may fail. In addition, if you consult the ODP.NET documentation you will see that there is no OracleCommandBuilder.GetInsertCommand that takes a Boolean. However, OracleCommandBuilder inherits from System.Data.Common.DbCommandBuilder which does have an overloaded GetInsertCommand that takes a Boolean.
So, in the case of the (incorrect) code above, DbCommandBuilder.GetInsertCommand(Boolean) is the method that was to be invoked not the OracleCommandBuilder.GetInsertCommand. This is the cause of the runtime error and explains the complaint about the select command not being initialized. If you try to compile the code with Option Strict enabled you should receive: "Option Strict On disallows implicit conversions from 'System.Data.Common.DbCommand' to 'Oracle.DataAccess.Client.OracleCommand'."
From my perspective it seems like a good idea to always enable Option Strict.
