ora-00900: invalid SQL statement

[Casiopea]

I am using visual basic 6 and I want to add rows to a table in oracle.
If I use a recordset and assign to the source the name of a table it gives the ora-00900 error, however if I use the sql "select * from tabla1" it doesn't throw the error. The error appears just after rs.open

rs as new adodb.recordset
rs.source = "tabla1"
---It throws the error

rs.source = "select * from tabla1"
---It doesn't throw the error

 

[Thargy]

Please post the create table statement for the table into which you are attempting the insert.

Then post the insert statement you're attempting to use. That way we can determine whether or not the problem lies on the Oracle or VB side.

Regards

T

 

[HarleyQuinn]

If it is a VB problem (it could well be, .Source isn't generally used when opening Recordsets), it might be better to use a YourConnection.Execute and pass in the INSERT statement as a parameter.

Also, you don't show us rs.open (and possibly some more code leading up to it) so it's hard to say the exact reason (could also be to do with the type of cursor you're trying to use when opening the recordset), or one of many other things.

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Casiopea]

The code is as follows:
conexion_ora=new adodb.connection
conexion_ora.Open "Provider=MSDASQL;DSN=BDAPOL;DATABASE = BDAPOL", "bdapolicia", "xxxx"
rsoracle.ActiveConnection = conexion_ora
rsoracle.CursorType = adOpenKeyset
rsoracle.CursorLocation = adUseServer
rsoracle.LockType = adLockPessimistic
rsoracle.CacheSize = 10
rsoracle.Source = "pol_his"
rsoracle.Open

rsoracle.AddNew
rsoracle.Fields("codigo_p") = rsinformix.Fields("codigo_p")
rsoracle.Fields("fecha_c") = rsinformix.Fields("fecha_c")
rsoracle.Fields("codigo") = rsinformix.Fields("codigo")
rsoracle.Fields("motivo") = rsinformix.Fields("motivo")
rsoracle.Fields("tipo") = rsinformix.Fields("tipo")
rsoracle.Fields("codigo_nue") = rsinformix.Fields("codigo_nue")
rsoracle.Fields("usuario") = rsinformix.Fields("usuario")
rsoracle.Fields("fecha_u") = rsinformix.Fields("fecha_u")
rsoracle.Fields("ultimo") = rsinformix.Fields("ultimo")
rsoracle.Fields("clave_act") = rsinformix.Fields("clave_act")
rsoracle.Fields("clave_his") = rsinformix.Fields("clave_his")
rsoracle.Update

-----------------------------------------------------------
if rsoracle.source = "select * from pol_his" it doesn't show the error.
if rsoracle.source = "pol_his" it gives that error.
Before the database was in informix and it worked like this.
-----------------------------------------------------------
The table creation was as follows:
CREATE TABLE BDAPOL.pol_his
(
codigo_p NVARCHAR2(4),
fecha_c DATE,
codigo NUMBER,
motivo NVARCHAR2(60),
tipo CHAR(1),
codigo_nue NUMBER,
usuario NVARCHAR2(8),
fecha_u DATE,
ultimo CHAR(1),
clave_act NUMBER,
clave_his NUMBER,
FOREIGN KEY (codigo_p) REFERENCES personal (codigo_p)
) TABLESPACE TS_POL;

There was a primary key but a drop it to check if that was the problem, but it wasn't.
Greetings and thank you.

 

[Thargy]

Cas,

from your line which works correctly (rsoracle.source = "select * from pol_his" ) it looks like the fact that you're using a correct sql statement is helping.

Are you trying to bring all the records from pol_his into your VB application, and manipulate them there? If you are, then I believe that VB has a way of doing this. However, I am not expert in VB so I can't say for sure.

Regards

T

 

[Casiopea]

The question is with informix it worked, and now with oracle it doesn't, that would mean that I should change all the application, to use a "select *..." there, where there is a source=<table-name> and I don't understand why because the recordset property it admits table names, so I think the problem is from oracle or maybe the driver, but I don't guess where.