Need help with Oracle insert


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

}
 
Ad

Advertisements

W

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.
 
D

David Browne

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.
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
 
Ad

Advertisements

A

anon

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top