CODEWITHSUNDEEP
c#mysqldatabase
adkffsgfds
adkffsgfds

Reputation: 9

Search Data From Mysql and Set it into Textbox c#

I'm trying to make a program that searches data from MySQL database and displays the values in the text box using Text Change event.

So far here is my code:

private void textBox1_TextChanged(object sender, EventArgs e)
{       
    string sqlstring = "database = db_phonebook; user = root; password = ''; server = 'localhost'; SSL Mode = None";
    MySqlConnection mysqlcon = new MySqlConnection(sqlstring);
    MySqlCommand mysqlcom;
    MySqlDataReader mdr;
    
    mysqlcon.Open();
    
    string selectquery = "SELECT * FROM 'tbl_phonebook' WHERE CID =" + cid.Text;
    mysqlcom = new MySqlCommand(selectquery, mysqlcon);
    
    mdr = mysqlcom.ExecuteReader();
    
    if (mdr.Read())
    {
        name.Text = mdr.GetString("Name");
        address.Text = mdr.GetString("Address");
        contact.Text = mdr.GetString("Contact_Number");
        email.Text = mdr.GetString("Email_Address");
    }
    else
    {
        MessageBox.Show("Record Not Found!");
    }
    
    mysqlcon.Close();        
}

But the error says MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''tbl_phonebook' WHERE CID =' at line 1'

any idea how do I fix this?

Upvotes: 0

Views: 833

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

I'm sorry to say there is a LOT that is wrong here, some of it much more important even than the bug you know about. Try this instead, and learn from the patterns shown:

private void textBox1_TextChanged(object sender, EventArgs e)
{       
    string constring = "database = db_phonebook; user = root; password = ''; server = 'localhost'; SSL Mode = None";
    //backticks instead of single quotes around the table/object name
    string sql = "SELECT * FROM `tbl_phonebook` WHERE CID = @CID";
    //using blocks make sure the connection is closed and disposed, 
    // EVEN IF AN EXCEPTION IS THROWN
    // Original code would have left the connection hanging open.
    using (var con = new MySqlConnection(constring))
    using(var cmd = new MySqlCommand(sql, con))
    {
        //parameterized query instead of string concatenation.
        // THIS IS A **HUGE** DEAL!!
        // It's important enough you should STOP and go FIX
        // any code you have anywhere that already uses concatenation.
        cmd.Parameters.AddWithValue("@CID", cid.Text);
        con.Open();
        using (var mdr = cmd.ExecuteReader())
        {
    
            if (mdr.Read())
            {
                name.Text = mdr.GetString("Name");
                address.Text = mdr.GetString("Address");
                contact.Text = mdr.GetString("Contact_Number");
                email.Text = mdr.GetString("Email_Address");
            }
            else
            {
                //Do NOT show an interrupting textbox that steals focus from
                // an actively typing user based on a textchanged event!

                name.Text = "";
                address.Text = "";
                contact.Text = "";
                email.Text = "";
            }
        }
    }      
}

Upvotes: 1

Related Questions