Jboss Hosting, Jsp, Tomcat, J2Ee And Hibernate Programming Techniques Blog

CHAPTER 8 WRITING TO THE DATABASE Figure 8-7. The supported Formats are also saved. Figure 8-8. The supported Formats have been saved to the database. How It Works With a bit more work, you ve added the ability to save the supported Formats for a Player to the database. In order to do this, you need to perform two steps: Display the options on the page that may be selected in a suitable Web control. Save the selected options correctly to the database.
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

316 CHAPTER 8 WRITING TO THE DATABASE // loop through each of the formats foreach (ListItem objFormat in FormatList.Items) { // save if selected if (objFormat.Selected == true) { // set the parameter value myCommand.Parameters[”@FormatID”].Value = objFormat.Value; // execute the query myCommand.ExecuteNonQuery(); } } } catch { // indicate that we have an error blnError = true; } finally { // close the connection myConnection.Close(); } // return the error flag return(blnError); } 7. Save the page, and then open the Web site in your browser. In the list of Players, click the Add Player link, and you ll see that the list of Formats is populated. Enter the details for a new Player, and this time, specify the Formats that the Player supports. 8. Click the Insert Player button to save the Player to the database, along with the Formats it supports, as shown in Figure 8-7. 9. To see that the Format details have been saved to the database correctly, you can perform a SELECT query against the WhatPlaysWhatFormat table. The Player added has a PlayerID of 27, so look for this in the WPWFPlayerID column. As you can see in Figure 8-8, the two Formats have been added correctly.
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

CHAPTER 8 WRITING TO THE DATABASE // save the formats for the player bool blnError = SaveFormats(intPlayerID); // did an error occur? if (blnError == true) { QueryResult.Text = “An error has occurred!”; } else { // show the result QueryResult.Text = “Save of player ‘” + intPlayerID.ToString() + “‘ was successful”; // disable the submit button SubmitButton.Enabled = false; } } } 6. Add the new SaveFormats() method as follows: private bool SaveFormats(int intPlayerID) { bool blnError = false; // create the connection string strConnectionString = ConfigurationManager. ConnectionStrings[”SqlConnectionString”].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnectionString); try { // query to execute string strQuery = “INSERT WhatPlaysWhatFormat(WPWFPlayerID, . WPWFFormatID) VALUES (@PlayerID, @FormatID)”; // create the command SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // add the two parameters myCommand.Parameters.AddWithValue(”@PlayerID”, intPlayerID); myCommand.Parameters.Add(”@FormatID”, System.Data.SqlDbType.Int); // open the connection myConnection.Open();
We recommend high quality webhost to host and run your jsp application: christian web host services.

314 CHAPTER 8 WRITING TO THE DATABASE try { // query to execute string strQuery = “SELECT FormatID, FormatName FROM Format . ORDER BY FormatName”; // create the command SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // open the database connection myConnection.Open(); // run query SqlDataReader myReader = myCommand.ExecuteReader(); // set the data source and bind FormatList.DataSource = myReader; FormatList.DataTextField = “FormatName”; FormatList.DataValueField = “FormatID”; FormatList.DataBind(); // close the reader myReader.Close(); } finally { // always close the database connection myConnection.Close(); } } 5. Modify the SubmitButton_Click event handler as follows: protected void SubmitButton_Click(object sender, EventArgs e) { // save the player to the database int intPlayerID = SavePlayer(); // did an error occur? if (intPlayerID == -1) { QueryResult.Text = “An error has occurred!”; } else {
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

CHAPTER 8 WRITING TO THE DATABASE 1. Open Players_Insert.aspx and switch to the Design view. 2. Add a new CheckBoxList before the Insert Player button. Rename it FormatList, set its RepeatColumns property to 4 and its RepeatDirection to Horizontal. The layout should now be as shown in Figure 8-6. Figure 8-6. The new layout showing the Supported Formats CheckBoxList 3. Switch to the Source view and modify the Page_Load event as follows: protected void Page_Load(object sender, EventArgs e) { if (Page.IsPostBack == false) { // populate the list of manufacturers PopulateManufacturers(); // populate the list of formats PopulateFormats(); } } 4. Add the new PopulateFormats() method: private void PopulateFormats() { // create the connection string strConnectionString = ConfigurationManager. ConnectionStrings[”SqlConnectionString”].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnectionString);
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

312 CHAPTER 8 WRITING TO THE DATABASE // create the INSERT query string strQuery1 = “INSERT Player (PlayerName, PlayerManufacturerID, . PlayerCost, PlayerStorage) VALUES (?, ?, ?, ?);”; OdbcCommand myCommand1 = new OdbcCommand(strQuery1, myConnection); // add the parameters myCommand1.Parameters.AddWithValue(”?”, PlayerName.Text); myCommand1.Parameters.AddWithValue(”?”, ManufacturerList.SelectedValue); myCommand1.Parameters.AddWithValue(”?”, PlayerCost.Text); myCommand1.Parameters.AddWithValue(”?”, PlayerStorage.Text); // create the SELECT query string strQuery2 = “SELECT LAST_INSERT_ID();”; OdbcCommand myCommand2 = new OdbcCommand(strQuery2, myConnection); // open the connection myConnection.Open(); // execute the queries we need to execute myCommand1.ExecuteNonQuery(); intPlayerID = Convert.ToInt32(myCommand2.ExecuteScalar()); // close the connection myConnection.Close(); You ll still wrap all of the above code in a try..catch..finally block, so that if there is a problem, you can set the PlayerID value to -1 to indicate that an error occurred. To get the identity value in Microsoft Access, you can use the @@IDENTITY system variable. So, simply execute a different query to return the identity value: // create the SELECT query string strQuery2 = “SELECT @@IDENTITY;”; OleDbCommand myCommand2 = new OleDbCommand(strQuery2, myConnection); // open the connection myConnection.Open(); // execute the queries we need to execute myCommand1.ExecuteNonQuery(); intPlayerID = Convert.ToInt32(myCommand2.ExecuteScalar()); You ll see these versions of getting the value of the PlayerID column in the code download for this book. Try It Out: Setting the Player s Supported Formats Now that we ve looked at how to add the basic details for the Player, let s see how to add the Player s supported Formats.
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.

CHAPTER 8 WRITING TO THE DATABASE 311 Queries in MySQL 5.0 and Microsoft Access Before we move on to the next example and add the Format information, we ll quickly look at two areas where SQL Server 2005 differs from MySQL 5.0 and Microsoft Access: Only when using the SQL Server data provider to connect to SQL Server 2005 can you use named parameters. Neither the ODBC data provider when connecting to MySQL 5.0 nor the OLE DB data provider (which we use to connect to Microsoft Access) support named parameters. With those data providers, you need to add parameters in the order in which they appear in the query. Neither MySQL 5.0 nor Microsoft Access allows multiple queries to be executed as part of the same query batch, and neither supports the SCOPE_IDENTITY() function. Parameters and Queries in MySQL 5.0 and Microsoft Access As you ve learned in earlier chapters, you can t use named parameters with MySQL 5.0 or Microsoft Access using the Odbcor OleDb data providers. You need to change the query that you want to execute and add the parameters to the parameters collection in the correct order. For MySQL 5.0, replace the named parameters with the question mark character: INSERT Player (PlayerName, PlayerManufacturerID, PlayerCost, PlayerStorage) VALUES (?, ?, ?, ?) The query required for Microsoft Access is similar, except you must also specify the INTO keyword: INSERT INTO Player (PlayerName, PlayerManufacturerID, PlayerCost, PlayerStorage) VALUES (?, ?, ?, ?) Once the query is defined correctly, the parameters are added in the order in which they re required: myCommand.Parameters.AddWithValue(”?”, PlayerName.Text); myCommand.Parameters.AddWithValue(”?”, ManufacturerList.SelectedValue); myCommand.Parameters.AddWithValue(”?”, PlayerCost.Text); myCommand.Parameters.AddWithValue(”?”, PlayerStorage.Text); Identity Values and MySQL 5.0 and Microsoft Access Retrieving the identity value for a new row in a table requires two different queries to be executed. SQL Server 2005 allows you to execute these queries as part of the same query batch to the database by separating the queries with semicolons. However, neither MySQL 5.0 nor Microsoft Access supports this functionality. Therefore, you need to make two distinct queries to the database: the INSERT query to add the Player and a SELECT query to return the PlayerID. For MySQL 5.0, this is relatively easy, as there is a corresponding function: LAST_INSERT_ID() returns the value you re after. So, you create two queries and execute these one after the other:
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

310 CHAPTER 8 WRITING TO THE DATABASE INSERT Player (PlayerName, PlayerManufacturerID, PlayerCost, PlayerStorage) VALUES (@Name, @ManufacturerID, @Cost, @Storage); SELECT SCOPE_IDENTITY(); When sending a query to a SQL Server 2005 database, you can actually send multiple queries, separated by semicolons. You want to insert the Player into the database, but you also want to know the PlayerID of the Player that you ve added. In this example, you display the PlayerID as a confirmation that the Player has been added to the database. In the next example, you ll use this PlayerID when adding the details of the Formats that the Player supports. When using a column defined as an identity column, you can use the SCOPE_IDENTITY() function to retrieve the value of that column. In order to return the value from this function, you can use it as a column in a SELECT query. Note The system variable @@IDENTITY returns the value of the identity column last entered. In this instance, both the SCOPE_IDENTITY() function and the @@IDENTITY system variable would return the same value. However, in cases when you re using triggers, the @@IDENTITY system variable may return the wrong value; if the trigger also does an INSERT, it may return the identity value from a different table, whereas the SCOPE_IDENTITY() function returns the identity value from the original table. You should always use the SCOPE_IDENTITY() function to prevent any problems if triggers are added to your tables later. Thus, when you execute this query, you do so by calling ExecuteScalar() rather than ExecuteNonQuery() so you can capture the new PlayerID. ExecuteScalar() returns a generic object, rather than a string or an integer, so you cast it to an integer to make it easier to handle: // execute the query intPlayerID = Convert.ToInt32(myCommand.ExecuteScalar()); If there was an error when inserting the Player, an exception is thrown. You catch this and set the PlayerID to -1 to indicate that the INSERT query failed: catch { // return -1 to indicate error intPlayerID = -1; } Although all you re doing here is setting a flag to indicate that there has been an error, you re free to perform any other actions you want. If you want to send an e-mail message to the Web site administrator informing her that a problem has occurred, you can do so. Just be careful that your error-handling code doesn t throw an exception, as that would cause a runtime error to be displayed to the user! Once you ve executed the query and returned the PlayerID, you exit from the SavePlayer() method and either display an error message or a confirmation to the user. At this point, the user can return to the list of Players to confirm that the new Player has been added and to add another Player if desired.
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.

CHAPTER 8 WRITING TO THE DATABASE // did an error occur? if (intPlayerID == -1) { QueryResult.Text = “An error has occurred!”; } else { // show the result QueryResult.Text = “Save of player ‘” + intPlayerID.ToString() + “‘ was successful”; // disable the submit button SubmitButton.Enabled = false; } } The SubmitButton_Clickevent handler calls the SavePlayer() method to save the Player to the database, and this method returns the PlayerID value for the new Player, or it returns -1 if an error has occurred. If you don t have a valid PlayerID, you know that something has gone wrong, and you display an error message to the user. If the returned PlayerID is valid (not equal to -1), you can assume that the Player has been added to the database, and you display a success message showing the PlayerID of the Player just added to the database. If the Player has been added successfully, you also disable the SubmitButton, so that you can t save the details for the same Player twice by accident. (Of course, there is nothing stopping the user from adding the exact same Player again and again!) The SavePlayer() method is responsible for taking the details entered by the user and saving these details, using an INSERT query to the database: INSERT Player (PlayerName, PlayerManufacturerID, PlayerCost, PlayerStorage) VALUES (@Name, @ManufacturerID, @Cost, @Storage); This should look familiar, as it was the example used in the earlier introduction to the INSERT query, but instead of having actual values in the column value list, you re using parameters that you add using the AddWithValue method: // add the parameters myCommand.Parameters.AddWithValue(”@Name”, PlayerName.Text); myCommand.Parameters.AddWithValue(”@ManufacturerID”, ManufacturerList.SelectedValue); myCommand.Parameters.AddWithValue(”@Cost”, PlayerCost.Text); myCommand.Parameters.AddWithValue(”@Storage”, PlayerStorage.Text); You re using a parameterized INSERT query in the interest of security. You could just build the query with string concatenation, but as you learned in Chapter 4, parameters prevent users from trying to harm your database by sending malevolent SQL instructions through the TextBox. Once the Command object is created and the parameters added correctly, you execute the query against the database. But if you look at the code for the page, you ll see that the query that you re going to execute isn t quite what you just saw. In fact, the query that you send to the database is actually a query batch of two separate SQL queries: an INSERT and a SELECT query:
Check Tomcat Web Hosting services for best quality webspace to host your web application.

308 CHAPTER 8 WRITING TO THE DATABASE Figure 8-4. Invalid data causes exceptions, which thankfully are trapped. Figure 8-5. When debugging, you can see the details of the raised exception. Database Record Insertion The part of the example of particular interest is the SubmitButton_Click event handler: protected void SubmitButton_Click(object sender, EventArgs e) { // save the player to the database int intPlayerID = SavePlayer();
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.