That being said, let me describe one of the first problems I encountered in my new programming scenario:
I am building a website to allow users to add, edit, and delete items in a list of events. I am using an ASP.NET/Visual Studio MultiView control to manage the process flow for selecting and editing the events. The first view that the user sees is a GridView control displaying the complete list of events in the database.
From this GridView, the user will select an event to view or edit. When they select an event, they are taken to the next View in the MultiView control where they will see a DetailsView control with the specific details for that event.
For my application, I needed to have a third View control with additional details. This was not a problem until I tried to add a new item to my database. As soon as I added the new item, I realized that the GridView control was either reset to select nothing or else it kept its original selection rather than automatically selecting the new item. What’s worse, since my GridView’s data source is sorted, the GridView would often have the same index (relative position) selected, but it could be a completely different item since the actual values at each index had changed!
To avoid this problem, I needed to find some way for the application to insert a new item into my SQL database, return the Identity (ID) value for that new record, refresh the data displayed in my GridView List, and then automatically select the row in my GridView list that contains the new record. Here’s how I did it:
Step 1: Returning the primary key (identity) of the new record
The most important piece of this, it seemed to me, was getting the web page to actually record the value of the new identity column. I am used to programming with PHP and MySQL, and there is a handy built-in function that allows you to do this easily. You simply call a MySQL INSERT operation and then call the function mysql_insert_id(), which returns the Identity (ID) of the record inserted. There is no such built-in function in SQL, ASP.NET, or Visual Studio. So I had to build my own method to do this.
First, I wrote a Stored Procedure in SQL Server Management Studio that would take the values from my website, insert them into a new record in the SQL database, and return the value of the Identity of the newly-inserted record. Here’s the stored procedure I used:
|SQL Code Sample: Stored Procedure that inserts a new record and returns the Identity value|
Once you have created your stored procedure, it is a fairly simple matter to call it. In Visual Studio, select the SQL data source control and change the properties such that the InsertCommandType is “StoredProcedure” and in the InsertQuery Query Builder window (Where you would normally type the SQL INSERT query itself), type in the name of the stored procedure (In this case, spAddEventAndReturnEventIDValue).
Step 2: Finding and selecting the new record in your GridView control
Simply calling a stored procedure that returns a value is not enough to do the job, of course. The next thing you have to do is take that returned value and make it the selected value for the GridView control.
Used to dealing with DropDown lists, I thought I could just assign the GridView’s value property to the returned value using a statement something like this:
GridView1.SelectedValue = command.Parameters[“@NewID”].Value;
Unfortunately, it is not that easy. When I tried to do this, I got the following error message:
Compiler Error Message: CS0200: Property or indexer ‘System.Web.UI.WebControls.GridView.SelectedValue’ cannot be assigned to — it is read only
So much for doing things the easy way. The only thing left to do, as far as I could figure, would be to write a code that cycles through the entire GridView list until it finds a row that matches the value returned by my stored procedure (The Identity value of the newly-inserted record). In my case, the GridView was set to allow paging, so I had to not only identify the correct index of the new record, but also the right page.
I don’t know if this is the best or the only way to do things, but here’s the code that I came up with:
|C# Code Sample: Forcing a GridView to select the ID of a record inserted by a bound DetailsView|
The final piece that is required is to make sure that the function coded above actually runs when a new record is inserted. To do that, you’ll have to add some codes to your ASPX page manually. First, you need to find the SQL data source control that calls your stored procedure. It should look something like this:
<asp:SqlDataSource ID=”SQLDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString1 %>” InsertCommand=”spAddEventAndReturnEventIDValue” InsertCommandType=”StoredProcedure” >
Just add the call to the new On_Inserted method by adding OnInserted=”On_Inserted” somewhere in the data source’s <asp:SqlDataSource> tag, and your application should work. The new code for SqlDataSource1 should look like this:
<asp:SqlDataSource ID=”SQLDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString1 %>” InsertCommand=”spAddEventAndReturnEventIDValue” InsertCommandType=”StoredProcedure” OnInserted=”On_Inserted”>
Now, when you insert a new record via your DetailsView control, the new information will be stored in the SQL Server database, SQL Server will return the value of the Identity column for the new row, and your page will automatically set the GridView control to select the newly-inserted item. This is so much better than having to go back to the beginning, search for and select the record you just inserted, and page through the rest of the MultiView until you get to the section you need to work on!
That’s all for today…now get out there and Write the Web!