how to store data from mysql query in a variable

Question

when i execute a query i want the result obtained to be stored in a variable(a) and then i want that variable to be converted to int and multiplied by the value of another textbox(textbox1) and the product of this to be stored in another textbox(textbox2). heres the code of the entire form

    private void menustart_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'cmsDataSet.menulist' table. You can move, or remove it, as needed.



        String con = "datasource = localhost; port=3306;username=root;password=brianfdes93";
        MySqlConnection mycon = new MySqlConnection(con);

        //wine
        string qw = "select wine from cms.menulist";
        MySqlDataAdapter da = new MySqlDataAdapter(qw, mycon);
        DataTable dt1 = new DataTable();
        da.Fill(dt1);
        foreach (DataRow row in dt1.Rows)
        {
            string wine = string.Format("{0}", row.ItemArray[0]);
            comboBox1.Items.Add(wine); 
        }

        //nvstarter
        string qsnv = "select st_nonveg from cms.menulist";
        MySqlDataAdapter da1 = new MySqlDataAdapter(qsnv, mycon);
        DataTable dt2 = new DataTable();
        da1.Fill(dt2);
        foreach (DataRow row in dt2.Rows)
        {
            string nvs = string.Format("{0}", row.ItemArray[0]);
            comboBox2.Items.Add(nvs);
        }

        //vstarter
        string qsv = "select st_veg from cms.menulist";
        MySqlDataAdapter da3 = new MySqlDataAdapter(qsv, mycon);
        DataTable dt3 = new DataTable();
        da3.Fill(dt3);
        foreach (DataRow row in dt3.Rows)
        {
            string vs = string.Format("{0}", row.ItemArray[0]);
            comboBox3.Items.Add(vs);
        }

        //rice n v
        string qrnv = "select rice_nonveg from cms.menulist";
        MySqlDataAdapter da4 = new MySqlDataAdapter(qrnv, mycon);
        DataTable dtrnv = new DataTable();
        da4.Fill(dtrnv);
        foreach (DataRow row in dtrnv.Rows)
        {
            string rnv = string.Format("{0}", row.ItemArray[0]);
            comboBox4.Items.Add(rnv);
        }

        // rice v
        string qrv = "select rice_veg from cms.menulist";
        MySqlDataAdapter da4i = new MySqlDataAdapter(qrv, mycon);
        DataTable dtrv = new DataTable();
        da4i.Fill(dtrv);
        foreach (DataRow row in dtrv.Rows)
        {
            string rv = string.Format("{0}", row.ItemArray[0]);
            comboBox4i.Items.Add(rv);
        }



        //nvgravy
        string qgnv = "select g_nonveg from cms.menulist";
        MySqlDataAdapter da5 = new MySqlDataAdapter(qgnv, mycon);
        DataTable dtgnv = new DataTable();
        da5.Fill(dtgnv);
        foreach (DataRow row in dtgnv.Rows)
        {
            string gnv = string.Format("{0}", row.ItemArray[0]);
            comboBox5.Items.Add(gnv);
        }

        //vgravy
        string qgv = "select g_veg from cms.menulist";
        MySqlDataAdapter da6 = new MySqlDataAdapter(qgv, mycon);
        DataTable dtgv = new DataTable();
        da6.Fill(dtgv);
        foreach (DataRow row in dtgv.Rows)
        {
            string gv = string.Format("{0}", row.ItemArray[0]);
            comboBox6.Items.Add(gv);
        }

        //sanv
        string qsanv = "select s_nonveg from cms.menulist";
        MySqlDataAdapter da7 = new MySqlDataAdapter(qsanv, mycon);
        DataTable dtsanv = new DataTable();
        da7.Fill(dtsanv);
        foreach (DataRow row in dtsanv.Rows)
        {
            string sanv = string.Format("{0}", row.ItemArray[0]);
            comboBox7.Items.Add(sanv);
        }

        //sav
        string qsav = "select s_veg from cms.menulist";
        MySqlDataAdapter da8 = new MySqlDataAdapter(qsav, mycon);
        DataTable dtsav = new DataTable();
        da8.Fill(dtsav);
        foreach (DataRow row in dtsav.Rows)
        {
            string sav = string.Format("{0}", row.ItemArray[0]);
            comboBox8.Items.Add(sav);
        }
        //dessert
        string qd = "select dessert from cms.menulist";
        MySqlDataAdapter da9 = new MySqlDataAdapter(qd, mycon);
        DataTable dtd = new DataTable();
        da9.Fill(dtd);
        foreach (DataRow row in dtd.Rows)
        {
            string sav = string.Format("{0}", row.ItemArray[0]);
            comboBox9.Items.Add(sav);
        }



        mycon.Close();

        // to populate iod

        int nxtorder;

        String coni = "datasource = localhost; port=3306;username=root;password=brianfdes93";
        MySqlConnection myconi = new MySqlConnection(coni);
        string query = "select MAX(o_id) from cms.order";
        MySqlCommand o = new MySqlCommand(query, myconi);
        myconi.Open();

        MySqlDataReader reader = o.ExecuteReader();
        while (reader.Read())
        {
            if (reader.IsDBNull(reader.GetOrdinal("max(o_id)")))
            {
                nxtorder = 1;
                tid.Text = Convert.ToString(nxtorder);
            }
            else
            {
                nxtorder = Convert.ToInt32(reader["max(o_id)"]);
                tid.Text = Convert.ToString(nxtorder);
            }



        }





       //to fill guest txtbox




    }

    private void button1_Click(object sender, EventArgs e)
    {
        starting s = new starting();
        s.Show();
        this.Close();
    }





    private void button2_Click(object sender, EventArgs e)
    {

            tabControl1.SelectedTab = tabPage3;
            t1.Text = t11.Text;
            t2.Text = t12.Text;
            t3.Text = t13.Text;
            t4.Text = t14.Text;
            t4i.Text = t14i.Text;
            t5.Text = t15.Text;
            t6.Text = t16.Text;
            t7.Text = t17.Text;
            t8.Text = t18.Text;
            t9.Text = t19.Text;
            textBox1.Text = label49.Text;

    }

    private void comboBox1_SelectionChangeCommitted(object sender, EventArgs e)
    {

        string c1 = comboBox1.SelectedItem.ToString();
        t1.Text = t1.Text +c1+",";
    }

    private void comboBox2_SelectionChangeCommitted_1(object sender, EventArgs e)
    {
        string c2 = comboBox2.SelectedItem.ToString();
        t2.Text = t2.Text + c2 + ",";
    }

    private void comboBox3_SelectionChangeCommitted_1(object sender, EventArgs e)
    {
        string c3 = comboBox3.SelectedItem.ToString();
        t3.Text = t3.Text + c3 + ",";
    }

    private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
    {
        string c4 = comboBox4.SelectedItem.ToString();
        t4.Text = t4.Text + c4 + ",";
    }
    private void comboBox4i_SelectedIndexChanged(object sender, EventArgs e)
    {
        string c4i = comboBox4.SelectedItem.ToString();
        t4i.Text = t4i.Text + c4i + ",";
    }
    private void comboBox5_SelectedIndexChanged(object sender, EventArgs e)
    {
        string c5 = comboBox5.SelectedItem.ToString();
        t5.Text = t5.Text + c5 + ",";
    }

    private void comboBox6_SelectedIndexChanged(object sender, EventArgs e)
    {
        string c6 = comboBox6.SelectedItem.ToString();
        t6.Text = t6.Text + c6 + ",";
    }

    private void comboBox7_SelectedIndexChanged(object sender, EventArgs e)
    {
        string c7 = comboBox7.SelectedItem.ToString();
        t7.Text = t7.Text + c7 + ",";
    }

    private void comboBox8_SelectedIndexChanged(object sender, EventArgs e)
    {
        string c8 = comboBox8.SelectedItem.ToString();
        t8.Text = t8.Text + c8 + ",";
    }

    private void comboBox9_SelectionChangeCommitted_1(object sender, EventArgs e)
    {
        string c9 = comboBox9.SelectedItem.ToString();
        t9.Text = t9.Text + c9 + ",";
    }

    private void button1_Click_1(object sender, EventArgs e)
    {
        tabControl1.SelectedTab = tabPage3;
        t1.Text = t21.Text;
        t2.Text = t22.Text;
        t3.Text = t23.Text;
        t4.Text = t24.Text;
        t4i.Text = t24i.Text;
        t5.Text = t25.Text;
        t6.Text = t26.Text;
        t7.Text = t27.Text;
        t8.Text = t28.Text;
        t9.Text = t29.Text;
        textBox1.Text = label47.Text;
    }



    private void button3_Click(object sender, EventArgs e)
    {

        if (t1.Text != "" && t2.Text != "" && t3.Text != "" && t4.Text != "" && t5.Text != "" && t6.Text != "" && t7.Text != "" && t8.Text != "" && t9.Text != "")
        {
        try
        {
            String con = "datasource = localhost; port=3306;username=root;password=brianfdes93";
            MySqlConnection mycon = new MySqlConnection(con);
            string query = "insert into cms.menu(o_id,wine,st_nonveg,st_veg,rice_nonveg,rice_veg,g_nonveg,g_veg,s_veg,s_nonveg,dessert) values('" + this.tid.Text + "','" + this.t1.Text + "','" + this.t2.Text + "','" + this.t3.Text + "','" + this.t4.Text + "','" + this.t4i.Text + "','" + this.t5.Text + "','" + this.t6.Text + "','" + this.t7.Text + "','" + this.t8.Text + "','" + this.t9.Text + "')";
            MySqlCommand orderinsert = new MySqlCommand(query, mycon);
            MySqlDataReader reader;
            mycon.Open();
            reader = orderinsert.ExecuteReader();
            while (reader.Read())
            {
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }


        try
        {
            String con1 = "datasource = localhost; port=3306;username=root;password=brianfdes93";
            MySqlConnection mycon1 = new MySqlConnection(con1);
            string query1 = "insert into cms.order(price_per_plate,total_amount) values('" + this.textBox1.Text + "','" + this.textBox2.Text+ "')";
            MySqlCommand orderinsert1 = new MySqlCommand(query1, mycon1);
            MySqlDataReader reader;
            mycon1.Open();
            reader = orderinsert1.ExecuteReader();
            while (reader.Read())
            {
            }
            MessageBox.Show("MENU PREFERENCE SAVED");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }







        }
        else
        {
            MessageBox.Show("please complete the form before submitting");
        }

        payment p = new payment();
        this.Hide();
        p.Show();
    }

    private void tabPage3_Enter(object sender, EventArgs e)
    {
        String con2 = "datasource = localhost; port=3306;username=root;password=brianfdes93";
        MySqlConnection mycon2 = new MySqlConnection(con2);
        string query2 = "select guest from cms.order where o_id='" + tid.Text + "'";
        MySqlCommand orderinsert = new MySqlCommand(query2, mycon2);
        string a = "1";
        mycon2.Open();
        //  a = orderinsert.ExecuteScalar().ToString();
        MySqlDataReader dr = orderinsert.ExecuteReader();
        while (dr.Read())
        {
            a = dr.GetString(0);

        }
        string s = textBox1.Text;
        int a1 = Convert.ToInt16(a);
        int a2 = Convert.ToInt32(string.IsNullOrEmpty(textBox1.Text));
        textBox2.Text = Convert.ToString(a1 * a2);
        mycon2.Close();
    }   
}

}

i get the following error

input string was not in a correct format

i get the above error for a2.

how do i solve this ?? is there an alternate way?

textbox 1 will be auto populated with a number when the form loads — Brian, Apr 11 '14 at 11:28

score 0 · Answer 1 · edited Apr 11 '14 at 11:16

0

Convert.ToInt16() is used to convert the given value into short(int16) not int(int32). Although they're integers , but short value should be assign to short(int16) , not int(int32).you need to use Convert.ToInt32() method for converting the value into integer.

Try This:

int a2 = Convert.ToInt32(textBox1.Text);

edited Apr 11 '14 at 11:16

Poomrokc The 3years

1,099
2
10
23

answered Apr 11 '14 at 11:09

Sudhakar Tillapudi

25,935
5
37
67

Short == Int16, Int == Int32, Long == Int64 – FabianCook Apr 11 '14 at 11:13
http://stackoverflow.com/questions/9696660/what-is-the-difference-between-int-int16-int32-and-int64 – FabianCook Apr 11 '14 at 11:13
All of them are integers. – Poomrokc The 3years Apr 11 '14 at 11:16
tried changing it to int32 yet it is giving me the same error – Brian Apr 11 '14 at 11:27
@Brian: i'm sure that your textbox contains some unwanted characters which stops the conversion, could you check what exactly the textbox contains? – Sudhakar Tillapudi Apr 11 '14 at 11:31

score 0 · Answer 2 · answered Apr 11 '14 at 11:26

0

This error is thrown if textBox1.Text is empty. Use String.IsNullOrEmpty(textBox1.Text) to check if textBox1.Text is null or empty before you convert it to int16 or int 32. And if it's null then the problem is with the textbox itself.

answered Apr 11 '14 at 11:26

Charbel Boutros

11
1

score 0 · Answer 3 · answered Apr 11 '14 at 13:15

Replace :

int a2 = Convert.ToInt32(string.IsNullOrEmpty(textBox1.Text));

with :

try
{
    int a2 = Int.Parse(textBox1.Text);
}
catch (Exception ex)
{
    Console.WriteLine("Failed to parse to int! " + ex.Message);
}

string.IsNullOrEmpty always returns a bool.

how to store data from mysql query in a variable

3 Answers3