deshi777
asked on
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.ICon
'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.DataG
<System.Diagnostics.Debugg
Me.DataGrid1 = New System.Windows.Forms.DataG
CType(Me.DataGrid1, System.ComponentModel.ISup
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.Anchor = CType((((System.Windows.Fo
Or System.Windows.Forms.Ancho
Or System.Windows.Forms.Ancho
Me.DataGrid1.CaptionVisibl
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeCol
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.DataGri
Me.Name = "frmTest"
Me.Text = "frmTest"
CType(Me.DataGrid1, System.ComponentModel.ISup
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;D
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
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.ICon
'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.DataG
<System.Diagnostics.Debugg
Me.DataGrid1 = New System.Windows.Forms.DataG
CType(Me.DataGrid1, System.ComponentModel.ISup
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.Anchor = CType((((System.Windows.Fo
Or System.Windows.Forms.Ancho
Or System.Windows.Forms.Ancho
Me.DataGrid1.CaptionVisibl
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeCol
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.DataGri
Me.Name = "frmTest"
Me.Text = "frmTest"
CType(Me.DataGrid1, System.ComponentModel.ISup
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;D
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
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;D
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
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;D
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
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.