How to Update an OLEDB Table Using Parameters to Prevent Update Failures?

How to Update a Table Using OLEDB Parameters

Issue

You have a table with three fields: LM_Code, M_Name, and Desc. You want to update the M_Name and Desc fields based on the LM_Code using an UPDATE command. However, when you use a normal UPDATE command, the fields are not getting updated.

Resolution

By using OLEDB parameters, you can ensure that the fields are updated. The following sample code demonstrates how to update a table using OLEDB parameters:

using System;
using System.Data;
using System.Data.OleDb;

namespace OLEDB_Parameters
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection string
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                                     "Data Source=C:\path\to\your.mdb;Persist Security Info=False;";

            // Create a new OleDbConnection object
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                // Open the connection
                connection.Open();

                // Create a new OleDbCommand object
                using (OleDbCommand command = connection.CreateCommand())
                {
                    // Set the command text
                    command.CommandText = "UPDATE Master_Accounts SET M_Name = ?, Desc = ? WHERE LM_Code = ?";

                    // Add the parameters
                    command.Parameters.AddWithValue("M_Name", "New Account Name");
                    command.Parameters.AddWithValue("Desc", "New Description");
                    command.Parameters.AddWithValue("LM_Code", "LM001");

                    // Execute the command
                    int rowsAffected = command.ExecuteNonQuery();

                    // Check if the update was successful
                    if (rowsAffected > 0)
                    {
                        Console.WriteLine("Update successful.");
                    }
                    else
                    {
                        Console.WriteLine("Update failed.");
                    }
                }

                // Close the connection
                connection.Close();
            }
        }
    }
}

In this code:

• The AddWithValue method is used to add parameters to the command object. Each parameter is assigned a value and a name.
• The ExecuteNonQuery method is used to execute the UPDATE command.
• The rowsAffected variable is used to check if the update was successful.

The above is the detailed content of How to Update an OLEDB Table Using Parameters to Prevent Update Failures?. For more information, please follow other related articles on the PHP Chinese website!