Code snippets to connect Microsoft Access Database (Office Access) : Checking user availability (custom login) and selecting data and saving data.

Default.aspx

<script>
    function SaveMember() {
        var ID = document.getElementById('<%=hfID.ClientID %>').value;
        var OrderStatus = document.getElementById('<%=ddlOrderStatus.ClientID %>').value;
        var PaymentStatus = document.getElementById('<%=ddlPaymentStatus.ClientID %>').value;
        var DispatchDateTime = document.getElementById('<%=txtDispatchDateTime.ClientID %>').value;
        var CourierCompany = document.getElementById('<%=ddlCourierCompany.ClientID %>').value;
        var ConsignmentNumber = document.getElementById('<%=txtConsignmentNumber.ClientID %>').value;
        var DeliveryDetails = document.getElementById('<%=txtDeliveryDetails.ClientID %>').value;
        var Status = document.getElementById('<%=ddlStatus.ClientID %>').value;

        PageMethods.SaveMember(ID, OrderStatus, PaymentStatus, DispatchDateTime, CourierCompany, ConsignmentNumber, DeliveryDetails, Status, onSucess, onError);

        function onSucess(data) {
            if (data == "Error") {
                alert("Not Save.");
            }
            else {

                alert("Order Updated.");
                window.location.href = "Order.aspx?ID=" + ID;
            }
        }

        function onError(result) {
            alert(result);
        }
    }
</script>

Default.aspx.cs

protected void BindData(int ID)
{
    MicrosoftAccessDB(ID);
}

private void MicrosoftAccessDB(int ID)
{
    string connect = DBConfig.ConnStr();
    string query = "SELECT * FROM Orders2014 WHERE ID = " + ID;
    DataSet ds = new DataSet();
    using (OleDbConnection conn = new OleDbConnection(connect))
    {
        using (OleDbCommand cmd = new OleDbCommand(query, conn))
        {
            cmd.Parameters.Add("@p1", OleDbType.Char, 15).Value = ID;

            try
            {
                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
                adp.Fill(ds);

                //get current values from database
                var orderStatus = ds.Tables[0].Rows[0]["OrderStatus"];
                var paymentStatus = ds.Tables[0].Rows[0]["PaymentStatus"];
                var dispatchDateTime = ds.Tables[0].Rows[0]["DispatchDateTime"];
                var courierCompany = ds.Tables[0].Rows[0]["CourierCompany"];
                var consignmentNumber = ds.Tables[0].Rows[0]["ConsignmentNumber"];
                var deliveryDetails = ds.Tables[0].Rows[0]["DeliveryDetails"];
                var status = ds.Tables[0].Rows[0]["Status"];

                hfID.Value = ID.ToString();

                //Order Status Binding
                List<string> _orderStatus = OrderStatus();
                foreach (string item in _orderStatus)
                {
                    ddlOrderStatus.Items.Add(item);
                }
                ddlOrderStatus.SelectedValue = orderStatus.ToString();

                //Payment Status Binding
                List<string> _paymentStatus = PaymentStatus();
                foreach (string item in _paymentStatus)
                {
                    ddlPaymentStatus.Items.Add(item);
                }
                ddlPaymentStatus.SelectedValue = paymentStatus.ToString();

                //Dispatched Date and Time Binding
                txtDispatchDateTime.Text = dispatchDateTime.ToString();

                //Courier Company Bindig
                List<string> _courierCompany = CourierCompany();
                foreach (string item in _courierCompany)
                {
                    ddlCourierCompany.Items.Add(item);
                }
                ddlCourierCompany.SelectedValue = courierCompany.ToString();

                //Consignment Number Binding
                txtConsignmentNumber.Text = consignmentNumber.ToString();

                //Delivery Details Binding
                txtDeliveryDetails.Text = deliveryDetails.ToString();

                //Status Bindig
                List<string> _status = Status();
                foreach (string item in _status)
                {
                    ddlStatus.Items.Add(item);
                }
                ddlStatus.SelectedValue = status.ToString();
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}


private static void MicrosoftAccessDBSaveMember(string ID, string OrderStatus, string PaymentStatus, string DispatchDateTime, string CourierCompany, string ConsignmentNumber, string DeliveryDetails, string Status)
{
    string query = "UPDATE [Orders2014] SET [OrderStatus] = ?, [PaymentStatus] = ?, [DispatchDateTime] = ?, [CourierCompany] = ?, [ConsignmentNumber] = ?, [DeliveryDetails] = ?, [Status] = ? WHERE [ID] = ?";
    string connect = DBConfig.ConnStr();
    using (OleDbConnection conn = new OleDbConnection(connect))
    {
        using (OleDbCommand cmd = new OleDbCommand(query, conn))
        {
            conn.Open();
            OleDbParameter[] pram = new OleDbParameter[8];
            pram[0] = new OleDbParameter("@OrderStatus", ProperCase.MakeProperCase(OrderStatus));
            pram[1] = new OleDbParameter("@PaymentStatus", ProperCase.MakeProperCase(PaymentStatus));
            pram[2] = new OleDbParameter("@DispatchDateTime", DispatchDateTime);
            pram[3] = new OleDbParameter("@CourierCompany", CourierCompany);
            pram[4] = new OleDbParameter("@ConsignmentNumber", ConsignmentNumber);
            pram[5] = new OleDbParameter("@DeliveryDetails", DeliveryDetails);
            pram[6] = new OleDbParameter("@Status", Status);
            pram[7] = new OleDbParameter("@ID", ID);

            for (int i = 0; i < pram.Length; i++)
            {
                cmd.Parameters.Add(pram[i]);
            }
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}


private void MicrosoftAccessDB(string username, string password)
{
    string query = "SELECT TOP 1 * FROM UsersAndPasswords WHERE Username = '" + username + "' AND Password = '" + password + "'";
    DataSet ds = new DataSet();
    using (OleDbConnection conn = new OleDbConnection(ConnStr()))
    {
        using (OleDbCommand cmd = new OleDbCommand(query, conn))
        {
            cmd.Parameters.Add("@p1", OleDbType.Char, 15).Value = username;
            cmd.Parameters.Add("@p2", OleDbType.Char, 15).Value = password;
            OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
            try
            {
                conn.Open();
                adp.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    //user found
                    FormsAuthentication.RedirectFromLoginPage(username, true);
                    Session["username"] = username;
                    Response.Redirect("~/Admin");




                    lblInformation.Text = "Great.";
                }
                else
                {
                    lblInformation.Text = "Username and Password mismatched.";
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}