Need help with Oracle insert

[anon]

I am using the following code to insert into oracle9i
database using package/stored procedure. The proc should
insert one record at a time to a table in the Default
scott schema.

When I step through the code, everything seems to work
fine and I get the number of records affectd as 1.
However, no records are inserted and no error occurs.

This code is being called from a Web service. What am I
doing wrong?

Do I need any special permissions/grants, etc to do this
insert? If so how do I do that. I can use the Web
Service to read from the same database, but can not write
to it.


Thanks


public int InsertMovie(int ID, int Duration, int Media,
DateTime Date, int Genre, int Rental, string
Rating,
string Site, string Studio)
{
OracleCommand oraCmd = new OracleCommand
("MoviePkg.sp_InsertMovie",this.Connection);
oraCmd.CommandType = CommandType.StoredProcedure;
oraCmd.Parameters.Add(new OracleParameter
("p_ID",OracleType.Number)).Value = ID;
oraCmd.Parameters.Add(new OracleParameter
("p_Duration",OracleType.Number)).Value = Duration;
oraCmd.Parameters.Add(new OracleParameter
("p_Media",OracleType.Number)).Value = Media;
oraCmd.Parameters.Add(new OracleParameter
("p_Date",OracleType.DateTime)).Value = Date;
oraCmd.Parameters.Add(new OracleParameter
("p_Genre",OracleType.Number)).Value = Genre;
oraCmd.Parameters.Add(new OracleParameter
("p_Rental",OracleType.Number)).Value = Rental;
oraCmd.Parameters.Add(new OracleParameter
("p_Rating",OracleType.VarChar)).Value = Rating;
oraCmd.Parameters.Add(new OracleParameter
("p_Site",OracleType.VarChar)).Value = Site;
oraCmd.Parameters.Add(new OracleParameter
("p_Studio",OracleType.VarChar)).Value = Studio;
try
{
int recordsAffected =
oraCmd.ExecuteNonQuery();
return (recordsAffected);
}
catch (OracleException exep)
{
LogError(exep.Message.ToString
(),EventLogEntryType.Error,1001,10);
throw (exep);
}

}

 

[William Ryan]

Anon:

Where is the connection being opened? It's declared locally and I don't see
an call to Open. This should cause an exception to be raised though.

 

[David Browne]

I am using the following code to insert into oracle9i
database using package/stored procedure. The proc should
insert one record at a time to a table in the Default
scott schema.

When I step through the code, everything seems to work
fine and I get the number of records affectd as 1.
However, no records are inserted and no error occurs.

ExecuteNonQuery does not return the number of records affected for stored
procedure calls, only for directly coded SQL DML. So you have no reliable
indication that any records are being inserted. Start with invoking the
stored procedure from SQL Plus, and verify that it works. Then do the same
thing from your program.

David

 

[anon]

Thanks for your input. The connection is being opened in
the base class