Skip to content

Instantly share code, notes, and snippets.

@itorian
Created March 7, 2014 08:15
Show Gist options
  • Save itorian/9407455 to your computer and use it in GitHub Desktop.
Save itorian/9407455 to your computer and use it in GitHub Desktop.
Code snippets to connect Microsoft Access Database (Office Access) : Checking user availability (custom login) and selecting data and saving data.
<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>
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;
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment