Bug #64008 2 SQL statements no changes getting through
Submitted: 12 Jan 2012 10:32 Modified: 18 Jan 2012 7:27
Reporter: dave lilley Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.3.6 & 6.3.8 OS:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: TableAdapters

[12 Jan 2012 10:32] dave lilley
Description:
I have a message in the .net forums asking for help.
I have a posting here http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/e37ea895-b5d8-... in the hopes of finding out why things an't working out.

senario

2 SQL statements
     - Select * from Table
     - Select * from Table where fieldname = @param1

1st sql allows me to fill up a comboBox with valid customer names
2nd sql allows me to get A record and then access two other tables and retieve data from them and to display on screen.

I cannot get changes to the customer table put into the underling table.
All controls are bound to fields.

I'm using VS2010 TableAdapters, datasource objects in the belief they'll save me time in coding, testing etc on the Insert & Update actions.

So far it's proving to be a PAIN.

How to repeat:
I have tonight changed the palcement of the sql statements to this position
   FILL/GETDATA has Select * from table where fieldname = @param1
   FILLBY/GETDATABY have Select * from table.

as shown in the link in Description comments.

Even with the slave tables where they have only ONE SQL statement of "Select * from Table where Fieldname = @param1" was a job to get to work.

I had to do multiple change the .xsd file, Run program, do a change, check to see if it had got to the underlying table loops.

I cannot tell you want i eventually did to get success and now I'm in that same HELL again with 2 simple SQL statements (well i believe they're simple).

I've done it this way because I wanted to be sure that when i write to a Table with changes it is ONLY THAT table that gets hit.

Suggested fix:
make you're documentation clearer as to the fitness of use of the Mysql .net connector TabelAdapter, DataSource, etc controls with regard to multiple FILL/GETDATA, FILLBY/ GETDATABY pairs.

I would explect them to "simply WORK", No head butting as I've had to up with to get the statements to work.
[12 Jan 2012 10:38] Valeriy Kravchuk
Please, send exact CREATE TABLE statements for all tables involved, to begin with...
[15 Jan 2012 9:39] dave lilley
sample of the type of code I have used

Attachment: tableadapter1.zip (application/zip, text), 140.62 KiB.

[15 Jan 2012 9:50] dave lilley
please note the underlying DB is Mysql 5.1 and the Table schemea is as below...

id primary  auto increment
name        varchar(45)
add_nos     int
street_name varchar(45)
suburb      varchar(45)
city        varcar(45)
With the sample code posted before I hope you can expose ...
     A) What I am doing wrong
     b) what is missing in the documentation

ALso note their was a toolstrip component put on my form I noticed for the entry of my @PARAM1.
I deleted this as I cannot see WHY I'd want to use it.
In my program i use the tableadapter.Update(dataset,"this is the param1 value");

dave.
[17 Jan 2012 11:41] Bogdan Degtyariov
Hi Dave,

Thanks for sending the test project.
I was able to repeat the problem using MySQL Connector/NET 6.3.8 and 6.4.4.
[17 Jan 2012 13:24] Bogdan Degtyariov
I have done some more debugging and found a weird thing:

after modifying the data row I see changes inside the dataset, but the RowState remains "Unchanged":

((System.Data.DataRow)(this.custData.custnames[0])).RowState is "Unchanged"

That is why base DbDataAdapter.Update() method does nothing, although it is actually called from MySQL code (dataadapter.cs: lines 168-191):

protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping)
{

  List<MySqlConnection> connectionsOpened = new List<MySqlConnection>();
  try
  {
    // Open connections for insert/update/update commands, if 
    // connections are closed.
    foreach(DataRow row in dataRows)
    {
      OpenConnectionIfClosed(row.RowState, connectionsOpened);
    }

    int ret = base.Update(dataRows, tableMapping);
    return ret;
  }
  finally 
  {
    foreach(MySqlConnection c in connectionsOpened)
      c.Close();
  }
}
[17 Jan 2012 13:43] Bogdan Degtyariov
Looks like the problem in the DataSet or DataRow NET framework classes.
Otherwise I don't know why the row might have "Unchanged" state after the data was actually edited.

I am open to further discussions. Meanwhile setting the status "Not a bug".
[18 Jan 2012 7:27] dave lilley
Quote...

Looks like the problem in the DataSet or DataRow NET framework classes.
Otherwise I don't know why the row might have "Unchanged" state after the data was
actually edited.

I am open to further discussions. Meanwhile setting the status "Not a bug".

End of qoute...

Fine this isn't a bug (but take mild exception to the above statement when their is nothing to say what I've done is wrong in connector documentation I have read) but a usage issue.

Can you correct my sample app so it works as expected? (I haven't in 3/4 weeks of googling various terms using mysql, conector 6.3.8 or 6.3.6, tableadapter mutliple fill/gets in searches).

I am happy to write about this (even put something up in the forums of C# .net connectors here).

Note...
When i created the parameterised query i saw an auto generated striptool component put in my project. Because I deleted it did I create this condition?

Even though I seem a bit negative above I have wasted time trying to get the update query to work (I know I'm not the best programmer but I do endevour to search the internet for ideas on how to solve my problems before I post something forums) I appreciate you're time on this matter greatly.

regards,

Dave.

Can you suggest other resources I could go to for help if you are unable to correct my program to work as expected?