how to retrieve data from mysql database and display it into textbox in c#

Xonel

Hi, I have this code below that is not working like i want it to and has no errors when i debug. I want it to display a member values in respective textboxes that are in a table called 'addpeople' by a click of a button AFTER i key in the member ID of a member. Text boxes are like; Title member ID First name Last Name Address 1 Address 2 .... ... ... Email.. Here is the code:

private void btnView_Click(object sender, EventArgs e)
{

            string input = cmbMemberId.Text;
            string conn = "server=localhost;user=root;password='';database=m\_chama;";

            MySqlConnection myconn = new MySqlConnection(conn);
            
            string sql = "SELECT \* FROM addmember WHERE Member\_ID = '" + input + "';";

            
            MySqlDataAdapter da = new MySqlDataAdapter(sql, myconn);
            DataTable dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count == 0)
            {
                
                MessageBox.Show("No data found.", "Not Exists");

            }
            else
            {
                foreach (DataRow dr in dt.Rows)
            {
                if (dr\[0\] + "" == input)
                {
                    txtFirstName.Text = dr\[0\] + "";
                    break;
                }
            }
              
           }

        }

Abhinav S

Try debugging and stepping through your code and figure out if your query is returning correct data. If you are able to go into the else part of your code, check if there are any valid values inside dr[0]. After that you need to populate your textboxes, so you will need code like

foreach (DataRow dr in dt.Rows)
{
if (dr[0] + "" == input)
{
txtFirstName.Text = dr[0] + "";
break;
}
txtLastName = dr[1];
txtTitle = dr[2];
//and so on

where the indexes need to match the appropriate values.

OriginalGriff

There are two important things to do here: 1) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead:

            string sql = "SELECT \* FROM addmember WHERE Member\_ID = @ID;";
            MySqlDataAdapter da = new MySqlDataAdapter(sql, myconn);
            da.SelectCommand.Parameters.AddWithValue("@ID", input);

Don't use "*" as the column list in an sql command - particularly if you are going to use numeric indexes. Not only do they make your code liable to fail if the external database is changed, but the order in which SQL returns columns is not defined - when you specify "*" as the columns list SQL is at liberty to return them in any order it desires. Normally it returns them in definition order - but it doesn't have to, and there is no guarantee that future versions will do so. Name your columns in the SELECT (preferred, as it reduces the data returned to just the columns you want), and / or use names for the DataRow index. Implementing these may solve your problem, but you also should not add an empty sting onto the dataRow - it looks silly and is unnecessary. cast it to a string instead:

foreach (DataRow dr in dt.Rows)
{
string iD = (string) dr["iD"];
if ((string) dr["iD"] == input)
{
txtFirstName.Text = (string) dr["FirstName"];
break;
}
}

Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water

Xonel

Thank you guyz, You gave me ideas and it worked. :) This is the code that worked...

private void btnView_Click(object sender, EventArgs e)
{
string input = cmbMemberId.Text;
string conn = "server=[servername];user=[username];password=[userpassword];database=[databasename];";

        MySqlConnection myconn = new MySqlConnection(conn);
        string sql = "SELECT Member\_ID, First\_Name, ...., ......, ....., ....., ...., EMAIL FROM addmember WHERE Member\_ID = @ID;";

        MySqlDataAdapter da = new MySqlDataAdapter(sql, myconn);
        da.SelectCommand.Parameters.AddWithValue("@ID", input);
        DataTable dt = new DataTable();
        da.Fill(dt);

        if (dt.Rows.Count == 0)
        {
            MessageBox.Show("No data found.", "Not Exists");

        }
        else
        {
            foreach (DataRow dr in dt.Rows)
          
                {
                    if (dr\[0\] + "" == input)
                    {
                        cmbMemberId.Text = dr\[0\] + "";
                        txtFirstName.Text = dr\[1\].ToString();
                        	.....
			.....
			.....
			.....
			....
			....
                        txtEmail.Text = dr\[8\].ToString();

                        break;
                    }
                
                }

            }
    }

Thank you soo much.

OriginalGriff

You're welcome!

Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water

PIEBALDconsult

What the frack are you doing dr[0] + "" for? :confused: It's pointless. I suspect you just want to cast: if ( **(string)** dr[0] == input) and txtFirstName.Text = **(string)** dr[0] ;