none
Update requires a valid UpdateCommand when passed DataRow collection with modified rows. RRS feed

  • Question

  • It is my understanding that when using a TableAdapter and selecting the proper options, Insert, Delete and Update statements are generated. The Update statement should work just using the following:
    myTableAdapter.Update(changes);
    Where changes is the DataSet containing the modified data.

    This logic works great for one of the tables in the DB but not for another.

    Learning this new stuff is Confusing at best - Frustrating at worst.

    Help appreciated,

    Michael
    Tuesday, February 21, 2006 11:20 PM

Answers

  • Try this, it worked to me:

    Go to edit dataset with designer (you can go there from the "Data" menu then "show data sources" and "Edit dataset with designer".

    Right click the adapter and select "Configure". Click "Advanced options". Make sure the "Refresh the data table" is selected. Click OK and Finish.

    Tell me if it works. Good luck.

    Osama

    Saturday, September 9, 2006 9:12 PM
  • This error message appears to be some sort of catch-all. All of the problems I encountered concerned bad data imported from a MSAccess DB. Primary key - duplicates, nulls, etc.
    I guess it's too much to ask that error messages be accurate instead of wasting my time looking for an Update statement that is already there.

    Thanks for your comments,

    Michael

    Thursday, February 23, 2006 3:11 PM

All replies

  • Check the Update, Insert, Delete command of the myTableAdapter, and check the query statement of all of them.

    TableAdapters are created for only one table, if you want to modify more than one table, you have to change the query statements according to your needs, or create a second table adapter for the second table.



    • Proposed as answer by gsusPRADEEP Thursday, April 29, 2010 4:04 PM
    Wednesday, February 22, 2006 12:15 PM
  • I checked the commands before I posted the question - It is a second table adapter.
    I even created a new project from scratch with just that table adapter - no luck.

    • Proposed as answer by gsusPRADEEP Thursday, April 29, 2010 4:04 PM
    Wednesday, February 22, 2006 3:18 PM
  • This error message appears to be some sort of catch-all. All of the problems I encountered concerned bad data imported from a MSAccess DB. Primary key - duplicates, nulls, etc.
    I guess it's too much to ask that error messages be accurate instead of wasting my time looking for an Update statement that is already there.

    Thanks for your comments,

    Michael

    Thursday, February 23, 2006 3:11 PM
  • Here's what I found that resolved the problem...

    When working with the Wizards and Designers in Visual Studio the code generators building the UPDATE statement need a few essentials to build the code:

    1.  A table. Not a product of a JOIN or a table that includes an expression--just a table.
    2. A primary key or unique column on the table. Converting from an Access database does not usually bring over the PK which is needed to construct the WHERE clause to point to the row to change.
    3. The column names can't contain special characters.
    4. The columns can't "participate" in Foreign Key constraints.

    See ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/WD_ADONET/html/6e3fb8b5-373b-4f9e-ab03-a22693df8e91.htm for more information.

    Thanks to William Vaughn (MVP) for this info.

    Claude.

     

    • Proposed as answer by mkmurray Wednesday, June 24, 2009 8:18 PM
    Thursday, June 15, 2006 6:49 PM
  • Try this, it worked to me:

    Go to edit dataset with designer (you can go there from the "Data" menu then "show data sources" and "Edit dataset with designer".

    Right click the adapter and select "Configure". Click "Advanced options". Make sure the "Refresh the data table" is selected. Click OK and Finish.

    Tell me if it works. Good luck.

    Osama

    Saturday, September 9, 2006 9:12 PM
  • I have the same problem .. but the "Refresh the data table"  is grey-out

    Edwin

    • Proposed as answer by gsusPRADEEP Thursday, April 29, 2010 3:50 PM
    Monday, September 11, 2006 1:05 AM
  • The "Refresh the data Table is not being saved. Each time I go to "Advanced options" that chek box is not cheked and I get the same error

    AKL
    • Proposed as answer by gsusPRADEEP Thursday, April 29, 2010 3:51 PM
    Monday, September 11, 2006 7:17 PM
  •  i' ve this problem also,

    but i even can't mark the chek box "refresh the data table" because it is not enabled.

    i use the vs 2005 express verision. may it's the reason?

    is somebodt know if i can update the database table with the adapter by the dataset that has been change?

    i get the same error mentioned here:

    "Update requires a valid UpdateCommand when passed DataRow collection with modified rows"

    i'll appriciate any help!

    mikalush

    Tuesday, September 19, 2006 9:02 AM
  • Don't know why but I had the same problem and the only way to get over it is by doing this:

    Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

    myBuilder.GetUpdateCommand()

    da.UpdateCommand = myBuilder.GetUpdateCommand()

    da.Update(IPTable)

     

    AND IT WORK!!!!

    • Proposed as answer by m_ward Friday, July 10, 2009 4:41 PM
    Saturday, September 30, 2006 6:30 AM
  • Hi!

    I had the same Problem too.

    I solved the Problem very easy: Look up for your table and check if you have a PrimaryKey.

    If not set it!

     

    • Proposed as answer by dwanders Thursday, January 15, 2009 7:44 PM
    Friday, October 13, 2006 11:51 PM
  • Thanks a lot! It took me 4 hours to find out about that check box.
    Thursday, November 30, 2006 10:51 PM
  • Thanks Claude...  a good post.  It resolved my problem.
    • Proposed as answer by GeraldoMung Monday, October 17, 2016 5:33 AM
    Friday, December 15, 2006 1:17 PM
  • Hi, Allan, i am wondering if you have fixed the problem. I got the same error and The "Refresh the data Table is not being saved and Each time I go to "Advanced options" that chek box is not cheked and I get the same error.

     

    Thanks, I appreciate your help

    Monday, January 8, 2007 10:58 PM
  • Thanks,

    I tried this , followed by configuration change suggested by Osama Alborbar just above, and together they work !

    GG

    Wednesday, February 21, 2007 1:53 PM
  • Hello all,
    I've tried all that, added a primary key and  tried to set the "Refresh" property in the dataset designer (I can check it, but every time I open the dialog box again it has become unchecked again) but it still doesn't work! This is hair-rising!!! Microsoft, what have you been doing with this!

    What is more, the automatically generated TableAdapter for my VB 2005 form does *not* have an "updatecommand" property, so I can't even hand-code the SQL into it.

    Moreover, since it does not have any "updatecommand" property (only an "update" method), it prevents me from using things like SQLCommandBuilder.
    What were people thinking when they implemented this?

    I'm essentially just trying to reproduce the one-click functionality I had in Access with a totally simple table.

    Has anybody any further idea?
    Friday, March 30, 2007 3:52 PM
  • I had a similar problem.  It turned out that I had forgotten to set a primary key for the table which wasn't working.  After I corrected that and reconfigured, it worked fine.
    Tuesday, April 17, 2007 8:06 PM
  • Your tip worked fine for me. Thank you.
    Tuesday, June 12, 2007 1:49 PM
  • I don't have this error message, but when I use update, the database is not updated at all. Anyone can help me on this. By the way, I think when we change the setup of database, we need to rerun the dataset  designer to reflect the change.
    Wednesday, June 13, 2007 8:50 PM
  • I noticed that changes made and saved through the Emulator did not update the database although the routine functioned without a hitch. However in Deploying to the Device, the database was updated.

     

    n2a1s

    Thursday, June 14, 2007 7:32 AM
  • I couldnt get the checkbox to stay checked, like most others here.

     

    BUT...I found where I could define the UpdateCommand.  If you view the properties of your <tblName>TableAdapter, it does have a property for UpdateCommand.  My InsertCOmmand and SelectCommand were fille in, and both work great, but my UpdateCommand wasnt.

     

    I just added the sql for the Update, and it works!!!!!!!  I would have preferred to just have it "work on its own", but this was easy enough.

     

    Hope this helps.

    Saturday, June 23, 2007 11:53 AM
  • dude!!

    you solved my 2 day and a half problem... just wanted to say thanks, Tal!!

    Monday, June 25, 2007 6:22 PM
  • create a method for update.

    Goto Data->show data sources, Right click on the table you are having probs with and edit dataset with designer.  Right click on the table adapter and choose properties, expand Data->updateCommand.  Choose new, expand updatecommand '+'.  Go to command text and add table.  Select the fields you reuire you will end up wilth a query similar:

    UPDATE    TBLCustomer
      SET              name =, address =, phone =

     

    change to

    UPDATE    TBLCustomer
      SET              name =?, address =?, phone =?

     

    Hit f5 and test

     

    Hope this helps.

     

    • Proposed as answer by Pranav Vaidya Wednesday, August 26, 2009 3:03 PM
    Monday, July 16, 2007 9:46 AM
  • Same problem until i have set a primary key using SQL Server Management Studio and not the designer.

    Now is the f****d checkbox checked and the problem solved.

    Saturday, July 21, 2007 5:51 PM
  • very thanks that resolved my problem!

     

     

    Tuesday, July 24, 2007 1:39 AM
  • Osama's tip worked for me.  After I checked that option, no more error!!!!

     

    Thanks Dood!!!!

    Wednesday, July 25, 2007 1:13 AM
  • Thanks, this worked for me too!
    Monday, July 30, 2007 7:56 PM
  • I found your posting too important and useful, but can this be used in order to show data in datagrid from 2 tables in one datagrid?

    Can you provide any code?
    Wednesday, August 29, 2007 8:44 AM
  • Inserting a primary key has solved the problem.

     

    Thank You

     

    A.Narenga

     

     

    Wednesday, August 29, 2007 11:11 AM
  • Can you provide me code?

    Thanks

     narenga wrote:

    Inserting a primary key has solved the problem.

     

    Thank You

     

    A.Narenga

     

     

    Wednesday, August 29, 2007 11:35 AM
  • Only battled with this error message for two weeks (10 hours a days - 6 days a week)
    Lots of people having the same problem but each person needing a different solution to solve their problem.
    At first I thought I must be stupid - then mad - then, maybe I'm just not a programmer.
    Why is Microsoft so hell bent on screwing up our lifes.
    Anyway for me the solution was the one posted by Talbengal (THANK YOU)


    Talbengal - may you live long and happy
    Sunday, October 7, 2007 1:05 AM
  • Someone may have already alluded to this, but when I create the DataGridView and connect the TableAdapter, it only creates the SelectCommand.  It does not automatically create the UpdateCommand.  Edit the TableAdapter's properties and add your own UpdateCommand.  It will be blank, so you have to create it yourself.

    Update Table set fname=@fname, lname=@lname,address=@address where ixIndex=@ixIndex

    Some people had some trouble finding the TableAdapter's UpdateCommand property, but its there.  It just takes some time to find it.  Its hidden behind all the other junk in the UI.
    Friday, October 12, 2007 7:33 PM
  • I agree with Phil too.  This DataGridView is way more trouble than its worth.  Just let us keep using DataGrids and FlexGrids.

    Its taken 40+ hours from my time to figure out all the issues that have come up with the DataGridView.  I'm just trying to convert an old VB app, and I feel like I'm wasting too much time on it.
    Friday, October 12, 2007 7:40 PM
  •  skidave74 wrote:

    I couldnt get the checkbox to stay checked, like most others here.

     

    BUT...I found where I could define the UpdateCommand.  If you view the properties of your <tblName>TableAdapter, it does have a property for UpdateCommand.  My InsertCOmmand and SelectCommand were fille in, and both work great, but my UpdateCommand wasnt.

     

    I just added the sql for the Update, and it works!!!!!!!  I would have preferred to just have it "work on its own", but this was easy enough.

     

    Hope this helps.



    I finally got it after I read this post! Thanks a lot!
    Friday, October 26, 2007 2:23 AM
  • You people suck. Non of this works..Morons.

     

    Wednesday, October 31, 2007 9:26 PM
  • Hi, Aleaxue, i have fixed, just do some steps.

     

    1. create primary key in table

    2. reconfigure dataadapter

    3. run

     

    you will see, your program will run perfect.

     

    cheer

     

    Wednesday, December 26, 2007 1:37 PM
  • I'm a n00b at all this so I thought I'd say thanks for your response above - it was exactly what I needed to be able to update the dataset (and therefore the database).

    Thursday, January 3, 2008 11:33 AM
  •   I have found an easy solution.  It seems that at some point I had changed my database after the dataset was created or something of the sort.  What happened was the update command on my table adapter was missing.  To remedy this, I simply configured my dataset (at this point make sure that you have checked the geneerate ... statements option under advanced options) and click finish.  It will rebuild your statements for you (including the update command).  Good luck.

    Wednesday, January 16, 2008 4:14 PM
  • Well, here is another fine mess MS has gotten me into. The answer is a bug and here is the answer.

    1. Make sure you create a primary key when you create the table originally.
    2. Open the "Edit Dataset with Designer".
    3. If you have not added the table to the Designer, do so now. You will see a key beside of the column that you selected as a primary key. Select this column and right click on the key (another bug, you can't right click on the text and get the context menu) and select "Set Primary Key"
    4. The problem should be solved.

    MS has one goal in life and that is to make people miserable. For instance, there is no documentation in their VB Express help file on how to create an AutoNumber column. You have to do the following or your database will be deleted if you do it later (more then likely, it will reject any attempts at adding an AutoNumber column later):
    1. When you build the table, create the first column and select 'int' or 'bigint'.
    2. Down at the bottom of the page you will see that 'identity is not grayed out, change this to true. Leave or change the other items as desired.
    3. Change 'Allow null' to No.
    4. Change 'Prinary key' to yes.
    You now have an AutoNumber column like Access. As usual, you will see a totally different way it handles the numbering compaired to Access.

    The reason I added this is to aid in the first part. After you create the AutoNumber column, do steps 2 through 4 in the first part of this article.

    Hang around folks for the next screwing by MS.
    Tuesday, February 5, 2008 12:55 PM
  • Niaz,

     

    You are a genius. Thank you, thank you and thank you. I have spent 3 days trying to figure out why the wizard wouldn't generate the delete and update commands automatically on a new table (vs an older one) and all because I hadn't set a primary key.

     

    Thanks again,

    Peter

    Thursday, February 21, 2008 4:51 AM
  • Hi All,

    i have been stuck with this problem with errors regarding "validUpdate command" and Select command property needs to be initialised!!..i have tried everything but doesnt work. here i post my code..plzz tell the error i m not able to find!!

     

    Thank you,

     

    public partial class Form1 : Form

    {

    OleDbDataAdapter dp = new OleDbDataAdapter();

    OleDbConnection con = new OleDbConnection();

    BindingSource bsource = new BindingSource();

    DataSet set = new DataSet();

    OleDbCommandBuilder command = new OleDbCommandBuilder();

    string sel;

    public Form1()

    {

    InitializeComponent();

    }

    private void Loaddata()

    {

    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\web1\\desktop\\newdb.mdb";

    sel = "SELECT * FROM newtable";

    dp=new OleDbDataAdapter(sel,con);

    con.Open();

    OleDbCommandBuilder command = new OleDbCommandBuilder(dp);

    dp.SelectCommand = new OleDbCommand(sel, con);

    dp.SelectCommand.Connection = con;

    dp.Fill(set, "newtable");

    bsource.DataSource = set.Tables["newtable"];

    dataGridView1.DataSource = bsource;

    }

    private void button2_Click(object sender, EventArgs e)

    {

    DataTable dt = set.Tables["newtable"];

    this.dataGridView1.BindingContext[dt].EndCurrentEdit();

    dp.UpdateCommand = command.GetUpdateCommand();

    this.dp.Update(dt); \\ here is the error .. sometimes it says " Syntanx error in update command" or when i use the GetUpdateCommand line above..it gives me " SelectCommand property needs to be initialised!!

    }

    private void button1_Click(object sender, EventArgs e)

    {

    Loaddata();

    }

    }

     

    Chetan

    Monday, April 7, 2008 5:41 AM
  • Can some one help me out on same problem!...i have tried posting before but the code and both post are not getting updated!..so why does this problem occur..when you manually create all update & delete & insert command?...

     

    please help!!

    thank you

    Monday, April 7, 2008 9:50 AM
  • Hey,

    i tried all your solutions but the Validupdate command thing is still coming,

    primary key is set. and done!...but still have problems!!..i'll post the code .. i hope you can figure smthin out!

    thank you !!

     

    public partial class Form1 : Form

    {

    OleDbDataAdapter da = new OleDbDataAdapter();

    DataTable table = new DataTable();

    OleDbConnection conn = new OleDbConnection();

    BindingSource bs = new BindingSource();

    DataSet data = new DataSet();

    public Form1()

    {

    InitializeComponent();

    }

    private void Form1_Load(object sender, EventArgs e)

    {

    data = new DataSet();

    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\web1\\Desktop\\newdb.mdb";

    da = new OleDbDataAdapter("SELECT CustomerID,[Last Name],[Type of service],[Mode of payment] FROM newtable", conn);

    da.Fill(data, "newtable");

    bs.DataSource = data.Tables["newtable"];

    dataGridView1.DataSource = bs;

    dataGridView1.Enabled = true;

    conn.Close();

    }

    private void button1_Click(object sender, EventArgs e)

    {

    try

    {

    table.BeginLoadData();

    da.Fill(data,"newtable");

    table.EndLoadData();

    }

    catch (Exception ex)

    {

    Console.WriteLine("Error while loading", ex.ToString());

    }

    dataGridView1.Columns["CustomerID"].ReadOnly = true;

    conn = da.SelectCommand.Connection;

    da.UpdateCommand = new OleDbCommand();

    da.UpdateCommand.Connection = conn;

    da.UpdateCommand.CommandText = "UPDATE newtable SET [Last Name] = @Last Name,[Type of service] = @Type of service, [Mode of payment] = @Mode of payment WHERE CustomerID = @CustomerID";

    da.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.BigInt, 8, "CustomerID");

    da.UpdateCommand.Parameters.Add("@Last Name", OleDbType.VarChar, 50, "Last Name");

    da.UpdateCommand.Parameters.Add("@Type of service", OleDbType.VarChar, 100, "Type of service");

    da.UpdateCommand.Parameters.Add("@Mode of Payment", OleDbType.VarChar, 50, "Mode of payment");

    da.DeleteCommand = new OleDbCommand();

    da.DeleteCommand.Connection = conn;

    da.DeleteCommand.CommandText = "DELETE from newtable where (CustomerID= @CustomerID)";

    da.DeleteCommand.Parameters.Add("@CustomerID", OleDbType.BigInt, 8, "CustomerID");

    da.InsertCommand = new OleDbCommand();

    da.InsertCommand.Connection = conn;

    da.InsertCommand.CommandText = "INSERT INTO newtable([Last Name],[Type of Service],[Mode of Payment])VALUES ([@LastName],[@Type of Service],[@Mode of Payment])";

    da.InsertCommand.Parameters.Add("@LastName", OleDbType.VarChar, 50, "Last Name");

    da.InsertCommand.Parameters.Add("@Type of service", OleDbType.VarChar, 100, "Type of service");

    da.InsertCommand.Parameters.Add("@Mode of Payment", OleDbType.VarChar, 50, "Mode of payment");

    da.Update(data,"newtable");

    }

    private void button3_Click(object sender, EventArgs e)

    {

    table = data.Tables["newtable"];

    this.dataGridView1.BindingContext[table].EndCurrentEdit();

    this.da.Update(table); \\ this line gives the error

    }

    Monday, April 7, 2008 12:05 PM
  •  

    yeah I have also solved the problem by specifying a Primary Key.
    Monday, May 5, 2008 6:29 AM
  •  

    Thanks You really saved my time.
    Monday, May 5, 2008 7:54 PM
  • Thanks. Created a Primary Key in the table and problem was fixed.

    Friday, May 16, 2008 4:45 PM
  •  

    Primary key, this saved me. Thanks!

     

    Friday, June 27, 2008 8:52 AM
  • I found that what mynolix & Osama Alborbar said was true.

    First I set a primary key and then set the refresh data check box and it worked for me.

    Thanks guys.
    • Proposed as answer by MSDN_ToT Friday, January 7, 2011 6:04 AM
    Sunday, July 27, 2008 6:15 PM
  • Thank you Claude! Solved my problem

    Linda
    Tuesday, September 16, 2008 3:11 AM
  •  Talbengal wrote:

    Don't know why but I had the same problem and the only way to get over it is by doing this:

    Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

    myBuilder.GetUpdateCommand()

    da.UpdateCommand = myBuilder.GetUpdateCommand()

    da.Update(IPTable)

     

    AND IT WORK!!!!



    Yes, the above snipet works. But strange was it that in another application before, I didn't need the three SqlCommandBuilder lines at all:

    Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

    myBuilder.GetUpdateCommand()

    da.UpdateCommand = myBuilder.GetUpdateCommand()


    Thanks, Talbengal.




    Thursday, October 30, 2008 2:18 PM
  • Thanks to your suggestion, I solved too.

    The primary must be set in table definition not in table adapter.

    I removed it from xsd, corrected the definition then reinserted in xsd.

    Sunday, December 7, 2008 11:13 AM
  • Thanks

     

    Monday, December 8, 2008 6:36 AM
  •    Can i use Talbengal code if i am using Visual studio 2008 and the newest vb? It creates a lot of the stuff for me. one more question, When setting the keys do i need to set them first in the database and 2nd in the dataset?
    Thursday, February 5, 2009 3:55 PM
  •  Has anyone been able to get this to work when updating a View instead of a table?  I am beginning to think it's not possible??

    Thanks,
    Jennifer
    Tuesday, February 17, 2009 4:01 PM
  • I had the same problem with dynamically updating a datagrid, and I agree with Ali Raza Shaikh.  I looked at the tableAdapter I was using and when I clicked on it and checked the properties, I was missing the necessary SQL statements for the Delete and Update commands (you will probably need to scroll to the bottom of the properties window to find these).  After adding SQL statements to each of them my datagrid allowed all operations to be performed on it without error.
    Tuesday, February 24, 2009 12:07 AM
  • Hi,
    Just check that the table has a primary key.
    You have to re-import it in the designer before saving.
    Maymone
    www.maymoned.co.il
    BR

    IT Consultatn
    Thursday, April 30, 2009 6:22 AM
  • I'm having the same problem and nothing here seems to help. I'm programming the connections manually. The fill works fine, the dataset is created, but it crashes when I attempted the Update. I'm using an Access 7 database which I created using Access. The primary key, ID, exists.

    Public

    Sub FetchData() 
    Dim myConnection As New OleDb.OleDbConnection
    myConnection.ConnectionString =
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurrentDir & "\INAJ.accdb"
    Dim myAdapter As OleDb.OleDbDataAdapter
    myAdapter =
    New OleDb.OleDbDataAdapter("SELECT * FROM categories", myConnection)
    myConnection.Open()
    myAdapter.Fill(INAJDataset)
    myConnection.Close()
    End Sub

    Public
    Sub UpdateData() 
    Dim myConnection As New OleDb.OleDbConnection
    myConnection.ConnectionString =
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurrentDir & "\INAJ.accdb"
    Dim myAdapter As OleDb.OleDbDataAdapter
    myAdapter =
    New OleDb.OleDbDataAdapter _
    (
    "UPDATE categories SET CategoryName=@CategoryName,CategoryNumber=@CategoryNumber WHERE ID=@ID", myConnection)
    myConnection.Open()
    myAdapter.Update(INAJDataset)
    myConnection.Close()
    End Sub
    Thursday, April 30, 2009 8:41 PM
  • GerardNgawati  thanks, realy helped what you said
    Saturday, June 20, 2009 10:07 PM
  • ISiM to organize COMAD 2009 - the 15th International Conference - Call for papers

    Dear All,

     

    The 15th COMAD is organized by the International School of Information Management (ISiM), University of Mysore during December 9-11, 2009 at Infosys Technologies campus, Mysore. Similar to previous years, COMAD   2009’s scope  will  include  all   areas  in  the  data  management  space  including database management  systems, Web and Information  Retrieval and Data Mining. For details:  http://www.isim.ac.in/comad2009

     

    For close to two decades COMAD – the International Conference on Management of Data, modeled along the lines of ACM SIGMOD, has been a premier international database conference hosted in India. The first COMAD was held in 1989, and it has been held on a nearly annual basis since then. COMAD always had significant international participation with about 30% of the papers from outside India including Europe, USA, and East/South East Asia.

     

    Call for Papers  

     

    Original  research  submissions are  invited  not  only  in  traditional database areas  but also  in Data Quality, Web,  Information  Retrieval and Data Mining.

     

    We invite submission of original research   contributions as well as proposals   for demonstrations, tutorials,   industrial presentations, and panels.
    Areas of interest include but are not limited to:
    Data Management Systems:
       * Benchmarking and performance evaluation
       * Data exchange and integration
       * Database monitoring and tuning
       * Data privacy and security
       * Data quality, cleaning and lineage
       * Data warehousing
       * Managing uncertain, imprecise and inconsistent information
       * Multilingual data management
       * Novel Data Types
       * Parallel and distributed databases
       * Peer-to-peer data management
       * Personalized information systems
       * Storage and transaction management

    Web and Information Retrieval:
       * Categorization, Clustering, and Filtering
       * Document Representation and Content Analysis
       * Information Extraction and Summarization
       * IR Theory, Platform, Evaluation
       * Question Answering and Cross-Language IR
       * Web and IR
       * Social Network Analysis



    Data Mining
       * Novel data mining algorithms and foundations
       * Innovative applications of data mining
       * Data mining and KDD systems and frameworks
       * Mining data streams and sensor data
       * Mining multi-media, graph, spatio-temporal and semi-structured  data
       * Security, privacy, and adversarial data mining
       * High performance and parallel/distributed data mining
       * Mining tera-/peta-scale data
       * Visual data mining and data visualization

    To ensure wide visibility  of  material  published at  the  conference, we plan
    to make arrangements with ACM SIGMOD  for including  the proceedings  of the conference in the SIGMOD on-line and CD-ROM  archives.  Two awards- for  Best Paper and Best Student Paper, will be presented at the conference.

     

    Important Dates:

     

    ·         Research and Student Papers-July  31, 2009

    ·         Industrial Papers, Demo, Panel and Tutorial Proposals-August 28, 2009

    ·         Notification to authors-September 25th, 2009

    ·         Camera-ready copy due-October 30, 2009

    ·         Early-Bird Registration Deadline-December 1, 2009

    ·         Conference-December 9-11, 2009

     

     

    Conference Organization: 

     

    General Chair

     

    ·         Shalini Urs, ISiM, University of Mysore, India

     


    Organizational Chair

     

    ·          Srinath Srinivasa, IIIT-Bangalore, India

     


    Program Chairs

     

    ·         Sanjay Chawla, University of Sydney , Australia

    ·         Kamal Karlapalem, IIIT-Hyderabad, India.

     

    Contact Details:

    Dr. Shalini Urs

    COMAD 2009 – General Chair

    &

    Executive Director and Professor

    International School of Information Management

    University of Mysore, Manasagangotri

    Mysore – 570 006

    Tel: +91-821-2514699; +91-821-2411417

    Fax: +91-821-2519209,

    Email: office@isim.ac.in

     

    Regards

     

    Thursday, June 25, 2009 10:37 AM
  • Thanks sangat.

    Primary Key wasn't set and RefreshTable option in the Designer wasnot enables.


    It works!

    When you get to know how to do it, you would say how easy was that!! LOL!

    My nephew says: "Mathematics and Politics are a part of life."
    Saturday, June 27, 2009 11:00 PM
  • It is my understanding that when using a TableAdapter and selecting the proper options, Insert, Delete and Update statements are generated. The Update statement should work just using the following:
    myTableAdapter.Update(changes);
    Where changes is the DataSet containing the modified data.

    This logic works great for one of the tables in the DB but not for another.

    Learning this new stuff is Confusing at best - Frustrating at worst.

    Help appreciated,

    Michael


    Don't over complicate this. 
    The Clue is in this post: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx

    First,  Check your Table for a Primary Key. 
    If you don't have one, set one.  This should have been part of the initial design. 
    (From walking through this Forum I see this as a common error, but don't understand why this wasn't caught in the design walkthroughs.) 

    Next, Check the Table's  "Refresh your Datatable" Check box as follows:
    1.  Go to Edit in DatasetDesigner.
    2.  Select your table by right clicking on it.
    3.  Click on the Advance Options... button
    4.  Click on the Refresh the Data Table check box
    5.  Click Ok.
    6.  Click Next
    7.  Click Next (again)
    8.  Click Finish

    Finally, Retest the code.


    "A well-designed database also performs better. "  

    • Proposed as answer by m_ward Friday, July 10, 2009 5:07 PM
    Friday, July 10, 2009 5:07 PM
  • What can you do if the "refresh the data table box" is grayed out?
    Monday, August 17, 2009 7:00 PM
  • Make sure the table has a Primary Key and also make sure that Key field is included when you add the table and fields to the dataset.
    Wednesday, August 26, 2009 1:00 PM
  • thanks all it works!!

    the refresh box is grayed out only when the table doesnt have a primary key set.

    first set a primary key to the table
    go to tableadapter -- right click -- configure.. -- advanced options -- mark the check box "refresh the data table" -- click ok --  click finish

    Thursday, August 27, 2009 4:53 PM
  • Still very helpful, even after 3 years.
    Sunday, October 25, 2009 2:45 PM
  • This is still VERY VERY HELPFUL after years, great thread. I dont' really have a lot of experience in access databases. Was able to have one table for inner join sql statement and the other for another sql statement. Just wanted to update one table and this did it for me, thanks so much man!

    Many Thanks!!!!
    Monday, October 26, 2009 4:06 AM
  • I expanded on this and used the following to cover all bases:

            Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(adapter)

            adapter.UpdateCommand = myBuilder.GetUpdateCommand
            adapter.InsertCommand = myBuilder.GetInsertCommand
            adapter.DeleteCommand = myBuilder.GetDeleteCommand

    Friday, November 20, 2009 4:48 PM
  • my problem was that i didn't have a generated update query and i made one but i was practicing sql so it didn't generate the right way because i found no use in primary key, i made one and it was generated in a successful way
    Tuesday, December 29, 2009 5:18 AM
  • Wow - this is awesome - you just helped me solve problem with related data grids on all my projects not saving and deleting records properly - I had spec. char. in column names AND forgot the primary key on my child table.. Thanks So much  - Claudeb1965 and  William Vaughn (MVP)
    Thursday, January 14, 2010 9:05 PM
  • Hi I had read all posts and some of them ar similar to me, in the following cases:
    1. My datatable is dinamically create, so I don't have a designer view.
    2. My datatable it has a primary key, so I don't nee to create one.
    3. All my commands have the SQL statement setup.

    What I am trying to do is to get the Batch Update to work, using the UpdateBatchSize.

    If I use the CommandBuilder the single update works fines, but with sqlDataAdapter.UpdateBatchSize = 3 I get an error sayng the StatementType enumeration, 4, is invalid Parameter name: StatementType.

    Ok so I set the querys manually, but now the error is Update requires a valid UpdateCommand when passed DataRow collection with modified rows

    This is the code:

    Using sqlAdapterAllocs
                With sqlAdapterAllocs
                    'Dim cmdBuilder As New SqlClient.SqlCommandBuilder(sqlAdapterAllocs)
                    Using cnOmf As New SqlClient.SqlConnection(My.Settings.dns)
                        .SelectCommand = New SqlClient.SqlCommand(My.Resources.AllocationSelect, New SqlClient.SqlConnection(My.Settings.dsnTest))

                        .InsertCommand = New SqlClient.SqlCommand("insert into Sol.dbo.LstAllocations (BizId,SubRegId,PmtModeId,CarId,PctAlloc) values (@BizId,@SubRegId,@PmtModeId,@CarId,@PctAlloc);", cnOmf) 'cmdBuilder.GetInsertCommand()
                        .InsertCommand.Parameters.Add(New SqlClient.SqlParameter("@BizId", SqlDbType.SmallInt, 2, "BizId"))
                        .InsertCommand.Parameters.Add(New SqlClient.SqlParameter("@SubRegId", SqlDbType.SmallInt, 2, "SubRegId"))
                        .InsertCommand.Parameters.Add(New SqlClient.SqlParameter("@PmtModeId", SqlDbType.SmallInt, 2, "PmtModeId"))
                        .InsertCommand.Parameters.Add(New SqlClient.SqlParameter("@CarId", SqlDbType.SmallInt, 2, "CarId"))
                        .InsertCommand.Parameters.Add(New SqlClient.SqlParameter("@PctAlloc", SqlDbType.Decimal, 5, "PctAlloc"))
                        .InsertCommand.UpdatedRowSource = UpdateRowSource.None

                        .DeleteCommand = New SqlClient.SqlCommand("delete from Sol.dbo.LstAllocations where BizId=@BizId and SubRegId = @SubRegId and PmtModeId=@PmtModeId and CarId = @CarId; ", cnOmf) 'cmdBuilder.GetDeleteCommand()
                        .DeleteCommand.Parameters.Add(New SqlClient.SqlParameter("@BizId", SqlDbType.SmallInt, 2, "BizId"))
                        .DeleteCommand.Parameters.Add(New SqlClient.SqlParameter("@SubRegId", SqlDbType.SmallInt, 2, "SubRegId"))
                        .DeleteCommand.Parameters.Add(New SqlClient.SqlParameter("@PmtModeId", SqlDbType.SmallInt, 2, "PmtModeId"))
                        .DeleteCommand.Parameters.Add(New SqlClient.SqlParameter("@CarId", SqlDbType.SmallInt, 2, "CarId"))
                        .DeleteCommand.UpdatedRowSource = UpdateRowSource.None

                        .UpdateCommand = New SqlClient.SqlCommand("update Sol.dbo.LstAllocations set PctAlloc=@PctAlloc where AllocId = @AllocId;", cnOmf) 'cmdBuilder.GetUpdateCommand()
                        .UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@AllocId", SqlDbType.SmallInt, 2, "AllocId"))
                        .UpdateCommand.Parameters.Add(New SqlClient.SqlParameter("@PctAlloc", SqlDbType.Decimal, 5, "PctAlloc"))
                        .UpdateCommand.UpdatedRowSource = UpdateRowSource.None

                        .Fill(Me.dsData, "LstAllocations")
                    End Using
                End With
            End Using

     Me.sqlAdapterAllocs.UpdateBatchSize = intCarCount
                Me.sqlAdapterAllocs.Update(dsData, "LstAllocations")


    Please please any ideas??

    Tuesday, January 19, 2010 11:38 PM
  • I ran into this problem last week and have been slowly going mad trying to debug it.
    My issue - the SqlCommandBuilder been used to generate the update command was been disposed.

    Ensure that you are not disposing of your command builder class before calling the data adapters update method.
    • Proposed as answer by ahosie Wednesday, January 27, 2010 1:08 AM
    • Edited by ahosie Wednesday, January 27, 2010 1:09 AM that's not English!
    Wednesday, January 27, 2010 1:08 AM
  • If it's an option, try deleting your table adapter and re-adding it. Keep in mind that you will loose your queries. However, if you only have one or two it may be worth the trouble. Make sure your Primary Key is set on your table before you re-add it.
    Thursday, March 11, 2010 10:35 PM
  • It is my understanding that when using a TableAdapter and selecting the proper options, Insert, Delete and Update statements are generated. The Update statement should work just using the following:
    myTableAdapter.Update(changes);
    Where changes is the DataSet containing the modified data.

    This logic works great for one of the tables in the DB but not for another.

    Learning this new stuff is Confusing at best - Frustrating at worst.

    Help appreciated,

    Michael


    Don't over complicate this. 
    The Clue is in this post: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx

    First,  Check your Table for a Primary Key. 
    If you don't have one, set one.  This should have been part of the initial design. 
    (From walking through this Forum I see this as a common error, but don't understand why this wasn't caught in the design walkthroughs.) 

    Next, Check the Table's  "Refresh your Datatable" Check box as follows:
    1.  Go to Edit in DatasetDesigner.
    2.  Select your table by right clicking on it.
    3.  Click on the Advance Options... button
    4.  Click on the Refresh the Data Table check box
    5.  Click Ok.
    6.  Click Next
    7.  Click Next (again)
    8.  Click Finish

    Finally, Retest the code.


    "A well-designed database also performs better. "  

    Its really not fair to assume that this will solve everyone's problem. I have this same problem and I read this entire post. For me:

     

    I set a primary key both in access and in visual studio.

    I don't have the option of selecting the refresh data box as it is grayed out even after I set the primary key.

    It appears this isn't an option when using an access database according to the help file.

     

    So I'm still stuck on this error.

    Friday, April 16, 2010 7:39 PM
  • After a long overdue return to DB work I had the same problem today when trying to Update after changing data in a dataGridView.

    My problem was, as one of Claudeb1965's items states, a lack of primary key both in the table and the dataset as I had created them merely just to remind myself of a little ADO .NET.

    Setting PK's in the dataset and the table fixed the problem, thanks!

    Wednesday, April 21, 2010 1:53 PM
  • I too had same problem and I successfully cleared it by using solutions given in the Forum...I need to clarify the solution with easy two steps...

    Step 1 : Go to the Table -> Open Table Definition -> right click first column (Sl.No) column with int or bigint datatype and set primary key option. If your table doesnot contain such column Insert a column with int or bigint datatype and set it as primary key.This is useful for the Table Adapter to Update columns.

    Step 2: As friend suggested Go to Menu->Data--> ShowDataSources-> right click Dataset & select Edit Dataset with Designer -> right click TableAdapter & select configure --> Go to AdvancedOptions & click Refresh CheckBox. Definitely CheckBox will be active & get saved.

    Thats all..Go & Debug the problem solved

    Thanks

    This is Pradeep from India


    • Proposed as answer by gsusPRADEEP Thursday, April 29, 2010 4:04 PM
    Thursday, April 29, 2010 4:04 PM
  • Hello Michael,

    I had the same error but it was caused by not having a PRIMARY KEY on the table (in my case MySQL table) when I created the TableAdapter. What I did was: first, I added a PK to my table. Second: Deleted the TableAdapter and third: Created the TableAdapter again. That solved the problem. Hope this helps

    Cheers

    Thursday, April 29, 2010 6:35 PM
  • Hi Osama!! it worked for me...awesome!!! thanks for this answer :) :) :)


    Saturday, May 22, 2010 10:42 PM
  • thanks osama
    Tuesday, May 25, 2010 2:08 AM
    • Don't use the designer/auto generated code. It may be possible with that somehow also, but i don't know how.
    • Use the example in this article: http://msdn.microsoft.com/en-us/library/fbk67b6z.aspx as a template code. The problem with using it for a view is, it uses SqlCommandBuilder to autogenerate the update from the select, which it seems doesn't work with multiple tables.
    • Change that code so that you add the select/update commands to the SqlDataAdapter manually, as explained here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx

    This is the result which works for me (a little simplified, had some more Columns besides "Value" which where editable)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    using System.Diagnostics;
    using System.Drawing;
    
    namespace myProg
    {
     public class Form1 : System.Windows.Forms.Form
     {
      private DataGridView dataGridView1 = new DataGridView();
      private BindingSource bindingSource1 = new BindingSource();
      private SqlDataAdapter dataAdapter = new SqlDataAdapter();
      private Button reloadButton = new Button();
      private Button submitButton = new Button();
      private ComboBox landCombo = new ComboBox();
      private ComboBox dwServerCombo = new ComboBox();
    
      [STAThreadAttribute()]
      public static void Main()
      {
       Application.Run(new Form1());
      }
    
      // Initialize the form.
      public Form1()
      {
       this.AutoSize = true;
       dataGridView1.Dock = DockStyle.Fill;
    
       reloadButton.Text = "reload";
       submitButton.Text = "submit";
       reloadButton.Click += new System.EventHandler(reloadButton_Click);
       submitButton.Click += new System.EventHandler(submitButton_Click);
       landCombo.DropDownStyle = ComboBoxStyle.DropDownList;
       landCombo.Items.AddRange(new object[] { "Österreich", "Italien" });
       landCombo.SelectionChangeCommitted += new System.EventHandler(land_selection_changed);
       landCombo.SelectedItem = "Österreich";
       dwServerCombo.DropDownStyle = ComboBoxStyle.DropDownList;
       dwServerCombo.Items.AddRange(new object[] { "srv1" });
       dwServerCombo.SelectionChangeCommitted += new System.EventHandler(server_selection_changed);
       dwServerCombo.SelectedItem = "srv1";
       
    
       FlowLayoutPanel panel = new FlowLayoutPanel();
       panel.Dock = DockStyle.Top;
       panel.AutoSize = true;
       panel.Controls.AddRange(new Control[] { reloadButton, submitButton, landCombo,
                 dwServerCombo});
    
       this.Controls.AddRange(new Control[] { dataGridView1, panel });
    
       this.Load += new System.EventHandler(Form1_Load);
       this.Text = "db Editor";
       
      }
    
      private void Form1_Load(object sender, System.EventArgs e)
      {
       // Bind the DataGridView to the BindingSource
       // and load the data from the database.
       dataGridView1.DataSource = bindingSource1;
       GetData();
       dataGridView1.AllowUserToAddRows = false;
       dataGridView1.AllowUserToDeleteRows = false;
       this.Size = new System.Drawing.Size(810, 600);
      }
    
      private void reloadButton_Click(object sender, System.EventArgs e)
      {
       GetData();
      }
    
      private void submitButton_Click(object sender, System.EventArgs e)
      {
       try {
        dataAdapter.Update((DataTable)bindingSource1.DataSource);
        MessageBox.Show("DB - Update complete");
       }
       catch (SqlException ex)
       {
        MessageBox.Show("SqlException: \n" + ex.ToString());
       }
      }
    
    
    
      private void land_selection_changed(object sender, System.EventArgs e)
      {
       GetData();
      }
    
      private void server_selection_changed(object sender, System.EventArgs e)
      {
       GetData();
      }
    
      private void GetData()
      {
       try
       {
        String connectionString =
          "Integrated Security=SSPI;Persist Security Info=False;" +
          "Initial Catalog=foo;Data Source=" +
          dwServerCombo.SelectedItem.ToString();
        Debug.WriteLine(connectionString);
    
        SqlConnection dbconn = new SqlConnection(connectionString);
        dataAdapter = CreateCustomerAdapter(dbconn);
    
        dataAdapter.SelectCommand.Parameters["@Country"].Value =
         landCombo.SelectedItem.ToString();
    
        // Populate a new data table and bind it to the BindingSource.
        DataTable table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        dataAdapter.Fill(table);
        bindingSource1.DataSource = table;
    
        // Resize the DataGridView columns to fit the newly loaded content.
        dataGridView1.AutoResizeColumns();
    
       }
       catch (SqlException ex)
       {
        MessageBox.Show("SqlException: \n" + ex.ToString());
       }
      }
    
      private static SqlDataAdapter CreateCustomerAdapter(
       SqlConnection connection)
      {
       Debug.WriteLine("CreateCustomAdapter");
       SqlDataAdapter adapter = new SqlDataAdapter();
    
       // Create the SelectCommand.
       SqlCommand command = new SqlCommand("SELECT table1.ID, table2.Text " +
         " table1.Value from table1 join table2 on table1.id2 = table2.id2" + 
         " WHERE table1.Country = @Country", connection);
    
       command.Parameters.Add("@Country", SqlDbType.NVarChar, 50);
    
       adapter.SelectCommand = command;
    
       // Create the UpdateCommand.
       command = new SqlCommand(
        "UPDATE table1 SET Value = @Value, " +
        " WHERE ID = @oldID", connection);
    
       // Add the parameters for the UpdateCommand.
       command.Parameters.Add("@Value", SqlDbType.Decimal, 5, "Value");
       SqlParameter parameter = command.Parameters.Add(
        "@oldID", SqlDbType.Int, 4, "ID");
       parameter.SourceVersion = DataRowVersion.Original;
    
       adapter.UpdateCommand = command;
    
    
       return adapter;
      }
    
    
     }
     
    }
    
    Friday, May 28, 2010 9:33 AM
  • This also helped me. I was using MS Access. I had to replace the code here:

    Dim myBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(daUpdPers)


    Christine Lee
    Wednesday, July 14, 2010 4:16 PM
  • Here is another way,

     

    Go to Menu->Data--> ShowDataSources-> right click Dataset & select Edit Dataset with Designer -> right click TableAdapter & select configure --> Go to AdvancedOptions & click Refresh CheckBox. if the "use optimistic concurency" and "refresh the data table"  options are still grayed out, don't worry.

     

    -Create the tables from the beginning

    - right click the tables directory in database explorer

    - Select "create table"

    - Use "New table" dialog to define columns (For example table name: Mytable)

    - Don't forget to define Primary Key

    - Drag "Mytable" to dataset designer area

    - On designer window select the "Mytabletableadapter" window title bar just below the "Mytable" window

    - Examine the properties window, InsertCommand, SelectCommand  and UpdateCommand properties should be filled automatically. In this case you have everything you need to fix the problem

     

    Otherwise try following.

    - Now right click "Mytabletableadapter" window title bar, select "configure"

    - On Table Adapter Config. Wizard window, click "Next"

    - Check all the chek boxes

    - Click "Next" Again

    - Generated Select statement, insert statement, table mappings, fill method, get method and update methods will be confirmed

    - Click "Finish"

    - You can also try to open advanced window, uncheck the chekbox, press ok, open advanced window again check the checkbox and press next

    Monday, August 30, 2010 9:23 AM
  • Thanks Talbengal,

     

    Your post solved my problem.

     

    For the C# with MySQL solution:

                MySqlCommandBuilder sqlBuilder = new MySqlCommandBuilder(dataAdapter);
                sqlBuilder.GetUpdateCommand();

                dataAdapter.Update(dataSet, "dataMember");

     

    Thanks again,

    Andy

    Friday, October 15, 2010 10:06 AM
  • Thanks Pradeep

    Thanks a lot ...

    Saturday, November 20, 2010 2:21 PM
  • hi can we save data from 2 dataset in one table

    like dataset1+dataset2 =save in single table

    then how to do it.

     

    can u help me???

    Thursday, December 2, 2010 9:22 AM
  • HAHA, you are so cute!
    Friday, January 7, 2011 6:03 AM
  • I found that what mynolix & Osama Alborbar said was true.

    First I set a primary key and then set the refresh data check box and it worked for me.

    Thanks guys.

    Oh YEAH! That works!                                                                           F**k Microsoft~
    Friday, January 7, 2011 6:06 AM
  • I tried nearly everything suggested: Setting a primary key, unique field names with no special characters or spaces and I was unable to check the refresh table data due to it being disabled.

    And then I discovered that one statement was out of place. In the following code block

    ***if you notice all my DIM statments are here like they USED to be required to be in VB 6

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource, datenow As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim i, numofrecords As Integer

    *** I had to move one required DIM statement down in the code

     

    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"

    dbSource = "Data Source = c:\employeeclock03.mdb"
    con.ConnectionString = dbProvider & dbSource
    con.Open()
    sql =
    "SELECT * FROM EmployeeTable"
    da =New OleDb.OleDbDataAdapter(sql, con)
    'this guy right here
    *****Dim cb As New OleDb.OleDbCommandBuilder (da)

     As soon as I moved it down to here below da was defined my da.Update statement worked!!!!

    I guess thats one of the new things in vb.net is that dim statements no longer have to be above your code. Since I am new to .net that just blows my mind

    Friday, April 1, 2011 9:06 PM
  • I solved this problem by add UpdateCommand in TableAdapter and add string to update Dataset above 

    Discrete - my Table in DB SQL

     

    Me.DiscreteTableAdapter.Update(Me.St1DataSet.Discrete)

    SqlDataAdapter1.Update(St1DataSet)



    Thursday, May 12, 2011 8:05 AM
  • Try putting this before your Update() method:

    da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();
    

    So your code is something like:

    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter(sqlSelectCommand, connectionString);

    da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand(); da.Update(ds, "TableName");

    Sunday, May 15, 2011 3:07 PM
  • hi,

    this thread resolve my problem.

    in my case a not nullable field raise the exception, but without trick was impossible detect, with the trick raise the nullable problem.

    Now, i deleted the trick and manage the field not nullable and finally insert the row without problem

     

    thank you all

    Friday, July 22, 2011 10:30 AM
  • Edit the Data Set and add a Update command See image

    Then the
    INHEADTableAdapter.Update(DataSet1.INHEAD)
    Willl Work

    • Proposed as answer by JackWalker Friday, September 2, 2011 4:15 AM
    Friday, September 2, 2011 2:40 AM
  • create a method for update.

    Goto Data->show data sources, Right click on the table you are having probs with and edit dataset with designer.  Right click on the table adapter and choose properties, expand Data->updateCommand.  Choose new, expand updatecommand '+'.  Go to command text and add table.  Select the fields you reuire you will end up wilth a query similar:

    UPDATE    TBLCustomer
      SET              name =, address =, phone =

     

    change to

    UPDATE    TBLCustomer
      SET              name =?, address =?, phone =?

     

    Hit f5 and test

     

    Hope this helps.

     

    Spent 4 days trying to figure this out and THIS worked for me! Thanks for the HELP!!!

     

    Blah, it kinda worked. Now when i update changes it updates all the rows with the same changes :(

    • Edited by Crustie Monday, September 19, 2011 1:21 AM
    Sunday, September 18, 2011 11:06 PM
  • Thank you. This worked for me.
    Monday, October 3, 2011 7:50 PM
  • Coming late to this one, but adding a PRIMARY KEY did the trick for me.  
    Friday, March 2, 2012 5:38 PM
  • I came across this post because I was getting the same problem. I tried most suggestions above but in the end I deleted my DataSet.xsd file then recreated it from my database, this finally fixed the problem.

    Over & Out Big Buddy!

    Wednesday, March 28, 2012 7:29 AM
      • Hi guys... I'm still a noob in Visual Basic, im also experiencing the same problems, i can't update my table once i start editing it, i get this error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows.", I've tried the following:

        - Enable "Refresh the table" (i did add a primary key, still can't enable it)

        - i tried using the update command but it updates the whole column... not exactly what i'm looking for.

        I was wondering how do you create an update command for a single ROW only?? As in, the data in the datagrid can be edited anytime? Thanks.

        I'm using Visual Basic 2008 Express Edition + MS Access 2007... Hope this info helps... 


    • Edited by DX1992 Saturday, May 26, 2012 5:59 AM
    Saturday, May 26, 2012 5:58 AM
  • HI all,

    Osama's solution helps a lot. However, this seems to be a two way issue and that is but the second way out (At least thats what i have found).

    Try this and revert with your results;

    1. Right click the table in question and choose to view the data definition

    2. Select the column you want to set as Primary Key and select Primary Key from the resulting menu items(its about the first item)

    3. Save what you have done.

    4. Follow the instructions given by Osama above

    This should get you running in no time.

    Thanks to all for making coding a lot more easier.

    Lordoasis

    Saturday, November 17, 2012 8:09 PM
  • am 67, was novice  coder, doing fortran in 1968, when cpus were slow and memory limited, code had to be tight, ms computer sci.

    unlike now,  bloat is not only ok, is cryptically byzantine by design, having to grind thru the gist of this thread (absent any ms “unleashed” authored $$  book or free msdn definitively clearer) slog just to get a simple db ds table update to  work vb recurses the job security incentive for  those who keep it so arcanely complex  as to make it difficult for joe too easily replace ms cash products, which, of course,  recurses  the revenue stream that pays them to foil us, frustrated about their intricacies.

    Sunday, November 25, 2012 4:45 AM
  • But what if there is no table adapter in the designer (my case, as I create these in code)?

    I definitely have valid int primary keys in dataset table and in database.  The code has been working for 11 years, but stopped working when I moved to .Net 3.5 and a newer ODP.Net.  It simply fails to generate the Insert command under certain circumstances.

    Tuesday, August 20, 2013 3:52 PM
  • be sure that your TableAdapter is contain your primary key and your table has primary key.

    Monday, September 23, 2013 4:43 PM
  • thanx it helped !!:)
    Saturday, October 12, 2013 12:57 PM
  • In Advance option

    Refresh the data table . Option is not enable

    Tell Me what to do now Plz.

    Sunday, May 4, 2014 6:44 AM
  • Create ALWAYS                PRIMARY KEY and then -> OPEN Dataset->RIGHT CLICK ON SQL COMMAND in TABLE ADAPTER and CONFIGURE->Enter the missing columns in the FUC....ng statement AND YOU ARE DONE....FOR GOD SAKE THIS IS NOT NUCLEAR PHYSICS.....
    Friday, May 16, 2014 12:01 AM
  • YES

    Great worked like a charm :)

    Tuesday, January 20, 2015 9:32 AM
  • I am very thankful to you man. you have really helped me. once again than you so much
    Tuesday, April 21, 2015 4:27 AM
  • Hi!

    I had the same Problem too.

    I solved the Problem very easy: Look up for your table and check if you have a PrimaryKey.

    If not set it!

     


    Thanks a million!  I normally have that set and was in a hurry today multi-tasking and forgot to set the key.  It was bugging the heck out of me hunting it down.  Once I saw your post I facedpalmed myself and got it working! LOL
    Wednesday, September 16, 2015 11:03 PM
  • This is pretty close to my solution. Here is similar method using sqlcommand builder, and mysql connectionstring which is stored in setting strings. As documented on MSDN someplace.

      sqlconn.Open()
                    Dim sqlempDA As New MySqlDataAdapter
                    Dim sqlempcmd As New MySqlCommand("SELECT * FROM MACH Where MACHID = '" & My.Settings.strMach & "'", sqlconn)
                    Dim sqlemp As New DataTable
                    sqlempDA.SelectCommand = sqlempcmd
                    sqlempDA.FillSchema(sqlemp, SchemaType.Source)
                    sqlempDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    Dim objCommandBuilder As New MySqlCommandBuilder(sqlempDA)
                    sqlempDA.Fill(sqlemp)
                    sqlconn.Close()

                        sqlemp(0)("CUREMPID") = Me.ScanBox.Text
                        sqlemp(0)("EMPNAME") = Me.EMPNAME.Text

                        sqlempDA.Update(sqlemp)

    • Edited by Gtripodi Tuesday, October 27, 2015 1:03 PM
    Tuesday, October 27, 2015 1:00 PM
  • Hi, I am working on an Excel 2010 workbook project. I am facing similar error. When I go to Configure and then check the "refresh the data table" press ok and then press finish. It does not save that option. When I go back to configure and "advance options" I see the box unchecked.
    Wednesday, December 16, 2015 4:29 PM
  • THANKS TO U. UR METHOD WORKED FINE WITHOUT ANY ERRORS.......
    Tuesday, January 19, 2016 8:22 PM
  • Thanks a lot. Saved the day even after 7 years of publishing your answer. Great
    Friday, March 18, 2016 9:42 AM
  • but what about those tables or data that are used with OleDb.OleDbDataAdapte . 

    Monday, August 29, 2016 2:57 PM
  • Feel your frustration Michael

    "Update requires a valid UpdateCommand when passed DataRow collection with modified rows"

    is another MickySoft  red herring... error message not in the least bit helpful

    the data refresh option mentioned below, does not stick when you check the box, again with no error or explanation from MickySoft.

    Then if the table is without a primary key this can also cause problems but again no error message from the MS

    MS developers on this must make their families very proud that they can waste so many peoples time.....

    and I note that it is now 2017 and its still causing problems and  waste of time.

    Thursday, December 21, 2017 2:48 PM
  • On the plus side this year I manged to move 2 elements of my companies tech away from MS....

    are MS at all interested? in losing business.... I guess not.

    Thursday, December 21, 2017 2:50 PM
  • So ashamed are you that you had to delete my valid criticism ..... babies....
    Thursday, December 21, 2017 2:57 PM
  • I tried this and like others found that the setting was not saved. I tried it again, but this time I clicked Next until I reached the end of the options, THEN clicked Finish, and it saved the setting.
    Wednesday, September 12, 2018 6:50 PM
  • As stated in another reply below, your table must have a Primary Key assigned in SQL Server (Management Studio). Setting the key in the VS designer is not sufficient to create an UPDATE method that will update rows.

    After assigning the Primary Key in SQL Server, back in VS, edit the tableadapter and click the REFRESH box in the Advanced Options to force VS to re-read the changed SQL table.

    Tuesday, May 28, 2019 4:42 PM
  • Hello Michael

                       I know this is a reply to an old post, however this error is still relevant today.

    I found the error is caused by not having at least 1 of your table fields being a primary key. 

    So if you make certain that the table you are trying to update has at least 1 primary key field, this will not then cause an error and update your table.

    Tip: If you are updating changes in a Datatable, use the code below as this will update ALL changes in one action

    Me.Validate()
    Me.TeamBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.<YourDatasetName>)

    This code is located behind the Save option in the table BindingNavigator

    Hope this helps

    Cool


    Friday, August 14, 2020 9:05 AM