• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Can't Update DataGrid

I have a working DataGrid showing data from my datasource, but I can't update it.  It lets me change various cell values, but the changes aren't actually reflected in the datasource itself.  

This is a single form application and I'm intentionally using the ODBC .NET Data Provider to connect to a MySQL back end (although I tried this code with MS-Access and it still doesn't allow updates so please no messages about configuring MySQL -- thanks).

Hopefully this is something simple, but probably not.  Seems like to do anything in VB.NET it takes a vast amount of code.  Oh, well, suppose that's the price for having "managed code".

Thanks for the help!

Below is my code:


Imports Microsoft.Data.ODBC

Public Class frmTest
    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 DataGrid1 As System.Windows.Forms.DataGrid
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.DataGrid1.CaptionVisible = False
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(16, 16)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(468, 188)
        Me.DataGrid1.TabIndex = 3
        '
        'frmTest
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(528, 325)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "frmTest"
        Me.Text = "frmTest"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub frmTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DS As DataSet = New DataSet
        Dim dsn as String, sql As String
        dsn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=mydb;USER=root;PWD=root;OPTION=3;"
        sql = "SELECT * FROM MYTABLE"

        Dim DAdapt As OdbcDataAdapter = New OdbcDataAdapter(sql, strLibraryDSN)
        DAdapt.Fill(DS, "Main")  ' fill dataset

        ' DataGrid1 Properties ...
        DataGrid1.DataSource = DS.DefaultViewManager
        DataGrid1.DataMember = "Main"

    End Sub
End Class
0
deshi777
Asked:
deshi777
  • 2
  • 2
2 Solutions
 
Corey ScheichDeveloperCommented:
you have to set up an ODBCDataAdapter to work between your dataset and the datasource.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataodbcodbcdataadapterclasstopic.asp

You can drop it on the form and use

DataAdapter.Fill to fill your dataset
and
DataAdapter.Update to save back to the source.

Corey2
0
 
RobertRFreemanCommented:
Here are some tips on easily using dataadapters on a form.

http://vsnetdatabinding.blogspot.com/
0
 
RobertRFreemanCommented:
For an even easier method, just create a new connection on your ServerExplorer toolbar in VS.net.

Then you can drag and drop a table from mySQL.  This will create an adapter with all the insert update delete and select statements.  Then just go through the tutorial starting with generating the dataset.
0
 
deshi777Author Commented:
Corey and Robert, I am using a data adapter already (please look at my code).  None of the examples seem to fit what I need.

Does anyone know of a working example of a datagrid that uses the ODBC .NET Data Provider?

VB.NET is extremely frustrating and very poorly documented!  Grrrrr!

0
 
deshi777Author Commented:
Pretty much figured it out by trial and error (will split the points since the suggested links sent me on my way to finding the answer I needed).

Here's what I did:
1. Had to pull some of the variables out of the Form_Load event and make them visible to the entire Class (form) by Dim'ming them at the top of my code (Dim DAdapt, Dim DS, and Dim custCB).
2. Had to add the following line: custCB = New OdbcCommandBuilder(DAdapt)
3. Added a command button to fire the update to the data source (see code).

Here's the working solution's source code:


Imports Microsoft.Data.ODBC

Public Class frmTest
    Inherits System.Windows.Forms.Form

    Dim DAdapt As OdbcDataAdapter
    Dim DS As DataSet = New DataSet
    Dim custCB As OdbcCommandBuilder

#Region " Windows Form Designer generated code "
<... excluded for space ... copy from my initial post ...>
#End Region

    Private Sub frmTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dsn As String, sql As String
        dsn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=mydb;USER=root;PWD=root;OPTION=3;"          
        sql = "SELECT * FROM MYTABLE"
        DAdapt = New OdbcDataAdapter(sql, dsn)
        custCB = New OdbcCommandBuilder(DAdapt)
        DAdapt.Fill(DS, "Main")   ' fill dataset

        ' DataGrid1 Properties ...
        DataGrid1.DataSource = DS.DefaultViewManager
        DataGrid1.DataMember = "Main"

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DAdapt.Update(DS, "Main")   ' fill dataset
    End Sub
End Class
0
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.

Join & Write a Comment

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now