Solved

Need Help updating a MS Access table from a DataGrid

Posted on 2007-04-07
5
217 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
ID: 18870466
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
ID: 18870515
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
ID: 18870521
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
ID: 18870527
ps sorry, I didnt see that someone already posted an answer.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18870615
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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