why its doesnt update?

Mfwamba Tshimanga

Hi!
I receive this message "Not updated".
Please have a look if there is a wrong code somewhere. Because me I didnt see any wrong here:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

namespace Entrada

{

public partial class SaidaViatura : Form

{

OleDbConnection conn;

OleDbCommand comm;

string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\lunarentacar\lunarentacar\\app_data\office.mdb;Persist Security Info=False";

public SaidaViatura()

{

InitializeComponent();

}

private void cbxCars(object sender, EventArgs e)

{

conn = new OleDbConnection(connstr);

comm = new OleDbCommand();

conn.Open();

OleDbParameter chaparia = new OleDbParameter("@chaparia", SqlDbType.VarChar);

comm.Connection = conn;

comm.CommandText = "UPDATE cars SET chaparia = @chaparia WHERE (code= @code)";

try

{

comm.ExecuteNonQuery();

//dgvSaidaviatura.CurrentCell.Value = dgvSaidaviatura.Rows[dgvSaidaviatura.Rows.Count - 1].Cells[0].Value;

MessageBox.Show("Update");

}

catch (Exception)

{

MessageBox.Show("Not updated");

}

finally

{

conn.Close();

}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mfwamba Tshimanga11y ago
ok, thanx!
Vulpes12y ago
When you're using the OleDb data provider, you should create your parameters as you normally would.

However, you should then add them to the parameters collection in the same order as they appear in your SELECT, UPDATE, INSERT or DELETE statement and, instead of using their names, indicate their presence by question marks within this statement.

The reason is that the OleDb provider identifies parameters by position rather than by name.

So it replaces the first question mark with the first parameter in the collection, the second question mark with the second parameter in the collection and so on.

Should you ever use ODBC then it's the same with that.

Just a peculiarity of the way these data providers work :)
Mfwamba Tshimanga12y ago
Thanx Vulpes, its works. But I would like to learn something. 1. Why did you put the question marks? 2. It's a rule to put the parameter in order?
Vulpes12y ago
As you're using OleDb is it an MS Access database you're updating?

If it is, then I think you'll need to replace the named parameters with positional parameters (indicated by question marks) within the UPDATE command itself.

That also means that you need to be careful in which order the parameters are added to the parameters collection.

Try this:

private void cbxCars_SelectedIndexChanged(object sender, EventArgs e)
{
conn = new OleDbConnection(connstr);
comm = new OleDbCommand();
conn.Open();
OleDbParameter code = new OleDbParameter("@code", SqlDbType.VarChar);
OleDbParameter chaparia = new OleDbParameter("@chaparia", SqlDbType.VarChar);
OleDbParameter ultimokm = new OleDbParameter("@ultimokm", SqlDbType.NChar);

// add parameters in the same order as in the UPDATE statement
comm.Parameters.Add(chaparia);
comm.Parameters.Add(ultimokm);
comm.Parameters.Add(code);

code.Value = txtcodeSaidaviatura.Text;
chaparia.Value = cbxChapariaSaidaviatura.Text;
ultimokm.Value = txtUltimokmSaidaviatura.Text;
comm.Connection = conn;
comm.CommandText = "UPDATE saidaviatura SET chaparia = ?, ultimokm = ? WHERE (code = ?)";
try
{
comm.ExecuteNonQuery();
//dgvSaidaviatura.CurrentCell.Value = dgvSaidaviatura.Row[dgvSaidaviatura.Rows.Count - 1].Cells[0].Value;
MessageBox.Show("Updated");
}
catch (Exception)
{
MessageBox.Show("Not updated");
}
finally
{
conn.Close();
}
}
Mfwamba Tshimanga12y ago
@vulpes
Please see what I do. Its showing the message "updated". But when I open my database I realize that nothing was updated. Please can you have a look:

private void cbxCars_SelectedIndexChanged(object sender, EventArgs e)

        {

            {

                conn = new OleDbConnection(connstr);

                comm = new OleDbCommand();

                conn.Open();

                OleDbParameter code = new OleDbParameter("@code", SqlDbType.VarChar);

                OleDbParameter chaparia = new OleDbParameter("@chaparia", SqlDbType.VarChar);

                OleDbParameter ultimokm = new OleDbParameter("@ultimokm", SqlDbType.NChar);

                comm.Parameters.Add(code);

                comm.Parameters.Add(chaparia);

                comm.Parameters.Add(ultimokm);

                code.Value = txtcodeSaidaviatura.Text;

                chaparia.Value = cbxChapariaSaidaviatura.Text;

                ultimokm.Value = txtUltimokmSaidaviatura.Text;

                comm.Connection = conn;

                comm.CommandText = "UPDATE saidaviatura SET chaparia = @chaparia, ultimokm = @ultimokm WHERE (code = @code)";

                try

                {

                    comm.ExecuteNonQuery();

                    //dgvSaidaviatura.CurrentCell.Value = dgvSaidaviatura.Rows[dgvSaidaviatura.Rows.Count - 1].Cells[0].Value;

                    MessageBox.Show("Updated");

                }

                catch (Exception)

                {

                    MessageBox.Show("Not updated");

                }

                finally

                {

                    conn.Close();

                }
Vulpes12y ago
Although you're using a parameter called @code in the UPDATE query, you haven't actually created a corresponding OleDbParameter object and given it a value.

Also, whilst you've created an object to match the @chaparia parameter, you haven't given it a value.