ORA-00911: when using Insert command on datasource

Question

Trying to set up an insert statement in asp.net.

My double line insertCommand is as follows:

InsertCommand="INSERT INTO DVD(DVD_ID, DVD_TITLE, RENTAL_COST, RATING, COVER_IMAGE) VALUES (DVD_SEQ.NEXTVAL, :DVD_TITLE, :RENTAL_COST, :RATING, :COVER_IMAGE);
        INSERT INTO DVD_GENRE(DVD_ID, GENRE_ID) VALUES (DVD_SEQ.CURRVAL, :GENRE_ID)" 

The code for the insert parameters are:

<InsertParameters>
    <asp:ControlParameter ControlID="titleBox" DefaultValue="TITLEDEFAULT" 
        Name="DVD_TITLE" PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="rentalBox" DefaultValue="99" 
        Name="RENTAL_COST" PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="ratingList" DefaultValue="25" Name="RATING" 
        PropertyName="SelectedValue" Type="Int32" />
    <asp:Parameter Name="COVER_IMAGE" Type="String" DefaultValue="0.jpg" />
    <asp:ControlParameter ControlID="genreList" Name="GENRE_ID" 
        PropertyName="SelectedValue" />
</InsertParameters>

And my OracleDB table (DVD) which is being inserted into is:

create table dvd_genre
(
dvd_id integer not null,
genre_id integer not null,
primary key (dvd_id, genre_id),
foreign key(dvd_id) references DVD(dvd_id),
foreign key(genre_id) references GENRE(genre_id)
);

My only guess at what's causing this is the ';' character to break up the two insert statements. Yes I understand I should probably create a trigger but I've read this can work as well. If I remove the ';' character I get an error about the query not being properly ended. Any help would be much appreciated!!

Answer 1

1) "ORA-00911" means "invalid character"

2) Yes, the ";" is a good candidate. Q: Can you execute two SQL inserts in a single ASP command? I don't know ;)

3) Suggestion: break it down to one SQL statement, and see if the problem goes away.

4) If so, your options include:

a) issuing two SQL statements in the same place as your ASP code (that's what I'd do, but I don't know if it's an option in your program)

b) use a trigger (definitely not what I'd do)

c) create a PL/SQL stored procedure and invoke it from your ASP instead of the raw SQL (arguably the best choice of all)

IMHO..