ADO AddNew with Oracle?

[Running Bear]

Can anyone tell me whether it is possibe to use the ADO method 'AddNew' with Oracle?

At the moment I'm having trouble assigning the primary key (see bold below). I'm getting a type mismatch error??? .... Help! ;)


Set cn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")

' Open the Oracle Connection using an ODBC DSN
cn.open "DSN=fastcont;uid=fcontdba;pwd=harare"

' Set the cursor type & Location
rs.CursorType = 3 ':adOpenStatic, 2 ':adOpenDynamic, 1 ':adOpenKeyset
rs.LockType = 4 ':adLockBatchOptimistic
' Open the Recordset
rs.Open "MyTestTable", cn
rs.AddNew
rs.Fields("MyTestTable_ID") = "MyTestTable_ID.nextval"
rs.Fields("ANumber") = 1
rs.Fields("SomeText") = "This is"
rs.AddNew
rs.Fields("MyTestTable_ID") = "MyTestTable_ID.nextval"
rs.Fields("ANumber") = 2
rs.Fields("SomeText") = "some text"
rs.UpdateBatch


Cheers Al

[Roelf]

probably because you are trying to enter a string as value for the field, my guess is: loose the quotes in that line, but i dont know the nextval property so i cant be sure

[Roelf]

and you shouldn´t crosspost, http://codingforums.com/showthread.php?s=&threadid=9597

and on top of that, perhaps this post should be in the original question: http://codingforums.com/showthread.php?s=&threadid=9455

[Running Bear]

Roelf,

This is the problem I have as 'MyTestTable_ID' is the name of my sequence in Oracle. The .nextval gets the next value in the sequence and is used as the primary key.
Unfortunately Oracle doesn't work in the way that Access and SQL Server do in that the primary key field is automatically taken care of. If I wrote the query out like this

SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(MyTestTable_ID.nextval, 1, 'This is '); "

I would not have any problems inserting the data, but because I want to insert multiple records at the same time I'm having problems

Al

[Roelf]

cant you get the highest value for the ID column before you create the recordset (easily done with one simple query), then do the increment of the ID in your code and use the calculated id in the created recordset?

[Running Bear]

Roelf,

Thanks for pointing out that I shouldn't cross post, but I'm trying to find a solution to my problem and not everyone jumps from forum to forum.

You have my humble apologies!

I've altered my approach since you suggested I use the addnew method, I should have included a link to the original question again apologies!

[Running Bear]

Roelf,

Yes you can do that, however it defeats the point of using the sequence, which means I will need to manage it from my code... Not ideal. However if it's the only way to do it then what choice do I have.

Thanks for your help

Al

[whammy]

Please don't cross-post, as people usually DO frequent all of the forums of languages, etc. they are familiar with here (for instance, I will visit this forum, javascript forum, general server-side problems forum, general web building, css, pc/computer issues, etc.... and I know most of the people here do as well!). I'll take your apology to roelf just as well, though.

:)