Maximizing Lead Generation Marketing ROI

Marketers responsible for lead generation are all too familiar with some common challenges –getting closed-loop feedback from the sales organization, measuring marketing effectiveness beyond just lead quantity and cost per lead, and building strong alignment with the sales organization. effective lead generation marketing is very much tied to overcoming these challenges to prioritize lead quality over lead quantity.
The research report released in June 2008 showed that companies rating their lead generation marketing as more effective than competitors were three times as likely to report a strength in closed-loop sales tracking and measuring the ROI (return on investment) of their lead generation marketing. More than half of the marketers with more effective lead generation marketing (56%) expect that their sales organizations would rate the quality of leads as positive (top two scores on a 5-point rating scale) compared to just 20% of those with less effective marketing.

Lead quality is critical to managing and improving bottom line performance. In addition to these research findings, basic ROI analytics can be used to make your case to prioritize quality over quantity and guide your decisions to deliver greater performance and profitability. You ultimately need better insight into the two primary dimensions of quality – average profit per sale and average conversion rates from lead to sale. With insight into lead quality and ROI, the marketing team can unlock untapped opportunities with:

1. Better alignment of the marketing spend to value potential
2. Improvements to marketing strategy
3. Better tracking and metrics
4. Earning buy-in from the sales organization

We’ll run through some basic math first to build the support for acting on the strategy, metrics, and alignment opportunities.

Marketing Investment Levels Aligned to Value Potential

Cost per Lead is a very commonly used marketing metric (used by 34% of B2B lead generation marketers according to our research).
It can be an insightful metric but it is highly dependent on all leads having the same value and conversion potential, which is rarely the case. If your cost per lead goes up but you bring in better quality leads with either higher profit per sale or conversion rates, it will be a worthwhile investment. Marketing effectiveness can be improved with “value-tiered marketing investments” that align marketing spending with the expected returns for better ROI performance.

For example, you may be marketing to a target audience that has an average profit per sale of $4,000 (based on $16,000 revenue * 25% gross margin) and a lead-to-sale conversion rate of 7%. To achieve an ROI of 25% the maximum Cost per Lead is calculated as $224. The calculation is presented below.

            Maximum Cost per Lead = ($4,000 / (1 + 25%)) * 7% = $224

But suppose the target audience is made up of three equal-size segments with the following performance patterns:

Segments	Conversion Rate	Profit per Sale
Segment A	6%	$5,000
Segment B	10%	$4,000
Segment C	5%	$3,000
Total (average)	7.0%	$4,000

Segment A has the highest value, while Segment B has a highest conversion rate. It does not make sense to manage our marketing investment using the average Cost per Lead. Instead we run the basic calculation to determine the maximum Cost per Lead per segment.

Segments	Conversion Rate	Profit per Sale	Maximum Cost per Lead
Segment A	6%	$5,000	$240
Segment B	10%	$4,000	$320
Segment C	5%	$3,000	$120
Total (average)	7.0%	$4,000	$224

Now we can see that more marketing can be invested into Segment B based on the higher quality of the lead. In fact our previous marketing to Segment C that averaged $224 Cost per Lead was actually costing more in marketing than the profits returned and therefore hurting the company’s financial performance.

This is a very basic financial analysis which can be replaced with more advanced versions that take into account the sales investment in addition to the marketing investment and multiple stages of conversion rates from initial sales acceptance of leads through the sales pipeline. The other benefit of improving lead quality is that the cost of sales resources will be reduced as the leads unlikely to convert to a sale are removed from the sales pipeline. In the advanced versions of the ROI analysis, this becomes apparent.

Regardless of the level of sophistication, we have at least established the economic view of why lead quality and targeting are important to drive effectiveness, performance, and ROI. Segmentation of targeted prospects based on the two dimensions of lead quality allows you to invest more in high profit-potential segments and reduce spending with low profit-potential segments.

Improvements in Marketing Strategy

There are a number of key areas of marketing strategy that can benefit with better insight into lead quality. The most significant driver of ROI is targeting so improvements here are a top priority. Lead generation marketing can reach a broad audience with many different types of marketing tactics. The goal is to concentrate additional tactics and offers on the top segments of prospects. This is where lead scoring and predictive modeling can be used to set the priorities and establish investment levels based on the conversion probability and value probability.

Better marketing conditioning and nurturing of leads prior to the hand off to the sales team improves conversion rates. Improvements in the effectiveness of this stage of marketing can be assessed using lead-to-sale conversion rates and incremental customer value per sale. Once again, a basic ROI calculation can demonstrate how investing in additional marketing touchpoints prior to handing leads off to sales can generate significant returns from increased revenues.

The focus on narrowing the marketing efforts on fewer but better prospects to deliver higher quality leads always sparks the same question – “but what if the volumes drop too low?” If you apply the ROI analysis shown above and identify the point of diminishing returns – that is the point where the cost per lead is too high to reach your ROI target – it is certainly possible that your total lead quantities will decrease. But when you reach the point where your lead generation marketing will no longer be profitable, quantity will not help. You must then shift your priorities to improving effectiveness with the leads you are already generating. To achieve this effectively, marketing must be working in step with the sales organization to improve conversion rates within the sales pipeline.

Keep in mind that improving conversion rates in the sales pipeline has very high profit potential, even if lead quantity is not an issue. If only 2% to 10% of leads are converting to closed sales, that means 90% to 98% of the leads are leaking out of the funnel, offering plenty of room from performance improvements. Marketing organizations that are restricted to lead generation with a minimal role in supporting the sales pipeline should look closely at the opportunities here to build the case for better alignment and integration.

As part of your overall funnel management and getting the most value from the leads generated, ROI insight tied to lead quality can also support decisions for marketing to nurture rejected or stalled leads.

Better Tracking & Metrics

Marketing ROI is driven by three primary drivers of profitability – incremental customer value (ideally long-term profits), lead-to-purchase conversions rate, and cost per sale. Add “Total Sales Volume” to that list and you have the four most critical metrics for managing performance – keeping the company’s overall financial performance on track and guiding marketing and sales priorities to reach and exceed goals. These same metrics work at the campaign level to prioritize segments for targeting and guide budget allocation, as addressed earlier in this article. Other marketing metrics should align to these core metrics to provide greater depth of insight that can be acted upon. For example, a metric such as Average Value of First-Time Buyers may serve as an early indicator of a likely decline in future revenues if your marketing is now attracting leads from lower value segments.

In order to use better metrics that reflect lead quality, you must have better tracking – in particular the closed-loop feedback from the sales organization that captures the lead outcomes such as sales acceptance, funnel progression, and conversion to closed sale. The ratio of marketing qualified leads (“MQLs,” which represent the leads marketing deems ready to hand over to the sales organization) to sales qualified leads (“SQLs,” which represent the leads sales accepts as qualified for a sales contact) is very important. Marketing can improve this MQL to SQL ratio when provided with feedback to understand the drivers of rejected leads and profiles of the accepted leads.

Additional tracking detail from the sales organization that can support improvements in lead quality includes:

• Lead screening reasons (i.e., why MQLs are not accepted as SQLs).
• Lead progression rates through key funnel stages to show leakage points.
• Average days from lead hand-off to sales contact (reflecting sales capacity and the “freshness” of leads contacted).
• Good reporting on leakage reasons (i.e., why they dropped out of the sales pipeline).
• Joint win-loss analysis to assess leads lost late in the sales pipeline.
• Analyses of closed sales to provide information on both net close rate and incremental customer value (sometimes available through financial systems instead of sales reporting).

Marketing also has to take responsibility for tracking and maintaining quality data for performance analysis. The Demand Generation Case Study showed that access to data under marketing’s control was a weakness yet had a strong correlation to achieving highly effective and efficient marketing. In particular, marketing must maintain detailed tracking of outbound and inbound marketing touchpoints to prospects during the lead generation process. This information is critical to applying more advanced measurements that can properly attribute sales contribution to multiple touchpoints instead of just crediting the most recent lead source.

Earning Sales Buy-In

Simply wishing for, or even requesting, that the sales organization put extra effort into providing tracking information to marketing is not enough. Marketing must make the case that it can and will act on the information provided. As established at the beginning of this article, lead quality benefits the sales organization as well as the financial performance of the company. Leads with higher conversion rates use sales resources more efficiently. And higher conversion rates and higher value per sale both contribute to achieving sales goals.

The B-to-B Lead Generation: Marketing ROI and Performance Evaluation Study found that companies outgrowing their competitors had better alignment between sales and marketing than companies growing slower than competitors as shown in the chart below.

One of the most effective ways that we have found to break down the barriers between marketing and sales is to identify and work with a pilot team within the sales organization. The pilot team is typically a group that sees the potential value of lead quality, or is willing to experiment in order to improve performance. The tracking efforts are done without major investments into systems infrastructure or organization-wide process changes.

The closed-loop information provided back to marketing is then applied to improve lead quality from the initial targeting and tactical investments, to marketing’s lead qualification and contribution to the sales pipeline. A basic ROI analysis is used to run scenarios that indicate the key priorities for where additional marketing support can most effectively lead to incremental sales and profitability. Improving lead generation marketing is a continuous journey, but one that offers significant profit potential.

More Resources on

 video White paper / podcasts

marketing solutions 

marketing packages

webinars

How to Create a REG File for Windows XP

The Windows Registry is a database of system and application settings, as well as runtime information.

It can be a confusing morass of keys, subkeys, and values, but if you know what you're looking for you can quickly back up and fix almost anything.

Similar to a file explorer, registry values are organized into keys (folders) and subkeys (subfolders), with values (files) having a name and some bit of stored information. Backing up registry values in Windows XP creates \"reg\" files, which can be edited in a text editor and merged into the Registry to re-enable your preferred settings.

 Instructions
 Things You'll Need 
 Registry Editor (regedit.exe on Windows XP)Regular backups of registry values before editing.



 Manual Editing—Registry Editor 

1 Open the Registry Editor from the \"Start\" menu by selecting \"Run...\" and typing \"regedit\" in the popup dialog. 

2 Find the registry value in a registry tree (key) that needs to be fixed. For example, the setting for showing \"Recent Documents\" in the Start menu is stored at [HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Policies\\Explorer] in the registry value \"NoRecentDocsMenu\". 

3 Before changing anything in the registry, create a backup of the registry key by right-clicking on the key in the left side of the Registry Editor—in this case, \"Explorer\" is the lowest key -- and select \"Export\". Save the file to your Desktop with a recognizable name, such as \"Explorer Policy—Original.reg\", with the extension \"reg\" telling Windows it is a registry key file. 

4 Double-click on the registry value \"NoRecentDocsMenu\" to modify it (right-click to \"Rename\" it). In the popup window, change the \"Value data\" from 0 to 1 to disable \"Recent Docs\" in the Start Menu. Click \"OK\". 

5 Changing some registry settings may require a logoff/logon before taking effect, while others may require a computer restart. Try logging off and back on, then check the Start menu to see the effect.

 Manual Editing—Reg Files 

1 Find the backup registry file we created earlier, \"Explorer Policy—Original.reg\". The default double-click action may be to merge the setting with the registry, so right-click and select \"Edit\" to open it in Notepad. 

2 The first line of the file tells you what you used to create the registry file, and must be present— \"Windows Registry Editor Version 5.00.\" The registry key we changed appears between square brackets [ ], ending in \"...Policies\\Explorer].\" The registry values are beneath the key, given as \"name\"=type:value. Note that all of the values we saw earlier in the Registry Editor were saved to this file, not just the one we changed. 

3 Erase all of the values except the line with \"NoRecentDocsMenu\"=dword:..., and save the file as \"Explorer Policy—Recent Docs.reg\" to your Desktop. This file now has the instructions to find the given registry key (or create it if it doesn't exist) and modify (or create) the registry value with the given data type and value. This will restore the Recent Documents to the Start menu. 

4 Right-click on the new registry file and select \"Merge.\" Click \"OK\" to approve changing the registry, and \"OK\" for the popup indicating a successful change. 

5 Log off and back on and then check the \"Start\" menu to see the effect.


 Tips & Warnings 

• In a registry file, to remove a specific value, put a minus sign after the equals, like \"NoRecentDocsMenu\"=-. To delete the default value of a key, enter @=- on the next line below the key name in brackets. To delete the entire key (and all of its subkeys and values) put the minus directly after the open bracket in the key name, like [-HKEY_CURRENT_USER\\Software...
• To \"refresh\" Windows after a registry change, so you may not need to log out and back in, open the Task Manager with \"Ctrl+Alt+Del\" or \"Ctrl+Shift+Esc.\" Select \"explorer.exe\" and click \"End Process.\" After it reloads (the desktop and taskbar may flash), check to see if the new registry settings have taken effect.
• If a registry change causes Windows to fail, use the Recovery Console to restore the registry hive backup (.BAK) file, usually saved to the %Windir%\\System32\\config folder.
• Many programs create registry keys that remain even if they are uninstalled. A \"bloated\" registry can affect computer performance, since the registry is constantly being scanned for data. Careful use of registry-cleaner software may help, simplifying the manual steps to remove unneeded registry keys.

•  ALWAYS back up the registry before you edit anything. However, changing something major may prevent you from accessing the registry or even logging in to Windows, so consult appropriate documentation about how keys affect Windows behavior before \"going fishing.\" Creating a System Restore Point before editing may also be a good idea.
• Do not double-click registry files—this is usually the default action to merge their contents to the registry. Instead, always right-click registry files to ensure you are choosing the correct action.
• You must be logged in as a user with administrative privileges in order to edit the registry.

Installing Visual Studio 2010 – Error writing the project file. Class not registered

This weekend I installed the new Visual Studio 2010 on several different machines. I use multiple OS platforms  so it’s useful to have Visual Studio installed on machines with different configurations. I had no problems installing on Windows 7 and Windows Vista, but hit a snag on Windows Server 2003 (SP2).
After installing Visual Studio 2010, VS launched fine but when I attempted to create a project, I got the not-so-helpful error message “Error writing the project file. Class not registered”. OK.

I noticed that when I launched VS for the first time I did not get the option to select a settings configuration, such as General Development Settings or Web Development, like I did with the installs on other machines. In the VS 2010 UI, when I went to Tools | Import and Export Settings, the UI dialog had an empty list box where I expected to see a list of files with a .vssettings extension. After searching the Internet for this error message I came across several possible causes. Well to cut to the chase, it turns out that the machine I had installed onto did not have the required MSXML6 parser. A VS settings file is XML-based and evidently needs MSXML6 rather than earlier versions of the parser.

A quick install of that component from Microsoft.com/downloads solved the problem.

How to change GridView column alignments for dynamic data source ?

-->

By Default ASP.NET GridView Columns has left alignment text. We can use GridView ItemStyle property with either of GridView BondField or Template Field to change the default alignments.GridView properties,ItemStyle-HorizontalAlign having few set of values, using which we can change the alignments.

Above customization is true when you are using Bound Field or Template Field for binding the data . But If you want to customize the same set alignments for data source which are directly binding with GridView you have to use Gridview_RowDataBound or Gridview_PreRender methods .

Let’s say you are binding below set of records directly with gridview [sourcecode language="csharp"]

List<Employee> employees = new List<Employee>
{
new Employee{ID=1, Name="Employee 1", Address="Address 1" },
new Employee{ID=2, Name="Employee 2", Address="Address 2" },
new Employee{ID=3, Name="Employee 3", Address="Address 3" },
new Employee{ID=4, Name="Employee 4", Address="Address 4" },
new Employee{ID=5, Name="Employee 5", Address="Address 5" },
new Employee{ID=6, Name="Employee 6", Address="Address 6" }
};
GridView1.DataSource = employees;
GridView1.DataBind();

[/sourcecode]
As the above set of records binded without any data bound field, you have to override the RowDataBound Event to set the alignment.

-->
[sourcecode language="csharp"]
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Right

}
}
[/sourcecode]

DataControlRowType enum having the similar values as ItemStyle-Horizontal alignment.

Similarly you can override the same thing in GridView_PreRender Method as well

[sourcecode language="csharp"]

protected void GridView1_PreRender(object sender, EventArgs e)
{
foreach (GridViewRow row in GridView1.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
row.Cells[0].HorizontalAlign = HorizontalAlign.Left;
}
}
}

[/sourcecode]

-->

How to Keep Your Information Secure in the Cloud?

In 2011, hacking groups like Lulzsec and Anonymous provoked an Internet firestorm by hacking major Web sites like Fox.com and online services like Sony's PlayStation Network. Millions of user accounts were compromised. Usernames, passwords, home addresses and credit card information -- lax Web site security often allows hackers easy access to boatloads of personal information. We can blame corporations for poor security and hackers for maliciously attacking Web sites, but there's a third party often at fault in these attacks: ourselves, the users. No, it's not our fault Web sites get hacked. But poor Web safety habits put us at risk when we shouldn't be. How often do you use the same username and password? Every time you create a new profile? If someone hacked your Facebook account, could they just as easily get into your e-mail inbox? Reusing passwords -- or using weak passwords -- makes you an easy target for identity theft. Remembering multiple passwords can be a pain, but there are Web services that can help. We'll talk about one of the most popular options later in this Post. Internet cloud services -- services that store your data on a server rather than on your hard drive so you can access it from any Internet-enabled device -- are more powerful than ever before. Backing up photographs and important documents has never been easier. Google Docs and Gmail can take the place of Microsoft Word and Outlook Express. Banking sites take the place of expensive finance applications. All we have to do is be safe while we use them. Here are some simple safety tips for keeping your data secure in the cloud. First up: making your passwords as tough to crack as Fort Knox.


1: Be Alert and Play It Safe


Internet hazards like viruses are, for the most part, easy to avoid. Shady Web sites usually look shady; e-mail attachments from spam addresses are never worth opening. Antivirus software is always a smart precaution, but smart browsing is an even greater ally. What does this have to do with protecting your data in the cloud? The same rules apply when it comes to buying online or creating accounts on new Web sites: Make sure the site is trustworthy. If you're buying from a retailer you've never heard of, do a little research on them first. They could have notoriously lax security and have a history of losing customer credit card information to hacking breaches. Finally, be aware of what computers you're logged into. Browsers will often ask to save your login information and keep a login session alive as long as the browser is open. If you log in to Facebook or your e-mail account on a friend's laptop and then leave, you'll likely still be logged in to those sites. If they're trustworthy, that may not be a problem. But what if you're using a public computer? Stay logged in to one of those and anyone could gain access to your account. Yep, that would be bad. Unless you're using your own computer, remember to log out and never save your password and user information. Browse safe, and with a little luck, you'll never have to worry about anyone finding a single one of your online passwords.


2: Back Up Your Data


 If there's one piece of advice the tech savvy have been espousing for years and years, it's this: Back up your data. A power surge, faulty hard drive platter, robbery or other unexpected system failure could happen when you least expect it, and if your data isn't backed up you'll beat yourself up over it for weeks. Years ago, backing up data was an arduous task. Hard drive storage was costly, but floppy disks only held a paltry amount of data. Eventually, ZIP disks and CD burners offered enough space to facilitate backups, and DVDs and cheap hard drives made them easier still. But now we have something even better: the cloud.

Cloud storage solutions come in all shapes and sizes. Dropbox offers only a couple gigabytes of free storage, but its interface is incredibly simple to use. It creates a folder on your hard drive that's linked to the Web -- all you have to do to upload files is drag them into the folder. WindowsLiveSkydrive is designed to make it easy to view and edit Office documents in the cloud. Amazon's Cloud Drive offers 5 gigabytes of free storage and a Web interface for uploading your files. Other services, like SugarSync and Mozy, focus more on automatically backing up your important data and storing it, rather than making it easily accessible online. Here's the smartest way to backup your data: Don't rely on one service. Store files you access frequently in Dropbox and back up more in a free service like Amazon Cloud Drive.

Keep a local backup on a secondary hard drive or on an automated backup drive like Apple'sTimeCapsule. With your data securely backed up and your passwords uncrackable, there's only one thing left to be concerned about: your browsing habits.


3: Manage Passwords with LastPass 


LastPass is a password management utility that locks all of your unique passwords behind one master password. That means you can create separate logins for e-mail, Facebook, Twitter, cloud storage and everything else you do online, but still access those accounts by memorizing one single password. Web browsers will remember passwords for you, but LastPass can synchronize your information across multiple browsers and devices and fill in forms with a single click. LastPass will even help you create randomized passwords that no one will ever crack.

The service is free, but for a $1 per month premium account you gain access to the mobile version of LastPass for iOS, Android and most other mobile operating systems. What if LastPass gets hacked? That's possible, but LastPasshasprotocolsinplace to encourage users to change their master passwords in the event of a breach. More importantly, validation tools like IP and e-mail address verification make it difficult for an impostor to log in to your LastPass account. LastPass is just one example of a cloud-based service that makes managing data on the Web easier. When it comes to preserving your important pictures and files, finding the right backup services is key.

4: Don't Reuse or Share Passwords 


The annoyance of remembering passwords strikes again. It's bad enough that we tend to use simple, easy-to-remember passwords for our Web logins -- we also tend to pick one or two passwords and use them again and again for our e-mail, banking, Facebook and everything else. That's bad. In fact, that's really bad. If your password is compromised, someone could easily gain access to your e-mail account. And change that password. And then go to every site you're registered on and change those passwords -- the replacement passwords are always sent to your e-mail address. Use different passwords for different sites. At the very least, change up letters, symbols and capitalization if you plan to use the same word or phrase across multiple sites. Make absolutely sure you don't repeat a password across sites that have your credit card information or social security number. Your e-mail password is the most important. Keep it secure and don't use it for any other sites. One last password tip: Don't tell other people your passwords. Even if you trust them, it's not a particularly good idea. The more people who know your passwords, the greater the chances that those passwords could be accidentally compromised. All these password rules make our online lives more secure, but they don't make them easier. Next up: a tool for taking some of the inconvenience out of password management.


5: Play Smart with Passwords 


Passwords are designed to keep our information safe from prying eyes. They're like locks. A hacker may force the door and break your lock, but most of the time a strong lock keeps people out. But let's be honest: Passwords are annoying. Remembering them is a pain, so we often take the easy way out and use simple passwords that we won't forget. But if they're easy to remember, they're also easy to guess.

When the site RockYou.com was hacked in 2009, a security firm examined the 32 million compromised passwords and found that thousands upon thousands of users relied on the same basic phrases.

The password "123456" took first place with 290,731 hits; "12345," "123456789," "Password" and "iloveyou" rounded out the top five most-used passwords [source: Tom'sHardware].

If you use one of those passwords, change it. The more complicated your password is, the safer your data will be. It's true, complex passwords won't be as easy to recall. Find a safe place to record your passwords if you can't remember them. The best passwords combine letters, numbers and symbols into an unusual configuration. Don't take the easy route and capitalize the first letter of the word or use the numeral "1" in place of the letter "l" or a zero in place of the letter "O." Throw in a few random numbers or characters like a plus sign (+) or underscore (_) and you'll be far better off than anyone relying on "password123" or "qwerty" to keep them safe. Once you have a good password, what you do next is just as important: Don't spread it around.

DataGridView in Windows Forms – Tips, Tricks

DataGridView control is a Windows Forms control that gives you the ability to customize and edit tabular data. It gives you number of properties, methods and events to customize its appearance and behavior. In this article, we will discuss some frequently asked questions and their solutions. These questions have been collected from a variety of sources including some newsgroups, MSDN site and a few, answered by me at the MSDN forums.

Tip 1 – Populating a DataGridView

In this short snippet, we will populate a DataGridView using the LoadData() method. This method uses the SqlDataAdapter to populate a DataSet. The table ‘Orders’ in the DataSet is then bound to the BindingSource component which gives us the flexibility to choose/modify the data location.

C#

public partial class Form1 : Form

    {

        private SqlDataAdapter da;

        private SqlConnection conn;

        BindingSource bsource = new BindingSource();

        DataSet ds = null;

        string sql;

        public Form1()

        {

            InitializeComponent();

        }

private void btnLoad_Click(object sender, EventArgs e)

        {

            LoadData();

        }

        private void LoadData()

        {

string connectionString = "Data Source=localhost;Initial Catalog=Northwind;" + "Integrated Security=SSPI;";

            conn = new SqlConnection(connectionString);

sql = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight," +"ShipName, ShipCountry FROM Orders";

            da = new SqlDataAdapter(sql, conn);

            conn.Open();

            ds = new DataSet();

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);          

            da.Fill(ds, "Orders");

            bsource.DataSource = ds.Tables["Orders"];

            dgv.DataSource = bsource;          

        }

    }

VB.NET

Public Partial Class Form1

      Inherits Form

            Private da As SqlDataAdapter

            Private conn As SqlConnection

            Private bsource As BindingSource = New BindingSource()

            Private ds As DataSet = Nothing

            Private sql As String

            Public Sub New()

                  InitializeComponent()

            End Sub

Private Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs)

                  LoadData()

            End Sub

 Private Sub LoadData()

Dim connectionString As String = "Data Source=localhost;Initial Catalog=Northwind;" & "Integrated Security=SSPI;"

                  conn = New SqlConnection(connectionString)

sql = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight," & "ShipName, ShipCountry FROM Orders"

                  da = New SqlDataAdapter(sql, conn)

                  conn.Open()

                  ds = New DataSet()

Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

                  da.Fill(ds, "Orders")

                  bsource.DataSource = ds.Tables("Orders")

                  dgv.DataSource = bsource

            End Sub

End Class

Tip 2 – Update the data in the DataGridView and save changes in the database

After editing the data in the cells, if you would like to update the changes permanently in the database, use the following code:

C#

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            DataTable dt = ds.Tables["Orders"];

           this.dgv.BindingContext[dt].EndCurrentEdit();

            this.da.Update(dt);

        }

VB.NET

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs)

                  Dim dt As DataTable = ds.Tables("Orders")

                  Me.dgv.BindingContext(dt).EndCurrentEdit()

                  Me.da.Update(dt)

      End Sub

Tip 3 – Display a confirmation box before deleting a row in the DataGridView

Handle the UserDeletingRow event to display a confirmation box to the user. If the user confirms the deletion, delete the row. If the user clicks cancel, set e.cancel = true which cancels the row deletion.

C#

private void dgv_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)

        {

            if (!e.Row.IsNewRow)

            {

                DialogResult res = MessageBox.Show("Are you sure you want to delete this row?", "Delete confirmation",

                         MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (res == DialogResult.No)

                    e.Cancel = true;

            }

        }

VB.NET

Private Sub dgv_UserDeletingRow(ByVal sender As Object, ByVal e AsDataGridViewRowCancelEventArgs)

                  If (Not e.Row.IsNewRow) Then

                        Dim res As DialogResult = MessageBox.Show("Are you sure you want to delete this row?", "Delete confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

                        If res = DialogResult.No Then

                              e.Cancel = True

                        End If

                  End If

End Sub

Tip 4 – How to autoresize column width in the DataGridView

The snippet shown below, first auto-resizes the columns to fit its content. Then the AutoSizeColumnsMode is set to the ‘DataGridViewAutoSizeColumnsMode.AllCells’ enumeration value which automatically adjust the widths of the columns when the data changes.

C#

private void btnResize_Click(object sender, EventArgs e)

        {

            dgv.AutoResizeColumns();

            dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

        }

VB.NET

Private Sub btnResize_Click(ByVal sender As Object, ByVal e As EventArgs)

                  dgv.AutoResizeColumns()

                  dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

End Sub

Tip 5 - Select and Highlight an entire row in DataGridView

C#

int rowToBeSelected = 3; // third row

if (dgv.Rows.Count >= rowToBeSelected)

{             

       // Since index is zero based, you have to subtract 1

        dgv.Rows[rowToBeSelected - 1].Selected = true;

}

VB.NET

Dim rowToBeSelected As Integer = 3 ' third row

If dgv.Rows.Count >= rowToBeSelected Then

         ' Since index is zero based, you have to subtract 1

            dgv.Rows(rowToBeSelected - 1).Selected = True

End If

Tip 6 - How to scroll programmatically to a row in the DataGridView

The DataGridView has a property called FirstDisplayedScrollingRowIndex that can be used in order to scroll to a row programmatically.

C#

int jumpToRow = 20;

if (dgv.Rows.Count >= jumpToRow && jumpToRow >= 1)

{             

        dgv.FirstDisplayedScrollingRowIndex = jumpToRow;

        dgv.Rows[jumpToRow].Selected = true;

}

VB.NET

Dim jumpToRow As Integer = 20

If dgv.Rows.Count >= jumpToRow AndAlso jumpToRow >= 1 Then

            dgv.FirstDisplayedScrollingRowIndex = jumpToRow

            dgv.Rows(jumpToRow).Selected = True

End If

Tip 7 - Calculate a column total in the DataGridView and display in a textbox

A common requirement is to calculate the total of a currency field and display it in a textbox. In the snippet below, we will be calculating the total of the ‘Freight’ field. We will then display the data in a textbox by formatting the result (observe the ToString("c")) while displaying the data, which displays the culture-specific currency.

C#

private void btnTotal_Click(object sender, EventArgs e)

        {

            if(dgv.Rows.Count > 0)

             txtTotal.Text = Total().ToString("c");

        }

        private double Total()

        {

            double tot = 0;

            int i = 0;

            for (i = 0; i < dgv.Rows.Count; i++)

            {

                tot = tot + Convert.ToDouble(dgv.Rows[i].Cells["Freight"].Value);

            }

            return tot;

        }

VB.NET

Private Sub btnTotal_Click(ByVal sender As Object, ByVal e As EventArgs)

                  If dgv.Rows.Count > 0 Then

                   txtTotal.Text = Total().ToString("c")

                  End If

End Sub

Private Function Total() As Double

                  Dim tot As Double = 0

                  Dim i As Integer = 0

                  For i = 0 To dgv.Rows.Count - 1

                        tot = tot + Convert.ToDouble(dgv.Rows(i).Cells("Freight").Value)

                  Next i

                  Return tot

End Function

Tip 8 - Change the Header Names in the DataGridView

If the columns being retrieved from the database do not have meaningful names, we always have the option of changing the header names as shown in this snippet:

C#

private void btnChange_Click(object sender, EventArgs e)

        {

            dgv.Columns[0].HeaderText = "MyHeader1";

            dgv.Columns[1].HeaderText = "MyHeader2";

        }

VB.NET

Private Sub btnChange_Click(ByVal sender As Object, ByVal e As EventArgs)

                  dgv.Columns(0).HeaderText = "MyHeader1"

                  dgv.Columns(1).HeaderText = "MyHeader2"

End Sub

Tip 9 - Change the Color of Cells, Rows and Border in the DataGridView

C#

private void btnCellRow_Click(object sender, EventArgs e)

        {

            // Change ForeColor of each Cell

            this.dgv.DefaultCellStyle.ForeColor = Color.Coral;

            // Change back color of each row

            this.dgv.RowsDefaultCellStyle.BackColor = Color.AliceBlue;

            // Change GridLine Color

            this.dgv.GridColor = Color.Blue;

            // Change Grid Border Style

            this.dgv.BorderStyle = BorderStyle.Fixed3D;

        }

VB.NET

Private Sub btnCellRow_Click(ByVal sender As Object, ByVal e As EventArgs)

                  ' Change ForeColor of each Cell

                  Me.dgv.DefaultCellStyle.ForeColor = Color.Coral

                  ' Change back color of each row

                  Me.dgv.RowsDefaultCellStyle.BackColor = Color.AliceBlue

                  ' Change GridLine Color

                  Me.dgv.GridColor = Color.Blue

                  ' Change Grid Border Style

                  Me.dgv.BorderStyle = BorderStyle.Fixed3D

End Sub

Tip 10 - Hide a Column in the DataGridView

If you would like to hide a column based on a certain condition, here’s a snippet for that.

C#

private void btnHide_Click(object sender, EventArgs e)

        {

            this.dgv.Columns["EmployeeID"].Visible = false;

        }

VB.NET

Private Sub btnHide_Click(ByVal sender As Object, ByVal e As EventArgs)

                  Me.dgv.Columns("EmployeeID").Visible = False

End Sub

Tip 11 - Handle SelectedIndexChanged of a ComboBox in the DataGridView

To handle the SelectedIndexChanged event of a DataGridViewComboBox, you need to use the DataGridView.EditingControlShowing event as shown below. You can then retrieve the selected index or the selected text of the combobox.

C#

private void dataGridView1_EditingControlShowing(object sender,DataGridViewEditingControlShowingEventArgs e)

        {

            ComboBox editingComboBox = (ComboBox)e.Control;

            if(editingComboBox != null)

                editingComboBox.SelectedIndexChanged += newSystem.EventHandler(this.editingComboBox_SelectedIndexChanged);

        }

private void editingComboBox_SelectedIndexChanged(object sender, System.EventArgs e)

        {

            ComboBox comboBox1 = (ComboBox)sender;

            // Display index

            MessageBox.Show(comboBox1.SelectedIndex.ToString());

            // Display value

            MessageBox.Show(comboBox1.Text);

        }

VB.NET

Private Sub dataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e AsDataGridViewEditingControlShowingEventArgs)

www.GoDaddy.com  .com .us .net .org .biz .info .mobi .me .ws

                  Dim editingComboBox As ComboBox = CType(e.Control, ComboBox)

                  If Not editingComboBox Is Nothing Then

                        AddHandler editingComboBox.SelectedIndexChanged, AddressOfeditingComboBox_SelectedIndexChanged

                  End If

End Sub

Private Sub editingComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e AsSystem.EventArgs)

                  Dim comboBox1 As ComboBox = CType(sender, ComboBox)

                  ' Display index

                  MessageBox.Show(comboBox1.SelectedIndex.ToString())

                  ' Display value

                  MessageBox.Show(comboBox1.Text)

End Sub

Tip 12 - Change Color of Alternate Rows in the DataGridView

C#

private void btnAlternate_Click(object sender, EventArgs e)

        {

            this.dgv.RowsDefaultCellStyle.BackColor = Color.White;

            this.dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine;

        }

VB.NET

Private Sub btnAlternate_Click(ByVal sender As Object, ByVal e As EventArgs)

                  Me.dgv.RowsDefaultCellStyle.BackColor = Color.White

                  Me.dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine

End Sub

Tip 13 - Formatting Data in the DataGridView

The DataGridView exposes properties that enable you to format data such as displaying a currency column in the culture specific currency or displaying nulls in a desired format and so on.

C#

private void btnFormat_Click(object sender, EventArgs e)

        {

            // display currency in culture-specific currency for

            this.dgv.Columns["Freight"].DefaultCellStyle.Format = "c";

            // display nulls as 'NA'

            this.dgv.DefaultCellStyle.NullValue = "NA";

        }

VB.NET

Private Sub btnFormat_Click(ByVal sender As Object, ByVal e As EventArgs)

                  ' display currency in culture-specific currency for

                  Me.dgv.Columns("Freight").DefaultCellStyle.Format = "c"

                  ' display nulls as 'NA'

                  Me.dgv.DefaultCellStyle.NullValue = "NA"

End Sub

Tip 14 – Change the order of columns in the DataGridView

In order to change the order of columns, just set the DisplayIndex property of the DataGridView to the desired value. Remember that the index is zero based.

C#

private void btnReorder_Click(object sender, EventArgs e)

        {

             dgv.Columns["CustomerID"].DisplayIndex = 5;

             dgv.Columns["OrderID"].DisplayIndex = 3;

             dgv.Columns["EmployeeID"].DisplayIndex = 1;

             dgv.Columns["OrderDate"].DisplayIndex = 2;

             dgv.Columns["Freight"].DisplayIndex = 6;

             dgv.Columns["ShipCountry"].DisplayIndex = 0;

             dgv.Columns["ShipName"].DisplayIndex = 4;

        }

VB.NET

Private Sub btnReorder_Click(ByVal sender As Object, ByVal e As EventArgs)

                   dgv.Columns("CustomerID").DisplayIndex = 5

                   dgv.Columns("OrderID").DisplayIndex = 3

                   dgv.Columns("EmployeeID").DisplayIndex = 1

                   dgv.Columns("OrderDate").DisplayIndex = 2

                   dgv.Columns("Freight").DisplayIndex = 6

                   dgv.Columns("ShipCountry").DisplayIndex = 0

                   dgv.Columns("ShipName").DisplayIndex = 4

End Sub