SQL server to Oracle

 i have a system which is developed using vb and connected to Microsoft SQL Server database. it is fine. but now i need to make the system compatible with oracle.

i hit a problem when i try to update a field of a recordset that select records from 2 or more table using this sql :
set rs = Conn.Open("Select Table1.column1 from Table1, Table2 where Table1.Column2 = Table2.column2")
when i do a vb statement like this:
    rs!column1 = "1"

I get this error message.
    "Run-time error '-2147467259 (80004005)'
     ROW-00009: Cannot update row in a read only rowset"

i didn't get this message if i select the record from only 1 table. i know i can solve this if i change the sql but i will need to change a lot of codes for that.

i have already try using different cursor type and different lock type for vb but it still doesn't works. any help will be much appreciated. thanks.
Who is Participating?
Ryan ChongCommented:
Remember that some recordset is not updatable.

Any way try this instead:

Conn.Execute "Update Table1 Inner Join Table2 on Table1.column1 = Table2.column2 Set Table1.Column1 = 1"
Yes use update command which is compatible to both sql server and oracle.
One thing to keep in mind is that a recordset's abilities are controlled to a great extent by the "provider" that is being used.  So, switching from SQL Server's OleDB provider to Oracle's OleDB provider means that certain recordset methods may not be allowed in certain circumstances.  You can even find differences if you switch between the native OleDB provider and the ODBC OleDB provider, or between Microsoft's OleDB provider for Oracle and Oracle's OleDB provider for Oracle....

Just because you ask for an updateable recordset doesn't mean that the recordset will be opened as updatable. The exact properties of the recordset (such as locktype and cursortype) may be changed to the closest thing that the provider you are using supports, which might be different from what you have requested.

You will have to do some testing with SQL like you have above using different combinations of providers and recordset properties to see if you can get the recordsets to be updatable.

Some things to try, try setting the recordset's cursor type to adOpenKeyset instead of adOpenDynamic (I'm guessing at the constants here).  Try making sure that your select statement includes the primary key column of the table that you plan to update.  Make sure that the table you will be updating does have a primary key defined.

For each of the different combinations of things that you try, try switching between the Microsoft OleDB Provider for Oracle, the Oracle OleDB Provider for Oracle and the ODBC OleDB Provider.  You can see if the recordset was opened to be updateable by checking the recordset's Supports property:

If RS.Supports(adUpdate) then
   msgbox "I have an updateable recordset"
End if
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

is it compulsory to give an equijoin statement. i think you can open the recordset using 1 table itself.
ryancys is correct
where Table1.Column2 = Table2.column2 - creates readonly recordset, you need to use join

but update command should be faster and better.

max1978exAuthor Commented:
i'm sorry to say that i must use the join because that's the sql i give is simplified. there are other criteria that i need to select the records.

from what i can see here is the recordset is only read only. what i really want to know is are there any ways to work around the problem without changing the sql statement? this is because the system that i'm trying to convert is quite large. if i were to change the SQL, it would mean i have to rewrite the program all over again.

Have you tried the suggestions that I gave?  This is your only hope of not having to change the SQL (you might have to anyway, but it's a chance).

It is possible to have updateable recordsets that are the product of a join, but most likely only one of the join's table's columns will be updateable (in this case, it looks OK).

However, you are going to have to experiment.  Can you show the properties that you are setting for the recordset when you open it?  Specifically, what are you setting the CursorLocation, LockType, and CursorType to?  Can you show what you are using as a connect string when opening your Connection?

As I said, using adOpenKeyset may help make your recordset updateable, however it might need to be in combination with some other settings.

Either way, I suspect that you will have to modifiy your program to some extent to port it to Oracle.
>>>there are other criteria that i need to select the records.

Regular where statements should be ok like field a= 1 and field = 2
max1978exAuthor Commented:
i have tried Cursor type adOpenKeyset and adOpenDynamic, CursorLocation = adUseServer and adUseClient, LockType = adLockPessimistic and adLockOptimistic. it still doesn't works.

the connection string i use is :
"Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=scott;Password=tiger;Data Source=Test"

by the way do u know any connection string that i can use to specify the server and the database name instead of the Service name as the data source?
Most Oracle connect strings require the service name.  The only way around it I know of is to use the OleDB provider for ODBC, and just specify a DSN name.  However, in the ODBC DSN, I think you will still have to specify the Service name.  This is the only way that the SQLNet layer can communicate with the program layer.

So, you have tried each of those things, but you have only tried them with the Oracle OleDB provider for Oracle.  You should try the provider, I think it is MSDAORA which is the Microsoft OleDB provider for Oracle.  Or, there is also, I think, MSDASQL (you'd have to look that up), which is the OleDB provider for ODBC.  Then, you have to try each of the combinations again with each of these providers.

It's not enough to just try switching from adOpenKeyset to adOpenDynamic, you have to try every combination (adOpenKeyset with adUseClient and adLockOptimistic, adOpenKeyset with adUseServer and adLockOptimistic, adOpenKeyset with adUseClient and adLockPessimistic etc.)

It is possible, that you will find a combination that will let you do this particular update.

However, because recordset updates are so particular to the Provider and underlying DBMS, most people design their applications to use SQL Updates and Inserts and Deletes.  This avoids all such problems.  If you find that you are not able to update the recordset, then, unfortunately, you should bite the bullet and rewrite a lot of your code to issue the SQL updatates as ryancys first suggested.

max1978exAuthor Commented:
can anyone tell me is it possible to connect to oracle database from a vb program without installing oracle on the client?
Well, to connect to Oracle requires the client to be able to run SQLNet which is part of the Oracle Binaries.  They don't absolutely have to be installed on the client machine, however, there are many registry entries that must be made on the client machine to tell the client where the Oracle binaries are located, and these registry entries are usually made during the installation of the Oracle Client software.

What you can do is this.  Install the Oracle Client software on a network server.  Then, export the Oracle registry tree (I think in HKey_Local_Machine\Software) out to a .reg file.  Edit the .reg file and change any path information that is there, which might look like C:\Oracle\Bin to either a drive letter that all client computers will map to (say G:) or to a UNC name:

Then, go to each client and double-click this reg file to merge these Oracle registry entries with their registries.

You will also need to edit the environment of each client computer to include the Oracle directories in the Path variable.  Go back to the server where you installed the Oracle Client, copy the Path entries that Oracle made there, and then edit each client to include those Path statements (adjusted as with the paths mentioned above)

The only thing that I couldn't get to work off the network computer was the Oracle ODBC driver.  ODBC would only look for this file in the Window's system directory, even if I set up the Path to point to the network server.

Now, this doesn't mean that it's legal to do this.  That's up to you to figure out.  I'm sure Oracle would prefer that you pay for each client who uses the Oracle Client Binaries......
>>can anyone tell me is it possible to connect to oracle database from a vb program without installing oracle on the client?

You need to develop a client-server application.
In this case you need to install SQLNet only on the server.
p.s you don’t need to register the physical component on a client machine.
You need to register some information about server component on the client pc.

p.s if you have an activex dll on the server you need to use mts or com+ to host it.

This article details a method that can be used to ensure that your client application correctly traps for and resolves error 429 at run-time if the ActiveX DLL is present on the machine but not correctly registered.

This article demonstrates how to use the CLIREG32.EXE utility to manually register remote Visual Basic components on the client machine.
max1978exAuthor Commented:
i have tried the suggestions u all gave, but it still doesn't work. i think in the end i will need to change the sql any way.

about not installing oracle on the client side, i think in the end i will install it anyway because the if i had to change the registry everytime i install my app, the i will need to do the installation for the client every time a new installation is required.

thanks for all the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.