"Ah, but I was so much older then, I'm younger than that now."
My Boog Pages
Tuesday, June 20
INSERTing records into Access using ASP.NET's FormView control
Off and on for the past couple of weeks I've been using Microsoft's free Visual Web Developer 2005 Express to redesign a little Access database-driven website at work. I had hoped to make it simpler and easier to maintain, by using standard components instead of the custom code I had been using.
It turned out to be a little more complicated than I had thought. In particular I discovered a big problem with Access autoincrement fields. An autoincrement field is a long (32-bit) integer that is automatically assigned a unique ID when a new record is inserted. But when you are inserting records with ASP.NET, there's no good way to retrieve this value.
This is not as much of a problem with SQL Server, because you can create stored procedures, or even simple queries, that carry out both INSERT and SELECT statements - that is, they send and receive data in one operation. Access doesn't do this.
Background. In ASP.NET 2.0, there's a free-form data bound container called a FormView. This is similar to the GridView and DetailsView objects, but it is more flexible, as you are not limited in how you can lay out the controls within it (text boxes, drop-down boxes, etc.).
Each FormView contains templates for different views, such as an Item view (read-only), and ItemEdit view (updates existing records), and an ItemInsert view (just what it says). Each template contains markup defining the data fields and their layouts.
In my case, this FormView is connected to a control called an AccessDatasource control. This datasource has the location of the data file and SQL statements for the SELECT, UPDATE, INSERT, and DELETE procedures. This control also has events that are triggered during these procedures, which will become important when we get to the code.
Now, most ASP.NET pages only post data back to themselves, but I am using separate pages for viewing data and for editing it, and I'm using a standard HTML input field to pass the ID between them. This is called "cross-posting" in ASP.NET, and I'm sure it's a terrible idea and an example of my inexperience and general cluelessness, but I have good reasons for doing it that way.
To handle this, my Page_Load event handler contains the following code (all examples in VB):
Dim IDParam As New Parameter
If Not IsPostBack Then If String.IsNullOrEmpty(Request.Form("ID")) Then
IDParam = ServerDatasource.SelectParameters.Item("ID") IDParam.DefaultValue = Request.Form("ID") End If End If
This FormView control is set to default to Edit mode. The first line basically says that if no ID form field is suppled, put the FormView control in Insert mode. If a parameter is supplied, it's added to the data source. I could have used a FormParameter in the data source definition, but I want to be able to filter in other situations, as we'll see in a minute.
INSERTing records. So let's assume we are now in Insert mode. We fill in all the fields and click an Insert button to add the new record to the database. What happens next?
Among other things, the AccessDatasource_Inserting event fires. This event is raised once an INSERT procedure has started, but before the record is actually inserted. Here's the code to handle this event - note that "e" is the event arguments passed to this procedure automatically:
Dim InsertCommand As DbCommand Dim InsertConnection As DbConnection Dim InsertTransaction As DbTransaction
All we're really doing here is starting a transaction. Although my testing indicates that this may not always be required, using transactions guarantees that the data source won't be polluted by other database operations during this insert.
After this event completes, the record is inserted and the AccessDatasource_Inserted event fires. This is where the work gets done:
Dim InsertCommand As DbCommand Dim InsertTransaction As DbTransaction Dim ID As Int32 Dim IDParam As New Parameter
So, what are we doing here? First, we're grabbing the INSERT command and the transaction we set in the Inserting event handler. Then we're reusing the command to run a SELECT statement that retrieves the @@IDENTITY (autoincrement) value for the last record inserted. Once that is complete, we commit the transaction.
Next we set the same parameter that we used in the Page_Load procedure to the value of the new row. We no longer want to be in Insert mode, so we change the FormView's mode to Edit. Lastly, we refresh the data using the DataBind command.
And that's all there is to it. Just make sure that the FormView's ItemEdit template has a form field named "ID" that is bound to the data source's "ID" field, and you are good to go.
If you're code objects to manipulate Access data, you will run into a similar problem. You can handle it by handling the DataAdapter's RowUpdated event. It's similar to the procedure above.
If you get this to work for you, please leave a note in the comments so I can see if anyone's actually using this code.
