Database / XML / Reporting Topics include: ADO.NET, SQL, XML structures and schemas, Crystal Reports, and other reporting tools

Go Back  Xtreme .NET Talk > .NET > Database / XML / Reporting > MySQL Dataadapter - Problems since Mysql-Update


Reply
 
Thread Tools Display Modes
  #1  
Old 03-20-2012, 07:16 AM
obi obi is offline
Newcomer

Preferred language:
VB.NET
 
Join Date: Mar 2012
Posts: 2
obi is on a distinguished road
Default MySQL Dataadapter - Problems since Mysql-Update



Hi...

I'm running an onlineshop on an Ubuntu-Server and wrote a program to update the shop-database (MySQL) with data from internal SQL-Server DB.

Since I updated MySQL on the Ubuntu-Server from 5.1.41 to 5.1.61 (that update came automatically for security reasons) i have a weird problem.

First: every access to the MySQL-DB vie the shop(s) and from our CMS (Drupal) works perfect. No Problems at all.

For Speed-Reasons I use datatables/dataadapter to update the shop data in memory and write it back to MySQL after all neccessary updates/inserts/deletes are done. This worked perfect until the MySQL-Update.

Here is some sample code from my program that produces the following problem:
The first run works perfect, if the code is called a second time, the result from mysql for oxadapter.fill is empty (0 records) but there are DEFINITELY records in the DB. So on the second run all records coming from the internal sql-server are treated as new records and at the update i get a duplicate key. Why is the MySQL-Result empty? And why this happens just now since MySQL is updated?

Code:
...
...
    Private SteinConn As SqlConnection
    Private SteinSQL As SqlCommand
    Private SteinAdapter As SqlDataAdapter

    Private OxConn As MySqlConnection
    Private OxSQL As MySqlCommand
    Private OxAdapter As MySqlDataAdapter
    Private OxCB As MySqlCommandBuilder
...
...
...


Private Sub text()
        Dim dtStein As New DataTable
        Dim dtOxid As New DataTable


        ' connect databases
        SteinConn = New SqlConnection(strConnectSQL)        
        OxConn = New MySqlConnection(strConnectMYSQL)
        SteinConn.Open()                ' SQL-Server
        OxConn.Open()                   ' MySQL

        ' get data from SQL-Server
        SteinAdapter = New SqlDataAdapter("SELECT * FROM dbo.xxxxxx WHERE ShopNumber = 2", SteinConn)
        SteinAdapter.SelectCommand.CommandTimeout = 120
        SteinAdapter.Fill(dtStein)

        ' get data from mysql
        OxAdapter = New MySqlDataAdapter("SELECT aa,bb,cc,dd FROM aaa", OxConn)
        OxAdapter.SelectCommand.CommandTimeout = 120
        OxAdapter.Fill(dtOxid)


        ' here follows code to update the data in dtoxid
        ...
        ...
        ...


        ' update mysql
        OxCB = New MySqlCommandBuilder(OxAdapter)
        OxAdapter.InsertCommand = OxCB.GetInsertCommand
        OxAdapter.UpdateCommand = OxCB.GetUpdateCommand
        OxAdapter.DeleteCommand = OxCB.GetDeleteCommand
        OxAdapter.Update(dtOxid)

        OxConn.Close()
        SteinConn.Close()
…nd Sub
This code works perfect if it's called once... if I call it again then dtoxid has 0 rows after "OxAdapter.Fill(dtOxid)".

As stated before this works with the older MySQL-Version I'm using now (5.1.41) but not worked after a security-Patch to 5.1.61

Any Idea?
Reply With Quote
  #2  
Old 03-27-2012, 04:11 AM
obi obi is offline
Newcomer

Preferred language:
VB.NET
 
Join Date: Mar 2012
Posts: 2
obi is on a distinguished road
Default Re: MySQL Dataadapter - Problems since Mysql-Update

The problem is still alive... no ideas anyone?
Reply With Quote
  #3  
Old 05-30-2012, 03:04 AM
allblacks allblacks is offline
Newcomer
 
Join Date: May 2012
Posts: 1
allblacks is on a distinguished road
Default Re: MySQL Dataadapter - Problems since Mysql-Update

Hi ,

I have the same probleme, i upgrade mysql 5.0 to 5.1 yesterday and when i use my dataadapter it return nothing. ( i upgrade to 5.5 and this is the same)

My problem :
First i select the data to show them (no problem)
Second i modify one value and i update (still no problem)
Third i select the data to show the modify ( Return nothing)

Have you a solution ?

(sorry for my english)
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MYSQL and C# LogicalPerson Database / XML / Reporting 0 07-31-2008 01:03 AM
Cannot update MySQL database mihaiguran Database / XML / Reporting 2 05-06-2004 09:19 AM
MySQL ADO.NET blob dataadapter: How can I do it? normth Database / XML / Reporting 1 07-30-2003 12:53 AM
MySQL Mothra Database / XML / Reporting 10 06-16-2003 01:05 AM

Advertisement:







Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe