CODEWITHSUNDEEP
c#sqloledb
user3684557
user3684557

Reputation: 73

Update Command and C#

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

Upvotes: 0

Views: 531

Answers (3)

user3684557
user3684557

Reputation: 73

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

Upvotes: 0

mybirthname
mybirthname

Reputation: 18127

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.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152491

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.

Upvotes: 2

Related Questions