ORA-01036: when using Insert command on datasource

Question

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

My insertCommand is as follows:

InsertCommand="INSERT INTO DVD VALUES (DVD_SEQ.NEXTVAL, :DVD_TITLE, :RENTAL_COST, :RATING, :COVER_IMAGE)"

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
(
dvd_id integer primary key not null,
dvd_title char(100) not null,
rental_cost number(9,2) not null,
rating integer not null,
cover_image char(100),
foreign key(rating) references RATING(rating_id)
);

I'm thinking it's a potential conflict of variable types, as the types for the variables in the parameter section do differ from that in my table but I've tried swapping them around to more fitting types to no avail!

You may specify the column names as well in your insert query and try, `Insert into DVd(yourolumns....) values(...)` — Habib, Oct 15 '12 at 04:48
@Habib I don't think this is necessary, I have executed the same syntax query on the DB and it returned no errors. — Linky, Oct 15 '12 at 04:55
@JoachimIsaksson GENRE_ID isn't being used, it's used in the second query of the insert command which I'm yet to add as I'm still debugging this query :( — Linky, Oct 15 '12 at 04:56
The [documentation for the error](http://ora-01036.ora-code.com/) says `Action: Make sure that the variable being bound is in the sql statement.` which `GENRE_ID` is not. Try removing it. — Joachim Isaksson, Oct 15 '12 at 05:09
@JoachimIsaksson You're completely correct. Thank you very much for your help! — Linky, Oct 15 '12 at 05:10

score 0 · Answer 1 · answered Oct 15 '12 at 05:18

ORA-01036: illegal variable name/number means that you're binding a variable that is not a part of the SQL query.

In other words, you need to remove GENRE_ID from your InsertParameters (bound parameters) since it's not a part of the SQL and the error should go away.

ORA-01036: when using Insert command on datasource

1 Answers1