MySQL Bugs: #64008: 2 SQL statements no changes getting through

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

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.

Please, send exact CREATE TABLE statements for all tables involved, to begin with...

sample of the type of code I have used

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

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.

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.

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();
  }
}

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".

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?