Dynamically Add values to MS access database and retrieve them using variables

Question

I have a MS access database with a table called tarriff_table. In this table I have two columns upperLimit & perUnitCost. I have a form to insert this values. Now I want to use these values to calculate in code. I have the structure of the code but I am unable to connect database values to the variables I used.

Here is the code.

double demandCharge, VAT,upperLimit1, upperLimit2; 
double perUnitCost1, perUnitCost2;
private void button1_Click(object sender, EventArgs e)
{
 double con_unit = 0, bill = 0, mcon_unit = 0, totalbill = 0;
 con_unit = double.Parse(textBox1.Text);
 mcon_unit = double.Parse(textBox2.Text);
 if (con_unit < upperLimit1)
 {
  bill = con_unit * perUnitCost1;
 }
 else if (con_unit < upperLimit2)
 {
  bill = (upperLimit1 * perUnitCost1) + ((con_unit - upperLimit1) * perUnitCost2);
 }
 else bill = (upperLimit1 * perUnitCost1) + ((upperLimit2 - upperLimit1) * perUnitCost2);

 totalbill = Math.Ceiling((((((bill + demandCharge) * VAT) 
           + (bill + demandCharge)) / (con_unit)) * (mcon_unit)));
 textBox3.Text = Convert.ToString(totalbill);
}

This is a part of the code. But I assume it can generate the problem I am facing. Here I want to connect upperLimit columns to upperLimit variable and perUnitCost column to perUnitCost variable. How can I do it?

I want these variables(upperLimit1, upperLimit2, perUnitCost1, PerUnitCost2) value to be pulled from a database.

Important Note: In the code section perUnitCost2 means the value of the second row of the perUnitCost column in the database.

Note: There is a work around possible by calculating all the values before inserting them into the database. by doing this compile time can be reduced to half. As the compiler doesn't have to insert values and again retrieve them perform calculation.

2nd Note: I have figured out that I need a connection string to connect to a database. I will like the moderators to close the topic.

What is the issue with this code? The code does not have anything related to Ms-Access Database. — Chetan
– Chetan, Commented May 19, 2018 at 4:47
I want connect this code's variables(upperLimit[],range[]) to database columns(upperLimit, perUnitCost) respectively. But I don't know how to do it? @ChetanRanpariya — Khandkar Asif Hossain
– Khandkar Asif Hossain, Commented May 19, 2018 at 4:49
And you don't know how to write code to connect to MS-Access Database and query values from the table? — Chetan
– Chetan, Commented May 19, 2018 at 4:51
I know how connect with a database manually. But I don't know how to store those values dynamically into a variable for using those in the code given above. — Khandkar Asif Hossain
– Khandkar Asif Hossain, Commented May 19, 2018 at 5:01
Possible duplicate of How to query MS Access database with C# application? — Clint
– Clint, Commented May 19, 2018 at 5:16

Khandkar Asif Hossain · Accepted Answer · 2018-06-23 07:30:27Z

Here is a work around of my question.

private void btnsave_Click(object sender, EventArgs e)
    {
        string path = Path.Combine(Environment.CurrentDirectory, "Tenant info.mdb");
        connect.ConnectionString = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source="+ path;


        double con_unit = 0, bill = 0, presr = 0, prevr = 0, mcon_unit = 0, totalbill = 0;

        presr = double.Parse(textbox5.Text);
        prevr = double.Parse(textbox37.Text);
        con_unit = double.Parse(textbox38.Text);
        mcon_unit = ((presr) - (prevr));
        textBox2.Text = Convert.ToString(mcon_unit);
        if (con_unit < 75)
        {
            bill = con_unit * 3.8;
        }
        else if (con_unit < 200)
        {
            bill = (75 * 3.8) + ((con_unit - 75) * 5.14);
        }
        else if (con_unit < 300)
        {
            bill = (75 * 3.8) + (125 * 5.14) + ((con_unit - 200) * 5.36);
        }
        else if (con_unit < 400)
        {
            bill = (75 * 3.8) + (125 * 5.14) + (100 * 5.36) + ((con_unit - 300) * 5.63);
        }
        else if (con_unit < 600)
        {
            bill = (75 * 3.8) + (125 * 5.14) + (100 * 5.36) + (100 * 5.63) + ((con_unit - 400) * 8.70);
        }
        else if (con_unit < 600)
        {
            bill = (75 * 3.8) + (125 * 5.14) + (100 * 5.36) + (100 * 5.63) + (200 * 8.70) + (con_unit * 9.98);
        }
        else bill = (75 * 3.8) + (125 * 5.14) + (100 * 5.36) + (100 * 5.63) + (200 * 8.70) + ((con_unit - 600) * 9.98);

        totalbill = Math.Ceiling((((((bill + 70) * .05) + (bill + 70)) / (con_unit)) * (mcon_unit)));
        textBox3.Text = Convert.ToString(totalbill);

        tenantBillDataBindingSource.EndEdit();
        tenantBillDataTableAdapter.Update(this.tenant_InfoDataSet.TenantBillData);
        MessageBox.Show("Data Added to Database. Please Reopen the Form to Insert New Entry");
    }

Collectives™ on Stack Overflow

Dynamically Add values to MS access database and retrieve them using variables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related