----------------------------------------------------------------------------------------------------------------------------------------------------
Program Listing for:  mainForm.vb
Project:  odbcDlg
Namespace:  vb.net
----------------------------------------------------------------------------------------------------------------------------------------------------
Imports System.Data Imports System.Data.OleDb Imports System.Data.SqlClient Public Class Form1 Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code " Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call End Sub 'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) 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. Friend WithEvents TabControl1 As System.Windows.Forms.TabControl Friend WithEvents TabPage1 As System.Windows.Forms.TabPage Friend WithEvents TabPage2 As System.Windows.Forms.TabPage Friend WithEvents mygrid As System.Windows.Forms.DataGrid Friend WithEvents Label1 As System.Windows.Forms.Label Friend WithEvents Label2 As System.Windows.Forms.Label Friend WithEvents Label4 As System.Windows.Forms.Label Friend WithEvents txtAdd1 As System.Windows.Forms.TextBox Friend WithEvents txtLastName As System.Windows.Forms.TextBox Friend WithEvents txtFirstName As System.Windows.Forms.TextBox Friend WithEvents txtAgentID As System.Windows.Forms.TextBox Friend WithEvents txtAdd2 As System.Windows.Forms.TextBox Friend WithEvents ToolTip1 As System.Windows.Forms.ToolTip Friend WithEvents txtCompany As System.Windows.Forms.TextBox Friend WithEvents txtMI As System.Windows.Forms.TextBox Friend WithEvents txtcity As System.Windows.Forms.TextBox Friend WithEvents txtState As System.Windows.Forms.TextBox Friend WithEvents txtZip As System.Windows.Forms.TextBox Friend WithEvents txtPhone As System.Windows.Forms.TextBox Friend WithEvents txtFax As System.Windows.Forms.TextBox Friend WithEvents txtEmail As System.Windows.Forms.TextBox Friend WithEvents txtEmailType As System.Windows.Forms.TextBox Friend WithEvents chkEF As System.Windows.Forms.CheckBox Friend WithEvents chkActive As System.Windows.Forms.CheckBox Friend WithEvents cmdTop As System.Windows.Forms.Button Friend WithEvents cmdPrior As System.Windows.Forms.Button Friend WithEvents cmdNext As System.Windows.Forms.Button Friend WithEvents cmdLast As System.Windows.Forms.Button Friend WithEvents cmdNew As System.Windows.Forms.Button Friend WithEvents cmdSave As System.Windows.Forms.Button Friend WithEvents cmdDelete As System.Windows.Forms.Button Friend WithEvents SampleEnum As System.Windows.Forms.Button Friend WithEvents cmdRevert As System.Windows.Forms.Button Friend WithEvents TextBox1 As System.Windows.Forms.TextBox Friend WithEvents TextBox2 As System.Windows.Forms.TextBox Friend WithEvents Label10 As System.Windows.Forms.Label Friend WithEvents Label9 As System.Windows.Forms.Label Friend WithEvents Label8 As System.Windows.Forms.Label Friend WithEvents Label7 As System.Windows.Forms.Label Friend WithEvents label6 As System.Windows.Forms.Label Friend WithEvents Label3 As System.Windows.Forms.Label Friend WithEvents Label5 As System.Windows.Forms.Label Friend WithEvents TextBox3 As System.Windows.Forms.TextBox Friend WithEvents TextBox4 As System.Windows.Forms.TextBox Friend WithEvents TextBox5 As System.Windows.Forms.TextBox Friend WithEvents TextBox6 As System.Windows.Forms.TextBox Friend WithEvents TextBox7 As System.Windows.Forms.TextBox Friend WithEvents TextBox8 As System.Windows.Forms.TextBox Friend WithEvents TextBox9 As System.Windows.Forms.TextBox Friend WithEvents TextBox10 As System.Windows.Forms.TextBox <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.components = New System.ComponentModel.Container() Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(Form1)) Me.TabControl1 = New System.Windows.Forms.TabControl() Me.TabPage1 = New System.Windows.Forms.TabPage() Me.TextBox10 = New System.Windows.Forms.TextBox() Me.TextBox9 = New System.Windows.Forms.TextBox() Me.TextBox8 = New System.Windows.Forms.TextBox() Me.TextBox7 = New System.Windows.Forms.TextBox() Me.TextBox6 = New System.Windows.Forms.TextBox() Me.TextBox5 = New System.Windows.Forms.TextBox() Me.TextBox4 = New System.Windows.Forms.TextBox() Me.TextBox3 = New System.Windows.Forms.TextBox() Me.TextBox2 = New System.Windows.Forms.TextBox() Me.TextBox1 = New System.Windows.Forms.TextBox() Me.chkActive = New System.Windows.Forms.CheckBox() Me.chkEF = New System.Windows.Forms.CheckBox() Me.txtEmailType = New System.Windows.Forms.TextBox() Me.Label10 = New System.Windows.Forms.Label() Me.Label9 = New System.Windows.Forms.Label() Me.txtEmail = New System.Windows.Forms.TextBox() Me.Label8 = New System.Windows.Forms.Label() Me.Label7 = New System.Windows.Forms.Label() Me.txtFax = New System.Windows.Forms.TextBox() Me.txtPhone = New System.Windows.Forms.TextBox() Me.txtZip = New System.Windows.Forms.TextBox() Me.txtState = New System.Windows.Forms.TextBox() Me.txtcity = New System.Windows.Forms.TextBox() Me.label6 = New System.Windows.Forms.Label() Me.txtMI = New System.Windows.Forms.TextBox() Me.Label3 = New System.Windows.Forms.Label() Me.txtCompany = New System.Windows.Forms.TextBox() Me.txtAdd2 = New System.Windows.Forms.TextBox() Me.Label5 = New System.Windows.Forms.Label() Me.Label4 = New System.Windows.Forms.Label() Me.Label2 = New System.Windows.Forms.Label() Me.Label1 = New System.Windows.Forms.Label() Me.txtAdd1 = New System.Windows.Forms.TextBox() Me.txtLastName = New System.Windows.Forms.TextBox() Me.txtFirstName = New System.Windows.Forms.TextBox() Me.txtAgentID = New System.Windows.Forms.TextBox() Me.TabPage2 = New System.Windows.Forms.TabPage() Me.mygrid = New System.Windows.Forms.DataGrid() Me.cmdTop = New System.Windows.Forms.Button() Me.cmdPrior = New System.Windows.Forms.Button() Me.cmdNext = New System.Windows.Forms.Button() Me.cmdLast = New System.Windows.Forms.Button() Me.cmdNew = New System.Windows.Forms.Button() Me.cmdSave = New System.Windows.Forms.Button() Me.cmdDelete = New System.Windows.Forms.Button() Me.ToolTip1 = New System.Windows.Forms.ToolTip(Me.components) Me.SampleEnum = New System.Windows.Forms.Button() Me.cmdRevert = New System.Windows.Forms.Button() Me.TabControl1.SuspendLayout() Me.TabPage1.SuspendLayout() Me.TabPage2.SuspendLayout() CType(Me.mygrid, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'TabControl1 ' Me.TabControl1.Controls.AddRange(New System.Windows.Forms.Control() {Me.TabPage1, Me.TabPage2}) Me.TabControl1.Font = New System.Drawing.Font("Arial", 9.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.TabControl1.HotTrack = True Me.TabControl1.Location = New System.Drawing.Point(8, 56) Me.TabControl1.Multiline = True Me.TabControl1.Name = "TabControl1" Me.TabControl1.RightToLeft = System.Windows.Forms.RightToLeft.No Me.TabControl1.SelectedIndex = 0 Me.TabControl1.Size = New System.Drawing.Size(632, 368) Me.TabControl1.TabIndex = 8 ' 'TabPage1 ' Me.TabPage1.Controls.AddRange(New System.Windows.Forms.Control() {Me.TextBox10, Me.TextBox9, Me.TextBox8, Me.TextBox7, Me.TextBox6, Me.TextBox5, Me.TextBox4, Me.TextBox3, Me.TextBox2, Me.TextBox1, Me.chkActive, Me.chkEF, Me.txtEmailType, Me.Label10, Me.Label9, Me.txtEmail, Me.Label8, Me.Label7, Me.txtFax, Me.txtPhone, Me.txtZip, Me.txtState, Me.txtcity, Me.label6, Me.txtMI, Me.Label3, Me.txtCompany, Me.txtAdd2, Me.Label5, Me.Label4, Me.Label2, Me.Label1, Me.txtAdd1, Me.txtLastName, Me.txtFirstName, Me.txtAgentID}) Me.TabPage1.Location = New System.Drawing.Point(4, 24) Me.TabPage1.Name = "TabPage1" Me.TabPage1.Size = New System.Drawing.Size(624, 340) Me.TabPage1.TabIndex = 0 Me.TabPage1.Text = "Edit" ' 'TextBox10 ' Me.TextBox10.Location = New System.Drawing.Point(106, 284) Me.TextBox10.Name = "TextBox10" Me.TextBox10.Size = New System.Drawing.Size(170, 21) Me.TextBox10.TabIndex = 33 Me.TextBox10.Text = "" Me.TextBox10.Visible = False ' 'TextBox9 ' Me.TextBox9.Location = New System.Drawing.Point(106, 254) Me.TextBox9.Name = "TextBox9" Me.TextBox9.Size = New System.Drawing.Size(170, 21) Me.TextBox9.TabIndex = 32 Me.TextBox9.Text = "" Me.TextBox9.Visible = False ' 'TextBox8 ' Me.TextBox8.Location = New System.Drawing.Point(294, 224) Me.TextBox8.Name = "TextBox8" Me.TextBox8.Size = New System.Drawing.Size(170, 21) Me.TextBox8.TabIndex = 31 Me.TextBox8.Text = "" Me.TextBox8.Visible = False ' 'TextBox7 ' Me.TextBox7.Location = New System.Drawing.Point(106, 224) Me.TextBox7.Name = "TextBox7" Me.TextBox7.Size = New System.Drawing.Size(126, 21) Me.TextBox7.TabIndex = 30 Me.TextBox7.Text = "" Me.TextBox7.Visible = False ' 'TextBox6 ' Me.TextBox6.Location = New System.Drawing.Point(106, 194) Me.TextBox6.Name = "TextBox6" Me.TextBox6.Size = New System.Drawing.Size(170, 21) Me.TextBox6.TabIndex = 29 Me.TextBox6.Text = "" Me.TextBox6.Visible = False ' 'TextBox5 ' Me.TextBox5.Location = New System.Drawing.Point(106, 164) Me.TextBox5.Name = "TextBox5" Me.TextBox5.Size = New System.Drawing.Size(170, 21) Me.TextBox5.TabIndex = 28 Me.TextBox5.Text = "" Me.TextBox5.Visible = False ' 'TextBox4 ' Me.TextBox4.Location = New System.Drawing.Point(106, 134) Me.TextBox4.Name = "TextBox4" Me.TextBox4.Size = New System.Drawing.Size(170, 21) Me.TextBox4.TabIndex = 27 Me.TextBox4.Text = "" Me.TextBox4.Visible = False ' 'TextBox3 ' Me.TextBox3.Location = New System.Drawing.Point(106, 104) Me.TextBox3.Name = "TextBox3" Me.TextBox3.Size = New System.Drawing.Size(170, 21) Me.TextBox3.TabIndex = 26 Me.TextBox3.Text = "" Me.TextBox3.Visible = False ' 'TextBox2 ' Me.TextBox2.Location = New System.Drawing.Point(106, 74) Me.TextBox2.Name = "TextBox2" Me.TextBox2.Size = New System.Drawing.Size(170, 21) Me.TextBox2.TabIndex = 25 Me.TextBox2.Text = "" Me.TextBox2.Visible = False ' 'TextBox1 ' Me.TextBox1.Location = New System.Drawing.Point(106, 44) Me.TextBox1.Name = "TextBox1" Me.TextBox1.ReadOnly = True Me.TextBox1.Size = New System.Drawing.Size(152, 21) Me.TextBox1.TabIndex = 24 Me.TextBox1.Text = "" Me.TextBox1.Visible = False ' 'chkActive ' Me.chkActive.Font = New System.Drawing.Font("Arial", 9.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.chkActive.ForeColor = System.Drawing.Color.DarkCyan Me.chkActive.Location = New System.Drawing.Point(452, 84) Me.chkActive.Name = "chkActive" Me.chkActive.TabIndex = 15 Me.chkActive.Text = "Active Agent" ' 'chkEF ' Me.chkEF.Font = New System.Drawing.Font("Arial", 9.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.chkEF.ForeColor = System.Drawing.Color.DarkCyan Me.chkEF.Location = New System.Drawing.Point(452, 56) Me.chkEF.Name = "chkEF" Me.chkEF.TabIndex = 14 Me.chkEF.Text = "E Filer" Me.chkEF.ThreeState = True ' 'txtEmailType ' Me.txtEmailType.Location = New System.Drawing.Point(106, 286) Me.txtEmailType.Name = "txtEmailType" Me.txtEmailType.Size = New System.Drawing.Size(40, 21) Me.txtEmailType.TabIndex = 13 Me.txtEmailType.Text = "" ' 'Label10 ' Me.Label10.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label10.ForeColor = System.Drawing.Color.DarkCyan Me.Label10.Location = New System.Drawing.Point(24, 288) Me.Label10.Name = "Label10" Me.Label10.Size = New System.Drawing.Size(72, 16) Me.Label10.TabIndex = 23 Me.Label10.Text = "Email Type" ' 'Label9 ' Me.Label9.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label9.ForeColor = System.Drawing.Color.DarkCyan Me.Label9.Location = New System.Drawing.Point(24, 256) Me.Label9.Name = "Label9" Me.Label9.Size = New System.Drawing.Size(72, 16) Me.Label9.TabIndex = 22 Me.Label9.Text = "Email" ' 'txtEmail ' Me.txtEmail.Location = New System.Drawing.Point(106, 254) Me.txtEmail.Name = "txtEmail" Me.txtEmail.Size = New System.Drawing.Size(342, 21) Me.txtEmail.TabIndex = 12 Me.txtEmail.Text = "" ' 'Label8 ' Me.Label8.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label8.ForeColor = System.Drawing.Color.DarkCyan Me.Label8.Location = New System.Drawing.Point(238, 230) Me.Label8.Name = "Label8" Me.Label8.Size = New System.Drawing.Size(48, 16) Me.Label8.TabIndex = 20 Me.Label8.Text = "Fax" ' 'Label7 ' Me.Label7.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label7.ForeColor = System.Drawing.Color.DarkCyan Me.Label7.Location = New System.Drawing.Point(24, 228) Me.Label7.Name = "Label7" Me.Label7.Size = New System.Drawing.Size(72, 16) Me.Label7.TabIndex = 19 Me.Label7.Text = "Phone" ' 'txtFax ' Me.txtFax.Location = New System.Drawing.Point(320, 226) Me.txtFax.Name = "txtFax" Me.txtFax.Size = New System.Drawing.Size(118, 21) Me.txtFax.TabIndex = 11 Me.txtFax.Text = "" ' 'txtPhone ' Me.txtPhone.Location = New System.Drawing.Point(106, 224) Me.txtPhone.Name = "txtPhone" Me.txtPhone.Size = New System.Drawing.Size(114, 21) Me.txtPhone.TabIndex = 10 Me.txtPhone.Text = "" ' 'txtZip ' Me.txtZip.Location = New System.Drawing.Point(384, 194) Me.txtZip.Name = "txtZip" Me.txtZip.Size = New System.Drawing.Size(80, 21) Me.txtZip.TabIndex = 9 Me.txtZip.Text = "" ' 'txtState ' Me.txtState.Location = New System.Drawing.Point(336, 194) Me.txtState.Name = "txtState" Me.txtState.Size = New System.Drawing.Size(40, 21) Me.txtState.TabIndex = 8 Me.txtState.Text = "" ' 'txtcity ' Me.txtcity.Location = New System.Drawing.Point(106, 194) Me.txtcity.Name = "txtcity" Me.txtcity.Size = New System.Drawing.Size(224, 21) Me.txtcity.TabIndex = 7 Me.txtcity.Text = "" ' 'label6 ' Me.label6.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.label6.ForeColor = System.Drawing.Color.DarkCyan Me.label6.Location = New System.Drawing.Point(24, 198) Me.label6.Name = "label6" Me.label6.Size = New System.Drawing.Size(72, 16) Me.label6.TabIndex = 13 Me.label6.Text = "City" ' 'txtMI ' Me.txtMI.Location = New System.Drawing.Point(248, 74) Me.txtMI.Name = "txtMI" Me.txtMI.Size = New System.Drawing.Size(24, 21) Me.txtMI.TabIndex = 2 Me.txtMI.Text = "" ' 'Label3 ' Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label3.ForeColor = System.Drawing.Color.DarkCyan Me.Label3.Location = New System.Drawing.Point(24, 108) Me.Label3.Name = "Label3" Me.Label3.Size = New System.Drawing.Size(72, 16) Me.Label3.TabIndex = 11 Me.Label3.Text = "Company" ' 'txtCompany ' Me.txtCompany.Location = New System.Drawing.Point(106, 104) Me.txtCompany.Name = "txtCompany" Me.txtCompany.Size = New System.Drawing.Size(256, 21) Me.txtCompany.TabIndex = 4 Me.txtCompany.Text = "" ' 'txtAdd2 ' Me.txtAdd2.Location = New System.Drawing.Point(106, 164) Me.txtAdd2.Name = "txtAdd2" Me.txtAdd2.Size = New System.Drawing.Size(224, 21) Me.txtAdd2.TabIndex = 6 Me.txtAdd2.Text = "" ' 'Label5 ' Me.Label5.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label5.ForeColor = System.Drawing.Color.DarkCyan Me.Label5.Location = New System.Drawing.Point(24, 168) Me.Label5.Name = "Label5" Me.Label5.Size = New System.Drawing.Size(72, 16) Me.Label5.TabIndex = 8 Me.Label5.Text = "Address2" ' 'Label4 ' Me.Label4.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label4.ForeColor = System.Drawing.Color.DarkCyan Me.Label4.Location = New System.Drawing.Point(24, 138) Me.Label4.Name = "Label4" Me.Label4.Size = New System.Drawing.Size(72, 16) Me.Label4.TabIndex = 7 Me.Label4.Text = "Address1" ' 'Label2 ' Me.Label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label2.ForeColor = System.Drawing.Color.DarkCyan Me.Label2.Location = New System.Drawing.Point(24, 78) Me.Label2.Name = "Label2" Me.Label2.Size = New System.Drawing.Size(72, 16) Me.Label2.TabIndex = 5 Me.Label2.Text = "Name" ' 'Label1 ' Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label1.ForeColor = System.Drawing.Color.Blue Me.Label1.Location = New System.Drawing.Point(24, 46) Me.Label1.Name = "Label1" Me.Label1.Size = New System.Drawing.Size(72, 16) Me.Label1.TabIndex = 4 Me.Label1.Text = "Agent ID" ' 'txtAdd1 ' Me.txtAdd1.Location = New System.Drawing.Point(106, 134) Me.txtAdd1.Name = "txtAdd1" Me.txtAdd1.Size = New System.Drawing.Size(224, 21) Me.txtAdd1.TabIndex = 5 Me.txtAdd1.Text = "" ' 'txtLastName ' Me.txtLastName.Location = New System.Drawing.Point(280, 74) Me.txtLastName.Name = "txtLastName" Me.txtLastName.Size = New System.Drawing.Size(128, 21) Me.txtLastName.TabIndex = 3 Me.txtLastName.Text = "" ' 'txtFirstName ' Me.txtFirstName.Location = New System.Drawing.Point(106, 74) Me.txtFirstName.Name = "txtFirstName" Me.txtFirstName.Size = New System.Drawing.Size(128, 21) Me.txtFirstName.TabIndex = 1 Me.txtFirstName.Text = "" ' 'txtAgentID ' Me.txtAgentID.BackColor = System.Drawing.Color.Aqua Me.txtAgentID.Location = New System.Drawing.Point(106, 44) Me.txtAgentID.Name = "txtAgentID" Me.txtAgentID.ReadOnly = True Me.txtAgentID.Size = New System.Drawing.Size(128, 21) Me.txtAgentID.TabIndex = 0 Me.txtAgentID.Text = "" ' 'TabPage2 ' Me.TabPage2.Controls.AddRange(New System.Windows.Forms.Control() {Me.mygrid}) Me.TabPage2.Location = New System.Drawing.Point(4, 24) Me.TabPage2.Name = "TabPage2" Me.TabPage2.Size = New System.Drawing.Size(624, 340) Me.TabPage2.TabIndex = 1 Me.TabPage2.Text = "List" ' 'mygrid ' Me.mygrid.AlternatingBackColor = System.Drawing.Color.LightSkyBlue Me.mygrid.BorderStyle = System.Windows.Forms.BorderStyle.None Me.mygrid.CaptionText = "table listing" Me.mygrid.DataMember = "" Me.mygrid.FlatMode = True Me.mygrid.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.mygrid.Name = "mygrid" Me.mygrid.Size = New System.Drawing.Size(624, 336) Me.mygrid.TabIndex = 3 Me.mygrid.Tag = "" ' 'cmdTop ' Me.cmdTop.Image = CType(resources.GetObject("cmdTop.Image"), System.Drawing.Bitmap) Me.cmdTop.Location = New System.Drawing.Point(204, 16) Me.cmdTop.Name = "cmdTop" Me.cmdTop.Size = New System.Drawing.Size(40, 32) Me.cmdTop.TabIndex = 1 Me.ToolTip1.SetToolTip(Me.cmdTop, "Top Record") ' 'cmdPrior ' Me.cmdPrior.Image = CType(resources.GetObject("cmdPrior.Image"), System.Drawing.Bitmap) Me.cmdPrior.Location = New System.Drawing.Point(244, 16) Me.cmdPrior.Name = "cmdPrior" Me.cmdPrior.Size = New System.Drawing.Size(40, 32) Me.cmdPrior.TabIndex = 2 Me.ToolTip1.SetToolTip(Me.cmdPrior, "Previous Record") ' 'cmdNext ' Me.cmdNext.Image = CType(resources.GetObject("cmdNext.Image"), System.Drawing.Bitmap) Me.cmdNext.Location = New System.Drawing.Point(284, 16) Me.cmdNext.Name = "cmdNext" Me.cmdNext.Size = New System.Drawing.Size(40, 32) Me.cmdNext.TabIndex = 3 Me.ToolTip1.SetToolTip(Me.cmdNext, "Next Record") ' 'cmdLast ' Me.cmdLast.Image = CType(resources.GetObject("cmdLast.Image"), System.Drawing.Bitmap) Me.cmdLast.Location = New System.Drawing.Point(324, 16) Me.cmdLast.Name = "cmdLast" Me.cmdLast.Size = New System.Drawing.Size(40, 32) Me.cmdLast.TabIndex = 4 Me.ToolTip1.SetToolTip(Me.cmdLast, "Last Record") ' 'cmdNew ' Me.cmdNew.Image = CType(resources.GetObject("cmdNew.Image"), System.Drawing.Bitmap) Me.cmdNew.Location = New System.Drawing.Point(164, 16) Me.cmdNew.Name = "cmdNew" Me.cmdNew.Size = New System.Drawing.Size(40, 32) Me.cmdNew.TabIndex = 0 Me.ToolTip1.SetToolTip(Me.cmdNew, "New Record") ' 'cmdSave ' Me.cmdSave.Image = CType(resources.GetObject("cmdSave.Image"), System.Drawing.Bitmap) Me.cmdSave.Location = New System.Drawing.Point(506, 16) Me.cmdSave.Name = "cmdSave" Me.cmdSave.Size = New System.Drawing.Size(40, 32) Me.cmdSave.TabIndex = 6 Me.ToolTip1.SetToolTip(Me.cmdSave, "Save Changes") ' 'cmdDelete ' Me.cmdDelete.Image = CType(resources.GetObject("cmdDelete.Image"), System.Drawing.Bitmap) Me.cmdDelete.Location = New System.Drawing.Point(364, 16) Me.cmdDelete.Name = "cmdDelete" Me.cmdDelete.Size = New System.Drawing.Size(40, 32) Me.cmdDelete.TabIndex = 5 Me.ToolTip1.SetToolTip(Me.cmdDelete, "Delete Record") ' 'SampleEnum ' Me.SampleEnum.Image = CType(resources.GetObject("SampleEnum.Image"), System.Drawing.Bitmap) Me.SampleEnum.Location = New System.Drawing.Point(32, 14) Me.SampleEnum.Name = "SampleEnum" Me.SampleEnum.Size = New System.Drawing.Size(40, 32) Me.SampleEnum.TabIndex = 8 Me.SampleEnum.TabStop = False Me.ToolTip1.SetToolTip(Me.SampleEnum, "DataSet Iteration Example") ' 'cmdRevert ' Me.cmdRevert.Image = CType(resources.GetObject("cmdRevert.Image"), System.Drawing.Bitmap) Me.cmdRevert.Location = New System.Drawing.Point(546, 16) Me.cmdRevert.Name = "cmdRevert" Me.cmdRevert.Size = New System.Drawing.Size(40, 32) Me.cmdRevert.TabIndex = 7 Me.ToolTip1.SetToolTip(Me.cmdRevert, "Refresh Data") ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(648, 437) Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.cmdRevert, Me.SampleEnum, Me.cmdDelete, Me.cmdSave, Me.cmdNew, Me.cmdLast, Me.cmdNext, Me.cmdPrior, Me.cmdTop, Me.TabControl1}) Me.Name = "Form1" Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen Me.Text = "Modify Agents" Me.TabControl1.ResumeLayout(False) Me.TabPage1.ResumeLayout(False) Me.TabPage2.ResumeLayout(False) CType(Me.mygrid, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region ' the nextkeyval variable is used with getting a new insert key value, it is one way to track this, for the example, another way ' is via a select into a keys table (the current methodology) Dim nextkeyval As Integer = 750 ' this is the basic adodb objects Dim adoCn As New ADODB.Connection() Dim adors As New ADODB.Recordset() Dim myadapter As New OleDbDataAdapter() Dim format1 As New Formatter() ' change these values to affect how many screens pop up with debug type stuff Dim debugging As Boolean = False ' this will pop up the actual update and insert commands sent from a save, somewhat useful for learning ' how this stuff works Dim showUpdateCommands As Boolean = True ' this controls how you get a new key value for an insert see the property definition below this for ' nextPrimaryKeyVal Dim useSelectForKeyValue As Boolean = True Public mydataset As New DataSet() ' this is a useful way to move around in a record set and misc other things Dim bmb As BindingManagerBase ' these values should change to fit your data base and table: ' connection string, same as old ADO conn string Dim connstring = "Provider=MSDASQL.1;Persist Security Info=False;Driver={Microsoft Visual FoxPro Driver};" & _ "UID=;SourceDB=G:\U\SBOE\DATA\AZSBOE.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;" & _ "Collate=Machine;Null=Yes;Deleted=Yes;" ' these three values should be changed to fit your table Dim mainTableName As String = "Agents" Dim genericItemName As String = "Agent" ' if you want to create the text boxes on the first tabbed page manually set this to True ' and use the form designer to create the tab controls of the right type, ' and then modify the form routine bindControls below ' if you set it to False an automatic drawing routine will be done, you will probably need to modify ' this also, however, see bindControls subroutine Dim manualTextBoxes As Boolean = True ' this can be any valid order by expression, in this case we are just ordering by the primary id Dim orderClause As String = "nagentid" ' this is the offset of the primary key column, zero indexed, in our case, the first column is ' the primary key field, so it is zero Dim primaryKeyColumnIndex As Integer = 0 ' this is the primary key field name, assuming it to be of type numeric (integer or double), for other types ' of primary keys or for compound field type keys you will need to modify the logic a bit Dim primaryKeyColumnName As String = "nagentid" ' using a VB.N property ' next primary key value property, for use with inserts, you should change this code to however you get the primary ' key in your own table Public Property nextPrimaryKeyVal() As Integer Get Dim retValue As Integer = 0 ' if they aren't using a select and a keys table, we can just use a class member to track new primary key insert values, altho ' this isn't really a good way to do it. If Not useSelectForKeyValue Then Me.nextkeyval += 1 retValue = Me.nextkeyval Else retValue = getNextValueFromKeysTable("AGENT_ID") End If Return (retValue) End Get Set(ByVal Value As Integer) Me.nextkeyval = Value End Set End Property ' this returns the next valid key value, given the passed identifier string, i.e. "AGENT_ID", "LOCATION_ID" ' you should replace this with however you get unique key values (or you can replace the code directly in the ' property above) ' this is similar to an insert routine I use in Foxpro, I use a keys table to get next primary key values for tables ' this table has structure: keys.dbf (key_name c(20), key_value int) Private Function getNextValueFromKeysTable(ByVal keyIDString As String) As Integer Dim retValue As Integer = 0 ' otherwise use a select to get a new primary key value for the table ' you will want to change this according to your mechanism for getting a new table value ' since foxpro doesn't have an auto_number property I like to use a keys table, but you could also ' do a select max on the key value and add one to it, etc. Try Dim rows_affected As Integer Dim error_string As String = "" Dim currentKeyVal As Integer ' i grab the keys in a two sql statement process, first I do a select to get the newest key value for this key ' then I do a separate update call attempting to update this value to it's value plus one. in the update call ' where procedure i make sure the value is the same as it was before, to make sure someone else isn't grabbing ' the value. this makes the transaction fairly atomic. it would be nice to be able to do this in one call but that is difficult ' in foxpro (this is why auto_increment fields are so nice in other languages). we could also do a transaction here, but ' this logic should assure the two sql statements are atomic enough without requiring that. ' grab the key field last value currentKeyVal = singleValueSelect("select key_value from keys where key_name = [" & keyIDString & "]") ' return value of -1 means some error occurred didn't find a match in table If currentKeyVal = -1 Then MessageBox.Show("no key matches in trying to find next insert key value, possible bad key value or table corruption") retValue = 0 ' redundant but for clarity Exit Try End If ' get the incremented value as well Dim newKeyValue As Integer = currentKeyVal + 1 ' the update attempts to update for this key value if it equals the old value only, making the transaction atomic, otherwise ' this type of thing would require a transaction. it means it is more likely to fail however so the method might be good to ' be called 2 or 3 times. adoCn.Open(connstring) adoCn.Execute("update keys set key_value = " & newKeyValue & " where key_name = [" & keyIDString & "] and key_value = " & currentKeyVal, rows_affected) ' if didn't affect one row, then update failed If rows_affected <> 1 Then retValue = 0 ' redundant but for clarity Exit Try End If retValue = currentKeyVal Catch ex As Exception MessageBox.Show("exception encountered during update call:" & ex.ToString()) Finally ' rarely but in certain cases closing also causes an exception, so wrap this also Try adoCn.Close() Catch ex As Exception MessageBox.Show("Error on connection close:" & ex.ToString()) End Try End Try Return retValue End Function ' process a select that returns a single value Private Function singleValueSelect(ByVal selectString As String) As Integer Dim rows_affected As Integer Dim error_string As String = "" Dim currentKeyVal As Integer Dim adoConnection As New ADODB.Connection() Dim adoRecordSet As New ADODB.Recordset() Dim DataSet1 As New DataSet() Dim odda As New OleDbDataAdapter() Dim returnVal As Integer Try adoConnection.Open(connstring) ' process the select adoRecordSet.Open(selectString, adoConnection, _ ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic) ' I tried doing this with the command's execute statement rather than this more roundable way but had an error ' so for now I'm using a dataset odda.Fill(DataSet1, adoRecordSet, "table1") ' see if any data was returned from last select If DataSet1.Tables(0).Rows.Count = 0 Then Return -1 End If ' get the current integer value for this key Try returnVal = DataSet1.Tables(0).Rows(0).Item(0) ' if the item has no default to integer conversion than we catch this and set return value to -1 Catch ex As Exception returnVal = -1 Finally End Try Catch ex As Exception Finally ' dont close the recordset, the Fill method does that already with AdoDB recordsets adoConnection.Close() End Try Return returnVal End Function ' standard form load method Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.Text = "Modify " & Me.mainTableName ' always surround data opening stuff with try catch blocks (per Balena) Try ' connect to the database with the table adoCn.Open(connstring) ' grab the data, in this case the agents table adors.Open("select * from " & mainTableName & " order by " & orderClause, adoCn, _ ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic) ' this is interesting, you can actually change the adors references to data directly as below, this would ' be a way to do a one way only processing type thing via AdoDb If False Then Do Until adors.EOF adors("clastname").Value = UCase(adors("clastname").Value) adors.MoveNext() Loop End If ' fill a dataset with the query results via a dataadapter ' this fill method is overloaded to work with an old recordset, but note that it closes the cursor immediately, so no ' other actions like the above do until loop can now be executed after this call myadapter.Fill(mydataset, adors, Me.mainTableName) ' this is important, make sure the primary key value is readonly or updates will be messed up if they change it, doing it ' here instead of via the controls is more efficient, this will make it readonly in both the textbox and the grid controls mydataset.Tables(0).Columns(primaryKeyColumnIndex).ReadOnly = True ' this doesn't change the grid, I was experimenting, grid header columns will only show the raw sql fields per now mydataset.Tables(0).Columns(primaryKeyColumnIndex).Caption = "Agent ID" ' bind the grid to the dataset mygrid.SetDataBinding(mydataset, Me.mainTableName) Catch ex As Exception MessageBox.Show(ex.Message) Finally 'adors.Close() - closes the recordset explicitly causes a runtime error, remember Fill closes the recordset automatically adoCn.Close() End Try bindControls() End Sub ' procedure to bind the text box controls on the first page of the tab ' you will have to modify this and change the tab itself to reflect your table, or you may be able to do this via the form ' methods (doing it in code is sometimes more flexible) Private Sub bindControls() ' programmatically bind the text boxes to the fields ' this stuff should be hand modified as well as via the form designer to change the labels and so on ' to point to real columns in your table (I didn't want to automate this as it wouldn't look that great to have a ' generic field generation thing here) If Me.manualTextBoxes Then ' you will have to replace these values manually according to the pattern txtAgentID.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".nagentid")) txtFirstName.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".cfirstname")) txtMI.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".cmi")) txtLastName.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".clastname")) txtCompany.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".ccompany")) txtAdd1.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".caddress1")) txtAdd2.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".caddress2")) txtcity.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".ccity")) txtState.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".cstate")) txtZip.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".cpostalcode")) txtPhone.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".cphonenum")) txtFax.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".cfax")) txtEmail.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".email")) txtEmailType.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & ".cemailtype")) ' for check boxes make sure you use the "checked" parameter chkActive.DataBindings.Add _ (New Binding("checked", mydataset, mainTableName & ".lactive")) chkEF.DataBindings.Add _ (New Binding("checked", mydataset, mainTableName & ".lef")) Else ' or you could extend this and make it automatically propagate, using a technique like below, altho this ' may not look as cosmetic as doing it by hand With mydataset.Tables(0) ' for convenience we hide the custom controls to use the more automated ones, you should delete the custom ' controls in the example for your own stuff hideCustomControls() ' should optimize this in a step through procedure and make it based on the total number of columns ' but this is just by way of example TextBox1.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(0).ColumnName)) TextBox1.Show() TextBox2.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(1).ColumnName)) TextBox2.Show() TextBox3.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(2).ColumnName)) TextBox3.Show() TextBox4.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(3).ColumnName)) TextBox4.Show() TextBox5.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(4).ColumnName)) TextBox5.Show() TextBox6.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(5).ColumnName)) TextBox6.Show() TextBox7.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(6).ColumnName)) TextBox7.Show() TextBox8.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(7).ColumnName)) TextBox8.Show() TextBox9.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(8).ColumnName)) TextBox9.Show() TextBox10.DataBindings.Add _ (New Binding("Text", mydataset, mainTableName & "." & .Columns(9).ColumnName)) TextBox10.Show() Label1.Text = .Columns(0).ColumnName Label2.Text = .Columns(1).ColumnName Label3.Text = .Columns(2).ColumnName Label4.Text = .Columns(3).ColumnName Label5.Text = .Columns(4).ColumnName label6.Text = .Columns(5).ColumnName Label7.Text = .Columns(6).ColumnName Label8.Text = .Columns(7).ColumnName Label9.Text = .Columns(8).ColumnName Label10.Text = .Columns(9).ColumnName End With End If End Sub Private Sub Form1_Closed(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Closed End Sub Private Sub hideCustomControls() txtAgentID.Hide() txtFirstName.Hide() txtMI.Hide() txtLastName.Hide() txtCompany.Hide() txtAdd1.Hide() txtAdd2.Hide() txtcity.Hide() txtState.Hide() txtZip.Hide() txtPhone.Hide() txtFax.Hide() txtEmail.Hide() txtEmailType.Hide() chkEF.Hide() chkActive.Hide() End Sub Private Sub cmdPrior_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrior.Click bmb = Me.BindingContext(mydataset, Me.mainTableName) bmb.Position = Math.Max(bmb.Position - 1, 0) bmb = Nothing End Sub Private Sub cmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNext.Click bmb = Me.BindingContext(mydataset, Me.mainTableName) bmb.Position = Math.Min(bmb.Position + 1, bmb.Count) bmb = Nothing End Sub Private Sub cmdLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLast.Click bmb = Me.BindingContext(mydataset, Me.mainTableName) bmb.Position = bmb.Count bmb = Nothing End Sub Private Sub cmdTop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTop.Click bmb = Me.BindingContext(mydataset, Me.mainTableName) bmb.Position = 0 bmb = Nothing End Sub ' response to the new button, insert a record Private Sub cmdNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNew.Click Dim mytableref As DataTable = mydataset.Tables(Me.mainTableName) Dim dr As DataRow = mytableref.NewRow() ' grab a sample old row to get the column type, for determing blank values for insert Dim oldrow As DataRow = mytableref.Rows(0) Dim n As Integer ' get appropriate blank values, this is like Foxpro, which assigns certain default values via APPEND BLANK, ' instead of assigning to .NULL. to everything, which can cause errors if the field doesn't support NULL. For n = 0 To mytableref.Columns.Count - 1 If oldrow(n).GetType.ToString() = "System.String" Then dr(n) = "" ElseIf oldrow(n).GetType.ToString() = "System.Decimal" Then dr(n) = 0 ElseIf oldrow(n).GetType.ToString() = "System.Double" Then dr(n) = 0 ElseIf oldrow(n).GetType.ToString() = "System.DateTime" Then dr(n) = New DateTime(0) ElseIf oldrow(n).GetType.ToString() = "System.Int32" Then dr(n) = 0 ElseIf oldrow(n).GetType.ToString() = "System.Byte[]" Then dr(n) = New Byte() ElseIf oldrow(n).GetType.ToString() = "System.Boolean" Then dr(n) = False Else dr(n) = oldrow(n) MessageBox.Show("unknown data type at position:" & n.ToString() & vbCrLf & oldrow(n).GetType.ToString()) End If Next ' make sure we assign the primary key an appropriate value, using a property for this, see the code near top ' of form class ' when inserting make sure you assign the primary key value a new value or insert will likely fail if it defaults to ' zero and the primary key has a constraint on it dr("nagentid") = nextPrimaryKeyVal ' add the row and voila mytableref.Rows.Add(dr) ' accept changes is interesting, but in this context it makes the dataset go into an unstable state ' probably because it's meant to work with the normal, i.e. non AdoDb, type of datasets that work with ' normal oledb or sql connections, we are using AdoDb since oledb does not (yet) support ODBC ' drivers like Foxpro, at least, this seems to be the case per my study so far. ' mydataset.AcceptChanges() mytableref = Nothing dr = Nothing bmb = Me.BindingContext(mydataset, Me.mainTableName) bmb.Position = 0 bmb.Position = bmb.Count bmb = Nothing End Sub ' save button Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click Dim mytableref As DataTable = mydataset.Tables(Me.mainTableName) Dim n, n2 As Integer Dim agent_id_string As String Dim sql_update_string As String Dim rows_affected As Long Dim item_str As String Dim column_type As String Dim div1, div2 As String Dim val_list, col_list As String Dim error_string As String = "" For n = 0 To mytableref.Rows.Count - 1 If debugging Then 'MessageBox.Show("row:" + n.ToString() + ":" + mytableref.Rows(n).RowState.ToString()) End If If mytableref.Rows(n).RowState.ToString() <> "Unchanged" Then If mytableref.Rows(n).RowState.ToString() = "Deleted" Then agent_id_string = mytableref.Rows(n).Item(0, DataRowVersion.Original) sql_update_string = "delete from " & mainTableName & " where " & primaryKeyColumnName & " = " & agent_id_string If showUpdateCommands Then MessageBox.Show(sql_update_string, "delete command") End If ElseIf mytableref.Rows(n).RowState.ToString() = "Modified" Then agent_id_string = mytableref.Rows(n).Item(0, DataRowVersion.Original) sql_update_string = "update " & mainTableName & " where " & primaryKeyColumnName & " = " & agent_id_string & " set " ' start with 1 rather than 0 because no need to update the primary key to same value it already is For n2 = 1 To mytableref.Columns.Count() - 1 sql_update_string &= mytableref.Columns(n2).ColumnName & " = " column_type = mytableref.Columns(n2).DataType.ToString() Try sql_update_string &= format1.sql_formatted(column_type, mytableref.Rows(n).Item(n2)) Catch ex As Exception MessageBox.Show("type:" & column_type.ToString() & vbCrLf & "exception:" & ex.ToString()) End Try If n2 < mytableref.Columns.Count() - 1 Then sql_update_string &= " , " End If Next If showUpdateCommands Then MessageBox.Show(sql_update_string, "update command") End If ElseIf mytableref.Rows(n).RowState.ToString() = "Added" Then agent_id_string = mytableref.Rows(n).Item(0) col_list = "" : val_list = "" For n2 = 0 To mytableref.Columns.Count() - 1 column_type = mytableref.Columns(n2).DataType.ToString() col_list &= mytableref.Columns(n2).ColumnName val_list &= format1.sql_formatted(column_type, mytableref.Rows(n).Item(n2)) If n2 < mytableref.Columns.Count() - 1 Then col_list &= " , " : val_list &= " , " End If Next sql_update_string = "insert into " & mainTableName & " (" & col_list & ") values (" & val_list & ")" If showUpdateCommands Then MessageBox.Show(sql_update_string, "insert command") End If Else MessageBox.Show("weird type:" & mytableref.Rows(n).RowState.ToString()) End If ' now that we have the execute string, try to execute it Try adoCn.Open(connstring) adoCn.Execute(sql_update_string, rows_affected) If rows_affected <> 1 Then error_string &= "agent id:" & agent_id_string & " changes were not successfully added to the table" & vbCrLf End If Catch ex As Exception error_string &= "agent id:" & agent_id_string & " changes were not successfully added to the table" & vbCrLf ' if a driver type error (e.g. primary index violated) or similar ODBC errors grab the error also for convenience If ex.ToString.indexof("ODBC") <> -1 Then Dim xpos As Integer xpos = ex.ToString.indexof("ODBC") error_string &= ex.ToString().Substring(xpos - 1, 90) & vbCrLf End If Finally ' rarely but in certain cases closing also causes an exception, so wrap this also Try adoCn.Close() Catch ex As Exception MessageBox.Show("Error on connection close:" & ex.ToString()) End Try End Try End If Next If error_string <> "" Then MessageBox.Show("Some error(s) occurred in save:" & vbCrLf & error_string, "Errors Encountered") Else MessageBox.Show("Changes saved successfully") End If ' after a save, successful or not, we should make the grid reflect the actual table values, this also clears the ' flags so we don't end up resaving info, for the rowstate property for example. refreshData() End Sub ' delete button, uses the rows.delete() method Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click Dim mytableref As DataTable = mydataset.Tables(Me.mainTableName) bmb = Me.BindingContext(mydataset, Me.mainTableName) Dim agent_string As String = mytableref.Rows(bmb.Position).Item(0) If MessageBox.Show("delete " & Me.genericItemName & " " & agent_string & "?", "delete?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then mytableref.Rows(bmb.Position()).Delete() End If End Sub ' this is just an example of how to enumerate through the dataset to get field values, or possibly change them Private Sub SampleEnum_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SampleEnum.Click Dim table1 As DataTable Dim row1 As DataRow For Each table1 In mydataset.Tables() MessageBox.Show("sample enumeration for table:" & table1.TableName) For Each row1 In table1.Rows() If MessageBox.Show(row1.Item(0), "first field value", MessageBoxButtons.OKCancel, _ MessageBoxIcon.Asterisk) = DialogResult.Cancel Then Exit For End If Next Next End Sub Private Sub refreshData() ' always surround data opening stuff with try catch blocks Try ' connect to the database with the table adoCn.Open(connstring) ' grab the data, in this case the agents table adors.Open("select * from " & mainTableName & " order by " & orderClause, adoCn, _ ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic) mydataset.Clear() ' fill a dataset with the query results via a dataadapter ' this fill method is overloaded to work with an old recordset, but note that it closes the cursor immediately, so no ' other actions like the above do until loop can now be executed after this call myadapter.Fill(mydataset, adors, Me.mainTableName) ' this is important, make sure the primary key value is readonly or updates will be messed up if they change it, doing it ' here instead of via the controls is more efficient, this will make it readonly in both the textbox and the grid controls mydataset.Tables(0).Columns(primaryKeyColumnIndex).ReadOnly = True ' this doesn't change the grid, I was experimenting, grid header columns will only show the raw sql fields per now mydataset.Tables(0).Columns(primaryKeyColumnIndex).Caption = "Agent ID" ' bind the grid to the dataset mygrid.SetDataBinding(mydataset, Me.mainTableName) Catch ex As Exception MessageBox.Show(ex.Message) Finally 'adors.Close() - this causes a runtime, remember Fill closes the recordset automatically adoCn.Close() End Try End Sub ' method to handle currentCellChanged for grid, for testing Private Sub chgindex(ByVal sender As Object, ByVal e As EventArgs) _ Handles mygrid.CurrentCellChanged ' MessageBox.Show("changed") ' MessageBox.Show(mygrid.Item(mygrid.CurrentRowIndex, 0)) ' MessageBox.Show(mygrid.CurrentRowIndex.ToString()) ' TextBox1.Text = mygrid.CurrentRowIndex.ToString() ' mygrid. ' TextBox2.Text = "bb" End Sub ' this button lets them revert to the current version of the table without saving changes Private Sub cmdRevert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRevert.Click If MessageBox.Show("Revert to current table values, losing any changes you may have made?", _ "Revert", MessageBoxButtons.YesNo) = DialogResult.Yes Then refreshData() End If End Sub Protected Overrides Sub OnResize(ByVal e As System.EventArgs) MyBase.OnResize(e) Me.TabControl1.Width = Math.Max(Me.Width() - 20, 10) Me.TabControl1.Height = Math.Max(Me.Height - 90, 10) Me.mygrid.Width = Math.Max(Me.Width() - 30, 10) Me.mygrid.Height = Math.Max(Me.Height - 120, 10) 'MessageBox.Show("Form resize") End Sub End Class ' formatter class has some useful functions in it, related to translating dataset data types for values ' to something that would work in an sql statement Public Class Formatter ' Public Function sql_formatted(ByVal column_type As String, ByVal text_data_bytes As XXX) As String ' i overload this function in various versions below ' note that the Items value can be of any type, so using a simple ToString method call ' and making just one version of this function won't work because some of these types, like a byte() array ' don't have a ToString method. So I'm using different function versions to solve this. You could also use ' a type check in the calling logic, checking the type of the Item, but this way the code is simpler from the ' calling procedures and the extra stuff is handled in the class (also by breaking it up like this below ' 'you could do special things for ' the different types, like if you wanted double to display differently - if you were translating this to deal with ' some other database language for example). ' the byte array version (for Foxpro binary character and binary memo fields) is currently not supported Public Function sql_formatted(ByVal column_type As String, ByVal text_data_bytes As Byte()) As String MessageBox.Show("warning, binary data types not supported, binary data will not be saved") Return "[]" ' there should be a way to translate the byte array to a string for an insert or update but haven't puzzled it ' through yet. If False Then Dim mstr As String = "" Dim n As Int32 For n = 0 To text_data_bytes.Length - 1 mstr = mstr & text_data_bytes(n) Next Return sql_formatted(column_type, mstr) End If End Function Public Function sql_formatted(ByVal column_type As String, ByVal text_data_bytes As Byte) As String MessageBox.Show("call to normal byte") Return "" End Function Public Function sql_formatted(ByVal column_type As String, ByVal text_data As Double) As String Return sql_formatted(column_type, text_data.ToString()) End Function Public Function sql_formatted(ByVal column_type As String, ByVal text_data As DateTime) As String Return sql_formatted(column_type, text_data.ToShortDateString()) End Function Public Function sql_formatted(ByVal column_type As String, ByVal text_data As Int32) As String Return sql_formatted(column_type, text_data.ToString()) End Function Public Function sql_formatted(ByVal column_type As String, ByVal text_data As Boolean) As String Return sql_formatted(column_type, text_data.ToString()) End Function ' formats the appropriate column to the appropriate text for an insert or update, for example ' a string is quoted with brackets [string] (this is standard foxpro separator, you could also use quotes but this way you can have ' quotes in the string itself). a numeric type is returned with quotes. a logical is translated to an appropriate foxpro value ' e.g. .F. or .T. without quotes. Public Function sql_formatted(ByVal column_type As String, ByVal text_data As String) As String Dim div1, div2 As String Dim item_str As String item_str = text_data If (column_type = "System.Decimal") Then div1 = "" : div2 = "" ElseIf (column_type = "System.String") Then div1 = "[" : div2 = "]" ' foxpro true/false values are .T./.F. represented change this manually ElseIf (column_type = "System.Boolean") Then If item_str = "False" Then item_str = ".F." Else item_str = ".T." End If div1 = "" : div2 = "" ElseIf (column_type = "System.DateTime") Then div1 = "{" : div2 = "}" MessageBox.Show("date format:" & item_str) ElseIf (column_type = "System.Double") Then div1 = "" : div2 = "" ElseIf (column_type = "System.Int32") Then div1 = "" : div2 = "" Else MessageBox.Show("weird type:" & column_type) End If Return div1 & item_str.Trim() & div2 End Function End Class


Back to Portfolio