none
Problems with "Insert into" MS Access Database RRS feed

  • Question

  • I am trying to insert data to an Access Database from my form in Visual Studio Community 2017. The Access source database file (JDS_Accounts.accdb) is in the directory I chose when I created the database. In Visual Studio, I have the output directory set to "Copy if newer". Below, I have copied the code for the entire form, but the problem seems to be in the BtnAddRecord event.

    When I run my code and click on the "Add to Record" button, everything seems to work and no errors at all show up. However, data is not being written to my Access Database. I don't know what to troubleshoot because no errors are referenced by Visual Studio.

    Thank you very much!

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using System.Configuration;
     
    namespace JDS_Accounts
    {
        public partial class FrmS24DataEntry : Form
        {
            public FrmS24DataEntry()
            {
                InitializeComponent();
            }
     
            private void FrmS24DataEntry_Load(object sender, EventArgs e)
            {
                OleDbConnection Myconn = new OleDbConnection();
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = D:\JDS_ Access_Projects\JDS_Accounts\JDS_Accounts.accdb; Persist Security Info = False;";
                Myconn.ConnectionString = strConn;
     
                try
                {
                    Myconn.Open();
                    CheckConnection.Text = "Connection Successful!";
                    Myconn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error " + ex);
                }
            }
     
            private void BtnMain_Click(object sender, EventArgs e)
            {
                // Create a new instance of the FormMain class
                FrmMain FrmMain = new FrmMain();
     
                // Show the FormMain Form
                FrmMain.Show();
     
                this.Close();
            }
     
            private void BtnAddRecord_Click(object sender, EventArgs e)
            {
     
                string strMessage = "";
     
                string strSQL = "INSERT INTO CONTRIBUTIONS(ContrDate, ContrCashWW, ContrCashCong, ContrChkCong, ContrChkNum, ContrName, NRContrWW, NRContrCong) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
     
                OleDbConnection Myconn = new OleDbConnection();
     
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = D:\JDS_ Access_Projects\JDS_Accounts\JDS_Accounts.accdb; Persist Security Info = False;";
                Myconn.ConnectionString = strConn;
     
                OleDbCommand comm = new OleDbCommand
                    {
                        CommandType = CommandType.Text,
                        CommandText = strSQL,
                        Connection = Myconn
                    };
                        comm.Parameters.AddWithValue("ContrDate", DateTPickerReceipt.Calendar);
                        comm.Parameters.AddWithValue("ContrCashWW", CurrTxtBoxCashWW.DecimalValue);
                        comm.Parameters.AddWithValue("ContrCashCong", CurrTxtBoxCashCong.DecimalValue);
                        comm.Parameters.AddWithValue("ContrChkCong", CurrTxtBoxChkCong.DecimalValue);
                        comm.Parameters.AddWithValue("ContrChkNum", TxtBoxChkNum.Text);
                        comm.Parameters.AddWithValue("ContrName", CboBoxContrName.Text);
                        comm.Parameters.AddWithValue("NRContrWW", CurrTxtBoxNRWW.DecimalValue);
                        comm.Parameters.AddWithValue("NRContrCong", CurrTxtBoxNRCong.Text);
     
                        try
                        {
                            Myconn.Open();
                            strMessage = comm.ExecuteNonQuery().ToString() + "Record Has Been Added Successfully!";
                            comm.ExecuteNonQuery();
                            Myconn.Close();
                        }
     
                        catch (Exception ex)
                        {
                            strMessage = "Error: " + ex.Message;
                        }
                        return;
     
                    }
     
            
        }
    }
    Wednesday, September 27, 2017 2:45 AM

Answers

  • Since you indicated "Copy If newer" then try

    string strConn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = {System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "JDS_Accounts.accdb")}; Persist Security Info = False;";
    Have the (if in debug mode) folder open in Windows Explorer, look at the modified date. Do the insert in your app, right after the insert the time part of the date should update as the modified connection string is pointing to the same folder as the executable. If the time does not update you need to set a break-point on the curly brace, if hit look at the local window for $e and see what the error message is or inner exception message is.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jedasa Thursday, September 28, 2017 4:31 AM
    Thursday, September 28, 2017 12:26 AM
    Moderator

All replies

  • Have you checked the value of strMessage before return, and also the content of the file (that appears in Connection String) using Access?

    Note that your program actually tries to insert two rows.

    Wednesday, September 27, 2017 5:59 AM
  • Also note that you're writing to "D:\JDS_ Access_Projects\JDS_Accounts\JDS_Accounts.accdb" instead of the copied version in the EXE file's folder.
    Wednesday, September 27, 2017 6:08 AM
    Answerer
  • Thank you for your response Viorel,

    Actually strMessage doesn't return anything. But earlier in my code 'check connection' returns that the connection is successful. And the file referenced in the connection string is the original source file (JDS_Accounts.accdb) of my Access Database. This is what I am trying to write to. I hope I am understanding what you are suggesting? Also, I'm unclear about what you mean about my code inserting two rows? Thanks.

    Wednesday, September 27, 2017 11:59 PM
  • Thank you for responding cheong00,

    I guess I am not understanding things, but I thought that the data source in the connection string was what was supposed to be written to? That is what I thought I was doing in following the Microsoft Walkthroughs about how to make a connection to an Access Database? I went through all the steps in the walkthrough and entered the path to my original Access Database that I had originally created (JDS_Accounts.accdb).

    Thursday, September 28, 2017 12:15 AM
  • Since you indicated "Copy If newer" then try

    string strConn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = {System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "JDS_Accounts.accdb")}; Persist Security Info = False;";
    Have the (if in debug mode) folder open in Windows Explorer, look at the modified date. Do the insert in your app, right after the insert the time part of the date should update as the modified connection string is pointing to the same folder as the executable. If the time does not update you need to set a break-point on the curly brace, if hit look at the local window for $e and see what the error message is or inner exception message is.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jedasa Thursday, September 28, 2017 4:31 AM
    Thursday, September 28, 2017 12:26 AM
    Moderator
  • You mentioned "I have the output directory set to "Copy if newer", so I thought you want to write to the .accdb file in your EXE folder instead of that of project folder.

    You see, if you checked the content of the wrong file, it'd be quite logical that no written record is found there.

    Thursday, September 28, 2017 12:54 AM
    Answerer
  • Thank you for your response,

    I copied in the connection string that you suggested. Just to clarify, my database file (JDS_Accounts.accdb) is in 3 places: 1) the original file in the directory I chose from Access when I created the Database, 2) in my Visual Studio Project Folder for my program, and 3) in the bin/debug folder in the same Visual Studio Project Folder.

    When I opened Windows Explorer side by side with Visual Studio and ran my program and clicked on the "Add Record" button, I did indeed notice that the time updated, but only for the Access Database file in bin/debug. The Database file time did not change in the other two locations. I also noticed another file, JDS_Accounts.laccdb, immediately updated as well in the bin/debug folder. Then when I closed the program this file immediately disappeared from bin/debug. I suppose it must be a Access support file for when the Database is run.

    However, in any event, when I opened JDS_Accounts.accdb in the bin/debug folder, still there was no data that was updated, even though Windows Explorer showed that the file time had been updated?

    I wonder if this problem is somehow Access related? Also, why does the time of the Database files in the other two locations not update?

    Thanks for your help on this!

    Thursday, September 28, 2017 4:21 AM
  • Actually, after I run my program in Visual Studio, I check the database in all three locations - and none of the database files are updated. However, as I mentioned above in another post, the time of the database file (as noted in Windows Explorer) in indeed updated, but only for the database file in bin/debug. But still no data is being written to this file either.
    Thursday, September 28, 2017 4:27 AM
  • Let's look at a working sample that depends on the database being located in the bin\debug folder. We add a new record, return the new primary key upon success.

    The complete VS2015 project can be downloaded and tested here on Microsoft OneDrive.

    I always code database operations in a class, as shown here.

    using System;
    using System.Data.OleDb;
    using System.IO;
    
    namespace MS_AccessAddRecord_CS
    {
        public class Operations
        {
    
            OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.ACE.OLEDB.12.0",
                DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
            };
    
            public bool HasException { get; set; }
            Exception mException;
            public Exception Exception  { get { return mException; } }
    
            public bool AddNewRow(string Name, string ContactName, string ContactTitle, ref int Identfier)
            {
                bool Success = true;
    
                try
                {
                    using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
                    {
                        using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
                        {
    
                            cmd.CommandText = "INSERT INTO Customers (CompanyName,ContactName, ContactTitle) " + 
                                              "Values(@CompanyName,@ContactName, @ContactTitle)";
    
                            cmd.Parameters.AddWithValue("@CompanyName", Name);
                            cmd.Parameters.AddWithValue("@ContactName", ContactName);
                            cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle);
    
                            cn.Open();
                            // insert record
                            int Affected = cmd.ExecuteNonQuery();
                            // When Affected = 1 the insert was successful so get the new id for the new record
                            if (Affected == 1)
                            {
                                // get new identifier
                                cmd.CommandText = "Select @@Identity";
                                // we have this when this method ends and place it in a TextBox for proof it worked
                                Identfier = Convert.ToInt32(cmd.ExecuteScalar());
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Success = false;
                    HasException = true;
                    mException = ex;
                }
                return Success;
            }
        }
    }

    We call the add method as shown below.

    using System;
    using System.Windows.Forms;
    
    namespace MS_AccessAddRecord_CS
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            /// <summary>
            /// Here we mock up a new record as pulling information from controls
            /// is not relevant to the database operation. If the insert works, the
            /// new identifier comes back and placed into a TextBox, otherwise we
            /// see if an exception was thrown and if so show the error message.
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button1_Click(object sender, EventArgs e)
            {
                var ops = new Operations();
                int newIdentifier = 0;
                if (ops.AddNewRow("ABC Records","Karen Payne","Owner", ref newIdentifier))
                {
                    textBox1.Text = Convert.ToString(newIdentifier);
                }
                else
                {
                    if (ops.HasException)
                    {
                        MessageBox.Show($"Encountered an exception: {ops.Exception.Message}");
                    }
                    else
                    {
                        MessageBox.Show("Add failed");
                    }                
                }
            }
        }
    }

    Here we see the new identifier

    In regards to the other locations, for that you need a connections to change to point to the others or have separate connections for each database.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, September 28, 2017 10:15 AM
    Moderator