I'm new in using Visual C#. Can you give me bit of information as to how I can manage the data entered by a user using a form and store it in an access database?

In the application I have created, I planned of using OleDbCommand to manage the storage of data but what I usually receive is an error "An unhandled exception of type 'System.IndexOutOfRangeException' occured in system.data.dll"
"Additional Information: An OleDbParameter with ParameterName 'Check Voucher' is not contained by this OleDbParameterCollection."

This was a portion of my code.

private void RetrieveLedgerInfo()
{
  objLedgerInfo.Parameters["Check Voucher"].Value = 
     txtCVNo.Text;
  objLedgerInfo.Parameters["Payee"].Value = 
     txtPayee.Text;
  oleDbConnection1.Open();
  objLedgerInfo.ExecuteNonQuery();
  oleDbConnection1.Close();
}
private void menuItem2_Click(object sender, System.EventArgs e)
{
    RetrieveLedgerInfo();
}

The class below is a proxy (or wrapper) for making oledb calls.
You can use std sql scripts.
Just make sure that you have the appropriate connection string for your db.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;  // ado.net stuff
using System.Data.OleDb;

namespace DbProxy
{

    public class OleDbProxy
    {
        private string dbConnString;

        public OleDbProxy(string db_conn_str_)
        {
            dbConnString = db_conn_str_;
        }

        /// <summary>
        /// purpose:
        ///     - performs the sql command 
        ///         - eg CREATE, DROP, INSERT, UPDATE, ...
        /// requirements:
        ///     - sql_ is  a valid, sql cmd
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the cmd
        ///     - result >= 0
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="sql_">the non query sql command</param>
        /// <returns></returns>
        public virtual int NonQuery(string sql_)
        {
            int result = 0;
            OleDbConnection conn = null;
            try
            {
                conn = GetConnection();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql_, conn);
                result = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return Math.Abs(result);
        }

        /// <summary>
        /// purpose:
        ///     - performs the sql command 
        ///         - eg CREATE, DROP, INSERT, UPDATE, ...
        /// requirements:
        ///     - sql_ is  a valid, sql cmd
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the cmd
        ///     - result >= 0
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="sql_">the non query sql command</param>
        /// <returns></returns>
        public virtual object ExecScalar(string sql_)
        {
            object result = null;
            OleDbConnection conn = null;
            try
            {
                conn = GetConnection();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql_, conn);
                result = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// purpose:
        ///     - performs the sql query 'query_'
        /// requirements:
        ///     - query_ is  a valid, readonly query
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the query
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="query_"></param>
        /// <returns></returns>
        public virtual DataSet ReadOnlyQuery(string query_)
        {
            bool read_query = false;
            string write_query_type = "none";
            try
            {
                string query2 = query_.ToLower().TrimStart();
                int select_pos = query2.IndexOf("select");
                if (select_pos >= 0)
                {
                    read_query = true;
                    int insert_pos = query2.IndexOf("insert");
                    int update_pos = query2.IndexOf("update");
                    int delete_pos = query2.IndexOf("delete");

                    if (insert_pos > 0 && select_pos > insert_pos)
                    {
                        write_query_type = "insert";
                        read_query = false;
                    }
                    if (update_pos > 0 && select_pos > update_pos)
                    {
                        write_query_type = "update";
                        read_query = false;
                    }
                    if (delete_pos > 0 && select_pos > delete_pos)
                    {
                        write_query_type = "delete";
                        read_query = false;
                    }
                }

                if (read_query)
                {
                    return Query(query_);
                }
                else
                {
                    throw new Exception("Only Read queries are allowed: " + write_query_type);
                }
            }
            catch (Exception ex_)
            {
                //throw new Exception(ex_.Message);
                throw ex_;
            }
        }

        /// <summary>
        /// purpose:
        ///     - performs the sql query 'query_'
        /// requirements:
        ///     - query_ is  a valid sql query
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the query
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="query_"></param>
        /// <returns></returns>
        public DataSet Query(string query_)
        {
            OleDbConnection conn = null;
            DataSet ds = new DataSet("QueryResults");
            try
            {
                conn = GetConnection();
                OleDbDataAdapter adapter = new OleDbDataAdapter(query_, conn);
                adapter.FillSchema(ds, SchemaType.Mapped, ResultTableName);
                rowsReturned = adapter.Fill(ds, ResultTableName);
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return ds;
        }

        /// <summary>
        /// purpose:
        ///     - updates the db from the contents of dt_
        /// requirements:
        ///     - query_ is  a valid sql query which for the db table coresponding to dt_
        ///     - eg:
        ///         - dt_ has name "Orders" and contains columns OrdNum, ClientId, Total etc
        ///         - query == "SELECT OrdNum, ClientId, Total FROM Orders"
        ///     - are able to use a 'CommandBuilder' to create the insert, update & delete commands
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the no. of rows updated in the db
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="query_"></param>
        /// <param name="dt_"></param>
        /// <returns></returns>
        public int Update(string query_, DataTable dt_)
        {
            int rows = 0;
            OleDbConnection conn = null;
            try
            {
                conn = GetConnection();
                OleDbDataAdapter adapter = new OleDbDataAdapter(query_, conn);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
                rows = adapter.Update(dt_);
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return rows;
        }

        /// <summary>
        /// returns a new connection to the underlying db
        /// user is responsible for opening & closing the connection
        /// </summary>
        /// <returns></returns>
        public OleDbConnection GetConnection()
        {
            OleDbConnection db_connection = null;
            try
            {
                db_connection = new System.Data.OleDb.OleDbConnection(dbConnString);
            }
            catch (Exception)
            {
            }
            return db_connection;
        }



        /// <summary>
        /// returns the name that the 'result table' will have
        /// ie the only table in the data set returned by a call to one of the query fns
        /// </summary>
        public string ResultTableName
        {
            get
            {
                return resultTablename;
            }
        }

        private string resultTablename = "ResultSet";

        /// <summary>
        /// no. of rows returned in last valid query
        /// </summary>
        /// <returns></returns>
        public string RowCount()
        {
            return rowsReturned.ToString();
        }

        private int rowsReturned = 0;
    }

}

Thanks a lot. Can you send me some application in Visual C# that involves data storage and retrieval to and from an Access database.

I would rather clearly understand if I see some applications so that I could internalize how these stuff works.

Thanks a lot...

This article has been dead for over six months. Start a new discussion instead.