Editing Data That Has Foreign Key Constraints
| In relational databases such as Microsoft SQL Server, Microsoft Access, or Oracle, among others it is quite common for tables to have fields that serve as a foreign key to other tables. For example, in the pubs database, the titles database table has a pub_id field, which is a foreign key into the primary key field pub_id of the publishers table. The pub_id in the titles table maps a particular book to a particular publisher. A foreign key constraint requires that the value entered into the foreign key is equal to the value of the primary key field in the table that the foreign key maps to. That is, in the titles table, the pub_id foreign key value must be set to a value that exists in the publishers table's pub_id field. NOTE Foreign keys and foreign key constraints help maintain relational integrity in a relational database system. The topic of the semantics and syntax of foreign keys is well beyond the scope of this book if you're not familiar with foreign keys, consider perusing the references in the "On the Web" section at the end of this chapter. When providing editing support for data that contains foreign keys, it is vital that the user only be allowed to select a valid entry for the foreign key fields. There are a number of foreign key relationships employed by various tables in the pubs database, such as the pub_id field in the titles table, which specifies the publisher for each book. For example, for the book The Busy Executive's Database Guide, the pub_id field has a value of 1389. This maps to a primary key value in the publishers table for the publisher Algodata Infosystems. When editing this book, the user might want to change the publisher to Ramona Publishers, which has a pub_id of 1756. Clearly, you can't expect the user to have these cryptic numbers memorized. NOTE The pub_id field in the publishers and titles tables are char(4) data types. Although foreign keys are most commonly integer data types, they can be of other types, so long as each foreign key value maps to an existing primary key value in the corresponding table. To summarize, when a user is allowed to enter a foreign key field, she must only be allowed to change the foreign key value to a legal value. It is our job to ensure that this is the case. Additionally, we should allow the user to choose from, perhaps, the name of the publisher, as opposed to the actual pub_id. The DropDownList Web control is well suited for the editing constraints imposed by a foreign key. The DropDownList requires that the user select from a predetermined list of items, such as the list of all publishers in the publishers table. Furthermore, the text that appears in the DropDownList can be the value of the publisher table's pub_name field, while the value for each DropDownList item can be the pub_id. Given this, we can allow the editing of data containing a foreign key by implementing a customized editing interface for the foreign key column (or columns) that consists of a data-bound DropDownList. NOTE A DropDownList is a Web control rendered as an HTML list box that can only have one item selected from it at a time. It is equivalent to the ListBox Web control when the ListBox's SelectionMode property is set to Single. Recall the two challenges we are faced with when providing a customized editing interface: setting the editing interface's initial value to the value of the DataSource field that the column being edited is representing, and obtaining the value the user entered in the event handler that fires when the Update button is clicked. With the customized editing interface for our foreign key DataGrid column, the second task is fairly trivial the real challenge involved is in assigning the value of the edited row's pub_id field to the default value to the DropDownList. Listing 9.7 contains the server-side code and HTML content needed for an ASP.NET Web page that allows the editing of the pubs database's titles table. A data-bound DropDownList is used for the DataGrid's pub_id customized editing interface. Listing 9.7 A Data-Bound DropDownList Is Used to Edit a Foreign Key Column1: <%@ import Namespace="System.Data" %> 2: <%@ import Namespace="System.Data.SqlClient" %> 3: <script runat="server" language="VB"> 4: Dim ddlDataSet as DataSet = New DataSet() 5: 6: Sub Page_Load(sender as Object, e as EventArgs) 7: If Not Page.IsPostBack then 8: BindData() 9: End If 10: End Sub 11: 12: 13: Sub BindData() 14: '1. Create a connection 15: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 16: Dim objConn as New SqlConnection(strConnString) 17: 18: '2. Create a command object for the query 19: Dim strSQL as String = _ 20: "SELECT title_id, title, t.pub_id, p.pub_name, price FROM titles t " & _ 21: "INNER JOIN publishers p ON t.pub_id = p.pub_id ORDER BY title" 22: Dim objCmd as New SqlCommand(strSQL, objConn) 23: 24: objConn.Open() 'Open the connection 25: 26: 'Finally, specify the DataSource and call DataBind() 27: dgTitle.DataSource = objCmd.ExecuteReader(CommandBehavior. CloseConnection) 28: dgTitle.DataBind() 29: 30: objConn.Close() 'Close the connection 31: End Sub 32: 33: 34: Function GetPublishers() as DataSet 35: '1. Create a connection 36: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 37: Dim objConn as New SqlConnection(strConnString) 38: 39: '2. Create a command object for the query 40: Const strSQL as String = _ 41: "SELECT pub_id, pub_name FROM publishers ORDER BY pub_name" 42: 43: Dim myDataAdapter as SqlDataAdapter 44: myDataAdapter = New SqlDataAdapter(strSQL, objConn) 45: 46: 'Fill the DataSet 47: objConn.Open() 'Open the connection 48: myDataAdapter.Fill(ddlDataSet, "Publishers") 49: objConn.Close() 'Close the connection 50: 51: Return ddlDataSet 'Return the DataSet 52: End Function 53: 54: 55: Function GetSelectedIndex(pub_id as String) as Integer 56: 'Loop through the DataSet ddlDataSet 57: Dim iLoop as Integer 58: Dim dt as DataTable = ddlDataSet.Tables("Publishers") 59: For iLoop = 0 to dt.Rows.Count - 1 60: If pub_id = dt.Rows(iLoop)("pub_id").ToString() then 61: Return iLoop 62: End If 63: Next iLoop 64: End Function 65: 66: 67: Sub dgTitle_EditRow(sender As Object, e As DataGridCommandEventArgs) 68: dgTitle.EditItemIndex = e.Item.ItemIndex 69: BindData() 70: End Sub 71: 72: Sub dgTitle_UpdateRow(sender As Object, e As DataGridCommandEventArgs) 73: 'Get information from columns... 74: Dim ddlPublishers as DropDownList = e.Item.Cells(2). FindControl("ddlPublisher") 75: 76: Dim titleID as String = dgTitle.DataKeys(e.Item.ItemIndex) 77: 78: 79: 'Update the database... 80: Dim strSQL as String 81: strSQL = "UPDATE titles SET pub_id = @pubIDParam " & _ 82: "WHERE title_id = @titleIDParam" 83: 84: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 85: Dim objConn as New SqlConnection(strConnString) 86: 87: Dim objCmd as New SqlCommand(strSQL, objConn) 88: 89: Dim pubIDParam as New SqlParameter("@pubIDParam", SqlDbType.Char, 4) 90: Dim titleIDParam as New SqlParameter("@titleIDParam", SqlDbType. VarChar, 6) 91: 92: pubIDParam.Value = ddlPublishers.SelectedItem.Value 93: objCmd.Parameters.Add(pubIDParam) 94: 95: titleIDParam.Value = titleID 96: objCmd.Parameters.Add(titleIDParam) 97: 98: 'Issue the SQL command 99: objConn.Open() 100: objCmd.ExecuteNonQuery() 101: objConn.Close() 102: 103: dgTitle.EditItemIndex = -1 104: BindData() 105: End Sub 106: 107: Sub dgTitle_CancelRow(sender As Object, e As DataGridCommandEventArgs) 108: dgTitle.EditItemIndex = -1 109: BindData() 110: End Sub 111: 112: </script> 113: <form runat="server"> 114: <asp:DataGrid runat="server" 115: Font-Name="Verdana" Font-Size="9pt" CellPadding="5" 116: AlternatingItemStyle-BackColor="#dddddd" 117: AutoGenerateColumns="False" DataKeyField="title_id" 118: OnEditCommand="dgTitle_EditRow" 119: OnUpdateCommand="dgTitle_UpdateRow" 120: OnCancelCommand="dgTitle_CancelRow"> 121: 122: <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt" 123: Font-Bold="True" HorizontalAlign="Center" /> 124: 125: <Columns> 126: <asp:EditCommandColumn ButtonType="LinkButton" HeaderText="Edit" 127: EditText="Edit" UpdateText="Update" CancelText="Cancel" /> 128: <asp:BoundColumn DataField="title" 129: HeaderText="Title" ReadOnly="True" /> 130: <asp:TemplateColumn HeaderText="Publisher"> 131: <ItemTemplate> 132: <%# DataBinder.Eval(Container.DataItem, "pub_name") %> 133: </ItemTemplate> 134: <EditItemTemplate> 135: <asp:DropDownList runat="server" 136: DataTextField="pub_name" 137: DataValueField="pub_id" 138: DataSource="<%# GetPublishers() %>" 139: SelectedIndex='<%# GetSelectedIndex(Container. DataItem("pub_id")) %>' 140: /> 141: </EditItemTemplate> 142: </asp:TemplateColumn> 143: <asp:BoundColumn DataField="price" DataFormatString="{0:c}" 144: HeaderText="Price" ReadOnly="True" /> 145: </Columns> 146: </asp:DataGrid> 147: </form> The first thing you'll probably notice about Listing 9.7 is its length. Although it might be a bit daunting at first, realize that the majority of the code in the listing is code we've examined in previous code listings. The DataGrid rendered by Listing 9.7 contains four columns: an EditCommandColumn (lines 126 127), a Title column (lines 128 129), a Publisher column (lines 130 142), and a Price column (lines 143 144). To simplify the exercise, and because we're only concerned with demonstrating how to provide editing for foreign key columns, only the Publisher column is made editable. The other columns in the DataGrid could easily be made editable through techniques we learned earlier in this chapter. Note that the Publisher column displays the name of the book's publisher, not the actual foreign key value. This is done by using an INNER JOIN in the SQL statement to obtain the fields form the publishers table that correspond to pub_id field for each row in the titles table. This SQL statement can be found on lines 19 through 21 in the BindData() subroutine. Specifically, the pub_name field from the publishers table is included in each record of our query's resultset, which corresponds to the name of the publisher for the particular book. The customized editing interface for the DataGrid's Publisher column is defined in the EditItemTemplate (lines 134 141). The customized editing interface is simply a DropDownList Web control. The DropDownList Web control can be data-bound; that is, we can supply DataTextField, DataValueField, and DataSource properties for the DropDownList. When the DropDownList is rendered, the items in the DropDownList are made up from the records in the specified DataSource. The DataTextField property specifies the DataSource field whose value should be displayed as the text for each DropDownList item, while the DataValueField property specifies the DataSource field whose value should be the value of the DropDownList item. In Listing 9.7, we set the DataTextField to pub_name because we want to display the publisher's name as the text of each item of the DropDownList (line 136). The DataValueField property is set to pub_id (line 137), because this is the information we need to update the pub_id field of the titles table when the user clicks the Update button. The DataSource is specified via data-binding syntax, because we have to provide code to construct an appropriate DataSource. Specifically, we want to create an object that can be used as a DataSource containing the pub_id and pub_name fields for each record from the publishers table. The DataSource for the DropDownList is returned by the function GetPublishers(), which we need to write and include in our server-side code. The GetPublishers() function (lines 34 52) accepts no parameters and returns a DataSet. The code for this function is fairly straightforward a connection to the database is opened, and a DataSet is filled with the results of a SQL query. Specifically, the SQL query retrieves the rows of the publishers table (lines 40 and 41). After we have filled the DataSet (line 48), the connection is closed and the DataSet is returned (line 51) . Note that the DataSet that is filled in the GetPublishers() function is declared outside the GetPublishers() function on line 4. You might be wondering why we would want to declare the DataSet outside the of the GetPublishers() function. We will examine the rationale behind this in a bit. For now, just be cognizant of the fact that the DataSet populated in the GetPublishers() function has been declared external to the function. By setting the DataTextField, DataValueField, and DataSource properties in the DropDownList, we have managed to add a data-bound DropDownList to the Publisher column's editing interface. When the user clicks the Edit button, he will see a DropDownList with the list of available publishers. However, recall the first task involved in creating a customized editing interface: The editing interface must default to the value of the DataSource field it is representing. In just providing the DataTextField, DataValueField, and DataSource properties, our DropDownList doesn't do this. That is, regardless of what publisher the book Emotional Security: A New Algorithm has, when its Edit button is clicked, the DropDownList of publishers would have the first publisher selected by default (Algodata Infosystems in our example, because the SQL query in the GetPublishers() function orders the results alphabetically by the publisher's name). To have the DropDownList automatically select the row's publisher, we must correctly set the DropDownList's SelectedIndex property. We do this on line 139 by using data-binding syntax to call a function GetSelectedIndex(publisherID ). Like the GetPublishers() function, we must create the GetSelectedIndex(publisherID ) function and include it in our ASP.NET code section. The GetSelectedIndex(publisherID ) function accepts as input the pub_id value of the book that's being edited. Its task is to determine what index in the DropDownList the specified pub_id resides in. That is, if the book that's being edited has a pub_id field value of 0736 for New Moon Books, the GetSelectedIndex(publisherID ) function must determine the index of the item in the DropDownList that has the value 0736 (or whose text is New Moon Books). The GetSelectedIndex(publisherID ) function can be found spanning line 55 to line 64. Because the DropDownList's SelectedIndex property is an integer property, the GetSelectedIndex(publisherID ) function returns an integer. To determine the index of the DropDownList item that corresponds to the passed-in pub_id, the GetSelectedIndex(publisherID ) function iterates through the ddlDataSet that was populated in the GetPublishers() function. Specifically, the ddlDataSet's Publishers DataTable is referenced as a local variable dt (line 58), which is then enumerated over. At each iteration, the passed-in pub_id value is compared to the value of the DataRow's pub_id field. If they match up, the current value of iLoop is immediately returned (line 61). We can iterate through the DataSet of publishers to determine the index of the DropDownList item that has the corresponding pub_id value because the DropDownList was constructed with the same DataSet from the GetPublishers() function. Hence, the ordering of the rows in ddlDataSet is identical to the ordering of the items in the DropDownList control. The reason the GetPublishers() function used a globally scoped DataSet instead of creating a local DataSet was so that the GetSelectedIndex(publisherID ) function could access the same DataSet without having to query the database again. NOTE Intuitively, it might seem that the GetSelectedIndex(publisherID ) function should be able to reference the DropDownList in the EditItemTemplate and iterate through its Items collection, rather than using the abstruse DataSet method. However, we cannot use this approach, because when the GetSelectedIndex(publisherID ) function executes, the actual DropDownList has yet to be added to the DataGrid's row's TableCell. Because it has not been added yet, it cannot yet be referenced. With the DropDownList's SelectedIndex property properly set, we've completed the first task of providing a customized editing interface. All we have left to do is provide the code to retrieve the value chosen by the user from the editing interface, and update the database with this information. This occurs in the dgTitle_UpdateRow event handler (lines 72 105). On line 74, the local variable ddlPublishers is used to reference the DropDownList in the editing interface. The FindControl(controlID ) method we examined earlier is employed here to retrieve the DropDownList. On lines 81 and 82, a parameterized UPDATE statement is provided that updates the particular book's pub_id field. Two SqlParameter objects, pubIDParam and titleIDParam, are created on lines 89 and 90, and their Value properties are set on lines 92 and 95. The pubIDParam's Value is set to the Value of the SelectedItem from the DropDownList, while the Value of the titleIDParam is set to the correct title_id field using the DataKeys collection. On line 100, the actual SQL UPDATE statement is executed, thereby updating the database with the user's edits. NOTE Note that the data type of the pubIDParam is declared to be of type char(4) (line 89), because the foreign key data type is a char(4), not an int. Figure 9.7 contains a screenshot of Listing 9.7 when the Edit button for a particular row is clicked. Note that a DropDownList Web control is provided in the Publisher column's editing interface, and that it defaults to the current publisher of the book whose Edit button was clicked. Figure 9.7. When edited, the Publisher column is displayed as a data-bound DropDownList of all existing publishers. Extending the Editing Interface to Allow for NULL ValuesWhen using foreign keys in a database model, sometimes you might want to allow a foreign key to contain NULL values. For example, the titles table allows for the pub_id column to contain NULLs; books whose pub_id is NULL might be books that are still in the conceptual stages and have yet to garnish interest from any particular publisher. We'd like to be able to extend the code from Listing 9.7 to allow the user to select an option from the DropDownList that corresponds to a NULL value. Perhaps a DropDownList item might read "No Publisher Yet," or something similar. Similarly, we'd like to be able to display a DropDownList item for those titles whose pub_id field is NULL. With our code in Listing 9.7, there is no way to give a title a NULL-value publisher. Furthermore, there is no way to edit a title that has a NULL-value publisher, because the SQL statement on lines 19 21 in Listing 9.7 uses an INNER JOIN to merge the publisher table with the title table. Because there cannot be a publisher with a NULL pub_id, the INNER JOIN precludes any titles with a NULL-value publisher from ending up in the final resultset. To enable the user to edit books with NULL publisher values, and to allow a book to be altered to contain a NULL publisher value, we'll have to make the following high-level changes to the code in Listing 9.7:
As you can see, there are quite a number of changes needed to the code in Listing 9.7. Listing 9.8 contains the enhanced code to enable the Publisher column editing interface to support NULL-valued publishers. Listing 9.8 The User Can Edit Titles That Have a NULL-Valued Publisher[View full width] 1: <%@ import Namespace="System.Data" %> 2: <%@ import Namespace="System.Data.SqlClient" %> 3: <script runat="server" language="VB"> 4: ' ... The Page_Load, dgTitle_EditRow, and dgTitle_CancelRow 5: ' ... event handlers have been omitted for brevity. 6: ' ... Refer back to Listing 9.7 for details ... 7: 8: Dim ddlDataSet as DataSet = New DataSet() 9: 10: Sub BindData() 11: '1. Create a connection 12: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 13: Dim objConn as New SqlConnection(strConnString) 14: 15: '2. Create a command object for the query 16: Dim strSQL as String = _ 17: "SELECT title_id, title, t.pub_id, p.pub_name, price FROM titles t " & _ 18: "LEFT JOIN publishers p ON t.pub_id = p.pub_id ORDER BY title" 19: Dim objCmd as New SqlCommand(strSQL, objConn) 20: 21: objConn.Open() 'Open the connection 22: 23: 'Finally, specify the DataSource and call DataBind() 24: dgTitle.DataSource = objCmd.ExecuteReader(CommandBehavior. CloseConnection) 25: dgTitle.DataBind() 26: 27: objConn.Close() 'Close the connection 28: End Sub 29: 30: 31: Function GetPublishers() as DataSet 32: '1. Create a connection 33: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 34: Dim objConn as New SqlConnection(strConnString) 35: 36: '2. Create a command object for the query 37: Const strSQL as String = _ 38: "SELECT pub_id, pub_name FROM publishers ORDER BY pub_name" 39: 40: Dim myDataAdapter as SqlDataAdapter 41: myDataAdapter = New SqlDataAdapter(strSQL, objConn) 42: 43: 'Fill the DataSet 44: objConn.Open() 'Open the connection 45: myDataAdapter.Fill(ddlDataSet, "Publishers") 46: objConn.Close() 'Close the connection 47: 48: 'Add the NULL option to the DataSet 49: Dim dt as DataTable = ddlDataSet.Tables("Publishers") 50: Dim NULLRow as DataRow = dt.NewRow() 51: NULLRow("pub_id") = -1 52: NULLRow("pub_name") = "No Publisher Yet" 53: 54: dt.Rows.InsertAt(NULLRow, 0) 'Insert the row at the front 55: 56: Return ddlDataSet 'Return the DataSet 57: End Function 58: 59: 60: Function GetSelectedIndex(pub_id as Object) as Integer 61: If pub_id.Equals(DBNull.Value) then 62: Return 0 63: Else 64: Dim strPubID as String = pub_id.ToString() 65: 66: 'Loop through the DataSet ddlDataSet 67: Dim iLoop as Integer 68: Dim dt as DataTable = ddlDataSet.Tables("Publishers") 69: For iLoop = 0 to dt.Rows.Count - 1 70: If strPubID = dt.Rows(iLoop)("pub_id").ToString() then 71: Return iLoop 72: End If 73: Next iLoop 74: End If 75: End Function 76: 77: Function EmitPublisherName(pub_name as Object) as String 78: If pub_name.Equals(DBNull.Value) then 79: Return "No Publisher Yet" 80: Else 81: Return pub_name.ToString() 82: End If 83: End Function 84: 85: 86: Sub dgTitle_UpdateRow(sender As Object, e As DataGridCommandEventArgs) 87: 'Get information from columns... 88: Dim ddlPublishers as DropDownList = e.Item.Cells(2).FindControl ( The SQL query that populated our DataGrid in Listing 9.7 did not include those titles whose pub_id was NULL because an INNER JOIN was used, and there are no publishers with a NULL pub_id value. To be able to retrieve those titles that have a pub_id field with the value NULL, we need to use a LEFT JOIN, as opposed to an INNER JOIN, for the SQL query that populates the DataGrid. The BindData() subroutine in Listing 9.8 has been altered to use this new SQL statement (lines 16 18). NOTE A LEFT JOIN returns all rows from the left table in the join (titles), regardless of whether there's a matching publishers.pub_id for the titles.pub_id. For more information on LEFT JOINs and RIGHT JOINs, see the links in the "On the Web" section at the end of this chapter. Although this SQL query will retrieve all the titles, the value of the returned pub_name field will be NULL for those titles that have a NULL value for pub_id. Because this is the field we are displaying in the DataGrid's Publisher column, we need to update the Publisher column's ItemTemplate. If we fail to update the ItemTemplate, those titles that have the value NULL for pub_id will display a blank string for the Publisher name. Rather than having a blank string displayed, we'd like to have a message No Publisher Yet displayed. To accomplish this, let's update the Publisher column's ItemTemplate to call a custom function. On line 146, the data-binding syntax has been changed from Listing 9.7 from <%# DataBinder.Eval(Container.DataItem, pub_name) %> to <%# EmitPublisherName(DataBinder.Eval(Container.DataItem, "pub_name")) %> The EmitPublisherName(pubName ) is a function we must write and include in the code section for our ASP.NET page. This function needs to accept an input parameter of type Object, because we don't know if the value returned by DataBinder.Eval(Container.DataItem, "pub_name") will be a string value or a DbNull value. The EmitPublisherName(pubName ) function (lines 77 83) is fairly simple. It checks to see whether the pass-in Object parameter is equal to the type DBNull (line 78). If it is, it returns the string No Publisher Yet. If the Object parameter is not NULL, we know it contains the name of the book's publisher and can simply return this string (line 81). The next task we are faced with involves updating the GetPublishers() function so that the ddlDataSet DataSet has a new row added to it that represents the NULL option. Recall that the ddlDataSet DataSet is the specified DataSource for the data-bound DropDownList in the Publisher column's editing interface. Because we want this DropDownList to include an item for the NULL option, we must add an appropriate record to the ddlDataSet DataSet prior to returning the DataSet. This is accomplished on lines 49 through 54. The DataTable of the DataSet is referenced on line 49, and a new DataRow is created on line 50. The pub_id and pub_name fields are then set in line 51 and 52, respectively. Finally, the row is added to the front of the DataTable's Rows collection on line 54. At this point, we have altered the DropDownList in the Publisher column's editing interface to include an option for the user to specify that a book should have a NULL value for the pub_id field. However, we also need to update the GetSelectedIndex(publisherID ) function. In Listing 9.7, this function accepted a single input parameter of type String because it expected a valid pub_id of type char(4). However, we need to accept an input parameter of type Object in case the pub_id value is NULL. The updated GetSelectedIndex(publisherID ) function can be found starting a line 60. Note that its input parameter, pub_id, is of type Object. On line 61, we check to see whether this input parameter is of type DBNull. If it is, we return 0, because the No Publisher Yet option is the first option in the DropDownList. (The reason the No Publisher Yet option is the first option in the DropDownList is because in the GetPublishers() function, we added this option as the first row in the DataSet's Publishers DataTable.) If, however, the pub_id input parameter is not NULL, we convert it to an Int32 (line 64) and then use the code we used earlier to iterate through the ddlDataSet DataSet. The final change we need to implement to support NULL-value pub_id fields is updating the dgTitle_UpdateRow event handler so that it is able to update the database with a NULL value for pub_id. We need to issue one of two SQL UPDATE statements, depending on whether the user has set the Publisher column's DropDownList to the NULL option. If the Publisher column's DropDownList has been set to the NULL option, the Value of the DropDownList's SelectedItem will be 1. On line 100 we check to see whether this is the case. If the Value is 1, the user wants to set the title's pub_id to NULL. That is, the "No Publisher Yet" item from the DropDownList has been selected. In this event, we use the SQL UPDATE statement defined on lines 102 and 103. If, on the other hand, the user has set the book's pub_id field to a specific publisher, we need to use a SQL UPDATE statement that has a parameter for the pub_id. The SQL UPDATE statement on lines 105 and 106 is identical to the SQL UPDATE statement in the dgTitle_UpdateRow event handler of Listing 9.7. On line 108, the SqlParameter pubIDParam has its Value set to the Value of the DropDownList's SelectedItem; on line 109, the pubIDParam is added to the Parameters collection of the objCmd object. The remainder of the dgTitle_UpdateRow event handler simply issues the SQL UPDATE statement (lines 112 121) and returns the DataGrid to its pre-edited state (lines 123 and 124). Figure 9.8 contains a screenshot of Listing 9.8 when viewed through a browser. Specifically, Figure 9.8 captures the screen after the user has clicked Edit for a particular row. Note that the DropDownList in the Publisher column for the row being edited contains the "No Publisher Yet" option. Furthermore, books that are not being edited and have a NULL-value publisher show the text No Publisher Yet in their Publisher column. Figure 9.8. The DropDownList includes a "No Publisher Yet" option. |
"ddlPublisher") 89: Dim titleID as String = dgTitle.DataKeys(e.Item.ItemIndex) 90: 91: 'Update the database... 92: Dim strSQL as String 93: Dim pubIDParam as New SqlParameter("@pubIDParam", SqlDbType.Char, 4) 94: Dim titleIDParam as New SqlParameter("@titleIDParam", SqlDbType. VarChar, 6) 95: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 96: Dim objConn as New SqlConnection(strConnString) 97: Dim objCmd as New SqlCommand() 98: 99: 'Determine if we are dealing with a NULL or not... 100: 
EAN: 2147483647
Pages: 111