Call Insert Into / Append Query from MS Access using Csharp WinForm

Question

Hello Everyone
Can anyone help me resolving this issue with calling a Query of MS Access (Append / Insert Into )Query using Csharp Winform with multiple parameters.
Details:

INSERT INTO tblFees ( StudentID, MonthName, yearfor, DateofVoucher)
SELECT
AllStudents.StudentID,

-------------- following are the parameters should be used from csharp winform
----------------Inserting from Forms should be used from csharp winform controls
Forms![Generator_Fees_Main]!COmboMonths AS MonthName,
Forms![Generator_Fees_Main]!CombYear AS [Year],
Forms![Generator_Fees_Main]!TextDateofVoucher AS DateofVoucher,

FROM AllStudents
-------------------------- following are the parameters should be used from csharp winform controls
WHERE (((AllStudents.Status)="Active"))and NOT EXISTS(SELECT StudentID FROM tblFees WHERE StudentID=AllStudents.StudentID and MonthName= Forms![Generator_Fees_Main]![COmboMonths]
and YearFor= Forms![Generator_Fees_Main]![CombYear]);

------- note we use above parameters in MS Access forms to insert the selected values from controls.
I want same as on CSharp Winform

hope I will get its resolution very soon.

Thank you in advance

Answer 1

Recommend creating a class for your database work.

In the class, have a private scoped variable for the connection to the database.

Create methods to populate ComboBox and other controls.

public static string ConnectionString =>
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb";

Create a method to execute your SQL, below is a sample. Note each value for inserts need to be parameters off the OleDbCommand. The return value is the new primary key for this example, yours will be different. And your WHERE value will be a parameter.

public static (int identifier, Exception exception) SampleInsert(string companyName, string contactName)
{

    using var cn = new OleDbConnection { ConnectionString = ConnectionString };
    using var cmd = new OleDbCommand() { Connection = cn };

    cmd.CommandText =
        "INSERT INTO Customers (CompanyName,ContactName) Values (@CompanyName,@ContactName)";

    cmd.Parameters.Add("@CompanyName", 
        OleDbType.LongVarChar).Value = companyName;

    cmd.Parameters.Add("@ContactName", 
        OleDbType.LongVarChar).Value = contactName;

    try
    {
        cn.Open();

        cmd.ExecuteNonQuery();
        return ((int)cmd.ExecuteScalar(), null);
    }
    catch (Exception ex)
    {
        return (-1, ex);
    }

}

Example for reading data for ComoBox controls, make sure to set DisplayMember for the ComboBox, in this case it would be to CategoryName

public static DataTable CategoriesDataTable()
{
    DataTable table = new ();

    using var cn = new OleDbConnection { ConnectionString = ConnectionString };
    using var cmd = new OleDbCommand() {Connection = cn};

    cmd.CommandText = "SELECT CategoryID, CategoryName FROM Categories;";

    cn.Open();

    table.Load(cmd.ExecuteReader());

    return table;
}