MySQL Bugs: #44197: DataAdapter not update from DataSet

Bug #44197	DataAdapter not update from DataSet
Submitted:	9 Apr 2009 21:28	Modified:	17 May 2009 8:01
Reporter:	Thomas Lee	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.1.29	OS:	Windows (XP ver 2002, SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	SQL insert command not update DataAdapetr

Description:
'got "massage "CategoryID" cant be NULL", but look products or categories 
'table no data is null. Error occurred at dscProducts.Update() step. 
'Follow other case inform, try ? instead of @n,@ID got same result.
'Same program work for MS Access.

Protected Sub cbApply_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbApply.Click
        ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                   & "server=127.0.0.1;" _
                   & "uid=Thomas;" _
                   & "pwd=system2;" _
                   & "database=northwind;" '_

        Dim aConn As New OdbcConnection(CONNECTIONSTRING)
        Dim SQL As String = "Insert into Products (ProductName, CategoryID)  
                         Values (@n, @ID)"
        'Dim SQL As String = "Insert into Products (ProductName, CategoryID) 
                         Values (?n, ?ID)"
        Dim aParam As OdbcParameter
        Dim aProdRow As DataRow   'for dataset 

        dscProducts.InsertCommand = New OdbcCommand(SQL, aConn)

        'start stat field names for insert
        aParam = dscProducts.InsertCommand.Parameters.Add(New OdbcParameter
                        ("@n", SqlDbType.VarChar))
        With aParam
            .SourceColumn = "ProductName"
            .SourceVersion = DataRowVersion.Current
        End With

        aParam = dscProducts.InsertCommand.Parameters.Add(New OdbcParameter
                       ("@ID", SqlDbType.Int))
        With aParam
            .SourceColumn = "CategoryID"
            .SourceVersion = DataRowVersion.Current
        End With

        'state actual values for insert for @n, @Id
        aProdRow = aDataset.Tables("Products").NewRow
        aProdRow.Item("ProductName") = "BudMeister Stout Ale"
        aProdRow.Item("CategoryID") = 1
        aDataset.Tables("Products").Rows.Add(aProdRow)

        'update the database(dataadapter) from dataset 
        Try
            dscProducts.Update(aDataset, "Products")
            'dscProducts is dataadapter
        Catch eEx As Exception
            Call MsgBox(eEx.Message)
        End Try

        'Call FillTreeView()
        sbStat.Text = "row permanently added to database"

    End Sub

How to repeat:
'add above sub in the following public class
'and check products table is updated directly.
'DataSet is updated if shown in txt box or TreeView

Public Class Form1
    Inherits System.Windows.Forms.Form

    'Const CONNECTIONSTRING As String 
       = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"

    Const PRODID As Integer = 150
    Dim Connectionstring As String

    Dim FMaxProdID As Integer
    Dim aDataset As DataSet
    Dim aDataView As DataView
    Dim dscProducts As OdbcDataAdapter
    Dim dscCategories As OdbcDataAdapter
    Dim aProdRow As DataRow

    Public Sub New()
        MyBase.New()

        Form1 = Me

        'This call is required by the Win Form Designer.
        InitializeComponent()

        ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                   & "server=127.0.0.1;" _
                   & "uid=Thomas;" _
                   & "pwd=system2;" _
                   & "database=northwind;" '_

        Dim aConn As New OdbcConnection(CONNECTIONSTRING)

        dscProducts = New OdbcDataAdapter("Select * from Products", aConn)
        dscCategories = New OdbcDataAdapter("Select * from Categories", aConn)

        aDataset = New DataSet()

        dscProducts.Fill(aDataset, "Products")   'Products is real data table
        dscCategories.Fill(aDataset, "Categories")
        aDataset.Relations.Add("rProdCat", aDataset.Tables("Categories").Columns
     ("CategoryID"), aDataset.Tables("Products").Columns("CategoryID"))
        'set dataset table relation

         End Sub

I am running it in VS 2005 Express Edition, through Visual Basic

Hi Thomas and thanks for your report. I gather you're using MySQL server 5.1.29, right? Also, this is a c/ODBC problem, from what I see, so please tell me which version of c/ODBC you're using.

Thomas, please see my test case in Bug#44199 and adjust your test case to contain all info, even DDL/DML statements.

I am using the ODBC driver 3.51. and use Mysql 5.0 release. The Bug# 44199 test 
case seemed use VB6 not VB.Net.

Thomas, I am aware of the difference, I passed that report to you just to see how to write one... So, repeating myself, Thomas, please see my test case in Bug#44199 and adjust your test case to contain all info, even DDL/DML statements.

Can you attach such test case?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".