The BannerBuddy Application

The BannerBuddy application has several distinct parts: a SQL Server 2000 database that maintains all information, a Web Service that creates the HTML for the ad banner, an administrative application, and an NT Service that e-mails statistics to advertisers. This section describes the entire application except for the NT Service. The section entitled "Writing and Deploying NET Services in .NET" describes how to create and use NT Services. The section following entitled "The BannerBuddy Service" then discusses the NT Service that e-mails statistics to advertisers.

The Database

The database used is SQL Server 2000. You can download a script from http://www.ASPNET-Solutions.com/Chapter_12.htm that will create the database, or you can download the backed-up database. The database contains three tables: Advertiser, Advertisement, and History. The Advertiser table keeps all of the information related to an advertiser, including contact information. The Advertisement table keeps all information about an advertisement, including the banner image URL and statistics. The History table keeps a record of the ad impressions and clicks. Figure 12.1 shows the BannerBuddy database schema.

Figure 12.1. The BannerBuddy Database Schema

Displaying a Banner

The advertisements all have a weight value. The greater the weight, the more likely it is that an advertisement will be shown. The weight of each individual advertisement is relative to the total of all other advertisement weights. Although there are no hard, fast ranges, I suggest values from 1 to 100. The higher the value, the more often an ad appears. In this way, you can cause more important ad banners to appear more often. You can also charge more for customers with greater weights.

Figuring out how to use the weight values to select which ad banner appears next took some thought. The Views field keeps track of how many views an advertisement has had. The ViewLimit field keeps track of how many views an advertisement will have when it expires. These two fields can be used to gauge the previous display preponderance of each advertisement.

The stored procedure that calculates and returns the next ad can be seen in Listing 12.1. It starts by getting a total of the ViewLimit field for all active (or unexpired) advertisements. The following line from the stored procedure shows this:

 select @all=sum(ViewLimit) from Advertisement where Status=0 

A calculated field named Parts is then created by taking the difference between the Weight field of the ads divided by the total of the ViewLimit fields, and the Views field of the ads divided by the total of the ViewLimits field. The following shows the creation of this value:

 Parts=((Weight/@all)-(Views/@all)) 

Listing 12.1 The Stored Procedure Selects the Next Advertisement Based on the Weight of All Advertisements and Their History.

 CREATE PROCEDURE sp_GetNextAd AS   declare @all float   select @all=sum(ViewLimit) from Advertisement where Status=0   select ID,ImageURL,ImageWidth,ImageHeight,     Link,AltText,Parts=((Weight/@all)-(Views/@all))      from Advertisement where Status=0 order by Parts desc GO 

Another important part of the advertisement logic is expiring the advertisements when they've exceeded values in the ViewLimit, Clicks Limit, or EndDate fields. A trigger performs this housekeeping chore, and it can be seen in Listing 12.2. The trigger sets the Status field to 1 if the Views field catches up with the ViewLimit field, the Clicks field catches up with the ClicksLimit field, or the current date has exceeded the EndDate field. There are five fields that determine when an advertisement expires: EndDate, Clicks, ClicksLimit, Views, and ViewLimit. Advertisements need a way to expire based on the arrangements that have been made with advertisers. For instance, if the advertisement should expire at a certain date, then the EndDate field must be checked to ensure that the advertisement expires when appropriate. And the combinations of the Views/ViewLimit and Clicks/ClicksLimit fields give the software the opportunity to place a cap on the number of views and the number of clicks.

RECOMMENDED PRACTICE: The status field contains an integer value rather than a string containing values such as done and open. Queries on integer values are faster than queries on strings, so the performance of the queries is increased.

Listing 12.2 The Trigger That Expires Advertisements and Resets the Weight Accumulation

 CREATE TRIGGER tr_ExpireAd ON Advertisement   AFTER UPDATE AS   If Update(Views) or Update(Clicks)   Begin     Update Advertisement set Status=1     where Views>=ViewLimit or Clicks>=ClicksLimit or EndDate<getdate()   declare @ID int   select @ID = -1   select @ID=ID from Advertisement     where WeightAccumulation<Weight and Status=0   if @ID=-1     Update Advertisement set WeightAccumulation=0 and Status=0 End 

The code that creates the HTML for the ad banner can be found in a Web Service named TheAdBannerCS (or TheAdBannerVB for the VB version). The code makes a single method named NextAd() available for consumption. The NextAd() method, shown in Listing 12.3, calls the sp_GetNextAd stored procedure, and then creates the HTML for the ad banner.

The NextAd() method starts off by creating a connection to the database, and the connection is then opened. A SqlCommand object is created and set so that the sp_GetNextAd stored procedure will be called. A SqlDataReader object is returned from the SqlCommand.Execute Reader() method. Some default values are stored in local string variables for items such as the image URL, image width, and image height.

If a record is returned from the stored procedure, then the default values are replaced with the values from the first record returned, as follows:

 // Get the information from the recordset that we'll need. nAdID = Convert.ToInt32( objReader["ID"] ); strImageURL = Convert.ToString( objReader["ImageURL"] ); strImageWidth = Convert.ToString( objReader["ImageWidth"] ); strImageHeight = Convert.ToString( objReader["ImageHeight"] ); strLink = Convert.ToString( objReader["Link"] ); strAltText = Convert.ToString( objReader["AltText"] ); 

Another stored procedure is called to update the Views field in the database. To do this, another SqlCommand object is created, and its ExecuteNonQuery() method is called. The AdID is required for the stored procedure to know which advertisement to update, so the stored procedure takes a single integer parameter with this value.

The HTML is created by concatenating the values into a single string, as follows:

 strImageInfo = "<a href='" +   ConfigurationSettings.AppSettings["ClickAccepter"] +   "?Ad&Link=" + strLink +   "'><IMG src='/books/2/627/1/html/2/" + strImageURL +   "' AltText='" + strAltText +   "' Width='" + strImageWidth +   "' Height='" + strImageHeight +   "'></a>"; 

Finally, cleanup is performed. The data reader is closed, and the connection is closed.

Listing 12.3 The Web Service That Creates the Ad Banner HTML Code

 [WebMethod] public string NextAd() {   string strImageInfo = "";   // Create the database connection object.   SqlConnection objConnection =      new SqlConnection(        ConfigurationSettings.AppSettings["ConnectString"] );   try   {     // Open the database connection.     objConnection.Open();     // Create the command object. Set it for the     //   sp_GetNextAd stored procedure.     SqlCommand objCommand = new SqlCommand( "sp_GetNextAd",       objConnection );     objCommand.CommandType = CommandType.StoredProcedure;     // Execute the reader and get the recordset.     SqlDataReader objReader = objCommand.ExecuteReader();     // Set some default values in case we don't get a recordset.     int nAdID = 0;     string strImageURL = "http://www.nowhere.com/image.jpg";     string strImageWidth = "600";     string strImageHeight = "60";     string strLink = "http://www.ASPNET-Solutions.com";     string strAltText = "No banner record was found";     // Read a record.     if( objReader.Read() )     {       // Get the information from the recordset that we'll need.       nAdID = Convert.ToInt32( objReader["ID"] );       strImageURL = Convert.ToString( objReader["ImageURL"] );       strImageWidth = Convert.ToString( objReader["ImageWidth"] );       strImageHeight = Convert.ToString( objReader["ImageHeight"] );       strLink = Convert.ToString( objReader["Link"] );       strAltText = Convert.ToString( objReader["AltText"] );       // Close the reader.       objReader.Close();       // Create the command object. Set it for the       //   sp_UpdateAdViews stored procedure.       objCommand = new SqlCommand( "sp_UpdateAdViews",         objConnection );       objCommand.CommandType = CommandType.StoredProcedure;       // We need to give it a parameter of the Ad ID. This       //   way it can retrieve the correct ad information.       objCommand.Parameters.Add( "@ID", SqlDbType.Int );       objCommand.Parameters["@ID"].Direction = ParameterDirection.Input;       objCommand.Parameters["@ID"].Value = nAdID;       // Execute the SP.       objCommand.ExecuteNonQuery();     }     else     {       // Close the reader.       objReader.Close();     }     strImageInfo = "<a href='" +       ConfigurationSettings.AppSettings["ClickAccepter"] +       "?Ad&Link=" + strLink +       "'><IMG src='/books/2/627/1/html/2/" + strImageURL +       "' AltText='" + strAltText +       "' Width='" + strImageWidth +       "' Height='" + strImageHeight +       "'></a>";   }   catch( Exception ex )   {     strImageInfo = ex.Message.ToString();   }   finally   {     // If the database connection is open,     //   close it.     if( objConnection.State == ConnectionState.Open )     {       objConnection.Close();     }   }   return( strImageInfo ); } 

The code that must be inserted into applications to display an ad banner is simple. It just instantiates an instance of the Web Service, calls the NextAd() method, and sends the data out with the Response.Write() method. This method is as follows:

 public void InsertAdBanner() {   // Instantiate the Web service wrapper class.   com.aspnet_solutions.www.TheAdBanner1 sv =     new com.aspnet_solutions.www.TheAdBanner1();   // Output the new ad to the HTML stream.   Response.Write( sv.NextAd() ); } 

Responding to Banner Clicks

If a user clicks a banner, then the click needs to be recorded. A stored procedure handles the logic for doing this, and it is named sp_UpdateAdClicks, shown in Listing 12.4. This stored procedure also inserts a record into the History table.

Listing 12.4 The Stored Procedure That Updates Advertisement View and Click Counts

 CREATE PROCEDURE sp_UpdateAdClicks   @ID int AS   update Advertisement set Clicks=Clicks+1 where ID=@ID   insert into History (AdID,Type) VALUES (@ID,1) GO 

There's a page named AcceptClick.aspx that is the destination of all banner clicks. This page records the click, and then redirects to the true destination page. The code behind this page can be seen in Listing 12.5.

The Page_Load() method in Listing 12.5 creates and opens a database connection. It then creates a SqlCommand object with which it will access the sp_UpdateAdClicks stored procedure. The stored procedure takes a single parameter of the advertisement ID. This ID arrives at the page as a query parameter and is converted to an integer with the following code:

 Convert.ToInt32( Request.QueryString["AdID"] ) 

Once the stored procedure is executed, the database connection is closed. Finally, the user is redirected to the true destination URL with the Response.Redirect() method.

Listing 12.5 The AcceptClick.aspx.cs Source Code. This Code Is Called in Response to a User's Banner Click.

 private void Page_Load(object sender, System.EventArgs e) {     // Create the database connection object.   SqlConnection objConnection =      new SqlConnection(        ConfigurationSettings.AppSettings["ConnectString"] );   try   {     // Open the database connection.     objConnection.Open();     // Create the command object. Set it for the     //   sp_GetNextAd stored procedure.     SqlCommand objCommand = new SqlCommand( "sp_UpdateAdClicks",       objConnection );     objCommand.CommandType = CommandType.StoredProcedure;     // We need to give it a parameter of the Ad ID. This     //   way it can retrieve the correct ad information.     objCommand.Parameters.Add( "@ID", SqlDbType.Int );     objCommand.Parameters["@ID"].Direction = ParameterDirection.Input;     objCommand.Parameters["@ID"].Value =        Convert.ToInt32( Request.QueryString["AdID"] );     // Execute the SP.     objCommand.ExecuteNonQuery();   }   catch   {   }   finally   {     // If the database connection is open,     //   close it.     if( objConnection.State == ConnectionState.Open )     {       objConnection.Close();     }   }   // Redirect to the destination URL.   Response.Redirect( Request.QueryString["Link"] ); } 

Administering Banners

There has to be a way to add and edit information. And the information you need to add and edit includes advertisement records and advertiser records. The first page I'll talk about adds and edits advertisement information. This page can be seen in Figure 12.2, and the code for it can be seen in Listing 12.6.

Figure 12.2. This Is the Page for Adding and Editing Advertisements.

In Listing 12.6, the Page_Load() method of the AddEditAd.aspx.cs source code module can be seen. The first thing in this method is the conversion of the AdID session variable into an integer variable. This makes it easier to use the AdID value.

If we're not in a post back, meaning this is the first time the page is being rendered, we'll need to populate the user interface objects. We'll also do this only if the page is being used for editing an advertisement and not for adding a new one. The AdID variable will be 1 if it's a new add, so this value is checked before performing the code that populates the user interface. I selected the value of 1 because the ID field in the table starts at 1. If you modify the software and/or table in any way, make sure that an ID of 1 will always be an invalid ID.

To populate the user interface objects, a database connection is created and then opened. Next, a SqlCommand object is created for accessing the sp_GetAdInfo stored procedure. This stored procedure can be seen in Listing 12.7 (along with the sp_GetAdvertiserInfo stored procedure). The SqlCommand.ExecuteReader() method is called, returning a SqlData Reader that contains the correct advertisement's data.

Once a recordset is retrieved with the SqlDataReader.Read() method, the user interface objects can all be populated from the recordset. Then all of the database objects are closed.

The last thing you'll see in the method is where the StartDate TextBox is set with the current date if this isn't a post back. This is so that the starting date will always be set for the user.

Listing 12.6 The AddEditAd.aspx.cs Source Code. This Code Adds or Edits an Advertisement.

 private void Page_Load(object sender, System.EventArgs e) {   // Convert the Ad ID to an integer for convenience.   int nAdID = Convert.ToInt32( Session["AdID"] );   // See if we need to populate the user interface.   if( !IsPostBack && nAdID >= 0 )   {     // Create the database connection object.     SqlConnection objConnection =        new SqlConnection(           ConfigurationSettings.AppSettings["ConnectString"] );     try     {       // Open the database connection.       objConnection.Open();       // Create the command object. Set it for the       //   sp_GetAdInfo stored procedure.       SqlCommand objCommand = new SqlCommand( "sp_GetAdInfo",         objConnection );       objCommand.CommandType = CommandType.StoredProcedure;       // We need to give it a parameter of the Ad ID. This       //   way it can retrieve the correct ad information.       objCommand.Parameters.Add( "@ID", SqlDbType.Int );       objCommand.Parameters["@ID"].Direction =            ParameterDirection.Input;       objCommand.Parameters["@ID"].Value = nAdID;       // Execute the reader and get the recordset.       SqlDataReader objReader = objCommand.ExecuteReader();       // Read a record.       if( objReader.Read() )       {         // Set all the user interface objects from the         //   recordset.         AltText.Text = Convert.ToString( objReader["AltText"] );         ImageURL.Text = Convert.ToString( objReader["ImageURL"] );         ImageWidth.Text =              Convert.ToString( objReader["ImageWidth"] );         ImageHeight.Text =              Convert.ToString( objReader["ImageHeight"] );         Link.Text = Convert.ToString( objReader["Link"] );         ViewLimit.Text = Convert.ToString( objReader["ViewLimit"] );         ClicksLimit.Text =              Convert.ToString( objReader["ClicksLimit"] );         // We'll make sure to convert the DateTime objects         //   into short date strings.         EndDate.Text =           Convert.ToDateTime(                  objReader["EndDate"] ).ToShortDateString();         StartDate.Text =           Convert.ToDateTime(                  objReader["StartDate"] ).ToShortDateString();       }     }     catch( Exception ex )     {       // Alert the user to the error.       ErrorMessage.Text = ex.Message.ToString();     }     finally     {       // If the database connection is open,       //   close it.       if( objConnection.State == ConnectionState.Open )       {         objConnection.Close();       }     }   }   // We come here when this is a new record.   else if( !IsPostBack )   {     // Populate the start date field with today's date.     StartDate.Text = DateTime.Now.ToShortDateString();   } } 

Listing 12.7 The Stored Procedures That Get Advertisement, Advertisers' Information, Advertisement History, and a List of Advertisements.

 CREATE PROCEDURE sp_GetAdInfo   @ID int AS   select * from Advertisement where ID=@ID GO CREATE PROCEDURE sp_GetAdvertiserInfo   @ID int AS   select * from Advertiser where ID=@ID GO CREATE PROCEDURE sp_GetHistory   @Start DateTime,   @End DateTime AS   select dt,Hour=DATEPART(hh,dt) from history      where dt>=@Start and dt<=@End order by dt desc GO CREATE PROCEDURE sp_GetListOfAds AS   select AltText,ID from Advertisement order by AltText GO 

The code in Listing 12.8 shows the code behind the Default.aspx page. The Page_Load() method populates the AdList user interface object from the database. It gives users an easy way to select an advertisement that they might want to edit. The Page_Load() method populates the AdList object only when the method is not called in response to a post back. At that time, a database connection is created and opened. A SqlCommand object is created for accessing the sp_GetListOfAds stored procedure. The SqlCommand.ExecuteReader() method is called, returning a recordset of all the ads. The recordset is then bound to the AdList ListBox object. If there are no records found, the AdList object is hidden.

The NewAd_Click() method is fired when the user clicks the New Ad button. This method simply redirects to the AddEditAd.aspx page after setting the AdID session variable to 1. When the AdID session variable is 1, it indicates to the AddEditAd.aspx page that it's a new add and not an edited ad.

The EditInfo_Click() method just redirects to the EditAdvertiser.aspx page.

The AdList_SelectedIndexChanged() method fires when the user clicks an ad in the list. The first thing this method does is to set the AdID session variable so that the AddEditAd.aspx page will know what advertisement should be edited. Control is then redirected to the AddEditAd.aspx page.

The Logout_Click() method signs out using the FormsAuthentication.SignOut() method, and then redirects to the Login.aspx page.

The InsertAdBanner() method was discussed earlier. It invokes the Web Service that creates the HTML for the ad banner. This data is then inserted into the HTML stream with the Response.Write() method.

The Reports_Click() method redirects to the reports page.

Listing 12.8 The Default.aspx.cs Source Code. This Is the Controlling Module for the Application.

 private void Page_Load(object sender, System.EventArgs e) {   if( !IsPostBack )   {     // Create the database connection.     SqlConnection objConnection =       new SqlConnection(            ConfigurationSettings.AppSettings["ConnectString"] );     // Create a reader and set it to null.     SqlDataReader objReader = null;     try     {       // Open the database connection.       objConnection.Open();       // Create the command object and set it       //   for the sp_GetListOfAds SP.       SqlCommand objCommand =         new SqlCommand( "sp_GetListOfAds", objConnection );       objCommand.CommandType = CommandType.StoredProcedure;       // Execute the reader and get the recordset.       objReader = objCommand.ExecuteReader();       // Set the DataList properties: the DataSource,       //   the DataTextField, the and DataValueField.       AdList.DataSource = objReader;       AdList.DataTextField = "AltText";       AdList.DataValueField = "ID";       // Bind the data.       AdList.DataBind();       // If there are no records, hide the       //   DataList object.       if( AdList.Items.Count == 0 )       {         AdList.Visible = false;         EditAnAd.Visible = false;       }     }     catch( Exception ex )     {       // Alert the user to the error.       ErrorMessage.Text = ex.Message.ToString();     }     finally     {       // If the reader is non-null we'll       //   need to close it.       if( objReader != null )       {         objReader.Close();       }       // If the database connection is open,       //   close it.       if( objConnection.State == ConnectionState.Open )       {         objConnection.Close();       }     }   } } private void NewAd_Click(object sender, System.EventArgs e) {   // Set the session variable to -1 to indicate   //   a new ad.   Session["AdID"] = -1;   Response.Redirect( "AddEditAd.aspx" ); } private void EditInfo_Click(object sender, System.EventArgs e) {   Response.Redirect( "EditAdvertiser.aspx" ); } private void AdList_SelectedIndexChanged(object sender, System.EventArgs e) {   // Set the session variable to the Ad ID for the   //   selected ad.  try  {    Session["AdID"] = Convert.ToInt32( AdList.SelectedItem.Value );  }  catch  {    ErrorMessage.Text = "You did not make a selection.";  }   Response.Redirect( "AddEditAd.aspx" ); } private void Logout_Click(object sender, System.EventArgs e) {   // Log out.   FormsAuthentication.SignOut();   Response.Redirect( "Login.aspx?Force=1" ); } public void InsertAdBanner() {   // Instantiate the Web service wrapper class.   com.aspnet_solutions.www.TheAdBanner1 sv =     new com.aspnet_solutions.www.TheAdBanner1();   // Output the new ad to the HTML stream.   Response.Write( sv.NextAd() ); } private void Reports_Click(object sender, System.EventArgs e) {   Response.Redirect( "Reports.aspx" ); } 

The code in Listing 12.9 shows the code for the EditAdvertiser.aspx.cs source code module. The Page_Load() method populates the information from the database. It calls the sp_GetAdvertiserInfo stored prodedure shown in Listing 12.7. It starts off by creating a database connection and then opening it. A SqlCommand object is then created for accessing the sp_GetAdvertiserInfo stored procedure. The AdvertiserID is used as a parameter for the sp_GetAdvertiserInfo stored procedure (the parameter name is @ID).

When the SqlCommand.ExecuteReader() method is called, it returns a recordset with the advertiser's information. This is used to populate the user interface objects.

The AddParameter() method is used to simplify adding parameters to a SqlCommand object. This method is called from the SaveUpdate_Click() method. It just uses the SqlCommand.Parameters.Add() method to add the parameter, it sets the parameter direction, and then it sets the parameter value.

The SaveUpdate_Click() method fires when the user clicks the Save/Update button. It starts by creating and opening a database connection. It creates a SqlCommand object with which the sp_UpdateAdvertiser stored procedure will be accessed. The @ID parameter is added and its value set. Then, eight parameters are added by using the AddParameter() helper method. The parameters (which are all strings) that are added are @Name, @Password, @Address, @CityStateZip, @Phone, @Fax, @Email, and @URL. Finally, the SqlCommand.ExecuteNonQuery() method is called to execute the stored procedure. You can see the EditAdvertiser.aspx page in Figure 12.3.

Figure 12.3. You Can Edit the Advertiser Information with This Screen.

The sp_GetAdvertiserInfo stored procedure can be seen in Listing 12.9.

Listing 12.9 The EditAdvertiser.aspx.cs source code. This code allows an advertiser's information to be edited.

[View full width]

 private void Page_Load(object sender, System.EventArgs e) {   if( !IsPostBack )   {     // Create the database connection object.     SqlConnection objConnection =        new SqlConnection(           ConfigurationSettings.AppSettings["ConnectString"] );     try     {       // Open the database connection.       objConnection.Open();       // Create the command object and set for       //  the sp_GetAdvertiserInfo SP.       SqlCommand objCommand = new SqlCommand( "sp_GetAdvertiserInfo",         objConnection );       objCommand.CommandType = CommandType.StoredProcedure;       // We'll need to give it the ID as a parameter.       objCommand.Parameters.Add( "@ID", SqlDbType.Int );       objCommand.Parameters["@ID"].Direction =            ParameterDirection.Input;       objCommand.Parameters["@ID"].Value =            Convert.ToInt32( Session["AdvertiserID"] );       // Execute the reader and get a recordset.       SqlDataReader objReader = objCommand.ExecuteReader();       // Get the first record.       if( objReader.Read() )       {         // Set the user interface objects to the         //   appropriate data.         Name.Text = Convert.ToString( objReader["Login"] );         Password.Text = Convert.ToString( objReader["Password"] );         Email.Text = Convert.ToString( objReader["Email"] );         Phone.Text = Convert.ToString( objReader["Phone"] );         Fax.Text = Convert.ToString( objReader["Fax"] );         URL.Text = Convert.ToString( objReader["URL"] );         Address.Text = Convert.ToString( objReader["Address"] );         CityStateZip.Text =               Convert.ToString( objReader["CityStateZip"] );       }     }     catch( Exception ex )     {       // Alert the user to the error.       ErrorMessage.Text = ex.Message.ToString();     }     finally     {       // If the database connection is opened,       //  close it.       if( objConnection.State == ConnectionState.Open )       {         objConnection.Close();       }     }   } } // Helper method that adds a string parameter to a command object. private void AddParameter( ref SqlCommand objCommand, string strValue, string strParamName , int nSize ) {   // Add the parameter.   objCommand.Parameters.Add( strParamName, SqlDbType.NVarChar, nSize );   // Set the direction.   objCommand.Parameters[strParamName].Direction =      ParameterDirection.Input;   // Store the value.   objCommand.Parameters[strParamName].Value = strValue; } private void SaveUpdate_Click(object sender, System.EventArgs e) {   // Create the database connection object.   SqlConnection objConnection =      new SqlConnection(         ConfigurationSettings.AppSettings["ConnectString"] );   // Assume pessimism for success.   bool bSuccess = false;   try   {     // Open the database connection.     objConnection.Open();     // Convert the Advertiser ID into an integer for convenience.     int nAdvertiserID = Convert.ToInt32( Session["AdvertiserID"] );     // Create the command object and set for the     //   sp_UpdateAdvertiser SP.     SqlCommand objCommand = new SqlCommand( "sp_UpdateAdvertiser",       objConnection );     objCommand.CommandType = CommandType.StoredProcedure;     // We'll need to give it the ID as a parameter.     objCommand.Parameters.Add( "@ID", SqlDbType.Int );     objCommand.Parameters["@ID"].Direction = ParameterDirection.Input;     objCommand.Parameters["@ID"].Value = nAdvertiserID;     // Call the AddParameter() helper method to add the      //   parameters and set the data.     AddParameter( ref objCommand, Name.Text, "@Name", 150 );     AddParameter( ref objCommand, Password.Text, "@Password", 150 );     AddParameter( ref objCommand, Address.Text, "@Address", 150 );     AddParameter( ref objCommand, CityStateZip.Text, "@CityStateZip",       150 );     AddParameter( ref objCommand, Phone.Text, "@Phone", 150 );     AddParameter( ref objCommand, Fax.Text, "@Fax", 150 );     AddParameter( ref objCommand, Email.Text, "@Email", 150 );     AddParameter( ref objCommand, URL.Text, "@URL", 150 );     // Execute the query.     objCommand.ExecuteNonQuery();     // Indicate success.     bSuccess = true;   }   catch( Exception ex )   {     // Alert the user to the error.     ErrorMessage.Text = ex.Message.ToString();   }   finally   {     // Close the database connection if it's     //   open.     if( objConnection.State == ConnectionState.Open )     {       objConnection.Close();     }   }   // If successful, go back to the main page.   if( bSuccess )   {     Response.Redirect( "Default.aspx" );   } } 

Listing 12.10 The Stored Procedures That Update Advertisements and Advertisers.

[View full width]

 CREATE PROCEDURE sp_UpdateAdvertiser   @Name nvarchar(150),   @Password nvarchar(150),   @Address nvarchar(150),   @CityStateZip nvarchar(150),   @Phone nvarchar(25),   @Fax nvarchar(25),   @URL nvarchar(300),   @Email nvarchar(250),   @ID int AS   update Advertiser set Login=@Name,Password=@Password,Address=@Address,  CityStateZip=@CityStateZip,Phone=@Phone,Fax=@Fax,URL=@URL,Email=@Email where ID=@ID GO CREATE PROCEDURE sp_UpdateAd   @AltText nvarchar(300),   @ImageURL nvarchar(300),   @ImageWidth int,   @ImageHeight int,   @Link nvarchar(300),   @ViewLimit int,   @ClicksLimit int,   @EndDate DateTime,   @ID int out AS   Update Advertisement set AltText=@AltText,ImageURL=@ImageURL, ImageWidth=@ImageWidth ,ImageHeight=@ImageHeight,Link=@Link,ViewLimit=@ViewLimit,ClicksLimit=@ClicksLimit ,EndDate=@EndDate where ID=@ID GO