Linq to dataset question C# vb.net LINQ ASP.Net

I have a northwind sample i created and I am having trouble with My Linq query. I am using linq to dataset.

I am going to attach some sample code which will work with the northwind database on a sql server system.

I would award points to anyone who could tell me why i am having a problem with my search only when i choose Region, Fax, or PostalCode from the dropdown combo.  

Below is 2 forms w/ designers, 1 class library, and an app.config file.

you will need the northwind database on sql server, you can get it here -->

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en



Thanks in advance
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class frmCustomers
    Inherits System.Windows.Forms.Form
 
    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.ContextMenuStrip1 = New System.Windows.Forms.ContextMenuStrip(Me.components)
        Me.GetOrdersToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem
        Me.AddToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem
        Me.EditToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem
        Me.DeleteToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem
        Me.Panel1 = New System.Windows.Forms.Panel
        Me.gpxLayout = New System.Windows.Forms.GroupBox
        Me.lblCustID = New System.Windows.Forms.Label
        Me.txtCustomerID = New System.Windows.Forms.TextBox
        Me.txtCountry = New System.Windows.Forms.TextBox
        Me.lblCountry = New System.Windows.Forms.Label
        Me.txtCompany = New System.Windows.Forms.TextBox
        Me.txtPostalCode = New System.Windows.Forms.TextBox
        Me.txtCity = New System.Windows.Forms.TextBox
        Me.txtRegion = New System.Windows.Forms.TextBox
        Me.txtAddress = New System.Windows.Forms.TextBox
        Me.txtContactName = New System.Windows.Forms.TextBox
        Me.txtContactTitle = New System.Windows.Forms.TextBox
        Me.txtFax = New System.Windows.Forms.TextBox
        Me.txtPhone = New System.Windows.Forms.TextBox
        Me.lblFax = New System.Windows.Forms.Label
        Me.lblPhone = New System.Windows.Forms.Label
        Me.lblPostalCode = New System.Windows.Forms.Label
        Me.lblAddress = New System.Windows.Forms.Label
        Me.lblCity = New System.Windows.Forms.Label
        Me.lblRegion = New System.Windows.Forms.Label
        Me.lblContactName = New System.Windows.Forms.Label
        Me.lblContactTitle = New System.Windows.Forms.Label
        Me.lblCompany = New System.Windows.Forms.Label
        Me.pnlLocked = New System.Windows.Forms.Panel
        Me.btnEdit = New System.Windows.Forms.Button
        Me.btnAdd = New System.Windows.Forms.Button
        Me.pnlEdit = New System.Windows.Forms.Panel
        Me.btnCancel = New System.Windows.Forms.Button
        Me.btnSave = New System.Windows.Forms.Button
        Me.Panel2 = New System.Windows.Forms.Panel
        Me.GroupBox1 = New System.Windows.Forms.GroupBox
        Me.btnClear = New System.Windows.Forms.Button
        Me.pnlNavigate = New System.Windows.Forms.Panel
        Me.btnFirst = New System.Windows.Forms.Button
        Me.btnPrevious = New System.Windows.Forms.Button
        Me.btnLast = New System.Windows.Forms.Button
        Me.btnNext = New System.Windows.Forms.Button
        Me.Label1 = New System.Windows.Forms.Label
        Me.cboSearchSelect = New System.Windows.Forms.ComboBox
        Me.txtSearch = New System.Windows.Forms.TextBox
        Me.dgvCustomers = New System.Windows.Forms.DataGridView
        Me.ContextMenuStrip1.SuspendLayout()
        Me.Panel1.SuspendLayout()
        Me.gpxLayout.SuspendLayout()
        Me.pnlLocked.SuspendLayout()
        Me.pnlEdit.SuspendLayout()
        Me.Panel2.SuspendLayout()
        Me.GroupBox1.SuspendLayout()
        Me.pnlNavigate.SuspendLayout()
        CType(Me.dgvCustomers, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'ContextMenuStrip1
        '
        Me.ContextMenuStrip1.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.GetOrdersToolStripMenuItem, Me.AddToolStripMenuItem, Me.EditToolStripMenuItem, Me.DeleteToolStripMenuItem})
        Me.ContextMenuStrip1.Name = "ContextMenuStrip1"
        Me.ContextMenuStrip1.Size = New System.Drawing.Size(131, 92)
        '
        'GetOrdersToolStripMenuItem
        '
        Me.GetOrdersToolStripMenuItem.Name = "GetOrdersToolStripMenuItem"
        Me.GetOrdersToolStripMenuItem.Size = New System.Drawing.Size(130, 22)
        Me.GetOrdersToolStripMenuItem.Text = "Get Orders"
        '
        'AddToolStripMenuItem
        '
        Me.AddToolStripMenuItem.Name = "AddToolStripMenuItem"
        Me.AddToolStripMenuItem.Size = New System.Drawing.Size(130, 22)
        Me.AddToolStripMenuItem.Text = "Add"
        '
        'EditToolStripMenuItem
        '
        Me.EditToolStripMenuItem.Name = "EditToolStripMenuItem"
        Me.EditToolStripMenuItem.Size = New System.Drawing.Size(130, 22)
        Me.EditToolStripMenuItem.Text = "Edit"
        '
        'DeleteToolStripMenuItem
        '
        Me.DeleteToolStripMenuItem.Name = "DeleteToolStripMenuItem"
        Me.DeleteToolStripMenuItem.Size = New System.Drawing.Size(130, 22)
        Me.DeleteToolStripMenuItem.Text = "Delete"
        '
        'Panel1
        '
        Me.Panel1.Controls.Add(Me.gpxLayout)
        Me.Panel1.Controls.Add(Me.pnlLocked)
        Me.Panel1.Controls.Add(Me.pnlEdit)
        Me.Panel1.Location = New System.Drawing.Point(12, 12)
        Me.Panel1.Name = "Panel1"
        Me.Panel1.Size = New System.Drawing.Size(310, 436)
        Me.Panel1.TabIndex = 33
        '
        'gpxLayout
        '
        Me.gpxLayout.Controls.Add(Me.lblCustID)
        Me.gpxLayout.Controls.Add(Me.txtCustomerID)
        Me.gpxLayout.Controls.Add(Me.txtCountry)
        Me.gpxLayout.Controls.Add(Me.lblCountry)
        Me.gpxLayout.Controls.Add(Me.txtCompany)
        Me.gpxLayout.Controls.Add(Me.txtPostalCode)
        Me.gpxLayout.Controls.Add(Me.txtCity)
        Me.gpxLayout.Controls.Add(Me.txtRegion)
        Me.gpxLayout.Controls.Add(Me.txtAddress)
        Me.gpxLayout.Controls.Add(Me.txtContactName)
        Me.gpxLayout.Controls.Add(Me.txtContactTitle)
        Me.gpxLayout.Controls.Add(Me.txtFax)
        Me.gpxLayout.Controls.Add(Me.txtPhone)
        Me.gpxLayout.Controls.Add(Me.lblFax)
        Me.gpxLayout.Controls.Add(Me.lblPhone)
        Me.gpxLayout.Controls.Add(Me.lblPostalCode)
        Me.gpxLayout.Controls.Add(Me.lblAddress)
        Me.gpxLayout.Controls.Add(Me.lblCity)
        Me.gpxLayout.Controls.Add(Me.lblRegion)
        Me.gpxLayout.Controls.Add(Me.lblContactName)
        Me.gpxLayout.Controls.Add(Me.lblContactTitle)
        Me.gpxLayout.Controls.Add(Me.lblCompany)
        Me.gpxLayout.Location = New System.Drawing.Point(3, 3)
        Me.gpxLayout.Name = "gpxLayout"
        Me.gpxLayout.Size = New System.Drawing.Size(273, 366)
        Me.gpxLayout.TabIndex = 5
        Me.gpxLayout.TabStop = False
        '
        'lblCustID
        '
        Me.lblCustID.AutoSize = True
        Me.lblCustID.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblCustID.Location = New System.Drawing.Point(6, 22)
        Me.lblCustID.Name = "lblCustID"
        Me.lblCustID.Size = New System.Drawing.Size(103, 20)
        Me.lblCustID.TabIndex = 22
        Me.lblCustID.Text = "Customer ID:"
        '
        'txtCustomerID
        '
        Me.txtCustomerID.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtCustomerID.Location = New System.Drawing.Point(114, 19)
        Me.txtCustomerID.Name = "txtCustomerID"
        Me.txtCustomerID.Size = New System.Drawing.Size(84, 26)
        Me.txtCustomerID.TabIndex = 0
        '
        'txtCountry
        '
        Me.txtCountry.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtCountry.Location = New System.Drawing.Point(114, 270)
        Me.txtCountry.Name = "txtCountry"
        Me.txtCountry.Size = New System.Drawing.Size(143, 26)
        Me.txtCountry.TabIndex = 8
        '
        'lblCountry
        '
        Me.lblCountry.AutoSize = True
        Me.lblCountry.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblCountry.Location = New System.Drawing.Point(6, 273)
        Me.lblCountry.Name = "lblCountry"
        Me.lblCountry.Size = New System.Drawing.Size(68, 20)
        Me.lblCountry.TabIndex = 19
        Me.lblCountry.Text = "Country:"
        '
        'txtCompany
        '
        Me.txtCompany.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtCompany.Location = New System.Drawing.Point(114, 51)
        Me.txtCompany.Name = "txtCompany"
        Me.txtCompany.Size = New System.Drawing.Size(143, 26)
        Me.txtCompany.TabIndex = 1
        '
        'txtPostalCode
        '
        Me.txtPostalCode.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtPostalCode.Location = New System.Drawing.Point(114, 206)
        Me.txtPostalCode.Name = "txtPostalCode"
        Me.txtPostalCode.Size = New System.Drawing.Size(143, 26)
        Me.txtPostalCode.TabIndex = 6
        '
        'txtCity
        '
        Me.txtCity.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtCity.Location = New System.Drawing.Point(114, 147)
        Me.txtCity.Name = "txtCity"
        Me.txtCity.Size = New System.Drawing.Size(143, 26)
        Me.txtCity.TabIndex = 4
        '
        'txtRegion
        '
        Me.txtRegion.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtRegion.Location = New System.Drawing.Point(114, 174)
        Me.txtRegion.Name = "txtRegion"
        Me.txtRegion.Size = New System.Drawing.Size(143, 26)
        Me.txtRegion.TabIndex = 5
        '
        'txtAddress
        '
        Me.txtAddress.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtAddress.Location = New System.Drawing.Point(114, 115)
        Me.txtAddress.Name = "txtAddress"
        Me.txtAddress.Size = New System.Drawing.Size(143, 26)
        Me.txtAddress.TabIndex = 3
        '
        'txtContactName
        '
        Me.txtContactName.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtContactName.Location = New System.Drawing.Point(114, 238)
        Me.txtContactName.Name = "txtContactName"
        Me.txtContactName.Size = New System.Drawing.Size(143, 26)
        Me.txtContactName.TabIndex = 7
        '
        'txtContactTitle
        '
        Me.txtContactTitle.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtContactTitle.Location = New System.Drawing.Point(114, 83)
        Me.txtContactTitle.Name = "txtContactTitle"
        Me.txtContactTitle.Size = New System.Drawing.Size(143, 26)
        Me.txtContactTitle.TabIndex = 2
        '
        'txtFax
        '
        Me.txtFax.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtFax.Location = New System.Drawing.Point(114, 334)
        Me.txtFax.Name = "txtFax"
        Me.txtFax.Size = New System.Drawing.Size(143, 26)
        Me.txtFax.TabIndex = 10
        '
        'txtPhone
        '
        Me.txtPhone.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtPhone.Location = New System.Drawing.Point(114, 302)
        Me.txtPhone.Name = "txtPhone"
        Me.txtPhone.Size = New System.Drawing.Size(143, 26)
        Me.txtPhone.TabIndex = 9
        '
        'lblFax
        '
        Me.lblFax.AutoSize = True
        Me.lblFax.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblFax.Location = New System.Drawing.Point(6, 337)
        Me.lblFax.Name = "lblFax"
        Me.lblFax.Size = New System.Drawing.Size(39, 20)
        Me.lblFax.TabIndex = 8
        Me.lblFax.Text = "Fax:"
        '
        'lblPhone
        '
        Me.lblPhone.AutoSize = True
        Me.lblPhone.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblPhone.Location = New System.Drawing.Point(6, 305)
        Me.lblPhone.Name = "lblPhone"
        Me.lblPhone.Size = New System.Drawing.Size(59, 20)
        Me.lblPhone.TabIndex = 7
        Me.lblPhone.Text = "Phone:"
        '
        'lblPostalCode
        '
        Me.lblPostalCode.AutoSize = True
        Me.lblPostalCode.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblPostalCode.Location = New System.Drawing.Point(6, 209)
        Me.lblPostalCode.Name = "lblPostalCode"
        Me.lblPostalCode.Size = New System.Drawing.Size(99, 20)
        Me.lblPostalCode.TabIndex = 6
        Me.lblPostalCode.Text = "Postal Code:"
        '
        'lblAddress
        '
        Me.lblAddress.AutoSize = True
        Me.lblAddress.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblAddress.Location = New System.Drawing.Point(6, 118)
        Me.lblAddress.Name = "lblAddress"
        Me.lblAddress.Size = New System.Drawing.Size(72, 20)
        Me.lblAddress.TabIndex = 5
        Me.lblAddress.Text = "Address:"
        '
        'lblCity
        '
        Me.lblCity.AutoSize = True
        Me.lblCity.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblCity.Location = New System.Drawing.Point(6, 150)
        Me.lblCity.Name = "lblCity"
        Me.lblCity.Size = New System.Drawing.Size(39, 20)
        Me.lblCity.TabIndex = 4
        Me.lblCity.Text = "City:"
        '
        'lblRegion
        '
        Me.lblRegion.AutoSize = True
        Me.lblRegion.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblRegion.Location = New System.Drawing.Point(6, 177)
        Me.lblRegion.Name = "lblRegion"
        Me.lblRegion.Size = New System.Drawing.Size(64, 20)
        Me.lblRegion.TabIndex = 3
        Me.lblRegion.Text = "Region:"
        '
        'lblContactName
        '
        Me.lblContactName.AutoSize = True
        Me.lblContactName.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblContactName.Location = New System.Drawing.Point(6, 241)
        Me.lblContactName.Name = "lblContactName"
        Me.lblContactName.Size = New System.Drawing.Size(69, 20)
        Me.lblContactName.TabIndex = 2
        Me.lblContactName.Text = "Contact:"
        '
        'lblContactTitle
        '
        Me.lblContactTitle.AutoSize = True
        Me.lblContactTitle.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblContactTitle.Location = New System.Drawing.Point(6, 86)
        Me.lblContactTitle.Name = "lblContactTitle"
        Me.lblContactTitle.Size = New System.Drawing.Size(42, 20)
        Me.lblContactTitle.TabIndex = 1
        Me.lblContactTitle.Text = "Title:"
        '
        'lblCompany
        '
        Me.lblCompany.AutoSize = True
        Me.lblCompany.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblCompany.Location = New System.Drawing.Point(6, 54)
        Me.lblCompany.Name = "lblCompany"
        Me.lblCompany.Size = New System.Drawing.Size(80, 20)
        Me.lblCompany.TabIndex = 0
        Me.lblCompany.Text = "Company:"
        '
        'pnlLocked
        '
        Me.pnlLocked.Controls.Add(Me.btnEdit)
        Me.pnlLocked.Controls.Add(Me.btnAdd)
        Me.pnlLocked.Location = New System.Drawing.Point(3, 378)
        Me.pnlLocked.Name = "pnlLocked"
        Me.pnlLocked.Size = New System.Drawing.Size(273, 58)
        Me.pnlLocked.TabIndex = 38
        '
        'btnEdit
        '
        Me.btnEdit.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnEdit.Location = New System.Drawing.Point(139, 18)
        Me.btnEdit.Name = "btnEdit"
        Me.btnEdit.Size = New System.Drawing.Size(106, 32)
        Me.btnEdit.TabIndex = 30
        Me.btnEdit.Text = "Edit"
        Me.btnEdit.UseVisualStyleBackColor = True
        '
        'btnAdd
        '
        Me.btnAdd.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnAdd.Location = New System.Drawing.Point(27, 18)
        Me.btnAdd.Name = "btnAdd"
        Me.btnAdd.Size = New System.Drawing.Size(106, 32)
        Me.btnAdd.TabIndex = 26
        Me.btnAdd.Text = "Add"
        Me.btnAdd.UseVisualStyleBackColor = True
        '
        'pnlEdit
        '
        Me.pnlEdit.Controls.Add(Me.btnCancel)
        Me.pnlEdit.Controls.Add(Me.btnSave)
        Me.pnlEdit.Location = New System.Drawing.Point(3, 378)
        Me.pnlEdit.Name = "pnlEdit"
        Me.pnlEdit.Size = New System.Drawing.Size(273, 58)
        Me.pnlEdit.TabIndex = 39
        Me.pnlEdit.Visible = False
        '
        'btnCancel
        '
        Me.btnCancel.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnCancel.Location = New System.Drawing.Point(139, 17)
        Me.btnCancel.Name = "btnCancel"
        Me.btnCancel.Size = New System.Drawing.Size(85, 32)
        Me.btnCancel.TabIndex = 25
        Me.btnCancel.Text = "Cancel"
        Me.btnCancel.UseVisualStyleBackColor = True
        '
        'btnSave
        '
        Me.btnSave.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnSave.Location = New System.Drawing.Point(48, 17)
        Me.btnSave.Name = "btnSave"
        Me.btnSave.Size = New System.Drawing.Size(85, 32)
        Me.btnSave.TabIndex = 24
        Me.btnSave.Text = "Save"
        Me.btnSave.UseVisualStyleBackColor = True
        '
        'Panel2
        '
        Me.Panel2.Controls.Add(Me.GroupBox1)
        Me.Panel2.Location = New System.Drawing.Point(328, 12)
        Me.Panel2.Name = "Panel2"
        Me.Panel2.Size = New System.Drawing.Size(532, 436)
        Me.Panel2.TabIndex = 34
        '
        'GroupBox1
        '
        Me.GroupBox1.Controls.Add(Me.btnClear)
        Me.GroupBox1.Controls.Add(Me.pnlNavigate)
        Me.GroupBox1.Controls.Add(Me.Label1)
        Me.GroupBox1.Controls.Add(Me.cboSearchSelect)
        Me.GroupBox1.Controls.Add(Me.txtSearch)
        Me.GroupBox1.Controls.Add(Me.dgvCustomers)
        Me.GroupBox1.Location = New System.Drawing.Point(9, 3)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(523, 428)
        Me.GroupBox1.TabIndex = 5
        Me.GroupBox1.TabStop = False
        '
        'btnClear
        '
        Me.btnClear.Location = New System.Drawing.Point(240, 19)
        Me.btnClear.Name = "btnClear"
        Me.btnClear.Size = New System.Drawing.Size(75, 29)
        Me.btnClear.TabIndex = 34
        Me.btnClear.Text = "Clear"
        Me.btnClear.UseVisualStyleBackColor = True
        '
        'pnlNavigate
        '
        Me.pnlNavigate.Controls.Add(Me.btnFirst)
        Me.pnlNavigate.Controls.Add(Me.btnPrevious)
        Me.pnlNavigate.Controls.Add(Me.btnLast)
        Me.pnlNavigate.Controls.Add(Me.btnNext)
        Me.pnlNavigate.Location = New System.Drawing.Point(6, 375)
        Me.pnlNavigate.Name = "pnlNavigate"
        Me.pnlNavigate.Size = New System.Drawing.Size(508, 58)
        Me.pnlNavigate.TabIndex = 33
        '
        'btnFirst
        '
        Me.btnFirst.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnFirst.Location = New System.Drawing.Point(25, 18)
        Me.btnFirst.Name = "btnFirst"
        Me.btnFirst.Size = New System.Drawing.Size(93, 32)
        Me.btnFirst.TabIndex = 30
        Me.btnFirst.Text = "<<"
        Me.btnFirst.UseVisualStyleBackColor = True
        '
        'btnPrevious
        '
        Me.btnPrevious.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnPrevious.Location = New System.Drawing.Point(124, 18)
        Me.btnPrevious.Name = "btnPrevious"
        Me.btnPrevious.Size = New System.Drawing.Size(127, 32)
        Me.btnPrevious.TabIndex = 32
        Me.btnPrevious.Text = "<"
        Me.btnPrevious.UseVisualStyleBackColor = True
        '
        'btnLast
        '
        Me.btnLast.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnLast.Location = New System.Drawing.Point(390, 19)
        Me.btnLast.Name = "btnLast"
        Me.btnLast.Size = New System.Drawing.Size(93, 32)
        Me.btnLast.TabIndex = 31
        Me.btnLast.Text = ">>"
        Me.btnLast.UseVisualStyleBackColor = True
        '
        'btnNext
        '
        Me.btnNext.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnNext.Location = New System.Drawing.Point(257, 19)
        Me.btnNext.Name = "btnNext"
        Me.btnNext.Size = New System.Drawing.Size(127, 32)
        Me.btnNext.TabIndex = 29
        Me.btnNext.Text = ">"
        Me.btnNext.UseVisualStyleBackColor = True
        '
        'Label1
        '
        Me.Label1.AutoSize = True
        Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(7, 22)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(64, 20)
        Me.Label1.TabIndex = 23
        Me.Label1.Text = "Search:"
        '
        'cboSearchSelect
        '
        Me.cboSearchSelect.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
        Me.cboSearchSelect.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cboSearchSelect.FormattingEnabled = True
        Me.cboSearchSelect.Location = New System.Drawing.Point(367, 19)
        Me.cboSearchSelect.Name = "cboSearchSelect"
        Me.cboSearchSelect.Size = New System.Drawing.Size(147, 28)
        Me.cboSearchSelect.TabIndex = 32
        '
        'txtSearch
        '
        Me.txtSearch.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
 
 
        Me.txtSearch.Location = New System.Drawing.Point(77, 19)
        Me.txtSearch.Name = "txtSearch"
        Me.txtSearch.Size = New System.Drawing.Size(157, 26)
        Me.txtSearch.TabIndex = 5
        '
        'dgvCustomers
        '
        Me.dgvCustomers.AllowUserToAddRows = False
        Me.dgvCustomers.AllowUserToDeleteRows = False
        Me.dgvCustomers.AllowUserToOrderColumns = True
        Me.dgvCustomers.AllowUserToResizeColumns = False
        Me.dgvCustomers.AllowUserToResizeRows = False
        Me.dgvCustomers.CellBorderStyle = System.Windows.Forms.DataGridViewCellBorderStyle.RaisedVertical
        Me.dgvCustomers.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.DisableResizing
        Me.dgvCustomers.ContextMenuStrip = Me.ContextMenuStrip1
        Me.dgvCustomers.EditMode = System.Windows.Forms.DataGridViewEditMode.EditProgrammatically
        Me.dgvCustomers.Location = New System.Drawing.Point(6, 54)
        Me.dgvCustomers.MultiSelect = False
        Me.dgvCustomers.Name = "dgvCustomers"
        Me.dgvCustomers.RowHeadersWidthSizeMode = System.Windows.Forms.DataGridViewRowHeadersWidthSizeMode.DisableResizing
        Me.dgvCustomers.RowTemplate.Resizable = System.Windows.Forms.DataGridViewTriState.[False]
        Me.dgvCustomers.ScrollBars = System.Windows.Forms.ScrollBars.Vertical
        Me.dgvCustomers.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
        Me.dgvCustomers.Size = New System.Drawing.Size(508, 312)
        Me.dgvCustomers.TabIndex = 4
        '
        'frmCustomers
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(863, 451)
        Me.Controls.Add(Me.Panel1)
        Me.Controls.Add(Me.Panel2)
        Me.MaximizeBox = False
        Me.Name = "frmCustomers"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "Customers"
        Me.ContextMenuStrip1.ResumeLayout(False)
        Me.Panel1.ResumeLayout(False)
        Me.gpxLayout.ResumeLayout(False)
        Me.gpxLayout.PerformLayout()
        Me.pnlLocked.ResumeLayout(False)
        Me.pnlEdit.ResumeLayout(False)
        Me.Panel2.ResumeLayout(False)
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox1.PerformLayout()
        Me.pnlNavigate.ResumeLayout(False)
        CType(Me.dgvCustomers, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
 
    End Sub
    Friend WithEvents ContextMenuStrip1 As System.Windows.Forms.ContextMenuStrip
    Friend WithEvents EditToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
    Friend WithEvents AddToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
    Friend WithEvents GetOrdersToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
    Friend WithEvents Panel1 As System.Windows.Forms.Panel
    Friend WithEvents gpxLayout As System.Windows.Forms.GroupBox
    Friend WithEvents lblCustID As System.Windows.Forms.Label
    Friend WithEvents txtCustomerID As System.Windows.Forms.TextBox
    Friend WithEvents txtCountry As System.Windows.Forms.TextBox
    Friend WithEvents lblCountry As System.Windows.Forms.Label
    Friend WithEvents txtCompany As System.Windows.Forms.TextBox
    Friend WithEvents txtPostalCode As System.Windows.Forms.TextBox
    Friend WithEvents txtCity As System.Windows.Forms.TextBox
    Friend WithEvents txtRegion As System.Windows.Forms.TextBox
    Friend WithEvents txtAddress As System.Windows.Forms.TextBox
    Friend WithEvents txtContactName As System.Windows.Forms.TextBox
    Friend WithEvents txtContactTitle As System.Windows.Forms.TextBox
    Friend WithEvents txtFax As System.Windows.Forms.TextBox
    Friend WithEvents txtPhone As System.Windows.Forms.TextBox
    Friend WithEvents lblFax As System.Windows.Forms.Label
    Friend WithEvents lblPhone As System.Windows.Forms.Label
    Friend WithEvents lblPostalCode As System.Windows.Forms.Label
    Friend WithEvents lblAddress As System.Windows.Forms.Label
    Friend WithEvents lblCity As System.Windows.Forms.Label
    Friend WithEvents lblRegion As System.Windows.Forms.Label
    Friend WithEvents lblContactName As System.Windows.Forms.Label
    Friend WithEvents lblContactTitle As System.Windows.Forms.Label
    Friend WithEvents lblCompany As System.Windows.Forms.Label
    Friend WithEvents Panel2 As System.Windows.Forms.Panel
    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents dgvCustomers As System.Windows.Forms.DataGridView
    Friend WithEvents pnlNavigate As System.Windows.Forms.Panel
    Friend WithEvents btnFirst As System.Windows.Forms.Button
    Friend WithEvents btnPrevious As System.Windows.Forms.Button
    Friend WithEvents btnLast As System.Windows.Forms.Button
    Friend WithEvents btnNext As System.Windows.Forms.Button
    Friend WithEvents txtSearch As System.Windows.Forms.TextBox
    Friend WithEvents cboSearchSelect As System.Windows.Forms.ComboBox
    Friend WithEvents DeleteToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents pnlEdit As System.Windows.Forms.Panel
    Friend WithEvents btnCancel As System.Windows.Forms.Button
    Friend WithEvents btnSave As System.Windows.Forms.Button
    Friend WithEvents pnlLocked As System.Windows.Forms.Panel
    Friend WithEvents btnEdit As System.Windows.Forms.Button
    Friend WithEvents btnAdd As System.Windows.Forms.Button
    Friend WithEvents btnClear As System.Windows.Forms.Button
 
End Class
 
 
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class frmCustomers
    Inherits System.Windows.Forms.Form
#Region "Private Members"
    Private WithEvents cmCustomers As CurrencyManager
    Private dsNorthwind As New DataSet("Northwind")
    Dim tblCustomers As DataTable
 
    Private Enum AppStatus
        Lock
        Edit
        Add
    End Enum
 
#End Region
#Region "Initilization and Destruction"
    Public Sub New()
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
    
        AddHandler btnAdd.Click, AddressOf btnAdd_Click
        AddHandler btnCancel.Click, AddressOf btnCancel_Click
        AddHandler btnEdit.Click, AddressOf btnEdit_Click
        AddHandler btnFirst.Click, AddressOf btnFirst_Click
        AddHandler btnLast.Click, AddressOf btnLast_Click
        AddHandler btnNext.Click, AddressOf btnNext_Click
        AddHandler btnPrevious.Click, AddressOf btnPrevious_Click
        AddHandler btnSave.Click, AddressOf btnSave_Click
        AddHandler btnClear.Click, AddressOf btnClear_Click
        AddHandler dgvCustomers.MouseDown, AddressOf dgvCustomers_MouseDown
        AddHandler EditToolStripMenuItem.Click, AddressOf btnEdit_Click
        AddHandler DeleteToolStripMenuItem.Click, AddressOf DeleteToolStripMenuItem_Click
        AddHandler AddToolStripMenuItem.Click, AddressOf btnAdd_Click
        AddHandler GetOrdersToolStripMenuItem.Click, AddressOf GetOrders
        AddHandler cboSearchSelect.DropDownClosed , AddressOf cboSearchSelect_SelectedIndexChanged
        AddHandler cboSearchSelect.SelectedIndexChanged, AddressOf doSearch
        AddHandler txtSearch.TextChanged, AddressOf doSearch
 
 
        'Initilize Datagrid
        Dim dgColumn As DataGridViewColumn
 
        dgColumn = New DataGridViewColumn(New DataGridViewTextBoxCell)
        With dgColumn
            .Width = 68
            .Name = "CustomerID"
            .HeaderText = "ID"
            .DataPropertyName = "CustomerID"
        End With
        Me.dgvCustomers.Columns.Add(dgColumn)
        Me.dgvCustomers.Columns(0).SortMode = DataGridViewColumnSortMode.Automatic
 
        dgColumn = New DataGridViewColumn(New DataGridViewTextBoxCell)
        With dgColumn
            .Width = 180
            .Name = "CompanyName"
            .HeaderText = "Company Name"
            .DataPropertyName = "CompanyName"
        End With
        Me.dgvCustomers.Columns.Add(dgColumn)
        Me.dgvCustomers.Columns(1).SortMode = DataGridViewColumnSortMode.Automatic
 
        dgColumn = New DataGridViewColumn(New DataGridViewTextBoxCell)
        With dgColumn
            .Width = 120
            .Name = "ContactName"
            .HeaderText = "Contact Name"
            .DataPropertyName = "ContactName"
        End With
        Me.dgvCustomers.Columns.Add(dgColumn)
        Me.dgvCustomers.Columns(2).SortMode = DataGridViewColumnSortMode.Automatic
 
        dgColumn = New DataGridViewColumn(New DataGridViewTextBoxCell)
        With dgColumn
            .Width = 120
            .Name = "ContactTitle"
            .HeaderText = "Contact Title"
            .DataPropertyName = "ContactTitle"
        End With
        Me.dgvCustomers.Columns.Add(dgColumn)
        Me.dgvCustomers.Columns(3).SortMode = DataGridViewColumnSortMode.Automatic
 
 
        With Me.dgvCustomers
            .RowsDefaultCellStyle.BackColor = Color.Bisque
            .AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        End With
 
        With Me.dgvCustomers
            .RowTemplate.Height = 26
            .ColumnHeadersHeight = 26
            .ScrollBars = ScrollBars.Both
            .AutoGenerateColumns = False
            .RowHeadersVisible = False
            .AllowUserToResizeColumns = False
            .AllowUserToResizeRows = False
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .ReadOnly = True
            .Font = New Font(System.Drawing.FontFamily.GenericSansSerif, 11)
            .DataSource = New BindingSource(tblCustomers, "")
 
        End With
 
        Me.Invalidate()
        Me.Refresh()
        Application.DoEvents()
    End Sub
    Private Sub frmCustomers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim dbUtils As New dbLibrary.dbNorthwind
        dbUtils.GetCustomers(dsNorthwind)
        dbUtils = Nothing
 
        ' Set tblCustomers equal to a copy of the data table
        ' This will allow us to filter the table with Linq 
        ' without modifying the original dataset pulled from 
        ' the datasource
        tblCustomers = Me.dsNorthwind.Tables("Customers").Copy
 
        'Populate the combobox with all of the columns in the datatable
        For Each col As DataColumn In tblCustomers.Columns
            Me.cboSearchSelect.Items.Add(col.ColumnName.ToString)
        Next
        Me.cboSearchSelect.SelectedIndex = 1 ' Set selected index to company name
 
        BindData()
    End Sub
 
    Private Sub frmCustomers_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
        SetAppStatus(AppStatus.Lock)
    End Sub
#End Region
#Region "Event handlers"
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Me.txtCustomerID.Text.Trim <> "" Then
            Me.cmCustomers.EndCurrentEdit()
            Me.SetAppStatus(AppStatus.Lock)
            Me.dgvCustomers.Enabled = True
            Dim dbUtils As New dbLibrary.dbNorthwind
            Try
                dbUtils.UpdateCustomers(tblCustomers)
                tblCustomers.AcceptChanges()
                dsNorthwind = New DataSet
                dbUtils.GetCustomers(dsNorthwind)
            Catch ex As SqlException
                MsgBox(ex.Message)
                tblCustomers.RejectChanges()
                Me.cmCustomers.Position = 0
            Finally
 
                dbUtils = Nothing
            End Try
        Else
            MsgBox("Please enter a customer id")
            Me.txtCustomerID.Focus()
        End If
 
    End Sub
 
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Not IsNothing(cmCustomers) Then
            Me.cmCustomers.AddNew()
            Me.dgvCustomers.Enabled = False
            Me.SetAppStatus(AppStatus.Add)
            Me.txtCustomerID.Focus()
        End If
    End Sub
 
    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Not IsNothing(cmCustomers) Then
            Me.SetAppStatus(AppStatus.Edit)
        End If
    End Sub
 
 
    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.txtSearch.Clear()
        Me.txtSearch.Focus()
        Me.txtSearch.SelectAll()
        Me.cboSearchSelect.SelectedIndex = 1
    End Sub
 
    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.SetAppStatus(AppStatus.Lock)
        Me.cmCustomers.CancelCurrentEdit()
        Me.cmCustomers.Position = 0
        Me.dgvCustomers.Enabled = True
    End Sub
 
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Not IsNothing(cmCustomers) Then
            Me.cmCustomers.Position = cmCustomers.Count
        End If
        Me.txtSearch.Focus()
        Me.txtSearch.SelectAll()
    End Sub
 
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Not IsNothing(cmCustomers) Then
            If Me.cmCustomers.Position <> cmCustomers.Count Then
                Me.cmCustomers.Position += 1
            End If
        End If
        Me.txtSearch.Focus()
        Me.txtSearch.SelectAll()
    End Sub
 
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Not IsNothing(cmCustomers) Then
            If Me.cmCustomers.Position <> 0 Then
                Me.cmCustomers.Position -= 1
            End If
        End If
        Me.txtSearch.Focus()
        Me.txtSearch.SelectAll()
    End Sub
 
    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Not IsNothing(cmCustomers) Then
            Me.cmCustomers.Position = 0
        End If
        Me.txtSearch.Focus()
        Me.txtSearch.SelectAll()
    End Sub
 
    Private Sub DeleteToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim respone As MsgBoxResult
        respone = MsgBox("Are you sure you would like to delete this record?", MsgBoxStyle.YesNo, "Deleting record")
        If respone = MsgBoxResult.Yes Then
            Dim idx As Integer = Me.cmCustomers.Position
            Me.cmCustomers.Position = 0
 
            Me.tblCustomers.Rows(idx).Delete()
 
            Dim dbUtils As New dbLibrary.dbNorthwind
            Try
                dbUtils.UpdateCustomers(tblCustomers)
            Catch ex As Exception
                MsgBox(ex.Message)
                Me.tblCustomers.RejectChanges()
            Finally
                dbUtils = Nothing
            End Try
 
        End If
    End Sub
 
 
    Private Sub doSearch(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim strSearchField As String = Me.cboSearchSelect.SelectedItem.ToString
 
        If strSearchField = "" Then
            Dim dbUtils As New dbLibrary.dbNorthwind
            dsNorthwind = New DataSet
            dbUtils.GetCustomers(dsNorthwind)
            dbUtils = Nothing
        End If
        Try
            Dim ICustLondon As IEnumerable(Of DataRow) = _
                     From customer In dsNorthwind.Tables("Customers").AsEnumerable() _
                     Where customer.Field(Of String)(strSearchField).ToUpper.Trim Like txtSearch.Text.ToUpper.Trim & "*" _
                     Select customer
 
            Me.tblCustomers = ICustLondon.CopyToDataTable()
 
        Catch ex As Exception
            dgvCustomers.DataSource = Nothing
            tblCustomers = Nothing
        Finally
            BindData()
        End Try
    End Sub
    Private Sub dgvCustomers_MouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs)
        If e.Button = Windows.Forms.MouseButtons.Right Then
            Dim hti As DataGridView.HitTestInfo = sender.HitTest(e.X, e.Y)
            If hti.Type = DataGridViewHitTestType.Cell Then
                If Not Me.dgvCustomers.Rows(hti.RowIndex).Selected Then
                    Me.dgvCustomers.ClearSelection()
                    Me.dgvCustomers.Rows(hti.RowIndex).Selected = True
                    Me.cmCustomers.Position = hti.RowIndex
                End If
            End If
        End If
    End Sub
    Private Sub cboSearchSelect_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.txtSearch.Focus()
        Me.txtSearch.SelectAll()
    End Sub
#End Region
#Region "Private Methods"
    Private Sub SetAppStatus(ByVal Status As AppStatus)
        Dim blnReadOnly As Boolean
 
        Select Case Status
            Case AppStatus.Edit, AppStatus.Add
                blnReadOnly = False
                Me.pnlEdit.Visible = True
                Me.pnlLocked.Visible = False
                Me.dgvCustomers.Enabled = False
                Me.pnlNavigate.Enabled = False
                Me.txtSearch.Enabled = False
                Me.cboSearchSelect.Enabled = False
                Me.txtCompany.Focus()
                Me.txtCompany.SelectAll()
            Case AppStatus.Lock
                blnReadOnly = True
                Me.pnlEdit.Visible = False
                Me.pnlLocked.Visible = True
                Me.dgvCustomers.Enabled = True
                Me.pnlNavigate.Enabled = True
                Me.txtSearch.Enabled = True
                Me.cboSearchSelect.Enabled = True
                Me.txtSearch.Focus()
                Me.txtSearch.SelectAll()
        End Select
 
        For Each c As Control In gpxLayout.Controls
            If c.GetType Is GetType(TextBox) Then
                Dim txt As TextBox = CType(c, TextBox)
                txt.ReadOnly = blnReadOnly
            End If
            If c.GetType Is GetType(MaskedTextBox) Then
                Dim msk As MaskedTextBox = CType(c, MaskedTextBox)
                msk.ReadOnly = blnReadOnly
            End If
        Next
 
        If Status = AppStatus.Add Then
            txtCustomerID.ReadOnly = False
        Else
            txtCustomerID.ReadOnly = True
        End If
 
    End Sub
    Private Sub BindData()
        dgvCustomers.DataSource = tblCustomers
 
        If Not IsNothing(tblCustomers) Then
            With tblCustomers
                .Columns("CustomerID").DefaultValue = ""
                .Columns("CompanyName").DefaultValue = ""
                .Columns("ContactName").DefaultValue = ""
                .Columns("ContactTitle").DefaultValue = ""
                .Columns("Address").DefaultValue = ""
                .Columns("City").DefaultValue = ""
                .Columns("Region").DefaultValue = ""
                .Columns("PostalCode").DefaultValue = ""
                .Columns("Country").DefaultValue = ""
                .Columns("Phone").DefaultValue = ""
                .Columns("Fax").DefaultValue = ""
            End With
 
            Me.txtCustomerID.DataBindings.Clear()
            Me.txtCustomerID.DataBindings.Add("Text", tblCustomers, "CustomerID")
 
            Me.txtCompany.DataBindings.Clear()
            Me.txtCompany.DataBindings.Add("Text", tblCustomers, "CompanyName")
 
            Me.txtContactName.DataBindings.Clear()
            Me.txtContactName.DataBindings.Add("Text", tblCustomers, "ContactName")
 
            Me.txtContactTitle.DataBindings.Clear()
            Me.txtContactTitle.DataBindings.Add("Text", tblCustomers, "ContactTitle")
 
            Me.txtAddress.DataBindings.Clear()
            Me.txtAddress.DataBindings.Add("Text", tblCustomers, "Address")
 
            Me.txtCity.DataBindings.Clear()
            Me.txtCity.DataBindings.Add("Text", tblCustomers, "City")
 
            Me.txtRegion.DataBindings.Clear()
            Me.txtRegion.DataBindings.Add("Text", tblCustomers, "Region")
 
            Me.txtPostalCode.DataBindings.Clear()
            Me.txtPostalCode.DataBindings.Add("Text", tblCustomers, "PostalCode")
 
            Me.txtCountry.DataBindings.Clear()
            Me.txtCountry.DataBindings.Add("Text", tblCustomers, "Country")
 
            Me.txtPhone.DataBindings.Clear()
            Me.txtPhone.DataBindings.Add("Text", tblCustomers, "Phone")
 
            Me.txtFax.DataBindings.Clear()
            Me.txtFax.DataBindings.Add("Text", tblCustomers, "Fax")
 
            Me.cmCustomers = CType(Me.BindingContext(tblCustomers), CurrencyManager)
            Me.cmCustomers.Position = 0
 
        Else
            Me.cmCustomers = Nothing
            For Each c As Control In gpxLayout.Controls
                If c.GetType Is GetType(TextBox) Then
                    Dim txt As TextBox = CType(c, TextBox)
                    txt.ReadOnly = True
                    txt.Clear()
                End If
            Next
        End If
    End Sub
    Private Sub GetOrders()
        Dim ds As DataSet
        Dim objData As New dbLibrary.dbNorthwind
        ds = objData.getOrders(Me.tblCustomers.Rows(Me.cmCustomers.Position)("CustomerID"))
        objData = Nothing
 
        If Not IsNothing(ds) Then
            If ds.Tables("CustOrdersOrders").Rows.Count > 0 Then
                frmOrders.Orders = ds.Tables("CustOrdersOrders")
                frmOrders.ShowDialog()
            Else
                MsgBox("No orders to display", MsgBoxStyle.Information, "No orders")
            End If
        Else
            MsgBox("No orders to display", MsgBoxStyle.Information, "No orders")
        End If
    End Sub
#End Region
 
     
    
   
 
    Private Sub dgvCustomers_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgvCustomers.KeyPress
 
        Me.txtSearch.Text = e.KeyChar
        Me.txtSearch.Focus()
        Me.txtSearch.Select(Me.txtSearch.Text.Length, 1)
 
    End Sub
End Class
 
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class frmOrders
    Inherits System.Windows.Forms.Form
 
    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.btnClose = New System.Windows.Forms.Button
        Me.dgvOrders = New System.Windows.Forms.DataGridView
        CType(Me.dgvOrders, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'btnClose
        '
        Me.btnClose.Location = New System.Drawing.Point(380, 167)
        Me.btnClose.Name = "btnClose"
        Me.btnClose.Size = New System.Drawing.Size(75, 23)
        Me.btnClose.TabIndex = 1
        Me.btnClose.Text = "Close"
        Me.btnClose.UseVisualStyleBackColor = True
        '
        'dgvOrders
        '
        Me.dgvOrders.AllowUserToAddRows = False
        Me.dgvOrders.AllowUserToDeleteRows = False
        Me.dgvOrders.AllowUserToOrderColumns = True
        Me.dgvOrders.AllowUserToResizeColumns = False
        Me.dgvOrders.AllowUserToResizeRows = False
        Me.dgvOrders.CellBorderStyle = System.Windows.Forms.DataGridViewCellBorderStyle.RaisedVertical
        Me.dgvOrders.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.DisableResizing
        Me.dgvOrders.EditMode = System.Windows.Forms.DataGridViewEditMode.EditProgrammatically
        Me.dgvOrders.Location = New System.Drawing.Point(12, 12)
        Me.dgvOrders.MultiSelect = False
        Me.dgvOrders.Name = "dgvOrders"
        Me.dgvOrders.RowHeadersWidthSizeMode = System.Windows.Forms.DataGridViewRowHeadersWidthSizeMode.DisableResizing
        Me.dgvOrders.RowTemplate.Resizable = System.Windows.Forms.DataGridViewTriState.[False]
        Me.dgvOrders.ScrollBars = System.Windows.Forms.ScrollBars.Vertical
        Me.dgvOrders.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
        Me.dgvOrders.Size = New System.Drawing.Size(443, 151)
        Me.dgvOrders.TabIndex = 5
        '
        'frmOrders
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(467, 202)
        Me.ControlBox = False
        Me.Controls.Add(Me.dgvOrders)
        Me.Controls.Add(Me.btnClose)
        Me.Name = "frmOrders"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "Orders"
        CType(Me.dgvOrders, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
 
    End Sub
    Friend WithEvents btnClose As System.Windows.Forms.Button
    Friend WithEvents dgvOrders As System.Windows.Forms.DataGridView
End Class
 
Public Class frmOrders
 
    Private mOrders As DataTable
    Public WriteOnly Property Orders() As DataTable
        Set(ByVal value As DataTable)
            mOrders = value
        End Set
    End Property
 
    Private Sub frmOrders_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.dgvOrders.DataSource = mOrders
    End Sub
 
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
 
 
End Class
 
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
 
Public Class dbNorthwind
    Dim strConn As String = _
    System.Configuration.ConfigurationManager.ConnectionStrings("NorthWind").ConnectionString.ToString
 
    Public Sub GetCustomers(ByRef ds As DataSet)
        ds = New DataSet
 
        Dim cmdSQL As New SqlCommand("Select * From Customers")
 
        Dim sqlConn As New SqlConnection
        Try
            sqlConn = New SqlConnection(strConn)
            sqlConn.Open()
            cmdSQL.Connection = sqlConn
            Dim daSql As New SqlDataAdapter
            daSql.SelectCommand = cmdSQL
            daSql.Fill(ds, "Customers")
        Catch ex As SqlException
            ds = Nothing
        Catch ex As Exception
            ds = Nothing
        Finally
            sqlConn.Close()
            sqlConn = Nothing
            cmdSQL.Dispose()
            cmdSQL = Nothing
        End Try
    End Sub
 
    Public Function getOrders(ByVal CustomerID As String) As DataSet
        Dim ds As New DataSet
        Dim cmdSQL As New SqlCommand()
        cmdSQL.CommandType = CommandType.StoredProcedure
        cmdSQL.CommandText = "CustOrdersOrders"
        cmdSQL.Parameters.AddWithValue("@CustomerID", CustomerID)
 
        Dim sqlConn As New SqlConnection
        Try
            sqlConn = New SqlConnection(strConn)
            sqlConn.Open()
            cmdSQL.Connection = sqlConn
            Dim daSql As New SqlDataAdapter
            daSql.SelectCommand = cmdSQL
            daSql.Fill(ds, "CustOrdersOrders")
            Return ds
        Catch ex As SqlException
            Return Nothing
        Catch ex As Exception
            Return Nothing
        Finally
            sqlConn.Close()
            sqlConn = Nothing
            cmdSQL.Dispose()
            cmdSQL = Nothing
        End Try
    End Function
    Public Sub UpdateCustomers(ByRef dt As DataTable)
        Dim sqlConn As New SqlConnection
        sqlConn = New SqlConnection(strConn)
        sqlConn.Open()
 
        Dim sqlInsert As String = _
        "Insert Into Customers " & _
        "(CustomerID, CompanyName, ContactName, ContactTitle, " & _
        "Address, City, Region, PostalCode, Country, Phone, Fax) " & _
        " Values (@CustomerID, @CompanyName, @ContactName, @ContactTitle, " & _
        "@Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax) "
        Dim cmdInsert As New SqlCommand(sqlInsert, sqlConn)
 
        With cmdInsert.Parameters
            .Add(New SqlParameter("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID"))
            .Add(New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"))
            .Add(New SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"))
            .Add(New SqlParameter("@ContactTitle", SqlDbType.NVarChar, 30, "ContactTitle"))
            .Add(New SqlParameter("@Address", SqlDbType.NVarChar, 60, "Address"))
            .Add(New SqlParameter("@City", SqlDbType.NVarChar, 15, "City"))
            .Add(New SqlParameter("@Region", SqlDbType.NVarChar, 15, "Region"))
            .Add(New SqlParameter("@PostalCode", SqlDbType.NVarChar, 10, "PostalCode"))
            .Add(New SqlParameter("@Country", SqlDbType.NVarChar, 15, "Country"))
            .Add(New SqlParameter("@Phone", SqlDbType.NVarChar, 24, "Phone"))
            .Add(New SqlParameter("@Fax", SqlDbType.NVarChar, 24, "Fax"))
        End With
 
        Dim sqlUpdate As String = _
              "Update Customers " & _
              "Set " & _
              "CompanyName = @CompanyName, " & _
              "ContactName = @ContactName, " & _
              "ContactTitle = @ContactTitle, " & _
              "Address = @Address, City = @City, " & _
              "Region = @Region, " & _
              "PostalCode = @PostalCode, " & _
              "Country = @Country, " & _
              "Phone = @Phone, Fax = @Fax " & _
              "WHERE CustomerID = @CustomerID"
        Dim cmdUpdate As New SqlCommand(sqlUpdate, sqlConn)
 
        With cmdUpdate.Parameters
            .Add(New SqlParameter("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID"))
            .Add(New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"))
            .Add(New SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"))
            .Add(New SqlParameter("@ContactTitle", SqlDbType.NVarChar, 30, "ContactTitle"))
            .Add(New SqlParameter("@Address", SqlDbType.NVarChar, 60, "Address"))
            .Add(New SqlParameter("@City", SqlDbType.NVarChar, 15, "City"))
            .Add(New SqlParameter("@Region", SqlDbType.NVarChar, 15, "Region"))
            .Add(New SqlParameter("@PostalCode", SqlDbType.NVarChar, 10, "PostalCode"))
            .Add(New SqlParameter("@Country", SqlDbType.NVarChar, 15, "Country"))
            .Add(New SqlParameter("@Phone", SqlDbType.NVarChar, 24, "Phone"))
            .Add(New SqlParameter("@Fax", SqlDbType.NVarChar, 24, "Fax"))
        End With
 
        Dim sqlDelete As String = _
              "Delete From Customers " & _
              "WHERE CustomerID = @CustomerID"
        Dim cmdDelete As New SqlCommand(sqlDelete, sqlConn)
 
        With cmdDelete.Parameters
            .Add(New SqlParameter("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID"))
        End With
 
        Try
            Dim daSql As New SqlDataAdapter
            daSql.InsertCommand = cmdInsert
            daSql.UpdateCommand = cmdUpdate
            daSql.DeleteCommand = cmdDelete
 
            daSql.Update(dt)
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            sqlConn.Close()
            sqlConn = Nothing
            cmdInsert.Dispose()
            cmdInsert = Nothing
            cmdUpdate.Dispose()
            cmdUpdate = Nothing
            cmdDelete.Dispose()
            cmdDelete = Nothing
        End Try
 
    End Sub
End Class
 
 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name ="NorthWind" connectionString ="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"/>
  </connectionStrings>
    <system.diagnostics>
        <sources>
            <!-- This section defines the logging configuration for My.Application.Log -->
            <source name="DefaultSource" switchName="DefaultSwitch">
                <listeners>
                    <add name="FileLog"/>
                    <!-- Uncomment the below section to write to the Application Event Log -->
                    <!--<add name="EventLog"/>-->
                </listeners>
            </source>
        </sources>
        <switches>
            <add name="DefaultSwitch" value="Information" />
        </switches>
        <sharedListeners>
            <add name="FileLog"
                 type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" 
 
                 initializeData="FileLogWriter"/>
            <!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
            <!--<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->
        </sharedListeners>
    </system.diagnostics>
</configuration>

Open in new window

LVL 7
talker2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi talker2004;

The issue you are having is that some of the columns in the database allow null's and the Were clause must evaluate to a Boolean True or False. The columns Region, Fax, or PostalCode have null's in them so you need to test to make sure that it does not have a null and if it does don't evaluate it's field. The query below should work.

Fernando
Dim ICustLondon As IEnumerable(Of DataRow) = _
         From customer In dsNorthwind.Tables("Customers").AsEnumerable() _
         Where customer.Field(Of String)(strSearchField) IsNot Nothing AndAlso customer.Field(Of String)(strSearchField).ToUpper.Trim Like txtSearch.Text.ToUpper.Trim & "*" _
         Select customer

Open in new window

talker2004Author Commented:
so nice, i knew you would get it.

I also did know about the null values and it was my best guess at what was causing the problem, i simply did not know how to handle it through code.

Points well earned.

Only other question i have is what if i wanted to query all the postal codes with null values? Would that be a problem in linq?


Fernando SotoRetiredCommented:
Hi talker2004;

The issue is not so much that the value in the database is null but because it is null and the way you were setting the Where clause. The Where clause must evaluate to a Boolean so if we look at the original Where clause:

Where customer.Field(Of String)(strSearchField).ToUpper.Trim Like txtSearch.Text.ToUpper.Trim & "*" _

this, customer.Field(Of String)(strSearchField).ToUpper.Trim, evaluates to Nothing. Anything evaluated with Nothing results with Nothing and so the Where clause will throw an exception.

The following query will return only those records that have a null in the selected field. This is because customer.Field(Of String)(strSearchField) Is Nothing will evaluate to True or False and therefore will satisfy the Where clause.

            Dim ICustLondon As IEnumerable(Of DataRow) = _
                     From customer In dsNorthwind.Tables("Customers").AsEnumerable() _
                     Where customer.Field(Of String)(strSearchField) Is Nothing _
                     Select customer

In like manner the following query will return all records that have a null in the field or matches the search term in the text box.

            Dim ICustLondon As IEnumerable(Of DataRow) = _
                     From customer In dsNorthwind.Tables("Customers").AsEnumerable() _
                     Where customer.Field(Of String)(strSearchField) Is Nothing OrElse customer.Field(Of String)(strSearchField).ToUpper.Trim Like txtSearch.Text.ToUpper.Trim & "*" _
                     Select customer

Fernando

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

talker2004Author Commented:
great this worked perfect. Here is what i ended up doing for the search box.


Dim ICustomers As IEnumerable(Of DataRow)

            If Me.txtSearch.Text.Trim <> "" Then
                ICustomers = _
                 From customer In dsNorthwind.Tables("Customers").AsEnumerable() _
                 Where customer.Field(Of String)(strSearchField) IsNot Nothing _
                 AndAlso customer.Field(Of String)(strSearchField).ToUpper.Trim Like _
                 txtSearch.Text.ToUpper.Trim & "*" _
                 Select customer

            Else
                ICustomers = _
                     From customer In dsNorthwind.Tables("Customers").AsEnumerable() _
                     Where customer.Field(Of String)(strSearchField) Is Nothing OrElse _
                     customer.Field(Of String)(strSearchField).ToUpper.Trim Like "*" _
                     Select customer
            End If
talker2004Author Commented:
Thanks so much....
Fernando SotoRetiredCommented:
That should do it just fine.  ;=)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.