MySQL Bugs: #31338: TableAdapter wizard only generates INSERT command

Bug #31338	TableAdapter wizard only generates INSERT command
Submitted:	2 Oct 2007 10:57	Modified:	13 May 2008 7:54
Reporter:	Santiago Santos	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.3	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
When using Data Designer wizard to make a typed TableAdapter based on SQL SELECT command, the INSERT command is correctly generated but DELETE and UPDATE commands not.

MySql server 4.1.21-community-nt
Visual Studio 2005 Professional Edition (Spanish version)

I've tested this on another similar machine with same results. I've used another database and table too.

How to repeat:
 - On MySQL server create a database with this MyISAM table:

     CREATE TABLE `sat_tipos` (
       `ID` int(11) NOT NULL default '0',
       `Nombre` varchar(50) NOT NULL default '',
       PRIMARY KEY  (`ID`)
     ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 - Open VS 2005 and create a new Windows Application project.
 - Add a new DataSet to the project.
 - From toolbox add a TableAdapter (wizard will start automatically).
 - Click on new connection and select MySQL database.
 - Fill in server name, user id, password and database name. Check "Save my password" and click OK.
 - Click Next and select "Use SQL commands".
 - Click Next and type this: "SELECT ID, Nombre FROM sat_tipos".
 - Click Next, all options should be checked.
 - Click Next, here is the problem: only SELECT command and INSERT command are generated.
 - Click Finalize, the following error will appear (translated from spanish):

      TableAdapter not added
      Invalid specified conversion

I think, I have also this problem.. and mabey some others :/

Description:
Some problem with TableAdapter and 'Update' command.

MySql server 4.1.11
.NET 2.0
VS 2005 Professional

How to repeat:
1. Open VS 2005 and create a new Windows Application project.
2. Create connection to DB in Server Explorer
3. In Data Source window 'Add new data source" and next in wizard:
- select 'Data Base' 
- 'Next'
- chose data connection
- 'yes, include sensitive data in the connection string'
- 'Next'
- insert connectionName
- 'Next'
- select at least one table (not matter witch one, it could be the same as in post above)
- 'finish'
Then I have some info window titled 'Data Source Configuration Wizard' with text: Faild to add 

relation(s). Specified cast is not valid.
I press 'OK' and I have new DataSet.
4. A drag table from my new dataSet on form. someBindingNavigator, bindingSource and tableAdapter and 

someDataGridView are created automatically. Now F5 (compile+run). I modify some date and pres 'save'. 

(insert data is correct). Then program crush and in this line is hhighlight:

this.sat_tiposTableAdapter.Update(this.esfaDataSet.sat_tipos);

with debugComment:
InvalidOperationExeption was unhandled.
Message (I translate it from polish on english):
Update operation require correct UpdateCommand when it is transfer to DataRow with modified rows.

StackTrace:

w(polish) = in(english)

"   w System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   w System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   w System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n   w System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)\r\n   w System.Data.Common.DbDataAdapter.Update(DataTable dataTable)\r\n   w WindowsApplication8.esfaDataSetTableAdapters.sat_tiposTableAdapter.Update(sat_tiposDataTable dataTable) w C:\\_Work\\NET\\WindowsApplication8\\WindowsApplication8\\esfaDataSet.Designer.cs:wiersz 650\r\n   w WindowsApplication8.Form1.sat_tiposBindingNavigatorSaveItem_Click(Object sender, EventArgs e) w C:\\_Work\\NET\\WindowsApplication8\\WindowsApplication8\\Form1.cs:wiersz 22\r\n   w System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)\r\n   w System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)\r\n   w System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)\r\n   w System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)\r\n   w System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)\r\n   w System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)\r\n   w System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)\r\n   w System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)\r\n   w System.Windows.Forms.Control.WndProc(Message& m)\r\n   w System.Windows.Forms.ScrollableControl.WndProc(Message& m)\r\n   w System.Windows.Forms.ToolStrip.WndProc(Message& m)\r\n   w System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)\r\n   w System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)\r\n   w System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)\r\n   w System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)\r\n   w System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)\r\n   w System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)\r\n   w System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)\r\n   w System.Windows.Forms.Application.Run(Form mainForm)\r\n   w WindowsApplication8.Program.Main() w C:\\_Work\\NET\\WindowsApplication8\\WindowsApplication8\\Program.cs:wiersz 17\r\n   w System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)\r\n   w System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)\r\n   w Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()\r\n   w System.Threading.ThreadHelper.ThreadStart_Context(Object state)\r\n   w System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n   w System.Threading.ThreadHelper.ThreadStart()"

When I make mouse rightClick on table in DataSource Table -> Edit Data Set with Designer. Next RightClick on table -> configure. There, if I change query in QueryBuilder nad execute it works correct so -> and back to "TableAdapter Configuration Wizard"; then -> "finish" end I get this error:
Configure TableAdapter sat_tipos(or other table if you use other) faild. Specified cast is not valid.

I found the solution to my problem by adding the following commands to the namespace ...TableAdapters. ...Tabeladapter class:

to the class I added:
Private _commandBuilder As MySql.Data.MySqlClient.MySqlCommandBuilder

and to the InitAdapter sub I added:
Me._commandBuilder = New MySqlCommandBuilder(Me._adapter)
to the end of the sub

I did need to copy the entire DataSet from the DataSet file to a new class file
this sees to solve my problem I hope it helps.

I can also reproduce the problem under Windows Vista.

I was just messing with the same issue.  If you go back to version 5.1.2 of the connector everything works fine.  

Hopefully the developers can track down the changes that caused this problem.  In my testing 5.1.3 and 5.1.4 both had the same problem of not creating a Delete or Update command.  5.1.2 works perfectly.

i can also reproduce the problem with 5.1.4 connector VS2005 Windows XP SP2 with 
5.0.44-log
5.1.19-beta-community-nt-debug

uninstalling 5.1.4.0 and installing 5.1.2.2 fixed the problem

I can confirm this bug, too. I'm running VS2005+SP1 on Vista with Connector/Net 5.1.4.

THANKS TO LARRY KLOTH I can keep my work going ...

I *really* hope this bug gets solved in the upcoming 5.2 release.

Does anyone have a work around to this issue?

Same problem for me (5.1.4) :) Hope mysql connector team solve this problem!

For instant (2008-01-14) this bug seems to not be corrected in Connector/Net 5.2.

Here a reference pages I've found about the dev of this connector :

http://dev.mysql.com/doc/refman/5.1/en/connector-net-news-5-2-0.html

http://svn.mysql.com/svnpublic/connector-net/trunk/CHANGES

I´m also having the same problem. How long it will take until the next release?

Here's one work arround that I got working back in the 5.0.6 version. 
Add a class extension (partial class) to the given TableAdapter that overrides the Update methods and creates the delete/update using the command buidler:
   public partial class BlogTableAdapter 
    {
        private bool m_isInitialized = false;

        public void PrepareCommands()
        {
            this.ClearBeforeFill = true;
            string tableName = Adapter.TableMappings[0].DataSetTable;
            Adapter.SelectCommand = new MySql.Data.MySqlClient.MySqlCommand("Select * from " + tableName, Connection);
            MySql.Data.MySqlClient.MySqlCommandBuilder cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(Adapter);
            Adapter.UpdateCommand = cb.GetUpdateCommand();
            Adapter.InsertCommand = cb.GetInsertCommand();
            Adapter.DeleteCommand = cb.GetDeleteCommand();
            m_isInitialized = true;
        }

        [System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
        public virtual int Update(MySqlDS.BlogDataTable dataTable)
        {
            if (!m_isInitialized)
                PrepareCommands();
            // for some reason we need to open & close the connection using this method
            Adapter.InsertCommand.Connection.Open();
            int res = this.Adapter.Update(dataTable);
            Adapter.InsertCommand.Connection.Close();

            return res;
        }
}

You can also override the version of the update for the dataset, datarow[] and datarow.

Using 5.1.4 plug-in and latest driver sources from SVN trunk I can not repeat this  problem exactly as many things changed. Can anyone test with 5.1.4 (or newer snapshot) and inform me if this problem has gone away?

However, I'll note here that dragging table from server explorer shows no problem while using wizard (or running Configure wizard on existing table adapter) ends up with error:
"Configure TableAdapter sat_tipos failed." "Specified cast is not valid.". This might very well have something to do with c/NET wondering around my databases in search of table in question (which is freshly created in test database):
      5 Query       SHOW TABLE STATUS FROM `mix_test_ucl` LIKE 'sat_tipos'
      5 Query       SHOW TABLE STATUS FROM `mixtestucl` LIKE 'sat_tipos'
      5 Query       SHOW TABLE STATUS FROM `test` LIKE 'sat_tipos'
      5 Query       SHOW TABLE STATUS FROM `test_ucl` LIKE 'sat_tipos'
      5 Query       SHOW TABLE STATUS FROM `testcl` LIKE 'sat_tipos'
      5 Query       SHOW TABLE STATUS FROM `testdb` LIKE 'sat_tipos'
      5 Query       SHOW FULL COLUMNS FROM `test`.`sat_tipos`

Tested against server version 4.1.22-log.

I have tested with 5.1.4 and, in my case, problem persists exactly as with 5.1.3 version.

Santiago, what would you like me to do? Do you really believe it is possible to "fix" a problem one can't reliably reproduce?

Anyway, I tested again, this time with 5.1.4GA (no latest sources involved) and I found out I have no problem at all! The problem I described before was obviously related to not-released code in trunk...

I suggest reinstall (with help from MS uninstaller, not the one in Control Panel), checking of Assemblies with Microsoft .NET Framework 2.0 Configuration and/or testing on fresh box.

So, what now?

I don't believe that. I Know that you can't fix a problem that you can't reproduce, but this doesn't means that it's impossible to reproduce the problem.

As you can see this issue occurs not only to me, so there must be a common factor that gets the problem to happen. If you don't have this factor and you don't know which factor is it, it's natural that you can't resolve the problem.

I don't want you to make nothing, so if there are something that I could do to help you to reproduce this issue (like report you some specific information about my installation, etc.) I'll be pleased to collaborate.

Otherwise, we must wait until the good luck come for us and in a future version this problem dissapears.

Anyway, thanks for your help.

I have had the same problem with 5.1.3 in the code that i have been generating and finaly gave up and went back to 5.1.2 the backwords step ment reinstalling VS pro and .net 2.0 from scratch twice once to see if the problem was a fluke and the second to go back permintaly until the next Major rev.

if you need any info I may be able to provide just ask.

Larry

I just downloaded the latest update 5.2.0 the latest and greatest? thing.

and it does the same thing only generates the insert command-

Me._adapter.InsertCommand

it does NOT generate

Me._adapter.DeleteCommand OR Me._adapter.UpdateCommand

in the Private Sub InitAdapter() sub

attached is the file that just created all the InitAdapter() subs had all three commands befor changing the dewsrecordsTableAdapter class.

I guess its back to 5.1.2 untill you get things fixed.
Larry

this is the file i sed i would send to you

Attachment: ncwaDataSet.Designer.zip (application/x-zip-compressed, text), 63.42 KiB.

Guys, first of all, I am not here to pass judgments to my liking, I am here to help you. This means that even though I ruled this as CRp I will follow the discussion and try my best to help. So don't be alarmed :-)

Next, let's try to pin down this common factor. So far I gathered that:
 - One should have had prior installation of c/NET (for example 5.1.2)
 - Upgrade to c/NET 5.1.3 brought this problem
 - Upgrading to c/NET 5.1.4 and 5.2 does not fix things (?)
 - All of us checked GAC (Microsoft .NET Framework 2.0 Configuration) for proper entries (?)
 - All of us use VS2005 Pro (at least, as plug-in does not work with express)
 - The problem occurs only with MySQL server 4.1.x. Doesn't appear with MySQL server 5.x (?)

Can you confirm above points and, if needs be, add new ones please?

- One should have had prior installation of c/NET (for example 5.1.2)
-- no - I started out with 5.1.3 and went backwords to 5.1.2 after having problems with my programs.
 - Upgrade to c/NET 5.1.3 brought this problem
-- no see above
 - Upgrading to c/NET 5.1.4 and 5.2 does not fix things (?)
-- correct
 - All of us checked GAC (Microsoft .NET Framework 2.0 Configuration) for proper entries (?)
-- uninstalled and reinstalled with standard options.
 - All of us use VS2005 Pro (at least, as plug-in does not work with
express)
-- correct
 - The problem occurs only with MySQL server 4.1.x. Doesn't appear with MySQL server 5.x (?)
-- no I have MySql server 5.1.22-rc-community

Larry

Here's my response:

- One should have had prior installation of c/NET (for example 5.1.2) = Yes, I started with 5.0 > 5.1.2 > 5.1.3 > 5.1.4 > 5.1.5. > 5.2
 - Upgrade to c/NET 5.1.3 brought this problem = Yes, problem comes from 5.1.3 and persists, but I tested to back to 5.1.2 and get same problem.
 - Upgrading to c/NET 5.1.4 and 5.2 does not fix things (?) = Done, does not fix things.
 - All of us checked GAC (Microsoft .NET Framework 2.0 Configuration)
for proper entries (?) = Checked. All seems to be OK. All proyects and apps that use it work fine.
 - All of us use VS2005 Pro (at least, as plug-in does not work with
express) = VS2005 Professional.
 - The problem occurs only with MySQL server 4.1.x. Doesn't appear with
MySQL server 5.x (?) = Tested with 3.23.x, 4.1.x and 5.x. Same results.

Perhaps problem comes from some file (or registry record) installed with 5.1.3 that stay there even uninstall or install other version.

By default, Windows Installer only replaces an existing file if creation date and last write date are the same, so it's possible that if a 5.1.3 file was modified by some process, it could be intact between uninstall and new installations. I don't know, it's only a thought.

Try this to see if it helps to reproduce the problem:

1.- create a new windows application project in VS2005.
2.- from the data menu select add a new data source
3.- select database and click next
4.- click new connection
5.- change to a MySql connection
6.- enter information as needed for your sample database:
	I used Localhost, root, (password), check save password, membership
7.- click ok
8.- select yes include sensitive data in the connection string and click next
9.- click next
10.- select any or all tables
11. click finish

In 5.1.2 i have in the InitAdapter() sub _adapter.deletecommand, _adapter.insertcommand, _adapter.updatecommand

In 5.1.3 i have in the InitAdapter() sub, _adapter.insertcommand

In 5.2.0 i have in the InitAdapter() sub, _adapter.insertcommand

If in the subsequent application you call the … TableAdapters.update(??) command you get an error in executation
I don’t have 5.1.4 saved on my laptop at this time to try an install with this rev of software but in writing this information up for you I did install all three revs of software to verify the results indicated above.

I hope this helps to resolve the problem

Larry

this is the same as a former bug that was fixed but came back:
Version 5.1.2 - 6/12/2007
  - Fixed problem preventing the DataSet Designer or TableAdapter wizard from
    being able to generate insert, update, and delete statements.
    (Bug #26347)    
see what was fixed then and check to see if the problem came back.

Larry, I'm agree with you. So, we all seems to come from prior 5.1.2 version, it confirms that problem is dragged to post versions.

I think there must be a dll that all versions use that it isn´t updated between installations and this dll contains the fix.

Tonci, this may help you to reproduce the problem:

1. In a PC (or virtual PC) in which never was installed MySQL C/NET install a prior 5.1.2 version (like 5.1.0).
2. Test if problem occurs (it must be yes).
3. Uninstall C/NET and install 5.1.2 or above version.
4. Test if problem occurs. If yes this confirms problem persists across installations.

If you want more tests you can repeat step 1 directly with 5.1.2 (or above) version and confirm that problem doesn´t happens.

Thank you all for providing much added value to this report. I'm reopening this for new verification now.

Guys, thanks for bearing with me on this one... I cleaned my box and retested with 5.1.4GA and, truly, no UPDATE and DELETE statements were generated. Only INSERT and SELECT.
So, verified as described using:
 - MySQL server 5.0.56pb on WinXP Pro SP2 localhost
 - c/NET 5.1.4 and .NET FW 2.0

This has been strange to fight with, for example, I can't repeat the part where all sorts of SELECT's from I__S were issued... strange. As this appears to be regression, I'm raising severity.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43235

Fixed in 5.1.6 and 5.2.2

A note has been added to the 5.1.6 and 5.2.2 changelogs: 

Using the TableAdaptor wizard in combination with a suitable SELECT statement, only the associated INSERT statement would also be created, rather than the required DELETE and UPDATE statements

This is not fixed in 5.2.2 i think. Can't see the changes anywhere in svn (5.2 branch)

A Table Adapter base select statement should contain a WHERE clause that specifies a Primary Key or unique row. Otherwise the generated Update statement will not know what row to update!

Installed C/NET 5.1.6 and tested: the problem persists.

I've tested with WHERE clause too: same results.

I Have tested VS 2008 PRO, VS 2005 PRO, MySQL 4.1.2 and MySQL 5.1. Connector 5.2.2 and problem still persists. Update and delete statements still missing.