Category Archives: VB

Using Oracle SQL LIKE operator in parameterized OracleCommand

The SQL LIKE operator can be used in the CommandText with Oracle parameters. The syntax of comparing a OracleParameter using SQL LIKE operator is as follows:

Dim cmd As New OracleCommand
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE :param || '%'"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param || '%'"

Reference:Usage of Oracle binding variables with LIKE in C#

Leave a comment

Posted by on June 29, 2012 in .NET, ADO.NET, Oracle, SQL, VB


Highlight specific text in RichTextBox

TextBox is not capable of displaying text with multiple colors. We need to use RichTextBox in order to highlight specific text. The demo code here uses .NET Regular Expression to find the text and highlight them with red color.

Include this reference to use Regex class.

Imports System.Text.RegularExpressions

Highlight 8-digit numbers in the RichTextBox.

Dim regex As New Regex("[0-9]{8}")

For Each matchedText As Match In regex.Matches(Me.RichTextBoxApprovedVoucher.Text)
    Me.RichTextBox1.Select(matchedText.Index, matchedText.Length)
    Me.RichTextBox1.SelectionColor = Color.Red
Leave a comment

Posted by on June 18, 2012 in .NET, VB, Winform


Reset AutoIncrement column in DataTable

I have a auto-numbering column “AUTONUMBER” in a DataTable which is only used for showing the query result count, it is not related to any column in the database table. The AutoIncrementSeed and AutoIncrementStep properties of the column were set to 1. Before each query the DataTable.Clear() is called. I expected after every query the column would have started at 1 but instead it was incremented from the last number. The solution is simply add 4 lines after DataTable.Clear(). I don’t know exactly what happens under these code though…

'Add these 4 lines will reset the AutoIncrement column
dataTable.Columns("AUTONUMBER").AutoIncrementStep = -1
dataTable.Columns("AUTONUMBER").AutoIncrementSeed = -1
dataTable.Columns("AUTONUMBER").AutoIncrementStep = 1
dataTable.Columns("AUTONUMBER").AutoIncrementSeed = 1

Reference:Reset AutoIncrement in DataTable

1 Comment

Posted by on June 8, 2012 in .NET, ADO.NET, VB


GroupBox Validating event isn’t firing on ValidateChildren call

The Validating event code for GroupBoxes below checks if at lease 1 CheckBoxes checked in the group. It works with Form.AutoValidate enabled. But when Form.ValidateChildren() is called, this validating event isn’t firing.

' Validate if at least 1 of the CheckBoxes in the GroupBox is checked
Private Sub GroupBox_Validating(ByVal sender As System.Object, _
                                ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles GroupBoxStatus.Validating, GroupBoxClass.Validating

    Dim isValid As Boolean = False
    For Each ctrl As Control In DirectCast(sender, Control).Controls
        If TypeOf ctrl Is CheckBox Then
            If DirectCast(ctrl, CheckBox).Checked Then
                isValid = True
                Exit For
            End If
        End If

    If isValid = False Then
        Me.ErrorProvider1.SetError(DirectCast(sender, Control), "Check at least one item")
        e.Cancel = True
        Me.ErrorProvider1.SetError(DirectCast(sender, Control), "")
        e.Cancel = False
    End If

End Sub

Read the rest of this entry »

Leave a comment

Posted by on June 7, 2012 in .NET, VB, Winform


A workaround to use SQL IN clause in TableAdapters

This article is a simple implementation on how to use parameterized SQL query with IN clause or unknown number of parameters in TableAdapter.

When modifying those ancient database access codes, SQL query strings appear in the form of concatenated string very often. These codes are hard to read, maintain and prone to SQL Injection. Using DataSet Designer can easily improve it by accessing database with parameterized commands. But DataSet Designer does not support automatically generating commands with parameterized IN clause.

The idea of this article is originated from Extending TableAdapters for Dynamic SQL on The Code Project. The article demonstrate a way to change the command text programmatically by extending the TableAdapter which is defined as a partial class from the DataSet Designer. What’s not covered is how to parameterized the command.
Read the rest of this entry »

Leave a comment

Posted by on May 22, 2012 in .NET, ADO.NET, Oracle, SQL, VB


Tags: , , , , , ,

Not to select the first row in DataGridView by default

DataGridView makes the first row selected after DataBinding by default. I tried using DataGridView.ClearSelection() in the Form.Load event to unselect it with no avail. The problem is that data hasn’t been bond to the DataGridView in the Form.Load event. The method should be placed in the DataBindingComplete event of the DataGridView:

Private Sub DataGridView1_DataBindingComplete(ByVal sender As System.Object, _
    ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) _
    Handles DataGridView1.DataBindingComplete

    Dim gridView As DataGridView
    gridView = CType(sender, DataGridView)
End Sub

Reference: C# DataGridView: First row is being selected by default


Posted by on May 14, 2012 in .NET, VB, Winform



Insert delimiters between each character of a string

For example, to make the string “abcde” becomes “a,b,c,d,e” or “a b c d e”. What I’m going to do is to convert the string to a char array and then use String.Join to join the chars with the specific delimiters. There are different ways to accomplish it base on different versions of .NET Framework.

.NET 2.0
First using String.ToCharArray to convert the original string into a char array. Since String.Join takes a string array as the 2nd parameter, the char array needs to be converted into a string array. In .NET 2.0 we could use Array.ConverAll:

Read the rest of this entry »

Leave a comment

Posted by on May 9, 2012 in .NET, Lambda, LINQ, VB