VB.NET : Adjusting ComboBox DropDownList width to longest string width

A ComboBox dropdown width defaults to the width of the control itself. When items in the list are longer than the control, they get clipped and the user cannot read them without selecting. Windows Forms does not resize the dropdown automatically, so you have to measure and set it yourself.

This comes up any time a ComboBox is sized to fit a form layout rather than its content, which is most of the time. A narrow control with long display values is one of those small usability issues that is easy to overlook and just as easy to fix.

---

The fix

The function below measures every item string using the control's own font and Graphics context, accounts for the vertical scrollbar width when the item count exceeds MaxDropDownItems, and sets DropDownWidth to the widest measured value.

Public Shared Function AdjustComboBoxWidth(ByVal sender As Object, ByVal e As EventArgs)
    Dim senderComboBox  = DirectCast(sender, ComboBox)
    Dim width           As Integer = senderComboBox.DropDownWidth
    Dim g               As Graphics = senderComboBox.CreateGraphics()
    Dim font            As Font = senderComboBox.Font

    Dim vertScrollBarWidth As Integer = If(
        (senderComboBox.Items.Count > senderComboBox.MaxDropDownItems),
        SystemInformation.VerticalScrollBarWidth,
        0)

    Dim newWidth As Integer
    For Each s As String In DirectCast(sender, ComboBox).Items
        newWidth = CInt(g.MeasureString(s, font).Width) + vertScrollBarWidth
        If width < newWidth Then
            width = newWidth
        End If
    Next

    senderComboBox.DropDownWidth = width
    Return False
End Function

Wire it up to the DropDown event of any ComboBox so it runs each time the list opens. That way it adapts if the items change between openings:

AddHandler comboBox1.DropDown, AddressOf AdjustComboBoxWidth

Because the function signature matches the standard EventHandler delegate (sender As Object, e As EventArgs), the same shared function can be reused across every ComboBox on every form in the project without duplication.

Tip: Graphics.MeasureString can slightly overestimate string width due to GDI character spacing. If you find the dropdown a touch too wide, subtract a small constant (typically 2 to 4 pixels) from newWidth after measuring. Alternatively, use TextRenderer.MeasureText with TextFormatFlags.NoPadding for a tighter and more accurate measurement that matches how Windows Forms actually renders text.

---

When to use this

• Any ComboBox whose items are populated at runtime from a database or a dynamic source where the longest value is not known at design time.
• Forms where the ComboBox width is constrained by the layout and cannot simply be made wider to accommodate all content.
• Shared utility libraries where a single reusable handler can be attached to all ComboBox controls across a large Windows Forms application.

Found this useful? Drop a comment below if you have a variation or ran into an edge case I did not cover.

VB.NET : How to assign shortcut keys to the ToolStripButton?

ToolStripButton does not have a ShortcutKeys property the way a ToolStripMenuItem does. If you want a function key or any other keyboard shortcut to trigger a toolbar button, you need to intercept the keystroke at the form level yourself.

The most common scenario is a toolbar with an Execute or Refresh button that users expect to trigger with F5, matching the behaviour they know from Visual Studio, SQL Server Management Studio, and similar tools.

---

The fix

Override ProcessCmdKey on the form and call PerformClick on the target ToolStripButton when the matching key is pressed. ProcessCmdKey intercepts keystrokes before any focused control gets a chance to handle them, which makes it the right place for form-wide shortcuts.

' Assigning Shortcut Keys to ToolStrip Buttons
Protected Overrides Function ProcessCmdKey(
    ByRef msg      As System.Windows.Forms.Message,
    ByVal keyData  As System.Windows.Forms.Keys) As Boolean

    Select Case keyData
        Case Keys.F5
            ExecuteToolStripButton.PerformClick()
        Case Else
            'Do Nothing
    End Select

    Return MyBase.ProcessCmdKey(msg, keyData)
End Function

Returning MyBase.ProcessCmdKey(msg, keyData) at the end is important. It passes any unhandled keys back up the chain so that standard shortcuts such as Ctrl+C, Ctrl+Z, and arrow key navigation continue to work normally throughout the form.

Tip: To assign modifier combinations such as Ctrl+F5 or Shift+F5, combine the key values with the bitwise Or operator: Case Keys.F5 Or Keys.Control. The keyData parameter includes modifier flags, so this comparison works exactly as expected.

---

Handling multiple shortcuts

The Select Case block scales cleanly to as many shortcuts as you need. Add one Case per key combination and call PerformClick on the corresponding button:

Protected Overrides Function ProcessCmdKey(
    ByRef msg      As System.Windows.Forms.Message,
    ByVal keyData  As System.Windows.Forms.Keys) As Boolean

    Select Case keyData
        Case Keys.F5
            ExecuteToolStripButton.PerformClick()
        Case Keys.F5 Or Keys.Control
            CancelToolStripButton.PerformClick()
        Case Keys.F2
            EditToolStripButton.PerformClick()
        Case Else
            'Do Nothing
    End Select

    Return MyBase.ProcessCmdKey(msg, keyData)
End Function

---

When to use this

• Any form with a ToolStrip where users expect keyboard shortcuts to match familiar tools such as SSMS, Visual Studio, or Excel.
• Data entry forms where a single Execute or Save button needs to be reachable from the keyboard without the user moving their hands to the mouse.
• Situations where adding a ToolStripMenuItem with a ShortcutKeys property is not appropriate because the menu item itself should not be visible to users.

Found this useful? Drop a comment below if you have a variation or ran into an edge case I did not cover.

VB.NET : How to draw outline around a borderless form?

'Paints a thin border around the form
    Protected Overrides Sub OnPaintBackground(ByVal e As System.Windows.Forms.PaintEventArgs)
        MyBase.OnPaintBackground(e)

        Dim rect As New Rectangle(0, 0, Me.ClientSize.Width - 1, Me.ClientSize.Height - 1)
        e.Graphics.DrawRectangle(Pens.SkyBlue, rect)
    End Sub


P.S. Inherited from how-to-draw-outline-around-a-borderless-form

VB.NET : Copying files from one folder to another

The interface of my Application used a font available in Windows 7 but not in Windows XP. While opening the Application, it would not show nicely (in other words, my forms were disordered esp. @ bold controls). 

So, I needed the same fonts in Windows XP or the system not having those fonts.
I did what we would do to solve the problems from my Application itself, copying those fonts to the Windows Fonts folder.

Private Sub CopyFont()
        Dim fonts(5) As String 'To store fonts name inside the folder
        Dim i As Integer = 0
        Dim fontExists As Boolean = False
        Dim sourcePath As String = String.Empty
        Dim destinationPath As String = String.Empty
        Dim fontNamewithPath As String = String.Empty
        Dim fontName As String = String.Empty

        ' Listing all fonts found in "..\Fonts\" folder
        For Each foundFontFile As String In My.Computer.FileSystem.GetFiles(Application.StartupPath & "\Fonts\", FileIO.SearchOption.SearchTopLevelOnly, "*.ttf")
            fontNamewithPath = foundFontFile
            Dim font() As String = fontNamewithPath.Split("\")
            fontName = font(font.Length() - 1)

            fonts(i) = fontName.Trim(" ")
            i += 1
        Next

        ' Verifying whether the destined folder contains all the fonts
        For Each requiredFont In fonts
            For Each oldFont As String In My.Computer.FileSystem.GetFiles("C:\Windows\Fonts\", FileIO.SearchOption.SearchTopLevelOnly, requiredFont)
                fontNamewithPath = oldFont
                Dim font() As String = fontNamewithPath.Split("\")
                fontName = font(font.Length() - 1)

                If fontName = requiredFont Then
                    fontExists = True
                End If
            Next
        Next

        ' If the destined folder doesn't contains the fonts of the source folder, copy it to the destined folder
        If fontExists = False Then
            For Each requiredFont In fonts
                sourcePath = Application.StartupPath & "\Fonts\" & requiredFont
                destinationPath = "C:\Windows\Fonts\" & requiredFont
                My.Computer.FileSystem.CopyFile(sourcePath, destinationPath, FileIO.UIOption.AllDialogs, FileIO.UICancelOption.DoNothing)
            Next
        Else
            'MessageBox.Show("Font already exists", "Install Fonts")
            'Do Nothing
        End If

    End Sub


P.S. I did the above possible with some googling and some stackoverflow posts...

TSQL Questions

1. What is the output of both the queries on a database?
CREATE TABLE dbo.Zip_code
(
  id    VARCHAR(10) NULL,
  Descr VARCHAR(max) NULL
)
GO

INSERT INTO dbo.Zip_code VALUES('1111', 'AAAA')
GO
INSERT INTO dbo.Zip_code VALUES('2222', 'bbbb')
GO
INSERT INTO dbo.Zip_code VALUES('aaaa', 'bbbb')
GO

SELECT count(*) FROM Zip_code WHERE id = '1111'
SELECT count(*) FROM Zip_code WHERE id = 1111

Options: a) 1,1     b) 1, error    c) error, error

2. What is the output of the SELECT query?
CREATE TABLE test
(
  col1 INT,
  col2 CHAR(2)
)
GO
INSERT INTO test VALUES(1,'AB')

INSERT INTO test VALUES(2,'Ab')

INSERT INTO test VALUES(3,'aB')

INSERT INTO test VALUES(4,'ab')

INSERT INTO test VALUES(5,'XY')

INSERT INTO test VALUES(6,'xy')
GO
SELECT COUNT(*) FROM test WHERE col2 in ('AB', 'aB', 'xy')

Options: a) 3     b) 6    c) None

SQL Server: CTAS - Create Table As SELECT

CTAS stands for 'Create Table As SELECT'.

This method is used when table was not created earlier and needs to be created using data from another table. The new table is created using same data types as of the prior table.

Example:

USE AdventureWorks
GO

SELECT FirstName, LastName
INTO MyTable
FROM Person.Contact
WHERE EmailPromotion = 2


Reference : http://blog.sqlauthority.com/

VB: MultipleLayeredColumnHeader

This following code demonstrates how to display multiple layer column headers on the DataGridView control:


'Enable resizing on the column headers
            Me.DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing

'Adjust the height for the column headers
            Me.DataGridView1.ColumnHeadersHeight = Me.DataGridView1.ColumnHeadersHeight * 2

'Adjust the text alignment on the column headers to make the text display at the center of the bottom
            Me.DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter



'Handle the DataGridView.CellPainting event to draw text for each header cell

Private Sub DataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
     If e.RowIndex = -1 AndAlso e.ColumnIndex > -1 Then
          e.PaintBackground(e.CellBounds, False)
          Dim r2 As Rectangle = e.CellBounds
          r2.Y += e.CellBounds.Height / 2
          r2.Height = e.CellBounds.Height / 2
          e.PaintContent(r2)
          e.Handled = True
     End If
End Sub

'Handle the DataGridView.Paint event to draw "merged" header cells

Private Sub DataGridView1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles DataGridView1.Paint
'Data for merged Header cells
Dim feeTitles As String() = {"Admission", "MonthlyFee", "Id Card"}
For j As Integer = 0 To Me.DataGridView1.ColumnCount - 1 Step 2
'Get the column header cell bounds
Dim r1 As Rectangle = Me.DataGridView1.GetCellDisplayRectangle(j, -1, True)
r1.X += 1
r1.Y += 1
r1.Width = r1.Width * 2 - 2
r1.Height = r1.Height / 2 - 2

Using br As SolidBrush = New SolidBrush(Me.DataGridView1.ColumnHeadersDefaultCellStyle.BackColor)
e.Graphics.FillRectangle(br, r1)
End Using

Using p As Pen = New Pen(SystemColors.InactiveBorder)
e.Graphics.DrawLine(p, r1.X, r1.Bottom, r1.Right, r1.Bottom)
End Using

Using format As StringFormat = New StringFormat()
Using br As SolidBrush = New SolidBrush(Me.DataGridView1.ColumnHeadersDefaultCellStyle.ForeColor)
format.Alignment = StringAlignment.Center
format.LineAlignment = StringAlignment.Center
e.Graphics.DrawString(feeTitles(j / 2), Me.DataGridView1.ColumnHeadersDefaultCellStyle.Font, Brushes.Brown, r1, format)
End Using
End Using
Next
End Sub

C# - DoubleClickButton

I needed a double-click event for the button, which is never possible.
In order to perform double-click event on the button, you should create a new control should be created as:


namespace DoubleClickButton
{
    // Derive a button with extended functionality
    // from the Button class.
    public class DoubleClickButton: System.Windows.Forms.Button
    {
        // Note that the DoubleClickTime property gets 
        // the maximum number of milliseconds allowed between 
        // mouse clicks for a double-click to be valid.
        int previousClick = SystemInformation.DoubleClickTime;

        public new event EventHandler DoubleClick;

        protected override void OnClick(EventArgs e)
        {
            int now = System.Environment.TickCount;

            // A double-click is detected if the the time elapsed
            // since the last click is within DoubleClickTime.
            if (now - previousClick <= SystemInformation.DoubleClickTime)
            {
                // Raise the DoubleClick event.
                if (DoubleClick != null)
                    DoubleClick(this, EventArgs.Empty);
            }

            // Set previousClick to now so that 
            // subsequent double-clicks can be detected.
            previousClick = now;

            // Allow the base class to raise the regular Click event.
            base.OnClick(e);
        }

        // Event handling code for the DoubleClick event.
        protected new virtual void OnDoubleClick(EventArgs e)
        {
            if (this.DoubleClick != null)
                this.DoubleClick(this, e);
        }
    }
}

Reference: MSDN

SQL Server - alter column - adding default constraint

The following sql syntax is used for adding a default value constraint to a column in the table already created.

alter table <TableName>

  add constraint <df_ConstraintName>

  default <defaultValue> for <columnName>

Example:

alter table employee

  add constraint df_empSalary

  default 0 for empSalary

SQL update from one Table to another based on an ID

The simple Way to copy the content from one table to other is as follow:

UPDATE Table2
SET t2.FieldName1 = t1.FieldName, t2.FieldName2 = t1.FieldName2
FROM Table1 t1, Table2 t2
WHERE t1.FieldName0 = t2.FieldName0


Example:

UPDATE t1 SET t1.InvoiceNumber = t2.sn
FROM [Transaction] t1,
     (SELECT row_number() over (order by dttmCreated asc) as sn, id FROM [Transaction] 
       WHERE Convert(date,dttmCreated) >= Convert(date,'2018-05-17')) t2
WHERE t1.id = t2.id


Original article here...

SQL Server : How to find the table size?

sp_spaceused 'tablename'

VB.NET: How to generate Row Numbers in a column in DataGridView?

While filtering or loading data into the DataGridView, we don't have either sno or any row number from the query or the datasource and we want to view the row number to locate the data or to represent the particular row or to view in a regular format.


To get rid of the above problem I did some google and then get to know the event on which the increment can be done for DataGridView and then solved as below


The following code generates a serial number in a separate column in DataGridView:


Private Sub DataGridView1_RowPrePaint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowPrePaintEventArgs) Handles DataGridView1.RowPrePaint
        If e.RowIndex >= 0 Then
            Me.DataGridView1.Rows(e.RowIndex).Cells(0).Value = e.RowIndex + 1
        End If
    End Sub

VB.NET: How to get index of a column of a DataGridView?

Private Function GetColumnIndex(ByVal headerText)
        Dim columnIndex As Integer = 0


        For index As Integer = 0 To Me.DataGridView1.Columns.Count - 1


            If LCase(Me.DataGridView1.Columns(index).HeaderText) = LCase(headerText) Then
                 columnIndex  = index
                Exit For
            End If


        Next


        Return columnIndex 
    End Function

SQL Server: List all the databases

Lists databases that either reside in an instance of the SQL Server 2005 Database Engine or are accessible through a database gateway.



EXEC sp_databases

or,

SELECT name

FROM sys.databases

Resetting the id of a table having an identity column

I need to truncate the table having foreign keys. 
As this is not allowed, I deleted all the rows from the table and re-indexed the identity column of the table as this:


dbcc checkident('employee',reseed, 0)


where, employee is the name of table,
            reseed specifies that the current identity value should be changed,
and,    0 is the new seed value to use in reseeding the identity column


I did this in MS SQL Server 2005

Visual Studio.NET IDE: Clear Recent Projects List

Simply navigate to the following location in registry editor.

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList


Now, delete the entries

SQL Server : Truncate Database

I needed to clear or delete data in all tables of a database. I googled it and found out this:


CREATE PROCEDURE sp_EmplyAllTable
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Attaching SQL Server database using C#

private void AttachDatabase(string databaseName, string fileName, string loginName, string password)
{
     SqlConnection conn = new SqlConnection();
     SqlCommand cmd = new SqlCommand("", conn);


     conn.ConnectionString = "Data Source=" + serverName + ";Initial Catalog=Master;User Id = " + loginName + ";Password = " + password + "";


     cmd.CommandText = "exec sp_attach_single_file_db @dbname='" + databaseName + "',@physname='" + fileName + "'";


     try
     {
          conn.Open();
          cmd.ExecuteNonQuery();


          MessageBox.Show("Database Attached Successfully", "Attach Database");
     }
     catch (Exception)
     {
          //throw ex;
          MessageBox.Show("Could Not Attach Database \nLogin Failed for user " + loginName, "Attach Database");
     }
     finally
     {
          cmd.Dispose();
          conn.Dispose();
     }
}

C#: List all SQL Server Instances on a local network

I needed the list of SQL Server Instances to setup the database for my application to the particular server.


I did this with the help of google and stackoverflow. The code is as following:



private void ListSqlServers()
{
     string myServer = Environment.MachineName;
     DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();


     for (int i = 0; i < servers.Rows.Count; i++)
     {
          // Remove the following 'if' condition 
          // to list the servers of local machine
          // along with network servers


          // used to get the servers in the local machine
          if (myServer == servers.Rows[i]["ServerName"].ToString()) 
          {
               if ((servers.Rows[i]["InstanceName"] as string) != null)
                    comboBox1.Items.Add(servers.Rows[i]["ServerName"] + "\\" + servers.Rows[i]["InstanceName"]);
               else
                    comboBox1.Items.Add(servers.Rows[i]["ServerName"]);
          }
     }
}