none
C# connection with MySql Database, how to calculate with data RRS feed

  • Question

  • Hello,

    I want to make a program that calculates the profit of a user. But it wont take data from my database.

    It only uses the zero (see: dblIncome = 0) that I needed to assign to avoid an error.

    Here is my code:

    using MySql.Data.MySqlClient; namespace prj_ecr { /// <summary> /// Interaction logic for Solvabiliteit.xaml /// </summary> public partial class Solvabiliteit : Window { private void btnTerug_Click(object sender, RoutedEventArgs e) { Ratios form = new Ratios(); form.Show(); this.Close(); } public Profit() { InitializeComponent(); double dblIncome = 0, dblExpenses = 0; string strgebruikersnaam = Login.strNodig; MySqlConnectionStringBuilder connectionStr = new MySqlConnectionStringBuilder { Server = "localhost", Database = "ecr", UserID = "root", Password = "usbw", ConnectionTimeout = 60, Port = 3307, AllowZeroDateTime = true }; MySqlConnection MySqlCon = new MySqlConnection(connectionStr.ConnectionString); try { if (MySqlCon.State == System.Data.ConnectionState.Closed) MySqlCon.Open(); String query = "SELECT income=@Income, expenses=@Expenses FROM invoergegevens WHERE username=@Username"; MySqlCommand sqlCmd = new MySqlCommand(query, MySqlCon); sqlCmd.CommandType = System.Data.CommandType.Text; sqlCmd.Parameters.AddWithValue("@Income",dblIncome); sqlCmd.Parameters.AddWithValue("@Expenses", dblExpenses); sqlCmd.Parameters.AddWithValue("@Username", strUsername); int count = Convert.ToInt32(sqlCmd.ExecuteScalar()); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { MySqlCon.Close(); } double dblTotal = dblIncome - dblExpenses lblResult.Content = "This is your profit " + Convert.ToString(dblTotal); } private void lblFeedback_Click(object sender, RoutedEventArgs e) { MessageBox.Show("You have this ..."); } } }

    Hope someone can help me, every hint helps.

    Have a nice day!


    • Edited by SnejjenS Monday, April 9, 2018 1:16 PM
    Monday, April 9, 2018 1:16 PM

Answers

  • Hello,

    The syntax is incorrect for your SQL statement, proper syntax

    SELECT income FROM invoergegevens WHERE username=@Username


    You don' do

    SELECT income=@Income, expenses=@Expenses. Nor setup parameters for them. If they are part of the WHERE condition place them in the WHERE condition.

    Or

    Use ExecuteScalar

    SELECT income - expenses as result 
    FROM invoergegevens 
    WHERE userName = @UserName


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, April 9, 2018 1:38 PM
    Moderator

All replies

  • Hello,

    The syntax is incorrect for your SQL statement, proper syntax

    SELECT income FROM invoergegevens WHERE username=@Username


    You don' do

    SELECT income=@Income, expenses=@Expenses. Nor setup parameters for them. If they are part of the WHERE condition place them in the WHERE condition.

    Or

    Use ExecuteScalar

    SELECT income - expenses as result 
    FROM invoergegevens 
    WHERE userName = @UserName


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, April 9, 2018 1:38 PM
    Moderator
  • Thank you for your answer,

    I have one more question;

    How do you put data from the database in a variable?

    Monday, April 9, 2018 2:42 PM
  • Can you be more specific as it "depends" on what you want e.g. if you want to place a single value of type numeric in a TextBox we simply assign the value via .ToString() since the .Text property is of type string, same for dates. Also if working with DataTable or a class implementation is a tad different.

    In any event this should be in a new post, close this post out, mark any replies as the answer or helpful first.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, April 9, 2018 2:58 PM
    Moderator
  • So we see that our program does not make the connection with our database. We think it's because they are already getting a value from the variable  like this: double dblIncome = 0. so we use this and it calculates in our textbox with these variables so the result is 0
    so we thought our problem is with this so we want to put in our dblIncome = something from <g class="gr_ gr_369 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="369" id="369">database</g>. But we don't know how we can put a SQL-code in a variable.

    Monday, April 9, 2018 3:41 PM
  • It does not matter if you had code similar to the following which in this case is SQL-Server but the same holds true for other databases.

    I have a base connection class (one for Oracle too) that only handles the connection and exceptions.

    namespace BaseCoreClassesLibrary.SqlServer
    {
        public class BaseSqlServerConnections : BaseExceptionsHandler
        {
            /// <summary> 
            /// This points to your database server 
            /// </summary> 
            protected string DatabaseServer = ".\\SQLEXPRESS";
            /// <summary> 
            /// Name of database containing required tables 
            /// </summary> 
            protected string DefaultCatalog = "NorthWindAzure1";
            public string ConnectionString
            {
                get
                {
                    return $"Data Source={DatabaseServer};" +
                            $"Initial Catalog={DefaultCatalog};" + 
                             "Integrated Security=True";
                }
            }
        }
    }
    

    Then

    public class DataOperations : BaseSqlServerConnections
    {
        public decimal Demo()
        {
            using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
            {
                using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                {
                    cmd.CommandText = "SELECT Freight FROM Orders WHERE OrderId = @OrderId"
                    cmd.Parameters.AddWithValue("@OrderId", 10249);
                    cn.Open();
                    return (decimal)cmd.ExecuteScalar();
                }
            }
        }
    }

    Usage

    private void button1_Click(object sender, EventArgs e)
    {
        var ops = new DataOperations();
        var freight = ops.Demo();
    }
    If I were to hit button1 several times there are no issues with the connection or retrieving data.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, April 9, 2018 4:35 PM
    Moderator
  • Our question is how do you put a value from your database into a variable double (double because we work with numbers)
    how do you put a value from database in a variable C#

    This is in a WPF form

    • Edited by Teampje Wednesday, April 11, 2018 9:06 AM
    Wednesday, April 11, 2018 8:41 AM
  • Our question is how do you put a value from your database into a variable double (double because we work with numbers)
    how do you put a value from database in a variable C#

    This is in a WPF form

    You should ask this in the WPF forum.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 11, 2018 9:17 AM
    Moderator