Solved

Trouble binding C1FlexGrid to MySQL ODBC Driver - reads but won't update

Posted on 2010-11-18
6
936 Views
Last Modified: 2012-05-10
I can't figure out how to get my C1 FlexGrid (2.6) to update table data in the underlying database connected via an ODBC DSN.

The grid displays the data as expected and allows me to change it but when I reopen the program, the changes are gone. I use the same ODBC DSN in other apps (VB6 with a good old vsFlexGrid) and can update the data there.

I'm using version 5.1 of the MySQL ODBC driver on a Win 7 x64 machine and VS 2010 / Visual Basic

I can't get this simple windows form application with a C1FlexGrid bound to a DataTable to update the database.

Any tips as to what I am doing wrong would be appreciated.

Here's the relevant code:

'Grid is a control on the form of type C1.Win.C1FlexGrid

Private oConnOdbc As OdbcConnection = New OdbcConnection("Provider=MSDASQL;DSN=IA CRM")

Private Sub Form1_Load...

Dim oAdapter As New OdbcDataAdapter() 
oAdapter.SelectCommand = New OdbcCommand("Select * From Testing", oConnOdbc) 

Dim oDataTable As New DataTable 
oAdapter.Fill(oDataTable)

Grid.DataSource = oDataTable

End Sub 

Private Sub Form1_FormClosing... 

' from what I have read, this shouldn't be necessary
Dim oDataTable As DataTable = CType(Grid.DataSource, DataTable) 

oDataTable.AcceptChanges()

End Sub

Open in new window

0
Comment
Question by:ou81aswell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34164967
Calling the AcceptChanges does not save changes to the database. Check this example

http://www.codeguru.com/csharp/.net/net_data/datagrid/article.php/c13041
0
 

Author Comment

by:ou81aswell
ID: 34165407
Thanks but that's for the DataGridView although I'm sure it's similar to the C1FlexGrid (from Component One) in the way that it interacts with the database.

I was hoping to avoid having to write a hundred lines of code in order to get the simplest of simplest grid based editors to work.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34165515
You dont have to write a lot of code. All you have to do is supply the Select command, then you can use the CommandBuilder to automatically produce the Insert, Update, and Delete commands for you. Then when you want to commit changes to the db, it only takes a function call

dataadapter.Update(tablename)
0
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

Author Comment

by:ou81aswell
ID: 34165713
I'm not familiar with the command builder. I will look into it. Thanks.

I guess my problem is that I'm going by examples (provided by Component One) that simply setup a DataTable and assign it to the grid's datasource property. They make no mention of data adapters or readers even though the examples are in VB.NET. Perhaps I'm looking at the wrong examples. I'm coming from ADO in VB6 land with the good old MS/VS FlexGrid ActiveX and so I'm extra confused!

I guess I need to study the code in the first link you sent but it's frustrating having spent so much time on the most trivial task. I was hoping someone could suggest what needs to be added to my code because I've been chasing my tail looking at all sorts of examples like this which led me to post my question here.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 34165870
You can try by changing this code

Dim oAdapter As New OdbcDataAdapter()
oAdapter.SelectCommand = New OdbcCommand("Select * From Testing", oConnOdbc)


to this

Dim oAdapter As New OdbcDataAdapter()
oAdapter.SelectCommand = New OdbcCommand("Select * From Testing", oConnOdbc)
Dim builder as New ODBCCommandBuilder(oAdapter) 'This is all you need to generate the commands.

Then on a button click (save button)

oAdapter.UpdateAll()
0
 

Author Comment

by:ou81aswell
ID: 34170563
The CommandBuilder worked but the OdbcDataAdapter doesn't have an UpdateAll method so I used  oAdapter.Update(oDataTable).
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

630 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