Need Help updating a MS Access table from a DataGrid

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
charlieb01Asked:
Who is Participating?
I wear a lot of hats...

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

SanclerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
charlieb01Author Commented:
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
Priest04Commented:
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
Priest04Commented:
ps sorry, I didnt see that someone already posted an answer.
0
SanclerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.