Help with Recordset.AddNew 1

[jabond007]

The backend is orcale 8i i am using MS OLE DB provider for Oracle.

When i do Recordset.addNew i get the following error
Run time Error : 3251
The operarion requested by the application is not supported by the provider

Any body knows why this is happening.

Help would be appreciated

 

[culshaja]

If you haven't changed the default settings on the recordset then it is set to

adOpenForwardOnly

.
You need to change this to either

adOpenKeyset

or

adOpenDynamic

.


James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[Deltaflyer]

I have never used VB running with orcale8, but i have used it with SQL7 and other simpler stuff like Access, Excel, Word, Etc.

Things that have messed with my programs before,

do you have insert permissions on the database ?
do you have text going into date fields ?
do you have text/integers going into integer/text fields ?
do you have any NULL entries going into Non-Null allowing fields ?
have you correctly linked through into the database ? ( can you read data? ).
have you managed to get an update to work ?

I know these are probably things you have already tried but you didn't give us much to go on.

Any chance of a copy of the coding you are using?
(Not all of it just the database access part.)

DeltaFlyer DeltaFlyer - The Only Programmer To Crash With Style.

 

[jabond007]

thanks James for your reply. I am using adOpenDynamic. the syntax is aas below. I cant edit any field

Set rsRecon = New Adodb.Recordset
rsRecon.Open strSQL, cnRecon, adOpenDynamic,adLockOptimistic

Hope somebody can tell me why

 

[culshaja]

What is your connection set to? Thishas a bearing as it will override your cusrsor type under certain situations.
You aint getting the data via an Oracle PL/SQL procedure are you?

As an aside - is there any reason why you aren't using OO4O as it is MUCH better when talking to Oracle databases than ADO?

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jabond007]

Delta flyer

thanks....I do have all the rights to the database. It gets connected. I can read data from the recordset. But i cant edit any field nor can i add new record nor update recordset.

the code is as follows

for connection
------------------
Set cnRecon = New Adodb.Connection
cnRecon.Open "provider=MSDAORA.1;user id=scott;password=tiger;Data Source=BSDWD"

for recordset
--------------
strSQL = "SELECT USER_ID,USER_NAME,USER_LAST_NAME,DEPT,TRADER FROM USERS_WIL1 where 1=0"
Set rsRecon = New Adodb.Recordset
rsRecon.Open strSQL, cnRecon, adOpenDynamic,adLockOptimistic

I need to update the recordset and ultimately the Database without using insert SQL (incidently that way it works).

Regards

 

[Deltaflyer]

James,

Are you trying to input/update to the database while you have it already open?

if you are using the above connection,recordset call and then you are trying to update the database, DeltaFlyer - The Only Programmer To Crash With Style.

 

[Deltaflyer]

Sorry, stupid Netscape. it crashed and sent half a message.

James,

Are you trying to input/update to the database while you have it already open?

if you are using the above connection,recordset call and then you are trying to update the database, although this should work, i have had problems while trying to do this with SQL Server 7.

???? - I know this is going to sound silly and you probably have good reasons, but why can't you use INSERT INTO. -????

DeltaFlyer


DeltaFlyer - The Only Programmer To Crash With Style.

 

[culshaja]

I still think that is has something to do with the connection. Try setting the connection to firstlt adUseServer and then adUseClient. Also set the ActiveConnection property seperately. Try the following:

Set cnRecon = New Adodb.Connection
cnRecon.ConnectionString = "provider=MSDAORA.1;user id=scott;password=tiger;Data Source=BSDWD"
cnRecon.CursorLocation = adUseClient
cnRecon.Open 


strSQL = "SELECT USER_ID,USER_NAME,USER_LAST_NAME,DEPT,TRADER FROM USERS_WIL1 where 1=0"
Set rsRecon = New Adodb.Recordset
set rsRecon.ActiveConnection = cnRecon
rsRecon.CursorType = adOpenDynamic
rsRecon.Source = strSQL
rsRecon.Open

I know the aboce code is a bit verbose compared to what you wrote but belive me it is far more efficient and less bug prone. Its also easier to maintain. I cannot stress hard enough that this is the better way to use ADO than bundling things together in the Open statement etc.

Sorry about the preaching but it is something that I feel VERY strongly about.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jabond007]

thanks guys....i think i will try OO4O

But i think i found the problem....It ia a conflict in the ADO version..Microsoft has a componet checker to check the versions. I need to talk to them and find which version to use. the version 2.1 gets overwritten by later versions when new applications are loaded to the machine....

I am not using a SQL/Procedure. I can use insert but in this application. i am trying to parse a text file to the database. So i need to create a new record to capture data. My problem is cant create the new record.

thanks guys. I learnt a few tricks from u guys

 

[btousig]

Did you get this to work? I tried the tip with no success.