Update Command and C#

Question

I am updating the dataset row with new data from textboxes, then trying to update it to my database. I keep getting this error:

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

How can I fix this error?

Here's my code:

protected void Save_Butt_Click( object sender, EventArgs e ) {
    OleDbConnection connect = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/PizzaOrders.mdb;Persist Security Info=True" );
    //set up connection string
    OleDbCommand command = new OleDbCommand("SELECT [title], [gname], [sname], [address], [suburb], [postcode], [dayphone], [email] FROM [users] WHERE ([username] = @username)", connect);
    OleDbParameter param0 = new OleDbParameter("@username", OleDbType.VarChar);

    param0.Value = HttpContext.Current.User.Identity.Name;
    command.Parameters.Add(param0);

    connect.Open();

    OleDbDataAdapter da = new OleDbDataAdapter(command);
    DataSet dset = new DataSet();

    da.Fill(dset);

    dset.Tables[0].Rows[0]["title"] = Title_DDL.Text;
    dset.Tables[0].Rows[0]["gname"] = Fname_txt.Text;
    dset.Tables[0].Rows[0]["sname"] = LN_txt.Text;
    dset.Tables[0].Rows[0]["address"] = Address_txt.Text;
    dset.Tables[0].Rows[0]["suburb"] = suburb_txt.Text;
    dset.Tables[0].Rows[0]["postcode"] = Postcode_txt.Text;
    dset.Tables[0].Rows[0]["dayphone"] = Phone_txt.Text;
    dset.Tables[0].Rows[0]["email"] = Email_txt.Text;

    da.Update(dset);
}

DataAdapter doens't generate the SQL commands to manipulate your database. You have to use a CommandBuilder to generate those commands: msdn.microsoft.com/en-us/library/tf579hcz(v=vs.110).aspx — Caffé
– Caffé, Commented Sep 19, 2014 at 15:51
Better tutorial: csharp.net-informations.com/dataadapter/… — Caffé
– Caffé, Commented Sep 19, 2014 at 15:54

D Stanley · Accepted Answer · 2014-09-19 15:53:05Z

2

Use an OleDbcommandBuilder to generate the UpdateCommnand:

OleDbDataAdapter da = new OleDbDataAdapter(command);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);

But you'll need to include the primary key in the SELECT command so the update command which rows to update.

answered Sep 19, 2014 at 15:53

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3684557 Over a year ago

Now I get... "OleDbCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size."

D Stanley Over a year ago

So set the size of your @username parameter to the size of the username column.

user3684557 Over a year ago

OleDbParameter param0 = new OleDbParameter("@username", OleDbType.VarChar, 255); Right? Now I don't get any errors, but the data is not being updated in the db.

D Stanley Over a year ago

Did you add the table's primary key to your SELECT statement?

D Stanley Over a year ago

Note that you could also write the UpdateStatement yourself and just set it on the DataAdapter. But you'll still need a primary key to know which row to update.

|

mybirthname · Accepted Answer · 2014-09-19 16:25:45Z

0

OleDbConnection connect = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/PizzaOrders.mdb;Persist Security Info=True" );

    connect.Open();
    //set up connection string
    OleDbCommand command = new OleDbCommand("SELECT [ID], [title], [gname], [sname], [address], [suburb], [postcode], [dayphone], [email] FROM [users] WHERE ([username] = @username)", connect);
    OleDbParameter param0 = new OleDbParameter("@username", OleDbType.VarChar);

    param0.Value = HttpContext.Current.User.Identity.Name;
    command.Parameters.Add(param0);



    OleDbDataAdapter da = new OleDbDataAdapter(command);
    DataSet dset = new DataSet();

    da.Fill(dset);

    dset.Tables[0].Rows[0]["title"] = Title_DDL.Text;
    dset.Tables[0].Rows[0]["gname"] = Fname_txt.Text;
    dset.Tables[0].Rows[0]["sname"] = LN_txt.Text;
    dset.Tables[0].Rows[0]["address"] = Address_txt.Text;
    dset.Tables[0].Rows[0]["suburb"] = suburb_txt.Text;
    dset.Tables[0].Rows[0]["postcode"] = Postcode_txt.Text;
    dset.Tables[0].Rows[0]["dayphone"] = Phone_txt.Text;
    dset.Tables[0].Rows[0]["email"] = Email_txt.Text;

    OleDbCommandBuilder builder = new OleDbCommandBuilder(ad);
            builder.QuotePrefix = "[";
            builder.QuoteSuffix = "]";

    da.Update(dset);

    connect.Close();

The exception means that you are trying to update the table without the primary key, because of that I add the field [ÏD] in your OleDbCommand. If other field is your primary key change ID with the primary key. Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

edited Sep 19, 2014 at 16:25

answered Sep 19, 2014 at 15:55

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

6 Comments

mybirthname Over a year ago

@user3684557 try it like this ?

mybirthname Over a year ago

@user3684557 i told you many times that you should add ID in the select clause. In your example it is not added, do you doing this in your code ?

user3684557 Over a year ago

"username" is my primary ID in the "user" table of my database.

mybirthname Over a year ago

the exception is the same ?

user3684557 Over a year ago

There is no error now. But there has been no change to my data base.

|

user3684557 · Accepted Answer · 2014-09-19 17:56:15Z

0

So I have worked out what the issue was... after an hour I figured out that the page was being reloaded before the button click event runs. Thus the page would update the screen with unmodified text from the DB, then when it tried to do an update on the rows... it found none of the fields were changed, so it didn't modify the data.

URRRGH! So frustrating... I knew it would be a simple issue... I fixed the page by placing a "get details" button on the page, so the page_load event would not override user input. sigh

answered Sep 19, 2014 at 17:56

user3684557

732 silver badges10 bronze badges

Collectives™ on Stack Overflow

Update Command and C#

3 Answers 3

6 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related