How to Update a Table Using Oledb Parameters

Updating a table row with OleDB from C#

With OleDb the position of the parameters matters a lot.

OleDb doesn't associate parameters' placeholders with the parameters' names but follows a strictly positional order. So, your query is correct, but when you add the parameters to the collection you should follow the parameter placeholders order.

command.CommandType = CommandType.Text;
command.CommandText = "UPDATE Bills SET ([Payer] = @Payer, [Category] = @Category, ...) WHERE Id = @Id";
command.Parameters.AddWithValue("@Payer", bill.Payer);
command.Parameters.AddWithValue("@Category", bill.Category);
....
command.Parameters.AddWithValue("@Id", bill.Id);

With Access you can name your parameters as you do for its big cousin Sql Server albeit the OleDb docs say that you should use the question mark as parameter placeholder, however the names are simply ignored when the OleDb provider associates the values to the placeholders.

As a side note, consider that AddWithValue is an handy but dangerous method. The parameter type is extracted by the value passed and sometimes this could create a 'DataType mismatch Exception' or wrong conversions (in particular if you pass dates or decimals as strings to AddWithValue)

See Can we stop using AddWithValue already?

EDIT After a long debug session in chat the final problem is identified in the Currency field written withot brackets. Currency is a reserved words in Access and should be enclosed in square bracket. This was not initially evident because the first query proposed by the OP was correctly typed with square bracket but then the square brackets disappeared from the query for whatever reason. The suggestion to NOT use AddWithValue stands to avoid unnecessary conversions from dates to strings and then back to strings....

command.CommandText = "UPDATE Bills SET ([Payer] = @Payer, [Category] = @Category, ...) WHERE Id = @Id";
command.Parameters.Add("@Payer", OleDbType.VarWChar).Value = bill.Payer;
command.Parameters.Add("@Category", OleDbType.VarWChar).Value = bill.Category;
....
command.Parameters.Add("@DueDate", OleDbType.Date).Value = bill.DueDate.Date;
....
command.Parameters.Add("@Id", OleDbType.Integer).Value = bill.Id;

OleDbCommand, UPDATE

OleDbCommand doesn't support named parameters.

From OleDbCommand.Parameters property

The OLE DB .NET Provider does not support named parameters for passing
parameters to an SQL statement or a stored procedure called by an
OleDbCommand when CommandType is set to Text. In this case, the
question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the
OleDbParameterCollection must directly correspond to the position of
the question mark placeholder for the parameter in the command text
.

Actually, using ? is not must but it is a comman.

Also use using statement to dispose your database connections and objects.

using(OleDbConnection con = new OleDbConnection(conString))
using(OleDbCommand command = con.CreateCommand())
{
command.CommandText = "UPDATE CustomersTable SET CompanyName = ? WHERE CompanyName = ?";
command.Parameters.Add("@p1", OleDbType.VarChar).Value = "xyz3";
command.Parameters.Add("@p2", OleDbType.VarChar).Value = "xyz4";
con.Open();
command.ExecuteNonQuery();
}

Update row in Access table using OleDb

In OleDb parameters are not recognized by their name but by their position in the parameters collection. You should simply change the line order of your parameters

querySaveStaff.Parameters.AddWithValue("@report",2);
querySaveStaff.Parameters.AddWithValue("@machine", 16);

In your current query the report's parameter is used in the Where statement not in the update part and of course nothing is updated because there is no record with WHERE MachineNumber = 2

Indeed, in OleDb you usually specify the parameters placeholder with a single ? not with the @something syntax, but Access, probably for easier portability with Sql Server accepts also the @ syntax, still the positions in parameter's collection should be the correct one expected in the query text.

Oledb update-command with parameters not working on access-db

Your parameters are not in the correct order. These 2 loops should be switched in the order they appear in the code.

This matters, the OleDbCommand does not use named parameters. They are ordinal based on the position they occur in the sql statement.

Documentation - OleDbCommand.Parameters

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

...

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text


Your code:

// should occur 2nd
foreach (var whereParameter in whereParameterizer.GetParameters())
{
whereBuilder.Append($" and {whereParameter.ParameterName} = @{whereParameter.ParameterName}");
parameterList.Add(whereParameter);
}

// should occur 1st
foreach (var updateParameter in updateParameterizer.GetParameters())
{
updateBuilder.Append($", {updateParameter.ParameterName} = @{updateParameter.ParameterName}");
parameterList.Add(updateParameter);
}

update data using oledb in c#

I believe your commandtext is where the trouble lies;

command.CommandText = "UPDATE Tcostumers SET cfname= " + cfname + "clname= " + clname + " WHERE cid = " + cid;

You require a comma between the set statements, and also as Gino pointed out the speechmarks.

Edit:

It's better than you use parameters for your variables, your current method is open to SQL injection, eg.

    private void button2_Click(object sender, EventArgs e)
{
OleDbCommand command = new OleDbCommand(@"UPDATE Tcostumers
SET cfname = @CFName,
clname = @CLName
WHERE cid = @CID", connect);

command.Parameters.AddWithValue("@CFName", textBox2.Text);
command.Parameters.AddWithValue("@CLName", textBox3.Text);
command.Parameters.AddWithValue("@CID", textBox1.Text);

try
{
connect.Open();
}
catch (Exception expe)
{
MessageBox.Show(expe.Source);
}
try
{
command.ExecuteNonQuery();

MessageBox.Show("DATA UPDATED");
}
catch (Exception expe)
{
MessageBox.Show(expe.Source);
}
finally
{
connect.Close();
}
}

Its how I tend to format my code, so do as you will with it. Hope it helps.

Oledb Update command

I don't know what database you are going against, however, I don't know if the OleDB is being picky on the ordinal sequence of your parameters. ie: Have you tried putting your "ID" parameter in the last position to match the actual order of the fields of your update command? I don't know if it's throwing it out.



Related Topics



Leave a reply



Submit