C# VS, sqlexpress - how do I use the datatable value and make a simple times calculation with textbox and appear the result in second textbox C#?

Question

0.00/5 (No votes)

See more:

, +

im new to this

i wanted to create only a plus calculation with button&txtbox and display it in other textbox using the data int in my sqlexprs db.

i've created a db and dtable in sqlserver management tool there is only product name and price.

how do i do this.. pls give an example

What I have tried:

This is my Data Access Layer.cs

public static class DAL
    {
        public static DataTable ExecSP(string spName, List<SqlParameter> sqlParams = null)
        {
            string strConnect = "Server=PC\\SQLEXPRESS;Database=MyLoginApp;Trusted_Connection=True;";     
            SqlConnection conn = new SqlConnection();
            DataTable dt = new DataTable();

            try
            {
                //Connect to the database
                conn = new SqlConnection(strConnect);
                conn.Open();

                //Build an sql command / query
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(sqlParams.ToArray());

                //Execute command
                SqlCommand command = conn.CreateCommand();
                SqlDataReader dr = cmd.ExecuteReader();

                //fill datatable with the results
                dt.Load(dr);


            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //No matter what happends this will run
                conn.Close();
            }
            return dt;
        }
    }

This's the button function i want to create..
"BasoSUCost" must be the value in dtable

 private void btnHitung_Click(object sender, EventArgs e)
        {
          if (int.TryParse(txtBasoSU.Text, out NumberofOrder))
            {
                ItemBasoSU = Double.Parse(txtBasoSU.Text);
                TotalPriceOfBasoSU = BasoSUCost * ItemBasoSU;
}

Posted 2-Sep-18 20:27pm

Muhammad nur Ihsan

Updated 4-Sep-18 21:03pm

Add a Solution

2 solutions

Solution 1

Um.
I see nothing that involves your DataTable outside the method in which you load it (and you'd be better off doing that with a DataAdapter rather than a DataReader - it involves a lot fewer round trips to the DB), so quite what you want to do is not obvious.

But ... two things do spring to mind: Why catch all exceptions if all you are going to do is re-throw them? That doesn't really help at all, it just complicates things. You can just have the try...finally block and skip the catch if that's what you are trying to do, or even better use a using block to Close and Dispose the connection (and another for the Command) instead of the try block.

C#

using (SqlConnection conn = new SqlConnection(strConnect))
    {
    using (SqlCommand cmd = new SqlCommand(spName, conn))
        {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqlParams.ToArray());
        using (DataAdapter da = new SqlDataAdapter(cmd))
            {
            da.Fill(dt);
            }
        }
    }

Secondly, if it's a double then it'll fail the int.TryParse test - the presence of a '.' in the text string will mean it isn't an integer, so the outer TryParse will fail and it'll never get to the Double.Parse. Use Double.TryParse as the one and only test, and use the value it converted:

C#

private void btnHitung_Click(object sender, EventArgs e)
    {
    double value;
    if (double.TryParse(txtBasoSU.Text, out value))
        {
        TotalPriceOfBasoSU = BasoSUCost * value;
        ...
        }
    }

Posted 2-Sep-18 21:38pm

OriginalGriff

Comments

Muhammad nur Ihsan 3-Sep-18 5:38am

what is double value; for? how do i get the value from the sql server datatable exactly..

OriginalGriff 3-Sep-18 6:20am

When you use TryParse, it store the result in a variable you pass to it via the out parameter, and returns a true/false for "good/bad conversion"
double value;
is just declaring the variable to stick the converted value into, if the conversion works.

Muhammad nur Ihsan 3-Sep-18 5:51am

its really hard to understand i just wanted to get the value from the datatable to my textbox..

OriginalGriff 3-Sep-18 6:21am

That' more complex than you think, because a DataTable doesn't contain just one value - it's a Table - which means it contains rows of columns of data.
The first thing you need to work out is which row do you need, and which column of that row?

Muhammad nur Ihsan 3-Sep-18 6:38am

alright so i have 2 column there is "Products" and "Price" i only have 5 rows one of em is "BasoSU" it has "2000" for price, like i want to multiply his price with the whatever number inputed from the textbox.... pls give an example again.. what do evrything i need to add to the program.

and btw the class DAL is for the login & register form do i need to create a class again for this problm?

OriginalGriff 3-Sep-18 6:41am

No, you don't - rows don't have "names", only columns do that.
Do you mean you have a row containing a Product column containing the text "BasoSU"?

Muhammad nur Ihsan 3-Sep-18 6:51am

yes a product column containing the text BasoSU
and a price column containing the text 2000 <-- i want to make a calculation like 2 * 2000 and set it to BasoSUCost in

"private void btnHitung_Click(object sender, EventArgs e)
{
if (int.TryParse(txtBasoSU.Text, out NumberofOrder))
{
ItemBasoSU = Double.Parse(txtBasoSU.Text);
TotalPriceOfBasoSU = BasoSUCost * ItemBasoSU;
}"

Muhammad nur Ihsan 3-Sep-18 6:44am

before im using the sql server to make a data i just write the value in the cs form like this

public partial class Meja1 : Form
{

//HARGA - PRICE HARGA MAKANAN
double cBasoSU_PRICE = 20000;
double cBasoC_PRICE = 15000;
double cBasoM_PRICE = 15000;
double cBasoB_PRICE = 15000;
double cMieA_PRICE = 11000;
double cMieAB_PRICE = 15000;

public Meja1()
{
InitializeComponent();
}

private void btnHitung_Click(object sender, EventArgs e)
{

//DEKLARASI
// COST //angka inputmakan //var dijadikan dariatas
Double BasoSUCost; Double ItemBasoSU; Double HargaBasoSU;
Double BasoCCost; Double ItemBasoC; Double HargaBasoC;
Double BasoMCost; Double ItemBasoM; Double HargaBasoM;
Double BasoBCost; Double ItemBasoB; Double HargaBasoB;

//MIE
Double MieACost; Double ItemMieA; Double HargaMieA;
Double MieABCost; Double ItemMieAB; Double HargaMieAB;

//CONVERT
//MAKANAN
BasoSUCost = cBasoSU_PRICE;
BasoCCost = cBasoC_PRICE;
BasoMCost = cBasoM_PRICE;
BasoBCost = cBasoB_PRICE;
MieACost = cMieA_PRICE;
MieABCost = cMieAB_PRICE;

if (int.TryParse(txtBasoSU.Text, out NumberofOrder))
{
ItemBasoSU = Double.Parse(txtBasoSU.Text);
HargaBasoSU = BasoSUCost * ItemBasoSU;

if (int.TryParse(txtBasoC.Text, out NumberofOrder))
{
ItemBasoC = Double.Parse(txtBasoC.Text);
HargaBasoC = BasoCCost * ItemBasoC;

if (int.TryParse(txtBasoM.Text, out NumberofOrder))
{
ItemBasoM = Double.Parse(txtBasoM.Text);
HargaBasoM = BasoMCost * ItemBasoM;

if (int.TryParse(txtBasoB.Text, out NumberofOrder))
{
ItemBasoB = Double.Parse(txtBasoB.Text);
HargaBasoB = BasoBCost * ItemBasoB;

if (int.TryParse(txtMieA.Text, out NumberofOrder))
{
ItemMieA = Double.Parse(txtMieA.Text);
HargaMieA = MieACost * ItemMieA;

if (int.TryParse(txtMieAB.Text, out NumberofOrder))
{
ItemMieAB = Double.Parse(txtMieAB.Text);
HargaMieAB = MieABCost * ItemMieAB;

mcFoodTotal = HargaBasoSU + HargaBasoC + HargaBasoM + HargaBasoB + HargaMieA + HargaMieAB;

txtServisMakanan.Text = String.Format("Rp. {0:N} ", mcFoodTotal);
}
}
}
}
}
}
i want to use the sqlserver to store the data so i can modify the value easily but it actually hard as hell lol

OriginalGriff 3-Sep-18 6:57am

It's not as "hard as hell" - it's just that you have to understand what is going on! And once you do, using a DB will become second nature, and you'll wonder what all the fuss was about...

Full details are way too much to go into in a little text box like this, but basically a DB table holds "similar" information together: so you have a "Product" column and a "Price" column, and each row of the table holds a different Product and it's associated price.
You make the table hold a NVARCHAR(MAX) column (which is SQL-speak for "string") call "Product" and a FLOAT column (which is SQL-speak for "double") called "Price".
When you want a specific value from the DB you can ask for just that one value via a SELECT query:

using (SqlCommand cmd = new SqlCommand("SELECT Price From MyTable WHERE Product = 'BasoSU'", con))
   {
   double value = (double) cms.ExecuteScalar();
   ...
   }

ExecuteScalar is intended to return just one single value, which you then cast back to a double.

Does that make sense?

Muhammad nur Ihsan 3-Sep-18 7:06am

ok , what is con for?

private void btnHitung_Click(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand("SELECT Price From Products WHERE Product = 'BasoSU'", con))
{
double BasoSUCost = (double)cmd.ExecuteScalar();
...
}

OriginalGriff 3-Sep-18 7:19am

It's the SqlConnection object you will have already opened.

And before you ask, can you guess what "MyTable" should be? :laugh:

Muhammad nur Ihsan 3-Sep-18 7:36am

yes my database name is MyLoginApp and the Table name is "Products"
please help i want to make the 2000 be BasoSUCost so i can make a calculate from it :(

i want to set the 2000 to be BasoSUCost in here

private void btnHitung_Click(object sender, EventArgs e)
{
if (int.TryParse(txtBasoSU.Text, out NumberofOrder))
{
ItemBasoSU = Double.Parse(txtBasoSU.Text);
TotalPriceOfBasoSU = BasoSUCost * ItemBasoSU;
}

OriginalGriff 3-Sep-18 7:42am

So you know what to replace "MyTable" with then.
ANd I know you know how to create and open an SqlConnection...

Muhammad nur Ihsan 3-Sep-18 7:50am

dang.. can i give my source to ya and help me out xD or how can i upload a picture here.. thanks for the respond anyway but still confuse me

OriginalGriff 3-Sep-18 8:00am

No, come on - this is your homework, not mine! :laugh:
Just think about it and look at what you have so afr - you have all the bits you should need.

Muhammad nur Ihsan 3-Sep-18 7:57am

can you explain the "double value = (double)cmd.ExecuteScalar();"

protected SqlConnection conn;

private void btnHitung_Click(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand("SELECT Price From Products WHERE Product = 'BasoSU'", conn))
{
double value = (double)cmd.ExecuteScalar();
}

OriginalGriff 3-Sep-18 8:01am

I did:
"ExecuteScalar is intended to return just one single value, which you then cast back to a double."
It executes the command you gave it, and returns a single value.

Muhammad nur Ihsan 3-Sep-18 8:31am

ExecuteScalar: Connection property has not been initialized
what did i miss?
im sorry for still questioning..!

protected SqlConnection conn;

private void btnHitung_Click(object sender, EventArgs e)
{
if (int.TryParse(txtBasoSU.Text, out NumberofOrder))
{
using (SqlCommand cmd = new SqlCommand("SELECT Price From Products WHERE Product = 'BasoSU'", conn))
{
double BasoSUCost = (double)cmd.ExecuteScalar();

ItemBasoSU = Double.Parse(txtBasoSU.Text);
HargaBasoSU = BasoSUCost * ItemBasoSU;
}
}

OriginalGriff 3-Sep-18 8:41am

Please, think about it!
From your original code:

string strConnect = "Server=PC\\SQLEXPRESS;Database=MyLoginApp;Trusted_Connection=True;";
SqlConnection conn = new SqlConnection();

what did you think that code was doing when you wrote it? :laugh:

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Muhammad nur Ihsan · Accepted Answer · 2018-09-04T21:03:00

Hell yes i got my solution!!

Form1 load

public Meja1()
        {
            InitializeComponent();
        }

        SqlConnection con = new SqlConnection(@"Data Source = PC\SQLEXPRESS; Initial Catalog = MyLoginApp; Integrated Security = True");
   
    private void Form1_Load(object sender, EventArgs e)
        {
            con.Open();
            string query = "SELECT * FROM products";
            
            SqlDataAdapter SDA = new SqlDataAdapter(query, con);
            DataTable dt = new DataTable();
            SDA.Fill(dt);
            dataGridView1.DataSource = dt;

            string query2 = "SELECT harga FROM products WHERE Product = 'BasoSU'"; 
            DataSet ds = new DataSet();
            lblBSU.Text = dataGridView1.Rows[0].Cells["Harga"].Value.ToString();
          }

Form1 Button

 private void btnHitung_Click(object sender, EventArgs e)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            DataTable dt = DataAccess.ExecSP("Products", sqlParams);
            SqlConnection conn = new SqlConnection();

            SqlDataAdapter adp = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand("select * from products",conn);
            adp.SelectCommand = cmd;
            DataSet ds = new DataSet();
            
//declare
Double BasoSUCost; Double ItemBasoSU; Double HargaBasoSU;

if (int.TryParse(txtBasoSU.Text, out NumberofOrder))
            {
                ItemBasoSU = Double.Parse(txtBasoSU.Text);
                BasoSUCost = Double.Parse(lblBSU.Text);
                HargaBasoSU = BasoSUCost * ItemBasoSU; 

             txtTotal.Text = String.Format("Rp. {0:N} ", HargaBasoSU);

this is what i want it looks weird but it works! haha
im using gridview and label to use it in arithmetic lol

C# VS, sqlexpress - how do I use the datatable value and make a simple times calculation with textbox and appear the result in second textbox C#?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

C# VS, sqlexpress - how do I use the datatable value and make a simple times calculation with textbox and appear the result in second textbox C#?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us