UPDATE fields

StewartBW 545 Reputation points
2024-04-30T13:38:47.02+00:00

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.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,402 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,610 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 37,131 Reputation points Microsoft Vendor
    2024-04-30T14:30:18.82+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Karen Payne MVP 35,201 Reputation points
    2024-05-01T15:06:25.55+00:00

    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" });
    
    0 comments No comments