none
How to insert data into mysql database from a combobox? RRS feed

  • Question

  • string constring = "datasource=localhost;port=3306;username=root;password="root";
    string Query = " insert into vehicle_automation.staff_details(name,code_given,Address,contact_details,department,emp_date) values('" + this.nametxt.Text + "','" + this.cdtext.Text + "','" + this.Addtext.Text + "','" + this.cncttext.Text + "'," + comboBox1.SelectedValue + ",'" + this.dtpicker.Text +"';"
                MySqlConnection conDatabase = new MySqlConnection(constring);
                
                MySqlCommand cmdDatabase = new MySqlCommand(Query, conDatabase);
               
                MySqlDataReader myReader;
    
                try
                {
                    conDatabase.Open();
                    myReader = cmdDatabase.ExecuteReader();
                    MessageBox.Show("The Data is Saved");
                    while(myReader.Read())
                    {}
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }        
    Above is my code for inserting a row to the staff_details table. How can i insert the selected data from the combobox to the database. When i tried to save the data with this query, my department section is empty. I have tried many formats for the combobox still showing the empty data in department column. Please Help me 
    Friday, December 5, 2014 3:36 PM

Answers

All replies

  • You should use the ExecuteNonQuery method, a DataReader is only used to read data:

         
     string constring = "datasource=localhost;port=3306;username=root;password="root";
    string Query = " insert into vehicle_automation.staff_details(name,code_given,Address,contact_details,department,emp_date) values('" + this.nametxt.Text + "','" + this.cdtext.Text + "','" + this.Addtext.Text + "','" + this.cncttext.Text + "'," + comboBox1.SelectedValue + ",'" + this.dtpicker.Text +"';"
                MySqlConnection conDatabase = new MySqlConnection(constring);
                
                MySqlCommand cmdDatabase = new MySqlCommand(Query, conDatabase);
    
                try
                {
                    conDatabase.Open();
                    cmdDatabase.ExecuteNonQuery();
                    MessageBox.Show("The Data is Saved");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }     
    

    Please remember to mark helpful posts as answer and/or helpful.
    Friday, December 5, 2014 4:22 PM
  • Most time, we insert data to database by "SqlCommand.ExecuteNonQuery Method"

    I suggest you read ADO.NET to know more about how to design data accessing program.

    By the way, your code will cause SQL injection, you should use parameters to pass values to SQL statements or stored procedures.

    about SQL Injection and commands and paramters , please refer to :
    Commands and Parameters
    Configuring Parameters and Parameter Data Types
    How To: Protect From SQL Injection in ASP.NET


    在現實生活中,你和誰在一起的確很重要,甚至能改變你的成長軌跡,決定你的人生成敗。 和什麼樣的人在一起,就會有什麼樣的人生。 和勤奮的人在一起,你不會懶惰; 和積極的人在一起,你不會消沈; 與智者同行,你會不同凡響; 與高人為伍,你能登上巔峰。

    Friday, December 5, 2014 4:34 PM
  • Thanks to the reply.  there is no change in the department column still its empty
    Friday, December 5, 2014 5:18 PM
  • Thanks for the reply i changed my queries i used add with value to add the values and used executenonquery();

    private void button1_Click(object sender, EventArgs e)
            {
                string constring = "datasource=localhost;port=3306;username=root;password=Ammoos123";
                string Query = " insert into vehicle_automation.staff_details(name,code_given,Address,contact_details,department,emp_date) values(@name, @code,@address,@contact,@dept,@empdata);";
                MySqlConnection conDatabase = new MySqlConnection(constring);
                MySqlCommand cmdDatabase = new MySqlCommand(Query, conDatabase);
                cmdDatabase.Parameters.AddWithValue("@name", textBox2.Text);
                cmdDatabase.Parameters.AddWithValue("@code", textBox1.Text);
                cmdDatabase.Parameters.AddWithValue("@address", richTextBox1.Text);
                cmdDatabase.Parameters.AddWithValue("@contact", textBox3.Text);
                cmdDatabase.Parameters.AddWithValue("@dept", this.comboBox1.SelectedItem);
                cmdDatabase.Parameters.AddWithValue("@empdata", dateTimePicker1.Text);
    
                try
                {
                    conDatabase.Open();
                    int affectedRows = cmdDatabase.ExecuteNonQuery();
                    if (affectedRows > 0)
                    {
                        MessageBox.Show("Insertion Successfull");
                    }
                    else
                    {
                        MessageBox.Show("Insertion Failed. Check the values inserted");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }        
            }

    This was the query i used and the program is working

    Saturday, December 6, 2014 6:29 AM