Modifying Data in an Oracle Database Using ASP.NET and ODP.NET

In all my years of web programming, I've never used an Oracle database... until now. I was recently tasked with converting an Access database form into a web application using ASP.NET. The twist is that the data in the Access form is linked to an Oracle database. It would be an understatement to say that Oracle is picky, so I felt the need to document all of the steps I had to take to modify or insert data into the Oracle database. Let me say now that there are multiple ways to connect to databases in ASP.NET (ODBC, OLEDB, Native Clients...) and each method may result in different issues. This article deals specifically with ASP.NET using the Oracle ODP.NET Data Client.

1. You will need to install the Oracle client on both your development machine and the machine that will host the ASP.NET web application.

- When installing the client, choose the Administration option, (which is about 1GB) instead of the Instant Client. The Administration option will give you the ODP.NET functionality that ASP.NET will want to use to connect to the data source. Without it, you will need to use OLEDB or ODBC connections, which is outside the scope of this post.

- I had issues getting the web server to work after installing the Oracle Client. My web page kept telling me that Oracle Client 8.x.x or higher was required. Try rebooting the web server. I restarted IIS multiple times, but it wouldn't work for me. Then the next Monday I came back into work and it was working magically. We had patching over the weekend, so I assume the reboot is what fixed the issue.

2. Once you have the Client installed, you should be able to start building your app. Aside from all of the HTML encoding that happens on the Oracle queries, it should work pretty much the same as a SQL Server database at this point. Add a gridview and tie the datasource to your Oracle database to verify things are working.

3. Now the hard part... Updating or Inserting data into the database.

- More times than not, I find myself having to override the built-in functionality of ASP.NET and do custom scripting to get what I need. Since everyone programs differently, I'm not going to spend a lot of time explaining why or what I was trying to do in my code, but only what steps I had to take to get the data to update or insert.

- If you try to update or insert data to your tables, you are quite likely to see this error: ORA-01036: illegal variable name/number

4. Here are the things I had to change to get rid of this error:

- In your (GridView/DataList/FormView) code, change all Binds to Evals. In my case, I had template fields, so I only needed to change the code within the Edit and Insert templates. The ItemTemplates did not matter.

- In the code for your DataSource, add ConflictDetection="OverwriteChanges"

- I also had to clear all update and insert parameters before the update or insert executed against the database. When Visual Studio generated my code, it supplied Update/Insert/Select Parameters that get passed back to the database on updates and inserts. I wanted to make sure that was all cleared out so I could control what was happening myself.

Private Sub myGridView_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles myGridView.RowUpdating myDataSource.UpdateParameters.Clear() myDataSource.UpdateCommand = "" myDataSource.UpdateCommand = "my custom update command here" End Sub

- Finally, if you have tried all of the above and you are still getting errors on update or insert, try removing the "DataKeyNames" from the GridView/DetailsView/DataView/Whatever, at least for any fields you may be trying to update.

After making all of these changes, I was able to update and insert data into the Oracle database and my ASP.NET web app is coming along quite nicely!