Hi,

I am just trying to learn ASP.NET but have run into a problem when attempting to insert data into my database.

I am using Oracle 10g Express Edition, Visual Studio 2005 and the code is in C#.

Here is the code I am using to call the insert statement;

        string sql = "INSERT INTO offices (office_number, city, address, tel_number) VALUES (45, 'London', 'Tottenham', '2342234234');";
        OracleCommand command = new OracleCommand();

        using (OracleConnection myConnection = new
            OracleConnection(ConfigurationManager.ConnectionStrings[
            "connectionString"].ConnectionString))
        {
            command.CommandText = sql;
            command.Connection = myConnection;

            try
            {
                myConnection.Open();
                command.ExecuteNonQuery();
                myConnection.Close();

            }
            catch (Exception ex)
            {
                 ...
            }
        }

The connection string is;

</connectionStrings>

I have created a function that retrieves data from the database OK which is displayed in a grid view, but when I run the insert nothing is inserted into the database.  I don't get any errors and I'm confident that the connection to DB is OK as I have already been able to retrieve data as mentioned, I just dont know if there is something I am missing such as a commit for example?  Have looked on the web and tried it a couple of different ways but still can't find a solution.

Any help would be gratefully received.

Regards

I see no reason that code shouldnt work, although I cant see your exception handling.  Are you sure you're not eating an exception?

How are you querying the data to confrim/deny the insert occurred?  Directly from sqlplus, for example?

cmd.ExecuteNonQuery will auto commit, no need to commit explicitely so that's not the issue.

 Unrelated to the issue at hand, but you really should get in the habbit of using bind variables rather than hard coding literal sql statements.

Hope it helps,
Greg

 have you tried the same query on oracle interface directally..?

Hi Greg,

Yeah thats whats troubling me, from what I have seen that code should work?  To check the data has not been inserted I have just been doing a select on the SQL command line (sqlplus).

I previously had bind variables but changed in a desperate attempt to get working.  I have no code in the exception block, apologies the ... was misleading. 

Appreciate the help

Regards

 PS I have tried the insert statement on sqlplus and it works ok  

You have NO code in the exception block?  Literally?   I had assumed you'd simply removed the code when you pasted it.  If you have NO code in the exception block, how do you know if an exception is occurring?   I got a dollar says an exception is indeed occurring and you just dont know it. Try removing the try/catch block and let the exception fly all the way out.

Greg

Hi Greg,

Many thanks for all your help, you were spot on about the exception (thats a dollar I owe you!) I had quickly put the code together without adding any exception handling code in the catch section (about which I am extremely embarresed).

The problem was the sql statement included a semi-colon at the end within the double-quotes.

string sql = "INSERT INTO offices (office_number, city, address, tel_number) VALUES (45, 'London', 'Tottenham', '2342234234');";

When I removed this it worked fine.  Thanks again for all your help.

 Regards