Insert multiple rows in Oracle using VB6

Question

I am trying to insert multiple rows in a table using BeginTrans...CommitTrans.
Below is code snippet:

For i = 1 To 5
    SQL = SQL & "Insert into TestTable(Field1,Field2,Field3) Values ('Col" & i & "','Col" & i + 1 & "','Col" & i + 2 & "')" & vbCrLf
Next i
conn.BeginTrans
    conn.Execute SQL
conn.CommitTrans

and following is the SQL prepared using the loop

Insert into TestTable(Field1,Field2,Field3) Values ('Col1','Col2','Col3')
Insert into TestTable(Field1,Field2,Field3) Values ('Col2','Col3','Col4')
Insert into TestTable(Field1,Field2,Field3) Values ('Col3','Col4','Col5')
Insert into TestTable(Field1,Field2,Field3) Values ('Col4','Col5','Col6')
Insert into TestTable(Field1,Field2,Field3) Values ('Col5','Col6','Col7')

When I run conn.CommitTrans I get ORA-00911: Invalid character

If I modify the SQL as

Insert into TestTable(Field1,Field2,Field3) Values ('Col1','Col2','Col3');
Insert into TestTable(Field1,Field2,Field3) Values ('Col2','Col3','Col4');
Insert into TestTable(Field1,Field2,Field3) Values ('Col3','Col4','Col5');
Insert into TestTable(Field1,Field2,Field3) Values ('Col4','Col5','Col6');
Insert into TestTable(Field1,Field2,Field3) Values ('Col5','Col6','Col7');

I get ORA-00933: SQL command not properly ended.
If I update further and replace ";" with "/" again get same error
Any help is greatly appreciated.
ThanX in advance...

Paul Michaels · Accepted Answer · 2010-11-12 09:49:06Z

1

You haven't specified what TestTable actually is (type of fields, etc). However, I would start by seeing if you can type the insert command into a standard Oracle client (e.g. TOAD)?

If you can't then check for any triggers or constraints on the table.

answered Nov 12, 2010 at 9:49

Paul Michaels

16.8k47 gold badges156 silver badges277 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeffrey Kemp · Accepted Answer · 2010-11-12 10:13:11Z

1

You probably need to execute each statement separately, e.g.:

conn.BeginTrans
For i = 1 To 5
    SQL = "Insert into TestTable(Field1,Field2,Field3) Values ('Col" & i & "','Col" & i + 1 & "','Col" & i + 2 & "')"
    conn.Execute SQL
Next i
conn.CommitTrans

answered Nov 12, 2010 at 10:13

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Collectives™ on Stack Overflow

Insert multiple rows in Oracle using VB6

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related