Deleting SQL Server Data, ASP.net

If you have code on your ASP.NET pages that allows visitors to enter a record, or where you add records through code without their input, you will likely need to delete records from your pages. This technique shows you how to use a SQL Delete query to delete records from an SQL Server table.

This technique displays a list of employees from an SQL Server table called Employees. Visitors select a name and then click the button. When they do that the employee selected is deleted from the database.

Defined on the page is this DropDownList control that will display the employee names:

dropdownlist id="ddlEmps" datatextfield="EmpName" datavaluefield="EmpID" runat="server"

and this Button control is defined:

button
id="butDelete" text="Delete" onclick="SubmitBtn_Click"
runat="server"
/ >

When the page first loads, the following code will populate the DropDownList control:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
If Not IsPostBack Then
Dim DBConn as SQLConnection
Dim DBCommand As SQLDataAdapter Dim DSPageData as New DataSet
DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;" _
& "User Id=sa;" _
"Password=yourpassword;") DBCommand = New SQLDataAdapter _ ("Select LastName + ' + FirstName "
& "as EmpName, EmpID " _
& "From Employees " _
& "Order By LastName, FirstName", DBConn)
DBCommand.Fill(DSPageData, _ "Employees")
ddlEmps.DataSource = _
DSPageData.Tables("Employees").DefaultView
ddlEmps.DataBind()
End If
End Sub

and this procedure fires when the Button control is clicked:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs)

When that happens, you need a Connection and a Command object:

Dim DBConn as SQLConnection Dim DBDelete As New SQLCommand

You start by connecting to the SQL Server database:

DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;"
"Password=yourpassword;")

Then the SQL Delete statement that will delete the record selected in the DropDownList control is placed into the CommandText property of the Command object:

DBDelete.CommandText = "Delete From Employees " _
& "Where EmpID = " & ddlEmps.SelectedItem.Value

The connection to the database for the Command object is made though the Connection object:

DBDelete.Connection = DBConn DBDelete.Connection.Open

and the offending record is deleted:

DBDelete.ExecuteNonQuery()

Adding SQL Server Data, ASP.net

When your ASP.NET application includes SQL Server database connectivity, you often will need to add records to the database through your ASP.NET pages. For example, you may track visitors as they visit your site. With each page they visit, you may want to add a hit record to your database. Or you may allow visitors to sign themselves up for a mailing list. In that case you would need to add their information to a database. This technique shows you how to add a record to an SQL Server database table.

The ASP.NET page presented with this technique allows visitors to add an employee record to the SQL Server database. Visitors enter information into the provided TextBox controls and click the Button control to add the new record to the database.

Defined on the page are TextBox controls like this one used to enter the new information through:

<asp:textbox
id="txtLastName" runat="Server"

This Button control allows visitors to submit their request:

<asp:button
id="butOK"
text=" OK "
onclick="SubmitEtn_Click" runat="server"
/>

When that button is clicked, this procedure fires:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs) To add a record, you will need a Connection object:

Dim DBConn as SQLConnection

and a Command object:

Dim DBAdd As New SQLCommand

The Connection object connects to the SQL Server database:

DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")

Next, you place into the CommandText property of the Command object the SQL Insert statement that adds the new record:

DBAdd.CommandText = "Insert Into Employees (" _
& "LastName, FirstName, BirthDate, Salary, " _
& "EmailAddress) values (" _
& "'" & Replace(txtLastName.Text, "'", """) _
& "', "
* "'" Replace(txtFirstName.Text, "'", """)
&
* "'" Replace(txtBirthDate.Text, "'", """)
&
Replace- (txtSalary.Text, "'", """) & ", " _
& "'" & Replace(txtEmailAddress.Text, "'", """) _
& “.).

The Command object will connect to the database through the Connection object: DBAdd.Connection = DBConn

That connection is opened:

DBAdd.Connection.Open

and the record is added to the database:

DBAdd.ExecuteNonQuery()

Online Store Benchmarking

This part of our web metrics program assessed and measured the current state of websites on which our products were sold. This initial assessment would then be compared to the same web- sites in the same measurement methods after improvement recommendations had been implemented.

This methodology uses parts from inspection methodology. Because it uses category experts as reviewers instead of actual customers, it does not measure usability. It does, however, measure how well a website is optimized for category and product profitability. This environmental scan is necessary to set the bar for improvement.

The strategy was to use an objective process to measure each site, provide recommendations and e-planograms to the online merchants based on what we learned, and then re-measure the effectiveness after the recommendations were implemented.

It was important that the results be reproducible, so back up copies of specific key findings were made in case the website changed. It was also critical to have a knowledgeable category expert conduct the evaluation because a nuance to one person could result in missed opportunities that would have gone overlooked. Benchmarking methodologies assess the current state of a website and how well it is optimized for sales.

Objectives

The online store benchmark program had four main objectives:

• Understand online store design and content in key areas that shape the user experience.
• Identify gaps and weaknesses in online store deployment and customer expectations.
• Define problem areas and issues related to performing key navigation and ordering tasks.
• Make recommendations for improved customer usability and chances for increased sales based on documented findings and conclusions.

The benchmarking must accommodate different types of store designs and configurations, because all online stores are different. The benchmarking must also accommodate the morphing online marketplace and have relevance to the online merchant.

Strategies

We defined the following strategies to accomplish our objectives:

• Establish objective evaluation criteria, and rate the store's effectiveness in selling key products.
• Quantify which important online sales features are present in key focus category areas.
• Develop search strategy and methodologies based on known customer expectations of the purchasing process.
• Recommend improvements of the site that will result in increased sales and enhance customer usability.
• Aggregate results, and relatively rank all stores evaluated.

Track effectiveness of the store over time, and define ROI of this process based on the increased sales or consumer loyalty index ratings.

The benchmarking program was designed as an ongoing, iterative process instead of a one-time program. Because Internet selling was growing at a rapid rate, it was important to monitor this business. Also, online stores carry a broader product mix than traditional channels. The sites reflect changed navigation models and product profitability.

The Benchmarking Process

We developed and used the following process:

1. Establish search criteria.
2. Develop a metrics scorecard.
3. Conduct the evaluation.
4. Map out purchase process flow issues.
5. Purchase products.
6. Total the scores.
7. Recommend improvements.

Recommendations were supported by annotated screen captures. Recommendations were detailed in an overall recommendations/assessment document that lists good and confusing aspects of navigation by area and category. It also noted best practices that reinforced what appeared to work well within the site.

A meeting was conducted with the online merchant to share key findings and deliver recommendations on online store improvement as it related to category profitability. Store executives, buyers, merchandisers, and members of the IT department attended the meeting, because all have an influence on the online store design.

Sub Category and Product Detail Pages of Six Functional Parts

Online stores use the following six basic component webpages in their store design:

• Home page
• Category pages
• Sub-category pages
• Product detail pages
• Informational content pages
• Checkout page

Sub-category Pages—"The Shelf"

Because most online stores carry hundreds or thousands of products, it is important to break up the information into manageable chunks. Products are displayed on a shelf—whether it is physical shelving in a brick-and-mortar store, a catalog page, a TV infomercial, or an e-commerce website product page. The product shelf is any web page on which a product is merchandised and sold. It can be a category page featuring many products, or it can be a specific product page with a single product.

In this example, there are many varieties of all-in-one printers. The subcategory page helps to narrow down the offering. A thorough understanding of customer needs is necessary to structure these pages. Depending on product complexities, informational content can provide customers with "why to buy" information that helps them select the right product to meet their needs. Emerging online tools—such as the shopping wizard—can help narrow choices.

Product Detail Pages—"The Package or SKU"

Because customers can't pick up virtual merchandise and read labels, the product detail page represents the product or behaves like the product package. It must clearly communicate what the product is and what the customer will receive as a result of its purchase. Anytime a page offers a product for sale, it must provide the following information for each of the products offered:

• What it is—the description, picture, uses
• Relevant and complete compatibility, sizing, color, or other information
• What's contained in the package—what the customer will receive
• Other items needed for immediate operation (batteries, cables, assembly, UL specifications)
• Spare or complementary items (extra batteries, film, or a carrying case)
• "Care and feeding" of the product (special polish, cleaning instructions)
• The price and any hidden charges (extra shipping or handling)
• The manufacturer's or designer's name
• Sample content (for example, sample book pages)

The customer must know clearly what the product is and what it looks like. And for customers who know exactly what they are looking for, accurate descriptions and specific product numbers or models must be included so they may easily recognize the correct product. Our research across more than 25 major elcommerce websites identified incomplete or inadequate information. In many cases, the web stores did not provide complete compatibility information.

Shoppers will not purchase from a site that cannot confirm their choice or be specific about what the product is that they are purchasing.

Product detail pages let customers know what they're getting and what else they may need or want. In this example, product features—the "speeds and feeds"—are listed. This includes products they may need to purchase in order to use the product. It also features other products the customer may want. Good clothing stores recommend coordinated accessories to give customers ideas to complete ensembles for a variety of social occasions.

The product detail page also needs to let people know how to buy the product. As simple as that sounds, customers who participated in website evaluation research had difficulty adding a product to the shopping cart. This function varies from website to website. Button labels can also vary. Some say "add to cart," "buy," or "add." The buttons also have different locations on these sites. Some are next to the products and some are far enough away to disassociate them from the product.

Also, some websites force customers to go to the shopping cart or checkout page every time they add an item to the shopping cart. This then forces them to start over or go back and forth if they are shopping for multiple items. It adds an unnecessary extra step.

Make it easy for the customer to know what they're getting and how to get it. Customers will leave tedious websites.

Iterating Through Records in an SQL Server Table

In many situations you no longer need to iterate through records in an SQL Server table in ASP.NET pages as you did with ASP. This is because you can now bind DataSet Tables directly to controls.

But on occasion you may still need to iterate through all the records in a DataSet Table so that they can be processed in some special way.

The DataSet Table object contains a Rows collection that allows you to iterate through each record it contains. This technique shows you how to create a loop so that you can process each record from an SQL Server table through a DataSet Table object.

The ASP.NET page presented with this technique iterates through each record in an Employees table using a For loop. The ID of each employee is placed into the Text property of this Label control as it is processed:

<asp:label id="1b1Message" runat="Server" />

The code that iterates through the records fires when the page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)

Within that procedure, these data objects are needed:

Dim DBConn as SQLConnection

Dim DBCommand As SQLDataAdapter Dim DSPageData as New DataSet

You also need a variable that will be used in the For loop:

Dim I as Long

You start by connecting to the SQL Server database:

DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")

Then you retrieve all the employee records:

DBCommand = New SQLDataAdapter ("Select * from Employees", DBConn)

and place them into a DataSet table object:

DBCommand.Fill(DSPageData, "Emps")

Next, you initiate a For block that will go from 0 to the total number of records in the DataSet table minus 1. You retrieve the record count in the DataSet Table object through the Count property of the Rows collection:

For I = 0 To DSPageData.Tables("Emps").Rows.Count - 1

Then within the loop you can process each record. You would use the variable "I" to refer to the CU rrent row within the current iteration of the loop:

'Code to process record

lblMessage.Text = lblMessage.Text _
& "
Processed Record: "
& DSPageData.Tables("Emps"). _ Rows(I).Item("EmpID")

You would then move on to process the next record: Next

Present Good Product Pages, the Successful Websites

If people don't understand what they are getting, they won't purchase.

Customer Insight

The following comments were representative of customers who found products. hut who were not presented with adequate information to con firm their choices:

• There's not enough definition there to tell me that it's what I'm looking for:"

Well. I think I got the right one. hut I'm not positive:

So then I have to go hack and take another course of action. Which is aggregating. I think I found it, but I don't know for sure.''

I found the product I need. I just can't buy it from them: The add-to-cart button s as too far from the product.

Inadequate and inconsistent information was presented to customers often. Decision making information was left out of most descriptions.

Make it easy for customers to understand and compare products by applying the following recommendations:

• Clearly state what the product is, how it operates. or describe its style. Include how it might be used, or list occasions for which it might he suitable.
• Include all available quantities or units (single, multiple) on the same page to simply navigation.
• Clearly explain alternatives or variations of the product if it conies in different 'flavors: '
• Ensure that one product description can differentiate it from other products and that the customer has a good understanding of what he is about to purchase
• Include product photos, and use up-to-date images and graphics. Customers use photos to confirm their selection.
• Include SKY and product codes, hut dc-emphasize obscure numbers.

Sometimes information provided on comparable products is very similar, and its hard for customers to distinguish differences in features or attributes. Routine reviews of products displayed in the category can let you see what your customers see. It's your oh to help them know why they want one product over another. If information is unclear to you, it is unclear to your customers.

Online merchants must merchandise and refresh hundreds or thousands of products monthly. It is difficult to give attention to all products. It is critical, however, to ensure that sour most profitable categories and top-selling products are presented appropriate to their values.

Test the Experience for Successful Websites

You can gain valuable insights into your own design with in-house testing.

Recommendations

Apply the following recommendations to ensure that you're keeping track of the customer experience:

• Understand that no design is perfect the first time.
• Test your own site with real customers.
• Test your site against competitor websites.
• The web yourself for real tasks. Buy half of your gifts online for the next holiday season, and observe how other sites make it easy or difficult for you to buy from them.

Test early and test often. After you've tested, test again at planned intervals. Customer habits change, products change, and the marketplaces change. Routine testing should be built into the website schedules.

Make Customers Feel at Home on the Home Page, the Successful Websites Rule

The home page serves as an entry point to the navigation scheme and user choices for the rest of the website. It also sets the tone for the customer's visit. It must be inviting and informative It should answer the question, "What can I do with this website'?"

Customer Insight

Customers verbally and physically reacted strongly to home pages. The following customer comments are representative of their reactions:

• My first reaction when I see this screen is 'Wow!' There are lots of things to read. I want to find what I'm looking for and get it:'
• I hate it Websites are supposed to make your life easier:'
• The home page had so much stuff on it, I didn't see the search box, So I didn't use it. I felt stupid trying to find it."
• "It's as busy as heck:"
• This has lots of advertising on it, and I find it kind of distracting. It takes me a while to figure out how I can get where I need to go:'
• Boy, there are lots to look at laughs nervously. It's a very busy website. I really hate looking at that much advertising:'
• They have too much information. When I'm looking for something specific. I want to find it easy and fast. If I go to a site and don't see it readily available, I don't stay on the site I go somewhere else."

People had physical reactions to the home pages through body language. Especially in their facial expressions. Some people became nervous or agitated just looking at busy websites, and they physically withdrew. They sat back in their chairs and folded their arms trying to avoid proceeding with the tasks.

Invite customers to shop and feel at home on the home page by applying the following recommendations:

• Inform the customer clearly what the website does, what she can do with it and why she should care.
• Use simple and direct language to clearly state what your company does and why visitors should work with or purchase from your company.
• Structure the home page clearly.
• Detail any available resources on your site.
• Ensure that navigational areas are recognizable at first glance.
• Enable customers to link back to the home page from any interior page in one click (company logo, bread-crumbing).

Home pages should factor in clarity and accessibility for the customer.

Recommendations for Successful Websites

The following recommendations will help you meet the requirements and demands of a successful website.

• Plan: Always have the capacity for twice your current volume.
• Realize: Traffic can grow by several hundred percent after a big campaign.
• Remember: Anybody you turn away will not return.
• Start Out Right: Don't run a big campaign with a bad web design. Fix the site first.

The above rules are minimum standards, requirements, and current customer expectations. Periodic reviews and affirmations keep these rules in the forefront. Share these rules with others in the organization as business online basics.

The rules are fundamentals in guiding decisions or features of a website. Content of a page is a major contributor to the customer experience.

Help Customers Successfully Search, aka. Keyword Search, the Successful Websites

Many users know exactly what they want and go straight for the search box. Unsuccessful searches equal missed sales, a diminished market basket, and eventual loss of the customer.

1) Customer Insight

The following are representative customer comments about their experiences with the keyword search text box and advance search mechanisms:

• Keyword search didn't take me anywhere except where I've already been:'
• I didn't like the search. When I typed in my words, it really didn't find anything for me"
• It would have been nice if it brought up what I searched for. But that's never been my experience."

I love search engines, when I type the words in right:

Some searches resulted in looping, where customers performed a search, received search results, selected an item from the list, and were brought back to the initial search box page. This occurred on several websites.

We also discovered that similar words typed in the search boa brought hack different results; which were also confusing to shoppers.

2) Recommendations

Users need to search when they are lost, and you don't know where they will he when they are lost. Help customers have successful searches by applying the following recommendations:

• Has e a prominent search held on the home page.
• Keep the search feature available on all pages.
• Make it easy for customers to search by keyword, and allow for common errors.
• Allow search by SKY or product codes. but don't sort results by these numbers.
• Return search results that provide the necessary information for confirmation of the correct product.
• Provide logical sorting of search results-either by relevance, by group or category, or alphabetically.
• If searching or a product name. Place that product page at the top of the search results.
• Ensure that search results lead to designated pages.
• Track the top 100 customer search words monthly.
• If searching for a brand, place the overview or compatibility page for that brand at the top of the search results rather than on random product listings.

Tracking the top 100 customer word searches on your website gives you insight into what customers are thinking or needing in the way of assistance. In some cases, you may find incremental sales opportunities if several customers search for a product that you don't currently otter. Or you'll gain insight into how shoppers look for products.

Failed searches lose potential customers. If you can turn that bailed search into a hit you have a new customer.

Speed Rules Successful Websites

Internet users do not like to wait. The taster your WebPages respond and the easier it is for the buyer to understand the page and take the next step the more successful the site.

a) Customer Insight

Customers made the following comments about website performance when they were asked to describe their experience with the website:

• Horrible. It was kind of slow, and you had to go looking for things:'
• Pictures take too long to load, and I don't have time to wait:'
• Nothing happened. Oh, there it is. I'm just impatient."

Some websites performed slower than others, and customers became impatient. Some labels and buttons took too long to render. On one website, the add-to-cart buttons were so slow to appear that customers couldn't figure out how to add an item into the shopping cart, even though they had found the product.

b) Recommendations

The following are recommendations to improve website performance and speed:

• Keep response time to 10 seconds maximum. Less for high-speed connections.
• Utilize a fast server and a high-end Internet connection.
• Minimize download times. Too often, WebPages load slowly, links are broken, and debugging messages pop-up on the site.
• Use few graphics, small graphics, and cached graphics
• Use task-sized product photos people want to see what they are buying. Use a small photo on the initial page. Link to larger photos with more details, and include several shots from different angles.
• Don't use photos in long lists of products on category pages unless they help to clearly differentiate products.
• Provide visual feedback to the customer if a transaction is going to take more than a second or two.
• Test page downloads using Internet connection speeds representative of typical website visitors.

All pages accessed during navigation should download in less than 10 seconds. Download speeds can be affected by Internet usage cycles. Internet traffic and usage can be heavier on certain days, peak hours, and special seasons like holidays. Testing during different days of the week and at different times gives you a more complete snapshot of speeds and performance.

Even with high-speed Internet connections, many customers still access the web over telephone lines. If a first-time customer encounters extremely slow performance on the home page, she may transfer the perception to the rest of the site and may not continue.

Simple text loads much quicker than graphics. If performance trade-offs are being made, use simple text links instead of fancy font graphics on web pages.