problem updating data in a datagridview through a query update in access database 2007?

Question

Hello

Other time I need your help, I am developing an app in C# using an Access database(2007), the problem I think is the query updtate, I have searched over internet but nothing works, I have a datagridview and It has 3 columns the user needs to puts data to the last 2 columns(5 rows), I already do it but when I fill the columns in the datagridview It doenst store the data in the database access. So please I need you help this is my code, help very apreciated :D

private void btnGuardar_Click(object sender, EventArgs e)
    {

        //using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Databsename.accdb"))
        //{

        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Databasename.accdb;Persist Security Info=false");


        int grade = 1;
        string x;
        string comment;

            for (int i = 0; i < this.dataGridView4.Rows.Count-1; i++)
            {

                x = dataGridView4.Rows[i].Cells[1].Value.ToString();
                //Console.WriteLine(x);
                MessageBox.Show(x);

                grade = int.Parse(x);


                comment = dataGridView4.Rows[i].Cells[2].Value.ToString();
                MessageBox.Show(comment);

                OleDbCommand cmd = new OleDbCommand("Update archievemnet set grade= @GRADE comment=@COMMENT WHERE idLine =1  ", conn);

                cmd.Parameters.Add(new OleDbParameter("@GRADE", OleDbType.Integer));
                cmd.Parameters["@GRADE"].Value = grade;

                cmd.Parameters.Add(new OleDbParameter("@comment", OleDbType.VarChar));
                cmd.Parameters["@COMMENT"].Value = comment;

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }



        //}



    }

Answer 1

This is not the best solution, but you may try supplying the values directly in the query.

OleDbCommand cmd = new OleDbCommand("UPDATE archievemnet SET " + 
" grade = " + textBoxGrade.Text +
" comment = " + textBoxComment.Text +
" WHERE idLine = 1  ", conn);

cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

EDIT:

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Databasename.accdb;Persist Security Info=false");

int idLine;
double grade;
string comment;

for (int i = 0; i < this.dataGridView4.Rows.Count-1; i++)
{    
    idLine  = Convert.ToInt32(dataGridView4[0, i].Value);
    grade = Convert.ToDouble(dataGridView4[1, i].Value);
    comment = Convert.ToString(dataGridView4[2, i].Value);

    OleDbCommand cmd = new OleDbCommand("UPDATE archievemnet SET " + 
    " grade = " + grade +
    " comment = '" + comment + "' WHERE idLine = "+ idLine, conn);

    cmd.Connection.Open();
    cmd.ExecuteNonQuery();
    cmd.Connection.Close();
}

Answer 2

In your code you have "Update archievemnet ...". This is possibly the name of your table but the proper English word is "Achievement". Check that you are spelling the name of the table correctly. You should also have a try/catch block around your code to catch any exceptions thrown by Access or the db connectors, and some logging in the exception block. (I use and recommend NLog.) You say you are getting an exception, what type is it and what is the message?

I think you are better off using parameters in your final code (like you started doing), rather than string concatentation like yonan2236 is recommending - but it can sometimes help debugging. You might try:

    String updateStmt = "UPDATE archievemnet SET " + 
        " grade = " + grade +
        " comment = '" + comment + "' WHERE idLine = "+ idLine;
    OleDbCommand cmd = new OleDbCommand(updateStmt, conn);

and stop the code in the debugger just before you create the command. Copy the value of updateStmt into Access and see if you can execute the statement directly.

Answer 3

Your UPDATE statement needs a comma before comment=

Look at this example which works on my system:

UPDATE tblFoo AS f SET f.parent_id = 99, f.foo_text = "updated"
WHERE (((f.id)=10));

Without the comma after 99 ...:

UPDATE tblFoo AS f SET f.parent_id = 99 f.foo_text = "updated"
WHERE (((f.id)=10));

... Access complains:

Syntax error (missing operator) in query expression '99 f.foo_text = "updated"'.