Solved

Need Help updating a MS Access table from a DataGrid

Posted on 2007-04-07
5
215 Views
Last Modified: 2010-04-23
Hi,
I need some help here - I am using VB.NET 2003 and trying to create a form to allow the user to rename string values from certain fields of a MS Access database table that I am displaying in a DataGrid. At this point all I want to show the user are two columns and allow him to edit only one (NameLong also known as "Sensor Name").
My code is below. I don't get any errors when I run this but the database table does not get updated either.  I can type in a new name and when I click the SaveClose button I am returned to my previous form but nothing else happens: no errors ans alsono updates to the database table.  I know I am missing something but I just can't figure out what.

Please take a look at my code below and point me in the right direction.

Thanks,
Charlie

Public Class frmRenameSensor
    Inherits System.Windows.Forms.Form
    ' constr is the connection string for the system database
    ' constr is defined in ProjConst.vb code
    Dim conn As New OleDb.OleDbConnection(constr)
    Dim daTC As New OleDb.OleDbDataAdapter("SELECT SensorID, NameLong, NameShort, OrderNum, IsSensor FROM AIN_Definition WHERE IsSensor=FALSE ORDER BY OrderNum", conn)

    Dim dsTC As New DataSet
    Dim dtTC As New DataTable


#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 datGrdRenameSensor As System.Windows.Forms.DataGrid
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents cmdSaveClose As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.datGrdRenameSensor = New System.Windows.Forms.DataGrid
        Me.Label1 = New System.Windows.Forms.Label
        Me.cmdSaveClose = New System.Windows.Forms.Button
        CType(Me.datGrdRenameSensor, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'datGrdRenameSensor
        '
        Me.datGrdRenameSensor.DataMember = ""
        Me.datGrdRenameSensor.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.datGrdRenameSensor.Location = New System.Drawing.Point(34, 72)
        Me.datGrdRenameSensor.Name = "datGrdRenameSensor"
        Me.datGrdRenameSensor.Size = New System.Drawing.Size(440, 272)
        Me.datGrdRenameSensor.TabIndex = 0
        '
        'Label1
        '
        Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(148, 16)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(184, 32)
        Me.Label1.TabIndex = 1
        Me.Label1.Text = "Rename Thermocouples"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'cmdSaveClose
        '
        Me.cmdSaveClose.Location = New System.Drawing.Point(348, 368)
        Me.cmdSaveClose.Name = "cmdSaveClose"
        Me.cmdSaveClose.Size = New System.Drawing.Size(126, 30)
        Me.cmdSaveClose.TabIndex = 2
        Me.cmdSaveClose.Text = "Save && Close"
        '
        'frmRenameSensor
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(512, 430)
        Me.ControlBox = False
        Me.Controls.Add(Me.cmdSaveClose)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.datGrdRenameSensor)
        Me.Name = "frmRenameSensor"
        Me.Text = "Rename Sensors"
        CType(Me.datGrdRenameSensor, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub cmdSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSaveClose.Click

        daTC.Update(dsTC.Tables(0))
        dsTC.Tables(0).AcceptChanges()

        'Dispose of the current form
        Me.Dispose()
        'Return to the Options Selection form
        myFormLibrary.frmOpSelect.Show()

    End Sub

    Private Sub frmRenameSensor_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.CenterToScreen()

        daTC.Fill(dsTC)
        daTC.Fill(dtTC)
        datGrdRenameSensor.DataSource = dtTC
        Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daTC)

        SetupTCNames()

    End Sub

    Public Sub SetupTCNames()

        datGrdRenameSensor.CaptionText = "Thermocouple Sensor Channels"

        Dim gs As New DataGridTableStyle
        gs.AllowSorting = False
        gs.MappingName = dtTC.ToString
        gs.HeaderBackColor = Color.DarkBlue
        gs.HeaderForeColor = Color.White
        gs.RowHeadersVisible = True

        Dim gsTextCol1 As New DataGridTextBoxColumn
        With gsTextCol1
            .MappingName = "SensorID"
            .HeaderText = "Sensor Tag"
            .ReadOnly = True
            .Width = 75
        End With
        gs.GridColumnStyles.Add(gsTextCol1)

        Dim gsTextCol2 As New DataGridTextBoxColumn
        With gsTextCol2
            .MappingName = "NameLong"
            .HeaderText = "Sensor Name"
            .ReadOnly = False
            .Width = 275
        End With
        gs.GridColumnStyles.Add(gsTextCol2)

        datGrdRenameSensor.TableStyles.Add(gs)

        datGrdRenameSensor.Update()

    End Sub
End Class
0
Comment
Question by:charlieb01
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
Comment Utility
This looks to be your problem

From Sub frmRenameSensor_Load

        daTC.Fill(dtTC)
        datGrdRenameSensor.DataSource = dtTC

From Sub cmdSaveClose_Click

        daTC.Update(dsTC.Tables(0))
        dsTC.Tables(0).AcceptChanges()

In the first snippet of code you are filling, and binding your datagrid to, the free-standing datatable dtTC.  In the second snippet you are updating from dsTC.Tables(0).  You could overcome the specific problem by changing the second snippet to

        daTC.Update(dtTC)
        dtTC.AcceptChanges()

But I don't understand why you should have, and fill with the same dataadapter, both a datatable in a dataset - dsTC.Tables(0) - and a free-standing datatable - dtTC.  The latter would be sufficient for the job in hand.  And it is good practice, before calling an .Update, to make sure that any outstanding edits in the datagrid are committed to the datatable.  So I suggest you add

        BindingContext(dtTC).EndCurrentEdit

as the first line in your Sub cmdSaveClose_Click.  Finally - although it will do no harm - the .AcceptChanges in that sub is really superfluous as the form itself is immediately disposed of so no further use can be made of the datatable anyway.

Roger
0
 

Author Comment

by:charlieb01
Comment Utility
Thanks Roger, That seemd to help - but I also moved the CommandBuilder up to the Form Level. I think this has an effect also.
Here is my revised code that works very well:

Public Class frmRenameSensor
    Inherits System.Windows.Forms.Form
    ' constr is the connection string for the system database
    ' constr is defined in ProjConst.vb code
    Dim conn As New OleDb.OleDbConnection(constr)
    'break up the query so it's easily readable.
    Dim qry1$ = "SELECT SensorID, NameLong, NameShort, OrderNum, IsSensor "
    Dim qry2$ = "FROM AIN_Definition WHERE IsSensor=FALSE ORDER BY OrderNum"
    Dim qry$ = qry1$ & qry2$
    Dim daTC As New OleDb.OleDbDataAdapter(qry$, conn)
    Dim cb As New OleDb.OleDbCommandBuilder(daTC)
    Dim dtTC As New DataTable

#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 datGrdRenameSensor As System.Windows.Forms.DataGrid
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents cmdSaveClose As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.datGrdRenameSensor = New System.Windows.Forms.DataGrid
        Me.Label1 = New System.Windows.Forms.Label
        Me.cmdSaveClose = New System.Windows.Forms.Button
        CType(Me.datGrdRenameSensor, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'datGrdRenameSensor
        '
        Me.datGrdRenameSensor.DataMember = ""
        Me.datGrdRenameSensor.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.datGrdRenameSensor.Location = New System.Drawing.Point(34, 72)
        Me.datGrdRenameSensor.Name = "datGrdRenameSensor"
        Me.datGrdRenameSensor.Size = New System.Drawing.Size(440, 272)
        Me.datGrdRenameSensor.TabIndex = 0
        '
        'Label1
        '
        Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(148, 16)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(184, 32)
        Me.Label1.TabIndex = 1
        Me.Label1.Text = "Rename Thermocouples"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'cmdSaveClose
        '
        Me.cmdSaveClose.Location = New System.Drawing.Point(348, 368)
        Me.cmdSaveClose.Name = "cmdSaveClose"
        Me.cmdSaveClose.Size = New System.Drawing.Size(126, 30)
        Me.cmdSaveClose.TabIndex = 2
        Me.cmdSaveClose.Text = "Save && Close"
        '
        'frmRenameSensor
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(512, 430)
        Me.ControlBox = False
        Me.Controls.Add(Me.cmdSaveClose)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.datGrdRenameSensor)
        Me.Name = "frmRenameSensor"
        Me.Text = "Rename Sensors"
        CType(Me.datGrdRenameSensor, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub cmdSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSaveClose.Click

        BindingContext(dtTC).EndCurrentEdit()
        daTC.Update(dtTC)
        Me.Dispose()                        'dispose of the current form
        myFormLibrary.frmOpSelect.Show()    'return to the Options Selection Form
 End Sub

    Private Sub frmRenameSensor_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.CenterToScreen()
        daTC.Fill(dtTC)
        dtTC.DefaultView.AllowNew = False
        datGrdRenameSensor.DataSource = dtTC

        SetupTCNames()

    End Sub

    Public Sub SetupTCNames()

        datGrdRenameSensor.CaptionText = "Thermocouple Sensor Channels"

        Dim gs As New DataGridTableStyle
        gs.AllowSorting = False
        gs.AlternatingBackColor = Color.LightBlue
        gs.MappingName = dtTC.ToString
        gs.HeaderBackColor = Color.DarkBlue
        gs.HeaderForeColor = Color.White
        gs.RowHeadersVisible = True

        Dim gsTextCol1 As New DataGridTextBoxColumn
        With gsTextCol1
            .MappingName = "SensorID"
            .HeaderText = "Sensor Tag"
            .ReadOnly = True
            .Width = 75
        End With
        gs.GridColumnStyles.Add(gsTextCol1)

        Dim gsTextCol2 As New DataGridTextBoxColumn
        With gsTextCol2
            .MappingName = "NameLong"
            .HeaderText = "Sensor Name"
            .ReadOnly = False
            .Width = 300
        End With
        gs.GridColumnStyles.Add(gsTextCol2)

        datGrdRenameSensor.TableStyles.Add(gs)

        datGrdRenameSensor.Update()

    End Sub
End Class
0
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
You have declared both dsTC and dtTC, which is not necessary. You should remove one (dsTc, for example), and it will work.

Just to clarify why your code is not working, it is because you are binding datagridview to a datatable

datGrdRenameSensor.DataSource = dtTC

so you need to call DataAdapter's Update method on this datatable too (in cmdSaveClose_Click event)

daTC.Update(dtTC)


Goran
0
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
ps sorry, I didnt see that someone already posted an answer.
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
Charlie

Although I agree it's tidier to put the commandbuilder alongside the instantiation of the dataadapter with its select command, it shouldn't in strict terms be necessary.  CommandBuilders are funny objects which don't seem fully to obey all the normal rules about scope.  Provided that they are used (and before the dataadapter actually needs to use any update commands) in any part of the code which can "see" the dataadapter - that is, which is "in scope" for the dataadapter itself - they work, even though they may themselves be declared with a scope that is not visible to the .Update command when that is called.

Roger

Goran, no problem ;-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now