none
Stuck on inserting tables RRS feed

  • Question

  • I keep getting continuous errors when running this statement to update tables I have stored in a mysql database:
    System.InvalidOperationException: Update requires a valid InsertCommand when passed DataRow collection with new rows.
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
       at StockProjectAdmin.Admin.bulkInjectionFolder(Object folderPath) in C:\Documents and Settings\user\My Documents\Visual Studio 2008\Projects\StockProjectAdmin\Admin.cs:line 244

    my code is as follows:

                conn = connectDB();
                for (int x = 0; x < bulkTable.Length; x++)
                {
                    try
                    {
                        if (!conn.State.Equals(ConnectionState.Open))
                        {
                            conn = connectDB();
                        }
                        comm = new MySqlCommand(String.Format("SELECT * FROM `{0}`",bulkTable[x].TableName, conn));
                        da = new MySqlDataAdapter(comm);
                        da.Update(bulkTable[x]); <-----------LINE 244
                    }
                    catch (Exception e)
                    {
                        fm1.AppendTextToConsole(e.ToString() + "\n");
                    }
                }

    The tables are already created on the database, and the columns on both this DataTable array and the mysql server are matched, but I keep getting that error. Any help on this would be appreciated. Thanks.
    Thursday, April 10, 2008 12:48 PM

All replies

  • Hi,

    All you ahve in your DataAdapter is a select statement. When you want to insert rows in a table, you need an insert statement. You can go about this in two different ways:

    1. Set one from code (da.InsertCommand = "INSERT ....")

    2. Generate one with a SqlCommandBuilder (not sure this exists for MySql, but you can probably use the SQL one)

    Hope this helps

    Thursday, April 10, 2008 1:32 PM
  • It looks like your source and destination tables are same in the above code. Did you intend to first retrieve from the bulkTable[x].TableName  into a server side DataTable ?  If so, you should use the Fill method instead of Update on the data-adpater.

    Thursday, April 10, 2008 1:37 PM
  • I am trying to take information in files(text files with the columns being separated by commas, the rows being a newline) and injecting them into a mysql database. I'm able to create an array of DataTables(I tried a DataSet, but an array seems to be better suited for inserting information in from files). It does look like I need to add a DataAdapter.InsertCommand statement in there to tell the adapter how and what to insert, I'll try playing with it when I get a chance, and it does look like MySql have InsertCommand functionality. Once all the info is in the database it should be as easy as a .Fill and then a .Update using a DataSet for modifying information. I'll try adding the statements, but I may still have trouble, if so I'll post back here. Thank you for your replies and moving the thread where it needs to be.
    Friday, April 11, 2008 2:58 AM
  • After hours of testing and intensive code modification I switched back to using a Dataset and attempted to insert one table at a time. I had a method to create empty tables(no rows) on the database, then used the Fill command to get a Dataset, which seems to be working for the most part, my main issue now being getting a proper insert statement to upload the modified tables. I tried using a Command Builder, however when I put a breakpoint on the dataadapter and check the insert statement, nothing is listed. My Fill statement is as follows:

                conn = connectDB();
                comm = new MySqlCommand("SELECT * FROM " + query2 + "`", conn);
                da = new MySqlDataAdapter(comm);
                mBuild = new MySqlCommandBuilder(da);
                da.Fill(bulkTable);

    And then my update statement is below:

                for (int x = 0; x < bulkTable.Tables.Count; x++)
                {
                    try
                    {
                        conn = connectDB();
                        comm = new MySqlCommand("SELECT * FROM stockdb.`" + bulkTable.Tables[x].TableName + "`", conn);
                        da = new MySqlDataAdapter(comm);
                        //da.InsertCommand.CommandText("I
                        mBuild = new MySqlCommandBuilder(da);
                        //bulkTable.Tables[x].Rows.CopyTo(test, 0);
                        da.Update(bulkTable, bulkTable.Tables[x].TableName);
                        fm1.AppendTextToConsole("Injected for " + bulkTable.Tables[x].TableName);
                    }
                    catch (Exception e)
                    {
                        fm1.AppendTextToConsole(e.ToString() + "\n");
                    }
                }

    For some reason the command builder isn't picking up on an update statement, in other examples I've seen it's worked just fine. Any ideas?
    Friday, April 11, 2008 3:48 PM
  •  I don 't have time to understand your code, but keep these things in mind.

     

    Command builder won't generae the INSERT command until you have manually created AND used a select command on that table.   If the DA (in the meantime) was used for another select command, you will have to remind it of the original select command used to build that table:

     

    da.SelectCommand.CommandText = strSelectCommandUsedEarlier

     

    which could be

     

     "SELECT * FROM ContactsTable"

     

    or whatever. THEN setup your command builder.

     

    Also, remember, if the dataable was built manually you cannot call da.UPDATE.  That command can only be used on a datatable created by a Da.Fill() using a SELECT query.

     

    Wednesday, April 16, 2008 11:56 AM
  • I seemed to have solved the issue by manually creating the insert query and sending it as a nonquery to my mysql connection. In the insert query I merely utilize the abilitiy to insert multiple rows in one statement, so utilizing string manipulation I run a for loop to add each row to the command and then run the command all at once. The only thing better than this would be the ability to either insert multiple tables, or run one insert query that references all of my tables, and inserts rows for all of them. The update information I've been given here is good for doing bulk updating though.
    Wednesday, April 16, 2008 7:48 PM