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
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>
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("Custom
Where customer.Field(Of String)(strSearchField) IsNot Nothing _
AndAlso customer.Field(Of String)(strSearchField).To
txtSearch.Text.ToUpper.Tri
Select customer
Else
ICustomers = _
From customer In dsNorthwind.Tables("Custom
Where customer.Field(Of String)(strSearchField) Is Nothing OrElse _
customer.Field(Of String)(strSearchField).To
Select customer
End If
Dim ICustomers As IEnumerable(Of DataRow)
If Me.txtSearch.Text.Trim <> "" Then
ICustomers = _
From customer In dsNorthwind.Tables("Custom
Where customer.Field(Of String)(strSearchField) IsNot Nothing _
AndAlso customer.Field(Of String)(strSearchField).To
txtSearch.Text.ToUpper.Tri
Select customer
Else
ICustomers = _
From customer In dsNorthwind.Tables("Custom
Where customer.Field(Of String)(strSearchField) Is Nothing OrElse _
customer.Field(Of String)(strSearchField).To
Select customer
End If
ASKER
Thanks so much....
That should do it just fine. ;=)
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
Open in new window