Need help with Oracle insert

  • Thread starter Thread starter anon
  • Start date Start date
A

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);
}

}
 
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.
 
anon said:
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.
Click to expand...

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
 
Back
Top