problems updating Access database from Textbox help?

Question

Hello everyone

I have searched, but nothing happens, I just starting using C#.NET and I a have a textbox on a form. I retrieve some data from the database and I display to a textbox through a combobox that indicate the section I want to display (I already do this!),but when I try to update nothing works, I click my button to update the access database(Access 2007) and nothing happens, the user just changes something and the button has to update the acces database, I hope you can help me :D this is my code so far:

        String textTobeUpdated = textBox3.Text;
        String thing = comboBox2.Text;




        using (var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDataBase.accdb"))

        using (var cmd = conn.CreateCommand())
        {


            cmd.CommandText = "UPDATE Section SET content = [@content] WHERE name= thing";
            cmd.Parameters.AddWithValue("@content",content);


            conn.Open();



            int rowsAffected = cmd.ExecuteNonQuery();
            if (rowsAffected == 1)
            {
                MessageBox.Show("Success");
            }
            else
            {
                MessageBox.Show(string.Format("{0} Rows Affected", rowsAffected));
            }

this code display a message that tells "Unhandled exception. If click in continue, the application will omit this error and intent continue. If click in exit, the application will close immediately" The are no especified value for some of the required parameters.

Answer 1

Looks like you execute the query twice (two areas where you call cmd.ExecuteNonQuery(); )

Remove the first one and leave the line that says

int rowsAffected = cmd.ExecuteNonQuery(); 

This line will perform the update, then count how many rows were updated. This count is then used to display a message to the user.

You're also not setting the content variable with a value, (I guess it should be the textbox value), and the update query itself seems a little odd, you haven't parameterised the 'thing' you're updating, so I guess it should be:

cmd.CommandText = "UPDATE Section SET content=[@content] WHERE name=[@thing]"; 
cmd.Parameters.AddWithValue("@content", textTobeUpdated);
cmd.Parameters.AddWithValue("@thing", thing);

Answer 2

If you are new to .NET and using a recent version of C#, then I would recommend that you write your data access code using LINQ.

You have to learn LINQ, but that is much more natural than learning ADO.NET.

You would then use a Repository classes to write the data access for your app, and you end up with a much more maintainable code base.

I think Slartibartfast's solution is correct, given the code, but I post this as an answer not a comment because using LINQ is the easier learning curve. You can then learn ADO.NET if you need to.

EDIT:

I think the bits of C# you need to read up on are LINQ, LINQ to SQL, LINQ to Entities, as well as the Repository Pattern for encapsulating Data Access code. Your code above is written using ADO.NET, which is the hard, low level way of doing it. Unless you have reams of legacy code, then LINQ is the way to go, I feel.

a. LINQ:

LINQ stands for language integrated query and it allows you to query a collection of objects much like you would a database. But it is also, in its LINQ to SQL and LINQ to Entities flavours, a Data Access technology. It allows you to query, create, edit and delete data in a database (or other datastore). It "looks" like SQL, but is strong typed.

b. Repository Pattern:

The Repository Pattern is a pattern that allows you to standardise (and test if so inclined) your data access code, all encapsulated in a class. It makes your code MUCH more maintainable.

The internet is awash with discussions, blogs and samples of how to use LINQ.

EDIT TO PROVIDE SAMPLE LINQ TO SQL OPTION:

The bare bones of what you want to do would be, roughly:

Assuming you have created the DBML file called dbDataContext, and that the record you want to update is in a table called MyTable:

// instantiate the datacontext object using the connection string
var db = new dbDataContext("myConnectionString");

// retrieve the record to update
var record = (from r in db.Section where r.Name == "thing" select r).Single();

if (record != null) record.Content = "Content";

try
{
// try to update the database
db.SubmitChanges();
MessageBox.Show("Success!");
}
catch
{
// Darn!  Didn't work...
MessageBox.Show("Ooops!");
}

Note how readable this is.

Answer 3

I doubt Access' database engine (ACE) will accept your UPDATE statement.

Here is an UPDATE statement which will work in Access, and change the content field values to "my updated contents" for all rows where a text field, name, is equal to "thing".

UPDATE [Section] SET content = 'my updated content' WHERE [name] = 'thing'

Compare that WHERE clause to your original:

WHERE name= thing

Without quotes to identify thing as a string value, ACE will not know what thing represents. The lack of quotes is probably the cause of your error message: "The are no especified value for some of the required parameters." ACE doesn't know what thing is, so treats it as a (missing) parameter.

Also notice I enclosed Section and name in square brackets. Both are reserved words; the brackets signal ACE they are table and field names respectively. See Problem names and reserved words in Access

Answer 4

Change: cmd.CommandText = "UPDATE Section SET content = [@content] WHERE name= thing";

To: cmd.CommandText = "UPDATE Section SET content = ? WHERE name= thing";

Through OleDB I don't think you can name the parameter in the command text to Jet/ACE. If you have more than one parameter, you'll have to keep them in order and continue to use '?'.

...Where [This] = ? AND [That] = ?;

Add parameters in the correct order.