?
Solved

Can't Update DataGrid

Posted on 2005-04-25
5
Medium Priority
?
268 Views
Last Modified: 2006-11-18
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
Comment
Question by:deshi777
  • 2
  • 2
5 Comments
 
LVL 13

Assisted Solution

by:Corey Scheich
Corey Scheich earned 750 total points
ID: 13861781
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
 
LVL 16

Accepted Solution

by:
RobertRFreeman earned 750 total points
ID: 13862638
Here are some tips on easily using dataadapters on a form.

http://vsnetdatabinding.blogspot.com/
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 13862656
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
 
LVL 1

Author Comment

by:deshi777
ID: 13890930
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
 
LVL 1

Author Comment

by:deshi777
ID: 13891920
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline
Suggested Courses

862 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