How to read data from the Mysql database of an application in c#?

Question

I have a table with 2 columns in phpmyadmin,I can connect and read one column but not the other, from the following error

An unhandled Exception of type 'Mysql.Data.Mysqlclient.Mysqlexception' occurred in Mysql.Data.dll
Additional information: Unknown column '(Name I search)' in 'Where clause'

 MySqlConnection conectar = new MySqlConnection(conex);
            MySqlCommand command = conectar.CreateCommand();
            command.CommandText = "SELECT id from hackers where Nick= "+textBox1.Text;
        try{    
        conectar.Open();
            MessageBox.Show("Conexão estabelecida!!");
        }
        catch (Exception ex)
        { MessageBox.Show(ex.Message); }
        MySqlDataReader reader = command.ExecuteReader();
        while(reader.Read())
        { label1.Text = reader["nick"].ToString(); }
        conectar.Close();
    }

When I use SELECT NICK FROM HACKERS WHERE id=x works but otherwise not

Answer 1

Two things.

First, the error occurs by syntax. If you search for strings, they have to be surrounded by single quotes.

That’s wrong:

... WHERE nick = x

'Cause the bank thinks you want to compare the columns nick and x (and x there is no).

That’s right:

... WHERE nick = 'x'

The worst, though, is that you mount your SQL command via concatenation. If it’s just an application to learn, it won’t hurt you now. But if you release a system to the Internet that searches that way, it’s vulnerable to attack. Then I ask that after solving the problem in the syntax of the command, do a search (can be here in Stack Overflow same) about injection of SQL. I leave it to you to research and study this in your own time.

Answer 2

See the example below...

public DataTable PesquisarPorNome(string NomePesquisado)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = myConnString;
        try
        {
            var SQL = string.Format("SELECT * FROM tbEspecialidades WHERE NomeEspecialidade  LIKE @NomePesquisado");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = SQL;
            cmd.Parameters.Add("@NomePesquisado", SqlDbType.NVarChar).Value = "%" + NomePesquisado + "%";
            SqlDataAdapter sqlA = new SqlDataAdapter();
            DataTable tabela = new DataTable();

            sqlA.SelectCommand = cmd;

            conn.Open();
            sqlA.Fill(tabela);

            return tabela;
        }
        finally
        {
            conn.Close();
        }
    }

Obs: the connection made here is to the bank sql server, simply changes to the mysql.

Answer 3

change:

command.CommandText = "SELECT id from hackers where Nick= "+textBox1.Text;

for

command.CommandText = "SELECT id from hackers where Nick like "+textBox1.Text;

Although I recommend using Entity Framework, it greatly facilitates access to the database because it converts your tables / records into classes / objects, thus facilitating access and manipulation of them.

Answer 4

If you want to fetch more than one information the sql statement should be

sqlCommand selectTable = new SqlCommand("SELECT * FROM nomeTabela WHERE nick = '@nick';", conexao);

and to load the data use the loop as follows.

while(reader.Read())
    { 
       label1.Text = reader["nick"].ToString();
       label2.Text = reader["2ªColuna"].ToString(); 
    }

Answer 5

Try:

 command.CommandText = string.Format("SELECT id from hackers where Nick='{0}'",textBox1.Text);