UPDATE fields

Question

Hello,

This works when using insert into:

Using command As New OleDbCommand("INSERT INTO Table (Column1) VALUES (?)", connection)
   command.Parameters.AddWithValue("@param1", field1)
   command.ExecuteNonQuery()
   command.Parameters.Clear()

Anyone can give me a tip on how to handle Update table fields with parameterized queries please?

Thanks.

Answer 1

Hi @StewartBW , Welcome to Microsoft Q&A,

When you use parameterized queries to update table fields, you can follow these steps:

Using command As New OleDbCommand("UPDATE [Table] SET Column1 = ? WHERE ConditionColumn = ?", connection)
     command.Parameters.AddWithValue("@param1", newValue)
     command.Parameters.AddWithValue("@param2", conditionValue)
     command.ExecuteNonQuery()
End Using

In this example:

• UPDATE [Table] SET Column1 = ? is the basic format of a SQL update statement, which tells the database which field of which table you want to update, and replaces the field values with question marks so that they can be replaced later using a parameterized query.
• WHERE ConditionColumn = ? is optional and allows you to specify a condition to determine which rows will be updated. Conditional columns and conditional values are also provided using parameterized queries.

In the command.Parameters.AddWithValue method, the first parameter is the parameter name you used in the SQL statement, and the second parameter is the value to be inserted. This way you can safely send parameters to the database without worrying about SQL injection attacks.

The difference between using Parameters.Add or Parameters.AddWithValue method to add parameters is:

• The Parameters.Add method requires you to explicitly specify the data type of the parameter, while the Parameters.AddWithValue method automatically infers the data type based on the provided value.
• The Parameters.Add method can provide more control, such as specifying the data type, size, direction, etc. of the parameters, while the Parameters.AddWithValue method is more concise and can add parameters faster.

The following is an example of using the Parameters.Add method:

Using command As New OleDbCommand("UPDATE [Table] SET Column1 = ? WHERE ConditionColumn = ?", connection)
     command.Parameters.Add("@param1", OleDbType.VarChar).Value = newValue
     command.Parameters.Add("@param2", OleDbType.Integer).Value = conditionValue
     command.ExecuteNonQuery()
End Using

When using the Parameters.Add method, you need to specify the data type of the parameter, such as OleDbType.VarChar or OleDbType.Integer. Doing this ensures that the type of the parameter matches the type of the database column, improving the safety and reliability of the code.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I would recommend using Dapper.

A site to learn Dapper.

Here is a project to learn from.

Simple add example

Create a class to represent the table in the database

public class Table1
{
    public int Id { get; set; }
    public string Column1 { get; set; }
}

Create a class for all data operations, here is just an add.

internal class Table1Operations
{
    private IDbConnection _cn = new OleDbConnection(
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb");
    public void Add(Table1 person)
    {
        const string statement =
            """
            INSERT INTO Table1
            (
                Column1
            )
            VALUES
            (
                @Column1
            )
            """;
        _cn.Execute(statement, new { person.Column1 });
    }
}

To call the code above

Table1Operations table1Operations = new();
table1Operations.Add(new Table1 { Column1 = "Some value" });