none
Update database using DataGridView with ODBC RRS feed

  • Question

  • I am using MySQL ODBC 5.1 Driver to build WindowsForm Application. I use DataGridView control to display/edit/update data table.

    So far, display and edit works fine with this code:
    ========================================================
    string selCmd = "select * from " + selectCommand;
    // Create a new data adapter based on the specified query.
    dataAdapter = new OdbcDataAdapter(selCmd, ConStr);

    // Populate a new data table and bind it to the BindingSource.
    DataTable table = new DataTable();

    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
    dataAdapter.Fill(table);
    bindingSource1.DataSource = table;
    ========================================================

    But when I try to update table with this code, it doesn't work:
    ========================================================
    // Update the database with the user's changes.
    dataAdapter.Update((DataTable)bindingSource1.DataSource);
    ========================================================

    Just wondering if "OdbcDataAdapter Update method works by itself through MySQL ODBC driver" or "I have to build update method to update MySQL data table"? How to perform the update?

    Thanks!

    Regards,
    Kevin
    Saturday, November 1, 2008 12:37 AM

Answers

  • Maybe you can try using the same code and MS ODBC driver against SQL server to see if the problem is specific to MySQL?

    Sunday, November 2, 2008 5:30 PM
  • ' I am using ORACLE database with ODBC. It works satisfactorily. I can insert or update database
    ' using datagridview. Following is the code I use. Ensure that the table has a primary key. If not error message will come.
    ' Do necessary changes to work with MySQL
    ' Visit http://www.articlescloud.com for worked examples.
    '



    Imports
    System

    Imports System.Data

    Imports System.Data.Odbc

    Imports System.Windows.Forms

    Public Class Form1

    Inherits System.Windows.Forms.Form

    Private bindingSource1 As New BindingSource()

    Private dataAdapter As New Odbc.OdbcDataAdapter()

    <STAThreadAttribute()> _

    Public Shared Sub Main()

    Application.Run(New Form1())

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim cn As Odbc.OdbcConnection

    Dim connectionString As String = _

    "DSN=scomp;" & _

    "Server=10.0.0.119;" & _

    "Uid=scomp;" & _

    "Pwd=scomp"

    cn = New Odbc.OdbcConnection(connectionString)

    cn.Open()

    Dim cmd As New Odbc.OdbcCommand("select * from COMMITMENT_REGISTER_PAYMENTS", cn)

    Me.dataAdapter = New Odbc.OdbcDataAdapter(cmd)

    ' With dataGridView1.ColumnHeadersDefaultCellStyle

    With DataGridView1

    .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

    End With

    Dim commandBuilder As New Odbc.OdbcCommandBuilder(Me.dataAdapter)

    Dim table As New DataTable()

    table.Locale = System.Globalization.CultureInfo.InvariantCulture

    Me.dataAdapter.Fill(table)

    Me.bindingSource1.DataSource = table

    Me.DataGridView1.DataSource = Me.bindingSource1

    Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua

    Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

    Dim data As New DataSet()

    data.Locale = System.Globalization.CultureInfo.InvariantCulture

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim table As New DataTable()

    Me.bindingSource1 = Me.DataGridView1.DataSource

    table = Me.bindingSource1.DataSource

    Me.dataAdapter.Update(table)

    End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    Me.Close()

    End Sub



    End Class

    Sunday, March 15, 2009 5:53 AM

All replies

  • Hi Kevin,

     

    This is a SQL Server Data Access Forum - I'm afraid that it's not the best place to post MySQL questions.

    Saturday, November 1, 2008 3:27 AM
  • Probably I am wrong with MySQL side, but just want to make sure microsoft stuff works fine with my code because I can not figure out the problem from MySQL side.

    Could you please give a comment on my code from microsoft side?

    Thanks.
    Saturday, November 1, 2008 9:33 AM
  • Maybe you can try using the same code and MS ODBC driver against SQL server to see if the problem is specific to MySQL?

    Sunday, November 2, 2008 5:30 PM
  •  

    Have you specified the UpdateCommand for your DataAdapter?
    Monday, November 3, 2008 1:42 AM
  • ' I am using ORACLE database with ODBC. It works satisfactorily. I can insert or update database
    ' using datagridview. Following is the code I use. Ensure that the table has a primary key. If not error message will come.
    ' Do necessary changes to work with MySQL
    ' Visit http://www.articlescloud.com for worked examples.
    '



    Imports
    System

    Imports System.Data

    Imports System.Data.Odbc

    Imports System.Windows.Forms

    Public Class Form1

    Inherits System.Windows.Forms.Form

    Private bindingSource1 As New BindingSource()

    Private dataAdapter As New Odbc.OdbcDataAdapter()

    <STAThreadAttribute()> _

    Public Shared Sub Main()

    Application.Run(New Form1())

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim cn As Odbc.OdbcConnection

    Dim connectionString As String = _

    "DSN=scomp;" & _

    "Server=10.0.0.119;" & _

    "Uid=scomp;" & _

    "Pwd=scomp"

    cn = New Odbc.OdbcConnection(connectionString)

    cn.Open()

    Dim cmd As New Odbc.OdbcCommand("select * from COMMITMENT_REGISTER_PAYMENTS", cn)

    Me.dataAdapter = New Odbc.OdbcDataAdapter(cmd)

    ' With dataGridView1.ColumnHeadersDefaultCellStyle

    With DataGridView1

    .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

    End With

    Dim commandBuilder As New Odbc.OdbcCommandBuilder(Me.dataAdapter)

    Dim table As New DataTable()

    table.Locale = System.Globalization.CultureInfo.InvariantCulture

    Me.dataAdapter.Fill(table)

    Me.bindingSource1.DataSource = table

    Me.DataGridView1.DataSource = Me.bindingSource1

    Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua

    Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

    Dim data As New DataSet()

    data.Locale = System.Globalization.CultureInfo.InvariantCulture

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim table As New DataTable()

    Me.bindingSource1 = Me.DataGridView1.DataSource

    table = Me.bindingSource1.DataSource

    Me.dataAdapter.Update(table)

    End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    Me.Close()

    End Sub



    End Class

    Sunday, March 15, 2009 5:53 AM
  • Figured I'd bump this since I figured it out (It may help someone somewhere even though it's old):

                cnn.Open();
                using (OdbcCommand columnQuery = new OdbcCommand(queryString, cnn))
                {
                    using (OdbcDataReader reader = columnQuery.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(0) && !reader.IsDBNull(1))
                            {
                                string[] CGSRow = { reader.GetString(0), reader.GetString(1) };

                                dataGridView1.Rows.Add(CGSRow);
                            }
                        }
                    }
                }
                
                cnn.Close();  

    Just make sure you manually add the columns first in the design editor. ;)

     

    Wednesday, June 16, 2010 2:28 PM