code to retrieve data from MyQSL Database into text boxes

[kheddy]

I'm new to C#.I have created a MySQL database. My problem now is to get the data from the database tables into text boxes and or combo boxes in window forms(visual studio 2010),allow for editing and send the edited values back to the database.

if I select the account_no(primary_key field)...the respective details of the person i.e.firstname,lastname,,age,location,gender...should be posted to the respective text/combo boxes...

I'll appreciate any help/assistance provided

[akshintlakalyan]

I'm new to C#.I have created a MySQL database. My problem now is to get the data from the database tables into text boxes and or combo boxes in window forms(visual studio 2010),allow for editing and send the edited values back to the database.

if I select the account_no(primary_key field)...the respective details of the person i.e.firstname,lastname,,age,location,gender...should be posted to the respective text/combo boxes...

I'll appreciate any help/assistance provided

By, using an appropriate Sql Command u retrieve the Values from the Database using Data Adapter, then store it to a DataSet, now take the every row details of the Dataset to relavant arrays like Firstname[] as string, similarly lastname[], age[],location[],gender[] k. Now is ur main work of placing the values to the textboxes or combobox, if u use textboxes then u can write textbox1.text= Firstname(i) by using a loop or if u use Combobox u can directly write combobox1.Items.Add(Arraylist) (where arraylist is ur Column arrays).

Hope it will help u.

Regards
Kalyan

[kheddy]

that gives me the idea of how to go about it.Let me research more...but if I don't ask too much, could you sketch for me the code? it will guide me in my research. I'll be grateful...please!!!

[muzikhera]

Hope this line of code gives you idea.

I made this as simple as i can.

private void btnClickMe_Click(object sender, EventArgs e)
        {
            SqlConnection Connect = new SqlConnection();

            Connect.ConnectionString = @"Data Source=BURBANK\SQLEXPRESS;" + "Initial Catalog= ERPdb;" + "Persist Security Info=True;" + "User ID=antonette;" + "Password=ilovemis";
            Connect.Open();

            SqlCommand cmd = new SqlCommand("//YOUR SELECT STATEMENT HERE", Connect);

            string fisrt = txtempID.Text;

            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                txtempID.Text = Convert.ToString(dr[1]);
                dr.Close();
                
                SqlDataReader dr1 = cmdLogin.ExecuteReader();
            }
            else
                MessageBox.Show("//ANY STATEMENT");
            {
                dr.Close();
            }

            try
            {
            }
            catch (SqlException ex)
            {
                MessageBox.Show("There is an Error" + ex);
            }
            finally
            {
                Connect.Close();
            }
        }

[mono_jit23]

You must use MySql instead of Sql . ie

MySqlConnection instead of SqlConnection

apart from this everything is similar between sql server and mysql

[kheddy]

that's helpful but still I'm a little bit confused. I specify my connection_strings in a file called "DBConnect.cs", and for this windows form called "EditStudent.cs",whose counterpart "EditStudent.Design" has the text,combo boxes to display the data. How do I achieve this now?which code to put in which place?Help

[akshintlakalyan]

U add the DBConnect.cs class to the Project where u r running EditStudent, and all the code parts u included in DBConnect.cs class can be accessible, through which u can connect to the database. If the Class .cs is already there in the project then u add the namespace of the project in the EditStudent.cs through which u can achieve what u want.

[kheddy]

check on this example I did with insert statement and the advice me accordingly:

this code is for entering a new farmer.it's in the DBConnect.cs that has the database connection string

    //Insert statement
    public bool newFarmer(string accountNum, string fname, string lname, string othnames, string loca, string SizeOfFarm, string owner, string regfee, string DateOFReg)
    {
        string query = "INSERT INTO farmer (account_no,firstname,lastname,othernames,location,size,ownership,registration_fee,date) VALUES('" + accountNum + "','" + fname + "','" + lname + "','" + othnames + "','" + loca + "','" + SizeOfFarm + "','" + owner + "','" + regfee + "','" + DateOFReg + "')";


        //open connection
        if (this.OpenConnection() == true)
        {
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
            if (cmd.ExecuteNonQuery() > 0)
            {
                //close connection
                this.CloseConnection();
                return true;
            }
            else
                return false;

        }
        else
            return false;

    }

and then in the form that I enter the farmer details I have this:

    private void buttonSave_Click_1(object sender, EventArgs e)
    {
        DBConnection db = new DBConnection();
        accNum = textBoxAccNum.Text.Trim();
        fname = textBFirstName.Text.Trim();
        lname = textLastName.Text.Trim();
        oname = txtBxOtherName.Text.Trim();
        loc = textBoxLocation.Text.Trim();
        sizeOFfarm = textBoSize.Text.Trim();
        own = comboBox1.SelectedItem.ToString();
        regFee = txtBxRegFee.Text.Trim();
        DateOfReg = datTimPickRegDate.Value.ToShortDateString();
        String[] dateArr = DateOfReg.Split('/');
        Array.Reverse(dateArr);
        DateOfReg = String.Join("-", dateArr);
        if (db.newFarmer(textBoxAccNum.Text, textBFirstName.Text, textLastName.Text, txtBxOtherName.Text, textBoxLocation.Text, textBoSize.Text, comboBox1.SelectedItem.ToString(), txtBxRegFee.Text, DateOfReg))
        {
            MessageBox.Show("Farmer Added Successfully.");
            reset();
        }
        else
            MessageBox.Show("Could Not Add Farmer");
    }

how do you now go about with one that takes values from database,allows you to edit and send the values back to the database?

[muzikhera]

Simple thing to do is to connect to your Db then use drag and drop method from Data Source Window to your form then it will have automatic navigator which can help you to navigate, add, delete, save, etc.

Hope this can help.

[kheddy]

@muzikhera.that's easy but it doesn't give me the freedom to customize the look of my form

[muzikhera]

Ofcourse not if your pertaining to LOOK/LAYOUT of your form ofcourse you can still customize it according to your will